Category Archives: Knowledge Base

Knowledge Base, WordWriter

How to add lists into a repeat block with WordTemplate

Problem

Creating lists, ordered or bulleted, using the template object is possible using repeat blocks. However, sometimes you may wish to include a list inside another repeat block, but you run into the following hurdles:

  • WordTemplate does not support nested repeat blocks
  • WordApplication can dynamically insert lists, but it is not available in WordWriter Standard Edition
  • WordTemplate nesting and grouping can also handle nested lists, but is only available in Enterprise Edition.

Solution

Using the WordTemplate object, one cannot populate a fully functional list within a repeat block. However, it is possible to emulate the look of such lists. The general idea is to concatenate the list items into one formatted string that Word displays as a list. These strings can be stored in any database. This solution works for both binary (.doc) and OOXML (.docx) formats in office 2007.

For bulleted lists, use the following format:
“• First item\r\n• Second item”

For numbered lists, use the following format:
“1) First item\r\n2) Second item”
Or
“1. First item\r\n2. Second item”

Note that the generic string format that will be displayed as a list in Word uses the two characters “\r\n” as the separator between the items.

ExcelWriter, Knowledge Base

How to preserve numerical strings in ExcelTemplate

Problem

When importing data using the ExcelTemplate class, you can choose to preserve strings or to have ExcelTemplate attempt to convert them to a number. Your choice depends upon how you import the data.

Solution

Excel stores values internally either as strings or numbers. The data you import can be in a variety of formats, including numbers, strings, dates, booleans, etc. ExcelTemplate uses the data type of your data to determine how it should be inserted into the Excel template. However, by default, ExcelTemplate also attempts to convert strings to numbers where appropriate. This article will explain what options are available for controlling this conversion.

Special case: Please note that preserving numbers with leading zeros as strings does not always result in the desired behavior. Special consideration should be taken when importing numbers with leading zeros. For information about the steps that should be taken to best do this, please refer to this post: How to preserve leading zeros with ExcelWriter.

Conversion features

There are two features which control whether the automatic conversion of numeric strings takes place:

1. PreserveStrings property

This property is set to false by default, but setting it to true will disable any automatic conversion of strings to numbers from occurring. Be sure to set this property before calling Process.

For example, if you wanted to disable the conversion of any numbers to strings when importing data, you would set the PreserveStrings property to true:

 xlt.PreserveStrings = true;

2. Convert and preserve data marker modifiers

The convert and preserve data markers modifiers allow finer control of which values are converted and which are not. Depending on how the PreserveStrings property is set, you can use these modifiers in your template to override the global behavior on certain columns.

For example, if PreserveStrings is set to its default value of false, you could set a specific column to not be converted by putting the Preserve modifier on that column’s data marker.

 %%=MyData.ColumnOfStrings(Preserve)

Recommended practices

By default, ExcelTemplate will attempt to convert strings to numbers wherever it can. This is desirable when your data is not already stored in objects of the appropriate data type. We generally recommend setting the PreserveStrings property to true, so that this automatic conversion will not occur. It is generally preferrable to use the appropriate object/data types for your data to eliminate the need for any conversion by ExcelWriter. This provides the most predictable behavior when importing data.

Knowledge Base, WordWriter

Reading form values with WordApplication

Problem

The WordApplication API can represent several Word elements that can appear in documents. These are outlined in Word file representation in WordApplication. These elements do not include form components, such as check boxes or text fields.

However, WordApplication can detect form elements on a basic level and some components can be parsed minimally.

Solution

WordApplication does not support manipulating form elements, but it is possible to get a handle on form fields using Document.GetElements(Element.Type.Field). This will return all the field elements in the document, even if they are not Mergefields or Hyperlinks.

GetElements() returns a collection of generic Element objects. Element is the parent class for all other objects represented by WordApplication.

The contents of an Element can be read in Element.Text, which is a text of the content contained in the Element.

For a few form fields, such as text fields, the value of the field will appear in Element.Text. Others, such as check boxes, will show a string form of the object (e.g. “FORMCHECKBOX”).

This is not officially supported in the WordApplication API, so we cannot guarantee that the form field values can be parsed by WordWriter.

ExcelWriter, Knowledge Base

How to hide a column using ExcelWriter

Problem

ExcelApplication provides programmatic control over Excel files, including the ability to hide worksheet columns. This post covers how to hide a column using the ExcelApplication API.

Solution

To set a column in a worksheet to be hidden, use Worksheet.GetColumnProperties to return a ColumnProperties object and set ColumnProperties.Hidden to true.

For example:

Worksheet.GetcolumnProperties(0).Hidden = true; //Hides column A in Worksheet

ExcelWriter, Knowledge Base

How to use PivotTables with ExcelTemplate

Solution

If you are interested in using pivot tables in your ExcelTemplate reports, please refer to our documentation on Templates and Pivot Tables.

NOTE: Only use the above guide if you are using ExcelWriter 7.6 or later. There were several issues with using data markers with pivot tables that were resolved in ExcelWriter 7.6.

If you are using ExcelWriter 7.5 or later, please refer to Templates and Pivot Tables in Older Versions instead.

Knowledge Base, WordWriter

WordWriter support for text boxes

Problem

You have a word Document that contains texboxes. You want to know if WordWriter supports modifying or creating textboxes.

Solution

WordTemplate will populate any merge fields that are placed inside of a text box. This is largely because WordTemplate does not parse the full document and ignores the locations of merge fields.

If you need to search for and replace text that appears in text boxes, you can do so with the WordTemplate object. More details are available in this post.

Unfortunately, WordApplication does not support creating or modifying text boxes. The full list of elements recognized by WordApplication is outlined in this documentation article.

ExcelWriter, Knowledge Base, Reporting Services

How to display parameters in a Reporting Services report

Problem

Many customers want to display the parameter values that were selected when a particular Reporting Services report was run. This is possible through Reporting Services formulas.

Solution

To display the parameters in a report:

  1. Open the report in Excel or Word with the OfficeWriter Designer
  2. Go to ‘Insert Formula’ > Build New Formula from the OfficeWriter Designer toolbar
  3. There will be a drop-down field that contains the report’s parameters. You can create a formula that includes the report parameters.
  4. This process will create a formula with the report parameter. To insert the formula you just created into the report, go back to Insert Formula on the OfficeWriter Designer toolbar.
  5. Select the formula you just created. This will insert a data marker (for Excel) or a merge field (for Word) into the report that will display the parameter value when the report runs.

ExcelWriter, Knowledge Base

How to remove data markers from ExcelTemplate PivotTable filters

Problem

When using PivotTables in ExcelTemplate in OOXML files (XLSX, XLSM), the drop-down filters show data marker values:

Solution

The default PivotTable behavior is to cache the filter items, even when they are no longer in the data source.

To make sure that this cached information is cleared when the PivotTable refreshes:

  1. In your template file, right-click on the PivotTable and select PivotTable options
  2. Go to the Data tab.
  3. Under Number of items to retain per field select None from the drop-down.

Make sure that Refresh data when opening file is also checked so that the PivotTable refreshes with the imported data.

When you populate the file with data, the data markers should be gone from the drop-down:

ExcelWriter, Knowledge Base

Preserving range references with ExcelTemplate

Problem

When populating a data marker, ExcelWriter inserts as many rows into the worksheet as there are rows in the data source. If a range reference includes a cell containing a data marker, the range reference will be modified to include all of the newly inserted rows, regardless of whether relative or absolute referencing is used. This behavior is desirable in many situations, such as using ExcelTemplate to populate the source data range for a chart.

However, in some cases, it is desirable that a range reference stays constant, such as when it is used in a formula.

Solution

You can use Excel’s INDIRECT worksheet function to ensure that a range reference is not modified even as new rows are inserted into the range.

For example, you have the following formula: =SUM(A1:A10)

Let’s say one of the cells in the range, A5, contains a data marker. If 10 new rows are inserted into the range when the data marker is populated, the formula will be updated to =SUM(A1:A19)

This behavior occurs even if you use absolute referencing. i.e., the formula =SUM($A$1:$A$10) will be converted to =SUM($A$1:$A$19).

The INDIRECT function converts a text string into a range reference. Because the range reference is created from a text string, it is not modified by Excel when rows are inserted. For the example above, you can preserve the range reference as A1:A10 with the following formula: =SUM(INDIRECT(“A1:A10”))

Related Links

Microsoft KB article for the INDIRECT function

Knowledge Base, WordWriter

How to remove WordWriter from the Global Assembly Cache

Problem

In OfficeWriter version 4.6.0 and below, the installer placed WordWriter in the Global Assembly Cache (GAC). This was to enable you to use WordWriter in a COM application, via a COM-Callable Wrapper. However, if you are not using WordWriter in COM or if you prefer not to have assemblies in the GAC, you may wish to remove WordWriter from the GAC.

Solution

General WordWriter uninstallation instructions can be found in our documentation.

To manage assemblies in the GAC, you can use the gacutil tool. The availability of this tool depends on the version of the .NET framework. You can find gacutil at the following locations:

  • For .NET 1.1: c:\Windows\Microsoft.NET\Framework\v1.1.xxxx
  • For .NET 2.0 through 3.5: c:\Program Files\Microsoft SDKs\Windows\v7.0A\Bin
  • For .NET 4.0: c:\Program Files\Microsoft SDKs\Windows\v7.0A\Bin\NETFX 4.0 Tools

On an x64 system, substitute Program Files (x86) for Program Files. The version of gacutil matches the version of the .NET framework. To see the version of gacutil, simply enter gacutil with no arguments at the command line.

If you do not already have gacutil, you may need to install required packages. For .NET 1.1, gacutil is bundled with the framework. For .NET 2.0 through 3.5, you should install the Microsoft Windows SDK for .NET Framework 3.5, of which there are different downloads depending on your operating system. For .NET 4.0, you should additionally install Visual Studio 2010. If you do not want to install the SDK or Visual Studio, such as in production, you can install it on a different system and simply copy gacutil.exe.

WordWriter 3.x uses .NET 1.1 while WordWriter 4.x and higher uses .NET 2.0. You should use a version of gacutil at least as high as the .NET version required by WordWriter. Specifically, use gacutil version 1.1 or higher for WordWriter 3.x and gacutil version 2.0 or higher for WordWriter 4.x.

To uninstall WordWriter from the GAC:

1. Determine the full strong name of the WordWriter assembly:

 gacutil /lr SoftArtisans.OfficeWriter.WordWriter

There may be multiple versions of WordWriter in the GAC. Note the full name of the assembly you want to remove; for example, SoftArtisans.OfficeWriter.WordWriter, Version=4.5.1.1641, Culture=neutral, PublicKeyToken=f593502af6ee46ae.

2. If you are using WordWriter in a web application, reset IIS to make sure the assembly is not being used. Remove the assembly from the GAC (note the use of quotes around the assembly’s full name):

 gacutil /uf "SoftArtisans.OfficeWriter.WordWriter, Version=4.5.1.1641, Culture=neutral, PublicKeyToken=f593502af6ee46ae"

However, you may find that gacutil does not succeed due to the following error: “Assembly could not be uninstalled because it is required by Windows Installer.” In this case, follow the steps described in this Microsoft knowledge base article. In particular, open the Registry Editor, navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Installer\Assemblies\Global and remove the reference to the WordWriter assembly above. Doing so means that the Windows installer no longer attempts to remove WordWriter from the GAC during automatic uninstallation, but otherwise the uninstallation process is unaffected.

If your web application references a WordWriter assembly that has been removed from the GAC, be sure to update the reference to point to another copy of the WordWriter assembly.