All posts by Alison

Full time OfficeWriter Product Owner, part-time pivot table enthusiast.
ExcelWriter, Knowledge Base

How to change strings to formatted numerical values

Problem

In the creation of an Excel workbook, sometimes a column contains numerical values stored as strings. For example, a percent column can display percents as strings. This poses a problem if you want to use these values for equations, or if you want to clean up the display of the file, as Excel will mark these strings with a green corner indicating that they can be displayed as numerals, along with a warning message: “The number in this cell is stored as text or preceded by an apostrophe.”

Solution

ExcelApplication allows you to change the NumberFormat of a column so that it will automatically format numerals. This alone is not enough to change the strings into formatted numerals, though, as you must trim any characters off the strings and convert them to doubles to be handled correctly by Excel.

Hence, converting the values for an entire column requires three steps:

  1. Gather the values from the column into a collection and convert them to numeric values.
  2. Apply the desired Number Format to the styles of column.
  3. Cycle through the collection and add the values back to each cell of the column.

For a code example of the three steps outlined above, please see the sample in our documentation for how to Convert strings to formatted numerical values.

ExcelWriter, Knowledge Base, WordWriter

Can a web application send additional information after streaming a file to the browser?

Problem

Your web application uses OfficeWriter to generate and stream a document or spreadsheet to the browser. Subsequently, you want to send additional instructions to the browser to update the web page, display a pop-up confirmation message, or re-direct to another web page.

Solution

It is not possible for a web application to stream a file to the browser and subsequently send additional information in the same response. This limitation is a consequence of the request-response nature of web programming and is not specific to OfficeWriter. The same limitation applies to any web application which performs media streaming or file downloading.

The HTTP protocol mandates that there can be only one response to each request. A response consists of HTTP headers and content. The HTTP headers provides information about the file and optionally specifies how it should be handled. In the case of file downloading, the response’s content must contain only file data. If there are non-file data in the response, such as HTML code to update the web page or perform additional actions, the foreign data would be taken to be part of the file, causing the file to be corrupted.

After a web application streams a file to the browser using OfficeWriter’s Save method, attempting to redirect to another web page may generate the following error: “Cannot redirect after HTTP headers have been sent.” This is because OfficeWriter’s Save method sets the appropriate HTTP headers before streaming the file and the instruction to redirect requires another set of HTTP headers.

Knowledge Base, WordWriter

How to import data into a Word document with WordWriter

Solution

WordWriter has two approaches for importing data:

Approach 1: WordTemplate

Use WordTemplate to import data into existing templates with merge fields.

Additional reading:

Approach 2: WordApplication

Call ImportData to generate Table in the document with the data.

Additional reading:

ExcelWriter, Knowledge Base

How to change a column of URLs imported with ExcelTemplate into hyperlinks

Problem

Sometimes your application requires creating a column of hyperlinks from URLs stored as strings. When ExcelTemplate populates a data marker with these strings, they remain static.

Solution

There are two ways to turn a column of strings into a column of hyperlinks:

  1. Fill a hidden column in the template with the URLs and use Excel’s HYPERLINK function to build the hyperlinks.
  2. Open the populated spreadsheet with the ExcelApplication object and use Cell.CreateHyperlink to turn the cell values into links.

Using the HYPERLINK Function

In this approach you can create a formula in a column in your worksheet that will reference another column that holds the URLs. To do this, place the datamarker for your URLs in an unused column on the same row in the same worksheet as the column that will contain the hyperlinks. For example, if you want the hyperlinks to begin in cell C5, place the data marker in row five of some other column, for instance AA5.

Now use Excel’s HYPERLINK() formula in C5 to reference the datamarker. Set C5’s formula to “=HYPERLINK(AA5)”, and as the datamarker in AA5 gets filled with URLs and stretches downward, the formula in C5 that references it will also stretch. If you don’t want the readers of the spreadsheet to be able to see the column of raw URLs, you can set it to hidden. In Excel, just right-click on a column header (the letter at the top) and choose ‘Hide’ from the drop-down menu. Now the column will not be visible, though it can be unhidden.

This method can be faster than using the ExcelApplication method (below), since it doesn’t require opening the file with ExcelApplication, but it has the disadvantage of leaving a column full of raw data in the worksheet.

Using ExcelApplication

After filling a column with URLs, you can open the workbook with the ExcelApplication object and convert the strings to hyperlinks. To iterate over a column of URLs and turn them all to hyperlinks, do something like this:


ExcelTemplate xlt = new ExcelTemplate();
xlt.Open("C:\\path_to_template\\template.xls");

// ... Data binding code ...

// Populate the spreadsheet and open it with ExcelApplication
xlt.Process();
ExcelApplication xla = new ExcelApplication();
Workbook wb = xla.Open(xlt);
Worksheet ws = wb["Sheet With URLS"];

// Define the area to convert to hyperlinks
Area URLArea = ws.CreateArea("C3:C90");
for (int i = 0; i < URLArea.RowCount; i++)
{
// Convert each cell to a hyperlink pointing to its URL
Cell c = URLArea[0, i];
c.CreateHyperlink(c.Value);
}

This will create hyperlinks in every cell in that Area using the value of the cell as the URL. Each cell will display the URL as the link text. If you want the cells to show some other text, change c.Value after the call to CreateHyperlink.

Keep in mind when using this technique that opening large files with the ExcelApplication object can be memory-intensive as it needs to create objects for each filled cell.

ExcelWriter, Knowledge Base

How to preserve leading zeros with ExcelWriter

Problem

Importing numbers with leadings zeros, such as zip codes and social security numbers, can pose a challenge. While these numbers are most often stored in a database as strings, they can be imported into Excel as either numbers or strings. Importing as numbers is the preferred option, as importing as strings will cause Excel to display a warning message that a number is being displayed as text.

Solution

When importing numbers with leading zeros, you have two options:

  1. Import or insert the values as numbers and not as strings. Set the appropriate special format as needed to display the desired number of leading zeros. This is the recommended practice.
  2. Import or insert the values as strings. Format the cells as General or Text. Excel will display a warning on each cell that a number is displayed as text. However, the cell values will contain the leading zeros.

Option 1: Import numbers with leading zeros as numbers (Recommended)

As mentioned above, the preferred option for importing numbers with leading zeros as numbers. Leading zeros will be stripped off, as numeric data types cannot contain leading zeros. To display the desired number of leading zeros within Excel on these numeric values, the appropriate formatting string should be set on the cells which contain the data.

It should be noted that a limitation to this approach is that when a value is read out of a cell there will be no leading zeros. If you would prefer to have leading zeros stored in the cell values (and have Excel display the warning message instead), you should use Option 2 as described below.

ExcelTemplate

When using ExcelTemplate, you have three options for converting your data from strings to an appropriate numeric data type. You can:

  • Let ExcelTemplate attempt to globally convert all strings to numeric values where appropriate. (This is the default behavior.)
  • Convert the applicable zip code values (and any other numbers) to numeric data types through native features of the language. Then set the PreserveStrings property to true to force ExcelTemplate to preserve all strings as strings. This approach will result in the most predictable results, since setting PreserveStrings to true will prevent ExcelWriter from performing string to numeric data type conversions.
  • Use a combination of Preserve and/or Convert data marker modifiers on specific columns. Which modifiers you use should depends on how PreserveStrings is set. If you choose this option, the recommended practice is to set PreserveStrings to true and use Convert data marker modifiers on zip code columns. For more info about data marker modifiers, please refer to the Creating Data Markers documentation.

Then set the appropriate cell formats on the template for the columns which will contain these values. Use the Zip Code or Social Security formats (listed under the Special category), or use your own custom string.

ExcelApplication

With ExcelApplication, no automatic string to number conversion exists. Therefore, you must convert the applicable zip code data using native features of the language. Set the appropriate cell formats on the existing file, if there is one, or use the ExcelApplication API to set it on an Area, Range, or individual Cell.

When setting the formatting through the ExcelApplication API, you should use the Style.NumberFormat property. For example, to set a zip code format on a cell, you would do:

 myCell.Style.NumberFormat = NumberFormat.Special_EN_US.ZipCode;

In the above example, the NumberFormat.SpecialENUS.ZipCode constant is equivalent to “00000”. This forces Excel to always display five digits, prepending zeros to the number as necessary.

Option 2: Import numbers with leading zeros as strings

As mentioned above, you can import numbers with leading zeros as strings instead of converting them to numbers. However, Excel will display a warning that a number is displayed as text. This will allow you store the leading zeros in the cell values, preventing the need to set a special cell format, and also allowing the value to be read from the cell so that it includes the leading zero.

ExcelTemplate

To preserve strings using ExcelTemplate, use a combination of the following techniques:

  • Set the global PreserveStrings property to true.
  • Use a combination of Preserve and/or Convert data marker modifiers on specific columns. Which modifiers you use depends on how PreserveStrings is set.

Set the applicable cell formats to General or Text in the template.

For more information about using ExcelTemplate to convert strings to numbers where appropriate, please see this post: How to preserve strings with ExcelTemplate.

ExcelApplication

To preserve string values with ExcelApplication, no action is needed. ExcelApplication will always preserve strings, because it provides no automatic conversion functionality.

Set the appropriate cell formats to General or Text, either on the template or using the ExcelApplication API. The following example sets a cell format to Text:

 myCell.Style.NumberFormat = "@";

ExcelWriter, Knowledge Base

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);

Knowledge Base, WordWriter

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.

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.