Category Archives: Knowledge Base

ExcelWriter, Knowledge Base

How to hide the secondary axis of a chart

Problem

When programatically adding a secondary x axis to a chart using ExcelApplication, a secondary y axis will automatically appear as well (and vice versa).

Solution

To hide a secondary axis in ASP.NET use the Visible property of the Axis element. Both the SecondaryCatagoryAxis property and the SecondaryValueAxis property, return an object that extends the Axis element.

The following code hides the secondary axes in ASP.NET:

Chart1.SecondaryCategoryAxis.Visible = false; //For the secondary X Axis
Chart1.SecondaryValueAxis.Visible = false; //For the secondary Y Axis

ExcelWriter, Knowledge Base

How to designate number formats in ExcelWriter across languages

Problem

While Microsoft Excel is available in different language versions, ExcelWriter is US-English based. What this means is that while ExcelWriter can generate spreadsheets in any language, there is no French, Russian, Chinese, etc. version of ExcelWriter. This requires special considerations when creating number formats for other languages in ExcelWriter.

You must understand what symbols are used as “separators,” “decimal placeholders,” and what remaining symbols will be interpreted as “literals.” This article will help you understand how ExcelWriter does this, and how this will be interpreted by a non-English versions of Excel (example, French, Chinese, Russian, etc). This post will use French as the non-English language example.

Solution

What is a number separator?

A number separator is a symbol or space that is used to group numbers so that they are easier to read. In English(US) and many other languages, separators occur between the thousands position and the hundreds position, and then again for every three numbers moving left of the decimal placeholder. The decimal placeholder may also change from language to language.

Compare these values for English (United States) and French:

Language Separator symbol Decimal Symbol Example using 1234567
English(US) Commas Period 1,234,567.00
French Spaces Comma 1 234 567,00

Specifying Number Formats for non-English Spreadsheets

Since ExcelWriter is only available in US-English, you must specify your number formats according to US-English standards. This will allow ExcelWriter to correctly identify the separators and decimal place holders. When the spreadsheet is opened in a non-English version of Microsoft Excel, those separators and placeholders will be correctly translated according to the language and regional settings in that version of Microsoft Excel.

ExcelWriter code sample


//--- Declare variables
ExcelApplication xla = new ExcelApplication();
Workbook wb = xla.Create();
Worksheet ws = wb.Worksheets[0];

ws.Cells["A1"].Value = 1234567
ws.Cells["A1"].Format.Number = "#,###.##;-#,###.##;;"
...[rest of code]

Here is how Cell A1 will display its number:

Language of Microsoft Excel: How the format will be translated:
English 1,234,567.00
French 1 234 567,00

ExcelWriter, Knowledge Base

How to populate XLS files with more than 65536 rows

Problem

This post describes 3 ways to populate a binary Excel template when there are more than 65536 rows in the data source. In order to accommodate a variable number of data rows, these approaches use a combination of ExcelApplication and ExcelTemplate. You can download a Visual Studio 2008 project containing a demo at the end of the article.

This post also discusses an alternative in the case that you do not have access to ExcelApplication.

Solution

For a binary Excel file (.xls), the number of rows of each worksheet is limited to 65536 rows (source: Excel specifications and limits). By comparison, an OOXML file (.xlsx or .xlsm) has a maximum of about 1 million (exactly 1048576) rows per worksheet. If the data source contains more than 65536 rows (and fewer than 1 million rows) and you are using ExcelWriter version 7.0 or higher, you can use ExcelTemplate with an OOXML template so that all data fit into a single worksheet.

If you want to use a binary template and the data source contains more than 65536 rows, the template must contain as many worksheets as necessary to accommodate all data rows. In order to generate the required number of worksheets, you can start with a template containing a single worksheet, then make copies of the original worksheet. Alternatively, you can start with the maximum number of worksheets required, then delete worksheets as necessary.

In this post, we use a binary template containing a single worksheet. Then we use ExcelApplication to make copies of the original worksheet so that the processed template contains exactly the number of worksheets required to accommodate all data rows. Depending on the approach, we will also need to modify the data marker(s) on each copy. Finally, we use ExcelTemplate to populate the processed template.

Support for OOXML files in ExcelApplication was introduced in ExcelWriter 8. In ExcelWriter v7.6.1 and earlier, ExcelApplication only supports binary files. If you are using an older version of ExcelWriter, you will not be able to dynamically insert or delete worksheets with an OOXML file.

If you cannot use ExcelApplication, you cannot dynamically insert or remove worksheets. In this case, you can have multiple templates, each with a different number of worksheets. See Using ExcelTemplate only.

As of ExceWriter 7.5, the following table summarizes the possible solutions for different scenarios. Note that more options would be available for ExcelWriter EE once ExcelApplication supports the OOXML format in a future version.

ExcelWriter edition ExcelWriter version Template format Available solution(s)
EE All Binary 1, 2, 3, 4
EE 7.0-7.6.1 OOXML Populate all data on single worksheet, 4
SE All Binary 4
SE 7.0+ OOXML Populate all data on single worksheet, 4

Using ExcelApplication and ExcelTemplate

1. Populate using a DataReader

When you use a forward-only data source such as a DataReader, the template must contain identical data markers on each worksheet. After the number of rows on a given worksheet reaches a limit, additional rows automatically overflow onto the next worksheet. This limit is specified in the MaxRows property of the DataBindingProperties parameter of the ExcelTemplate.BindData() method.

DataBindingProperties props = excelTemplate.CreateDataBindingProperties();
props.MaxRows = 65536;
excelTemplate.BindData(dataReader, "", props);

For illustration purpose, the above code snippet sets MaxRows to the maximum number of rows allowable on a worksheet. However, if the worksheet contains header or blank rows in addition to data rows, adjust this value accordingly.

If the template has more worksheets containing data markers than necessary to accommodate all rows, ExcelTemplate would attempt to populate the extra worksheets after DataReader reaches its end and consequently throw the following exception: SoftArtisans.OfficeWriter.ExcelWriter.SAException: Exhausted data marker at XX value: %%=YY.ZZ.

Note for ExcelWriter 3.9.x

In ExcelWriter 3.9.x, ExcelTemplate does not support the BindData method. Replace the data-binding code above with:

excelTemplate.SetDataSource(dataReader, "", 65536);

2. Populate using the (continue) data marker modifier

This approach is applicable when you use a scrollable data source such as a DataTable. The data markers on the overflow worksheets must have a (continue) modifier; e.g., %%=datasource.field1(continue). Except for the (continue) modifier, the data markers are otherwise the identical. In order to create the required number of worksheets, we use ExcelApplication to make copies of the first worksheet, then append (continue) to all data markers on each copy. See Data Marker Modifiers.

The (continue) modifier indicates that ExcelTemplate should continue to read the data source at the point where the previous worksheet leaves off. Again, set the DataBindingProperties.MaxRows property to limit the number of rows per worksheet.

DataBindingProperties props = excelTemplate.CreateDataBindingProperties();
props.MaxRows = 65536;
excelTemplate.BindData(dataTable, "", props);

For illustration purpose, the above code snippet sets MaxRows to the maximum number of rows allowable on a worksheet. However, if the worksheet contains header or blank rows in addition to data rows, adjust this value accordingly.

If the template has more worksheets containing data markers than necessary to accommodate all rows, the DataTable would rewind after reaching the last worksheet and the extra worksheets would contain repeated rows.

Note for ExcelWriter 3.9.x

In ExcelWriter 3.9.x, ExcelTemplate does not support the BindData method. Replace the data-binding code above with:

excelTemplate.SetDataSource(dataTable, "", 65536);

3. Populate individual worksheets

You must bind data to each individual worksheet using a different data source. Use the DataBindingProperties.WorksheetName property to specify the name of the worksheet you are targeting. Each worksheet’s data source must have only the rows you want on that worksheet. Because each worksheet has a different data source, it’s possible to have a different number of rows on each worksheet.

DataBindingProperties props = excelTemplate.CreateDataBindingProperties();
props.WorksheetName = workbook.Worksheets[0].Name;
excelTemplate.BindData(dataTable1, "datasource1", props);

The data markers on each worksheet must have a data source identifier matching its data source. If you use ordinal data source identifier, the first worksheet to be data-bound should have data markers like %%=#1.field1, the second data-bound worksheet, %%=#2.field2, and so on. If you use named data source identifier, the data marker should contain the name of the data source specified in ExcelTemplate.BindData. For example, if the data-binding call is BindData(dataTable, “datasourceN”, dataBindingProperties), the corresponding data markers should be %%=datasourceN.field1, %%=datasourceN.field2, and so on.

If you start with a single data source, you can partition it into smaller data sources containing non-overlapping blocks of rows. In the sample, we partition the original DataTable into a set of smaller DataTables, each of which contains 65536 rows (or whichever value the row limit is set to). The last DataTable gets the remaining rows and can have fewer than 65536 rows. Each smaller DataTable is used as the data source for a different worksheet.

If you bind the same data source to more than one worksheet, even under different names, ExcelTemplate will return the following error: This binding source named xxx was already added under the name yyy.

Note for ExcelWriter 3.9.x

This approach is not compatible with ExcelWriter 3.9.x because it isn’t possible to bind data to a specific worksheet.

Using ExcelTemplate only

The following discussion applies to the following scenarios:

  • You do not have access to ExcelApplication
  • You are unable to use ExcelApplication with an OOXML template

4. Using multiple templates

It may occur to you to use a template with as many worksheets as necessary and append the (optional) modifier to all data markers on each worksheet. The assumption is that ExcelTemplate would ignore such data markers if there are no data bound to a worksheet. However, this assumption is not correct. The (optional) modifier has to do with the presence or absence of a column, not a row. ExcelTemplate ignores a data marker marked as optional if there is no corresponding column in the data source. But if a data marker is mapped to a column in the data source, ExcelTemplate would attempt to populate all instances of such a data marker. If ExcelTemplateencounters a data marker and there is no data row left, its behavior depends on the type of data source. For a forward-only data source such as DataReader, ExcelTemplate would throw an “exhausted data marker” error. For a scrollable data source such as DataTable, ExcelTemplate would rewind the data source to the beginning, resulting in duplicate data.

Consequently, it’s imperative that the template contains exactly the number of worksheets required to accommodate all data rows. If you cannot use ExcelApplication, you would not be able to dynamically insert or remove worksheets at run time. An alternative is to create multiple templates, each one containing a different number of worksheets. You can determine which template to use depending on the number of rows in the data source.

Conclusions

Using DataReader for data retrieval is the simplest approach and has performance benefits. Using the (continue) data marker modifier involves performing additional processing on a worksheet after copying it and therefore is less efficient. Binding data to individual worksheets, because the data source must be processed, can have a lower performance than the other approaches.

About the demo

The attachment contains a Visual Studio 2008 project illustrating the 3 approaches described in this article. Each of the approaches is contained in a separate method. You must install ExcelWriter EE before running the demo.

Attachments

Knowledge Base, OfficeWriter, WordWriter

Unfolding a Word document with WordApplication

Problem

In the course of debugging, it can often be difficult to visualize the structure of documents as they are manipulated by WordApplication.

Solution

Our documentation has an overview of how WordApplication represents a Word document and how to insert elements using WordApplication. Additionally, there is a code sample that will produce a formatted text representation of the element hierarchy in a given document: Unfolding a Word document with WordApplication Sample.

For example, given the following document:

Our output generated by the code would look like this:

UnfoldingWordDocument

ExcelWriter, Knowledge Base, Reporting Services

Using Report Models as DataSources in OfficeWriter Designer

Problem

Prior to version 3.9.2, OfficeWriter designer did not support reports with queries based on Report Models. Publishing such a report from OfficeWriter Designer caused some fields to show gibberish or not show at all.

For more information about Report Models visit Microsoft web-site at http://technet.microsoft.com/en-us/library/cc678411.aspx.

Solution

From version 3.9.2 and above, OfficeWriter supports Report Model DataSources. Reports created using Visual Studio (2005 and 2008) or ReportBuilder version 2.0, that use a report model as a datasource are now parsed correctly by the OfficeWriter designer.

Note: Support for newer versions of Reporting Services (2008, 2008 R2) and Report Builder (3.0) have been added in later version of OfficeWriter. For more information, refer to the change log in the OfficeWriter Docs.

Reports may contain both regular SQL queries and semantic queries (Report Model-based queries). This support is seamless to the user with no change to the toolbar. However, a feature was added to improve the user experience.

In Report Models, all fields are mapped to entities (instead of tables). We added a feature that allows the user to know to which entity a certain field belongs. For example, if the user encounters a field First Name, it will have a way of knowing whether this is an employee or a customer first name. When a report model is the datasource, its fields will be displayed as a submenu of their entities (rather than the usual flat list of fields), as in the image below:

Figure 1.

Below is the same query, but in the unmapped view:

Figure 2.

The RDL alone does not contain sufficient information to map the fields to entities. Therefore, when a Report Model-based query is selected from the Select Query drop down, we attempt to retrieve the model (as an XML file) from the server. If the address to the model on the server is embedded in the RDL, we try to retrieve it immediately. If we are not able to get the model, or if there is no address embedded in the RDL, the following window will open:

Figure 3.

This dialog offers the following options:

  • Continue will continue without retrieving the model. This will not affect the functionality of the report, the only effect is that the fields will not be mapped to entities, but will be displayed in a single list (as in Figure 2).
  • Retry will attempt to retrieve the model from the server specified in the RDL if the address exists.
  • Browse… will open a window (as in Figure 4 below) that will allow the user to browse to a server and choose a model from a list of available models on the server.

Figure 4.

Note that each model has a unique ID which is not visible to the user. Two models, even if they are created based on the same database with the same entities and fields, will have different IDs. Therefore, mapping the fields of a report created with one model against another model will fail. However, each model preserves its ID when deployed to different servers, so fields can be mapped using a model from another server if it was deployed from the same source model.

Once the model is retrieved, the fields are mapped to entities under the Insert Fields drop-down list (as in Figure 1). Formulas created in Visual Studio or ReportBuider are not related to any entity and will show as regular fields at the bottom of the Insert Field drop-down.

This change applies to the client-side OfficeWriter designer. The server-side components of OfficeWriter require no special modification to work with Report Models. However, we recommend always using matching versions of the designer and the server-side installation of OfficeWriter.

Knowledge Base, WordWriter

How to add page numbers or other fields with WordApplication

Problem

WordApplication does not currently have support for inserting fields other than MergeFields and Hyperlinks. You want to insert a different kind of field, such as the current page number.

Solution

WordWriter has a MergeField class and a Hyperlink class which allow you to programmatically insert those fields. However, those are currently the only two fields which are included in the WordWriter API. In order to insert a field such as a page number into a newly generated document in WordApplication, you must first create a document with Microsoft Word and copy the field from there.

Inserting Footers with Fields

If you wish to create a footer which includes a field such as the page number, you must first create a document in Microsoft Word. Add all the text, fields, and formatting that you wish to appear in the final document to this file. For the purposes of this article, this file will be called ‘footer.doc’.*

In order to use your newly created footer, you must open the document in your WordApplication and copy the footer into the document you are generating programmatically:

//Create the document you want to insert a footer into
Document doc = wa.Create();

//Or open an existing file, instead of creating one
//Document doc = wa.Open("myFile.doc");

//Open the document with the footer you want to copy
Document footerDoc = wa.Open(Page.MapPath("footer.doc"));

//Get the footers for both documents
Element footerSource = footerDoc.Sections[0].get_Footer(Section.HeaderFooterType.All);
Element footerDestination = doc.Sections[0].get_Footer(Section.HeaderFooterType.All);

//Insert the footer into the destination document
footerDestination.InsertAfter(footerSource);
In this way, you can copy the footer into a newly generated document or one that was opened from a different file. For more information about retrieving footers or headers and inserting them as Elements, see the documentation for the Section and the Element classes.

Inserting Headers with Fields

Copying the header is very similar to copying the footer, except that you would insert the header Element instead of the footer. So if you have a document called ‘header.doc’ that you wish to copy the header from, you can say:

//Create the document you want to insert a header into
Document doc = wa.Create();

//Or open an existing file, instead of creating one
//Document doc = wa.Open("myFile.doc");

//Open the document with the header you want to copy
Document headerDoc = wa.Open(Page.MapPath("header.doc"));

//Get the headers for both documents
Element headerSource = footerDoc.Sections[0].get_Header(Section.HeaderFooterType.All);
Element headerDest = doc.Sections[0].get_Header(Section.HeaderFooterType.All);

//Insert the header into the document
headerDest.InsertAfter(headerSource);

Inserting Fields into the Body of a Document

If you instead want to insert a field into the body of the document along with programmatically generated content, you can retrieve only the Field Element from a pregenerated file. For example, if you want to insert a ‘Date’ field into the body of a document, you must first create a new file in Microsoft Word. Add a Date field to the body of this new document by going to Insert->Field and choosing Date. Save the document. For the purposes of this article, the file will be called ‘datefield.doc’.

In your WordApplication, when you want to insert the field, you will open the datefield.doc document, retrieve the field, and insert it into your own document:

//Open the document with the date field in it
Document fieldDoc = wa.Open(Page.MapPath("datefield.doc"));

//Get the date field from the body of the document. We use an index of 0
//because it is the first (and only) field element in the document.
Element dateField = fieldDoc.get_Elements(Element.Type.Field)[0];

//Insert the date field into the body of another document (called 'doc').
doc.InsertAfter(dateField);

In this way, you can programmatically add Microsoft Word fields to a newly generated document. For more information about how to specify what type of Element you want to retrieve from the document, see the documentation for the Elements property of the Element class and refer to the different types of Elements that you can ask for.

ExcelWriter, Knowledge Base

How to keep cell references absolute when using ExcelTemplate

Problem

When using ExcelTemplate to import data, a new row is inserted into the worksheet for each row of data in the data source. Any cell references to the cells where the new rows are being inserted will be updated to reflect that new rows have been inserted. This includes relative cell references (e.g. A5) and formulas (e.g. SUM(A5:A7)).

This is native Excel behavior: whenever a row (or column) is inserted or deleted, all cell references to that row/column will be updated.

In some cases, having the formulas or references updated may not be the desired behavior.

Solution

There are two ways to keep cell references absolute:

Use ‘$’ to denote absolute references

In Excel, absolute references are denoted with ‘$’. If a reference is absolute, then ExcelTemplate will not update the reference when rows are inserted. Here are some examples of absolute references:

  • $B5 – The column A is absolute and will not change, even if a new column is inserted between columns A and B. Rows will still update if new rows are inserted.
  • B$5 – The row 5 is absolute and won’t change if rows are inserted or deleted. If a column were to be inserted, then the column reference would update.
  • $B$5 – The cell reference is absolute and will refer to B5 even if rows/columns are inserted/deleted.

Use INDIRECT to preserve formula references

If a cell reference is pre-pended with INDIRECT, Excel treats the reference as a string and does not change it. For example, the formula =AVERAGE(INDIRECT(“Sheet1!E1:E10”)) will always refer to that particular range of cells.

Additional Reading

Knowledge Base, WordWriter

How to use SetCultureInfo with date functions and formatting

Problem

The SetCultureInfo method allows you to override the server’s default locale when generating a new Word file from a template with WordWriter. This is especially useful for the display of dates. It is important, however, to understand that any date formatting must be applied to the Word Template Merge Field instead of in the ASP.NET script.

Solution

To see a standard field code for a DateTimeObject merge field, right-click on the field, and choose Toggle Field codes:

 {MERGEFIELD DateTimeObject}

You can see that there are no formatting switches applied to this field. In this situation, the default format of “yyy-MM-dd HH:mm:ss” (4-digit year, month, day, military hour, minute, second) will be applied. To set the culture info to a particular language, such as German:

CultureInfo deDe = new CultureInfo("de-DE");
wt.CultureInfo = deDe;
For more information see our documentation on WordTemplate.CultureInfo.

Best coding practices

Unexpected date formatting behavior can be prevented by keeping the following in mind: The Word template is expecting to receive the date as a System.DateTime object. If it is passed a String instead of a System.DateTime object, it will not be able to apply the formatting codes and apply the correct Culture settings.

ExcelWriter, Knowledge Base

How to check if a cell is empty

Solution

A cell can contain a value and/or a formula. To check if a cell is empty use the Cell.Value and Cell.Formula properties to look for the following conditions:

XLS Files XLSX and XLSM Files
Language Cell.Value Cell.Formula Cell.Value Cell.Formula
C# null (empty string) (empty string) OR null (empty string)
VB.NET Nothing Nothing (empty string) OR Nothing Nothing
ASP/COM * (empty string) (empty string) (empty string) (empty string)

 

Example

//--- myCell is a Cell object 
if (string.IsNullOrEmpty(myCell.Value) && string.IsNullOrEmpty(myCell.Formula))
{
//--- Cell is empty
}


Knowledge Base, WordWriter

How to apply a banded style on a table with WordTemplate

Problem

In a template document created in Word 2003, table styles behave correctly when new rows are added by WordWriter. However, if a new template document is created in Word 2007 with the new table styles, these styles do not persist as expected when saved in the binary (.doc) format. This can easily be demonstrated with the banded coloring styles which are displayed as alternating row coloring.

After processing with WordTemplate, the additional rows in the table will have a solid background color instead of a banded styling. MS Word itself shows the same behavior when adding rows to a table styled with the new 2007 styles and saved to the 97-2003 binary file format (.doc). This appears to be a compatability issue at the file format level, with Word not fully supporting the new table styles in the binary file format.

Banded styles behave as expected with OOXML (.docx) files, which have been supported by WordTemplate since version 4.0.

Solution

Option 1: Save the file as .doc before applying table formatting

When Word 2007 opens a .doc file, it opens in compatibility mode which only makes available features that are supported for that format. However, when Word 2007 creates a new document, it doesn’t know which format the user will choose to save it in, so all options are shown, including those which aren’t compatible with the binary format.

The easiest solution would be to save the file as 97-2003 binary file format (.doc) before applying any table formatting. Once the file has been saved, only the styles compatible with the binary format will be available and you can use them freely. If you have already created a template file with 2007 formatting, simply reapply the styling to the tables after the file has been saved in binary format.

Option 2: Use the OOXML file format

Alternatively, with WordWriter 4.0 and above, the other good solution is to use the OOXML (.docx) file format. The resulting populated table will be as you expect when using that file format. The feature is supported in the file format level, so you can place your data marker in such formatted table and it will expand and maintain the selected style.