How to insert page breaks using ExcelWriter

Problem

ExcelWriter supports the insertion of horizontal and vertical page breaks within a Worksheet using the ExcelApplication class/object. This post covers how to insert page breaks in a worksheet.

Solution

C# Example


//--- The following namespace has been imported:
//--- SoftArtisans.OfficeWriter.ExcelWriter


//--- Instantiate ExcelApplication and create a new Workbook
ExcelApplication xla = new ExcelApplication();
Workbook wb = xla.Create();
Worksheet sheet1 = wb.Worksheets[0];


//--- Insert horizontal page break below cell A15
sheet1.InsertHorizontalPageBreak(sheet1.Cells["A15"]);


//--- Insert vertical page break to the right of cell H1
sheet1.InsertVerticalPageBreak(sheet1.Cells["H1"]);


//--- Save Workbook
xla.Save(wb, Response, "out.xls", false);

Updating the Table of Contents in a WordWriter generated document

Problem

In Microsoft Word, a table of contents is not automatically updated when a document is opened. If a table of contents (TOC) is created and the document is modified afterwards (either in Word on the client, or using WordWriter on the server) the TOC will need to be updated in order to accurately reflect the changes. Otherwise, you may experience some of the following problems:

  • If your TOC points to MergeFields in your template, even though the mergefield will be correctly populated by WordWriter on the server, the TOC entry will display a copy of the original MergeField.
  • Page numbers may be inaccurate.
  • If your headers are generated using SetRepeatBlock, the TOC will show only those which which existed in the template at design-time.

The TOC can be updated manually by the end-user (by right-clicking on the TOC and choosing “update field”). However, a more user-friendly approach is to include VBA in your template which will cause the TOC to be updated automatically when the new document is opened on the client.

This article explains how to build a table of contents, and how to create a VBA macro which will refresh the TOC client-side.

Solution

Creating a table of contents in your template

In Microsoft Word, a table of contents is created in the following manner:

1. Insert text in your document for the headings 2. Apply one of the heading styles to the text using “Format>Styles and Formats” from the menu, or the Styles dropdown in the formatting toolbar:

3. Add a new table of contents

Word 2010/2007: On the Insert Tab, go to Quick Parts > Field > TOC. Click on ‘Table of Contents’.

Word 2003: Through the menu, choose Insert > Reference > Index and Tables > Table of Contents tab.

4. Once in the Table of Contents window, choose the format of your table of contents (for example, how many level of headers you want to include, if you have nested headers). Word will automatically pick up all text with heading styles and put it into the TOC.

Below is an example of a template with a TOC which references a heading in a repeat block. The <> in the table of contents is not a merge field. It is just picking up the text from the actual merge field in the document. When the WordWriter-generated file is opened on the client, the TOC will still look this way if it is not updated manually or through a macro.

Using macros to update the TOC on the client

If you add the following macro to the Document_Open event of your template Word document, the table of contents will automatically update itself when the user opens the newly-generated file. If you have multiple TOCs, it will update all of them:

Private Sub Document_Open()
For i = 1 To ThisDocument.TablesOfContents.Count
ThisDocument.TablesOfContents.Item(i).Update
Next
End Sub

After the macro runs, the TOC will reflect all of the changes in the populated document:

Note: If you do not wish your users to get a macro security prompt every time they open the document, one option to consider is digitally signing your macros. See: Digitally sign a macro project.

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.

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.

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.

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 

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.

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.

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.

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:

Blogged