Category Archives: Knowledge Base

How to import TOP N rows in a data set with ExcelTemplate

Problem

ExcelTemplate allows users to specify the number of rows they want to important from a data set. For instance, importing only the top 20 rows into the report.

Solution

When importing multiple rows of data with the BindData() method, you can limit the maximum number of rows to import using the MaxRows property of the DataBindingProperties class.To limit the max number of rows to 20:

//Create a DataBindingProperties object
DataBindingProperties dataprops = xlt.CreateDataBindingProperties();


//Limit the number of rows to import to 20
dataprops.MaxRows = 20;


//Bind the data using that DataBindingProperties object
xlt.BindData(dataTable, "DataSourceName", dataprops);
...rest of ExcelTemplate code...

Getting started with conditional formatting in ExcelApplication

Problem

conditional format is a format in Excel that is applied to a cell if a specified condition is met. ExcelWriter’s ExcelApplication object also provides the ability to create conditional formats with the ConditionalFormat object.

These are some tips for getting started with conditional formatting in ExcelWriter.

Solution

Background

The ConditionalFormat object contains up to three Condition objects, which represent the condition that determines if the format is applied, and a Range object, which represents the cells to which the format will be applied.

The condition stores a ComparisonType, which indicates whether the cell values will be compared to one or two other values, or if a particular formula will be evaluated for each cell. The condition also stores a Style object that defines the formatting to be applied.

This example will show how to use a formula evaluation conditional format to apply a red font color if a cell value in a row of cells is less than the average of cell values across that row.

Note: This is equivalent to creating a conditional format in Excel with the option to ‘Use a formula to determine which cells to format’.

Defining the Formulas

The formula being evaluated to determine which cells to format is no different from the formula that would be used in Excel. If the formula evaluates to TRUE, the format will be applied to the cell.

In this example, there is a row of integer values. The conditional format should be applied if any one of those values is less than the average of the values. An example of this formula would be:

=(A3 < AVERAGE($A$3:$G$3))

Note: The reference to A3 is relative and will change for each cell in the range that the conditional format is applied to. $A$3:$G$3 is an absolute reference, and will not update for the cells in the range.

The other formula needed is the formula to define the range of cells that the conditional format will be applied to. In this case, the range formula is for cells A3 to G3 on Sheet1:

“=Sheet1!A3:G3”

For more information about defining formulas for ranges, please refer to this post.

Creating the Conditonal Format

To create and apply a conditional format with ExcelWriter:

  1. Create a ConditionalFormat with Worksheet.CreateConditionalFormat
  2. Create a Condition using the Excel evaluation formula in ConditionalFormat.CreateCondition
  3. Define the conditional format Style
  4. Define a Range to apply the conditional format to
  5. Apply the ConditionalFormat to the Range

Code snippet:

//Create a conditional format
ConditionalFormat cf = wb.CreateConditionalFormat();


//Create the condition to be evaluated when determining which cells to format
Condition cond = cf.CreateCondition(Condition.Comparison.FormulaEvaluation, "=(A3 < AVERAGE($A$3:$G$3))");


//The conditional format will apply a red font color
cond.Style.Font.Color = wb.Palette.GetClosestColor(128, 0, 0);


//Creates a range and applies the conditional format to the range
Range cond_range = wb.CreateRange("=Sheet1!A3:G3");
cond_range.SetConditionalFormat(cf);

Adding Conditions to the Conditional Format

Since a ConditionalFormat object can hold up to 3 Condition objects, up to 3 different conditional format styles and evaluation rules can be applied for a single conditional format. For example, any cell values in A3:G3 that are over 100 should have a bold, green font.

Create two conditions instead of one:

Condition cond1 = cf.CreateCondition(Condition.Comparison.FormulaEvaluation, "=(A3 < AVERAGE($A$3:$G$3))");


Condition cond2 = cf.CreateCondition(Condition.Comparison.FormulaEvaluation,"=(A3 > 100");

Define styles for both of the conditions:

cond1.Style.Font.Color = wb.Palette.GetClosestColor(128, 0, 0);
cond2.Style.Font.Color = wb.Palette.GetClosestColor(0, 128, 0);
cond2.Style.Font.Bold = true;

Setting PivotTable styles with ExcelApplication PivotTables

Problem

In Excel, there are numerous PivotTable settings available. These can be found by right clicking the PivotTable and selecting PivotTable options. Not all of these are accessible through the ExcelApplication PivotTable API yet.

Solution

For any properties that are not yet available in ExcelApplication, we recommend that you create a template file that contains a PivotTable and set the PivotTable options on that table, rather than creating a PivotTable from scratch.

Then use ExcelApplication to modify the existing PivotTable by changing the data source and adding PivotFields.

The settings will be preserved, even if the data source of the PivotTable is changed and new PivotFields are added.

To get a handle on a PivotTable:

 worksheet.PivotTables[0]; //By index in the worksheet worksheet.PivotTables["PivotTable1"]; //By name 

To change the data source of a PivotTable, use PivotTable.ChangeDataSource.

For more about building PivotTables, see Creating a Basic PivotTable.

WordWriter search and replace in Word textboxes

Problem

When trying to use SearchAndReplace with a Word document that has text in a Word textbox or shape, the text doesn’t get replaced.

Solution

Unfortunately, WordApplication does not recognize the shape or textbox objects and so they will not be included in the set of elements parsed by the code above. The full list of elements recognized by WordApplication is outlined in this article.

Using WordTemplate to replace text

One possible workaround is to use the WordTemplate object for text replacement instead of SearchAndReplace. When WordTemplate processes a template, it largely ignores where the merge fields are located.

This means if there is text in a shape or textbox that needs to be replaced, the original text can be placed in a merge field and then WordTemplate can populate the merge field with the new text. For example, the code below looks through a Word document for mergefields marked <> an populates them with the value “REPLACED TEXT”. Then passes the processed template to the WordApplication object.

//Start with the WordTemplate object
//Template contains merge fields named "REPLACE"
wt.Open(Page.MapPath("\templates\TextBoxTemplate.doc"));
Object[] values = {"REPLACED TEXT"};
String[] names = {"REPLACE"};
wt.SetDataSource(values, names, "DataSource");
wt.Process();
Document doc = wapp.Open(wt);
//...Other WordApplication code here...
wapp.Save(doc, Page.Response, "TextBoxTemplate.doc", False);

For more information about WordTemplate and mergefields, please refer to our WordTemplate tutorials.

How to export SSRS reports to XLSX, DOCX file formats

Problem

In Office 2007, Microsoft introduced the OOXML file formats (XLSX, XLSM), which come with benefits, such as an increase in the number of rows allowed in a file. Reporting Services reports designed for OfficeWriter allow for exports to the Office 2007 and 2003 file formats.

Solution

To export a report in a particular format, you need to explicitly save the report with the format you want to export it as:

  1. Open the report with the OfficeWriter Designer
  2. Click ‘Save As’ on the OfficeWriter Designer toolbar (for Office 2003) or ‘Save’ > ‘Save to disk’ (for Office 2007/2010)
  3. There will be three options for Reporting Definition files: Office 2007(exports to XLSX, DOCX), Office 2007 with macros (exports to XLSM, DOCM), Office 2003 and earlier (*exports to XLS, DOC)
  4. Select Office 2007 to save the RDL
  5. Publish the saved report to the server

Note: Office 2007 (XLSX, DOCX) is only available in OfficeWriter 4.0 and above. Office 2007 with macros (XLSM, DOCM) is only available in OfficeWriter 4.1 and above.

How to select a group of rows that have specific values in a cell or column

Problem

Often it necessary to filter the rows on one worksheet based on a certain criteria and copy over the affected rows to a separate worksheet. It is fairly easy to achieve this result with a little coding.

ExcelApplication object allows you to fully parse and design the Excel document from your code, therefore giving you the ability to provide all the conditional logic to structure your final report.

Solution

Given the following data in one worksheet:

Company Name
PRICES
Date Open High Low Close Volume Adj Close*
5-Jul-06 20.47 20.47 20.13 20.28 336,400 20.28
3-Jul-06 20.97 21.07 20.85 21.05 115,000 21.05
30-Jun-06 21.27 21.27 21.02 21.13 215,700 21.13
29-Jun-06 20.4 21.02 20.38 21.02 314,000 21.02
28-Jun-06 20.37 20.37 20.05 20.14 403,900 20.14
27-Jun-06 20.24 20.24 19.8 19.86 257,700 19.86
26-Jun-06 20.2 20.27 20.05 20.22 703,700 20.22
23-Jun-06 20.45 20.45 20.17 20.2 302,500 20.2
22-Jun-06 20.3 20.5 20.07 20.26 291,700 20.26
21-Jun-06 20.08 20.45 20.04 20.31 160,000 20.31
20-Jun-06 20.12 20.2 20 20.03 278,500 20.03
19-Jun-06 20.33 20.37 20.06 20.11 301,100 20.11
16-Jun-06 20.03 20.31 20.03 20.16 480,800 20.16
15-Jun-06 19.82 20.5 19.82 20.42 301,000 20.42
14-Jun-06 19.4 19.66 19.38 19.61 211,000 19.61
13-Jun-06 19.25 19.53 18.96 19.09 523,700 19.09
12-Jun-06 20.45 20.56 20.11 20.11 239,900 20.11
9-Jun-06 20.16 20.53 20.11 20.22 297,600 20.22
8-Jun-06 19.3 20.21 19.25 20.15 1,641,200 20.15
7-Jun-06 21.35 21.41 21.21 21.29 684,700 21.29
6-Jun-06 22.15 22.15 21.51 21.67 299,000 21.67
5-Jun-06 22.51 22.51 21.89 21.91 293,300 21.91
2-Jun-06 22.63 22.78 22.5 22.7 754,100 22.7
1-Jun-06 21.9 22.24 21.86 22.24 281,200 22.24

**Criteria for selected rows (blue highlight) to copy is volume < 300,000

Result sheet should contain:

3-Jul-06 20.97 21.07 20.85 21.05 115,000 21.05
30-Jun-06 21.27 21.27 21.02 21.13 215,700 21.13
27-Jun-06 20.24 20.24 19.8 19.86 257,700 19.86
22-Jun-06 20.3 20.5 20.07 20.26 291,700 20.26
21-Jun-06 20.08 20.45 20.04 20.31 160,000 20.31
20-Jun-06 20.12 20.2 20 20.03 278,500 20.03
14-Jun-06 19.4 19.66 19.38 19.61 211,000 19.61
12-Jun-06 20.45 20.56 20.11 20.11 239,900 20.11
9-Jun-06 20.16 20.53 20.11 20.22 297,600 20.22
6-Jun-06 22.15 22.15 21.51 21.67 299,000 21.67
5-Jun-06 22.51 22.51 21.89 21.91 293,300 21.91
1-Jun-06 21.9 22.24 21.86 22.24 281,200 22.24

Algorithm

  1. Loop through the cells and evaluate your test expression. In this case the cells to test are in the Volume column.
  2. If the expression evaluates to false, move on to the next row value in our test column.
  3. If the expression evaluates to true, call a helper function CopyWorksheetRow (sample below) to copy the values of current row from original worksheet to the destination worksheet.

Helper Method


/// /// Copies values of up to 20 columns for a given row from the original sheet
/// to the destination sheet
/// /// Worksheet object from which to copy from
/// Worksheet object to which to copy to
/// Row number to copy from the origin to the
/// destination
public static void CopyWorksheetRow(Worksheet origin, Worksheet destination, int row_from)
{
// create an area of 1 row / 20 columns (Note our sample only has 7 columns)
Area selected_row = origin.CreateArea(row_from, 0, 1, 20);
// out of that area extract only the cells
// with values (Note this will select that area of only 7 columns)
Area populated_cells = selected_row.PopulatedCells;
for(int x = 0; x < populated_cells.ColumnCount; x++)
{
destination.Cells[row_to_start_at,x].Value = populated_cells[0, x].Value;
destination.Cells[row_to_start_at,x].Style = populated_cells[0, x].Style;
}
row_to_start_at++;
}

Code to test the condition


ExcelApplication xap = new ExcelApplication();
Workbook wb = xap.Open(Page.MapPath("datadoc.xls"));


Worksheet data_sheet = wb.Worksheets[0];
Worksheet filtered_datasheet = wb.Worksheets.CreateWorksheet("mysheet", 1);


// set up a loop to look throught the cells in column 6 (indexed as 5) from
// rows 6 to 71
for(int x = 5; x < 71; x++)
{
int cellval = int.Parse(data_sheet.Cells[x, 5].Value.ToString());


// if the cells value matches our criteria
// call a function to copy this row onto a separate sheet.
// in my case I want to copy the rows where
// the volume of stock traded is under 300,000
if(cellval < 300000)
{
CopyWorksheetRow(data_sheet, filtered_datasheet, data_sheet.Cells[x, 5].RowNumber);
}
}
//Save to disk on the server
xap.Save(wb, Page.MapPath("C:\\MyReports\\output.xls"));

Saving ExcelWriter and WordWriter files

Problem

What options are available for saving an ExcelWriter or WordWriter generated file?

Solution

ExcelTemplate.SaveExcelApplication.SaveWordTemplate.Save, and WordApplication.Save all have the same four output options:

  • Save to disk – saves the generated file on the server
  • Save to an IO stream – streams the file to the specified IO stream or class derived from System.IO.Stream
  • Stream to the client as an attachment
  • Stream to the client as an inline file – If the user is using Internet Explorer, the file will be opened in the browser using IE’s inline browsing option. Otherwise, the file will be streamed to client.

How to use Named Styles with WordApplication

Problem

In Microsoft Word, a named style is a collection of settings for formatting attributes such as font, spacing, borders, and bullet type. A named style can be applied to a paragraph, a table, a list or a block of characters. Word comes with a large set of built-in named styles, such as Body Text and various heading styles.

While you cannot create named styles in WordWriter, you can use the WordApplication class to access named styles and apply them to a paragraph or a table. How to use named styles depends on whether you are using the WordApplication class to create a new document or to open an existing document.

This article also addresses the situation in which NullReferenceException is thrown when you try to use certain built-in named styles (see the section “Using the NamedStyle.Builtin enumeration”).

Solution

How to apply a named style

A named style is represented by the NamedStyle class. Named styles associated with a document are contained in a Styles collection, which is returned by the Document.Styles property. The Length method of the Styles collection returns the number of styles.

To retrieve a NamedStyle object, index into the Document.Styles collection by one of the following:

  1. An integer index – Document.Styles[0]
  2. The name of the style – Document.Styles[“Body Text”]
  3. A value of the NamedStyle.Builtin enumeration – Document.Styles[NamedStyle.BuiltIn.BodyText]

The most convenient method is to use the NamedStyle.Builtin enumeration, but be aware of one issue. Refer to the “Using the NamedStyle.Builtin enumeration” for more information.

To apply a named style to a paragraph, pass a NamedStyle object as parameter to the InsertParagraphBefore and InsertParagraphAfter methods (if no named style is desired, pass null in C# or Nothing in VB.NET). Alternatively, assign a NamedStyle object to the Style property of a Paragraph object.

To apply a named style to a table, assign a NamedStyle object to the Style property of a Table object.

The code sample below illustrates the use of named styles:

//--- doc is a new or existing document
//--- Applying a named style to a paragraph
NamedStyle style1 = doc.Styles[NamedStyle.BuiltIn.BodyText];
Paragraph para = doc.InsertParagraphAfter(style1);
para.InsertTextAfter("The quick brown fox jumps over the lazy dog", true);


//--- Applying a named style to a table
//--- Contemporary table style must exist in document
NamedStyle style2 = doc.Styles[NamedStyle.BuiltIn.TableContemporary];
Table myTable = doc.InsertTableAfter(2, 3);
myTable.Style = style2;

If you wish to modify or add to the formatting settings provided by a named style, you can pass a ParagraphFormatting object as a second parameter to the InsertParagraphBefore or InsertParagraphAfter methods, or assign aTableFormatting object to the Formatting property of a table.

Using named styles when creating a new document

Before you can access and use a named style with WordApplication, the style must already be stored in the document. In Word 2003, you can view the styles stored in a document (select Format, Styles and Formatting from the Word menu, then select Available Styles from the Show drop-down list on the Styles and Formatting panel). You can also view all possible styles (select Show All Styles on the Styles and Formatting panel). Normally, the number of styles stored in a document is only a subset of all possible styles. A style is not stored with the document until it is applied.

When you create a new document with the WordApplication.Create method, WordWriter automatically includes a set of styles with the newly created document. To avoid overburdening the document with unnecessary styles, the document is given a set of 28 most commonly used styles. These are given below:

  1. Normal
  2. Heading 1
  3. Heading 2
  4. Heading 3
  5. Heading 4
  6. Heading 5
  7. Heading 6
  8. Heading 7
  9. Heading 8
  10. Heading 9
  11. Default Paragraph Font
  12. Table Normal
  13. No List
  14. Block Text
  15. Body Text
  16. Body Text 2
  17. Body Text 3
  18. Body Text First Indent
  19. Body Text Indent
  20. Body Text First Indent 2
  21. Body Text Indent 2
  22. Body Text Indent 3
  23. Closing
  24. Date
  25. E-mail Signature
  26. Emphasis
  27. Envelope Address
  28. Header

Using named styles when opening an exisiting document

When you open an existing document with the WordApplication.Open method, you have access to only those named styles which are stored in the document, including custom named styles that you create before saving the document. Using custom named styles is discussed in the next section.

When you create a blank document in Word (select File, New from the menu), the document contains as many styles as are defined in the Word template on which the document is based. For example, a blank document created from the default Normal.dot template contains only a handful of basic styles. By contrast, a blank document based on the Contemporary Report.dot template has as many as 98 predefined styles.

Note that a document based on the default Normal.dot template contains fewer predefined styles than a document created with the WordApplication.Create method.

If you apply other styles to the document which are different from the default styles, the additional styles will be associated with the document and accessible to WordApplication.

Using custom named styles

In Word, you can create custom named styles. When you open an existing document containing custom styles, the custom styles are stored in the document’s Styles collection. You can access a custom style using an integer index or the style’s name.

For example, if you open an existing document containing a custom style named “My Style,” you can retrieve the NamedStyle object for “My Style” with Document.Styles[“My Style”].

Using the NamedStyle.Builtin enumeration

To get a NamedStyle object, you can use a value of the NamedStyle.Builtin enumeration as an index into the Document.Styles collection. For example, the following statement returns the the NamedStyle object for the Body Text style:

NamedStyle style;
style = Document.Styles[NamedStyle.BuiltIn.BodyText];

The NamedStyle.Builtin enumeration lists all of the possible built-in named styles in Word. The Document.Styles collection contains only the styles that are stored in the document. Typically, there are many more possible built-in styles in Word than are stored in the document.

Consequently, when you index into the Document.Styles collection using a value of the NamedStyle.Builtin enumeration, you must select a style that belongs to the collection. Otherwise, if you choose a style that does not exist in the collection, a NullReferenceException will be thrown. Unfortunately, this mistake is easy to make, because Visual Studio’s IntelliSense lists all of the values in the NamedStyle.Builtin enumeration.

For a new document created with the WordApplication.Create method, choose a NamedStyle.Builtin enumeration value corresponding to one of the 28 styles listed above. If you want to use a different style from these 28 styles, do not create a new document with WordApplication.Create. Instead, use the following workaround:

  1. In Word, create a blank document
  2. Enter some text and apply all desired styles
  3. Delete the text and save the document
  4. Open the saved document with WordApplication.Open
  5. Access the explicitly applied named style which should now be stored with the document

For an existing document, you must know a priori the named styles that exist in the document and choose the corresponding NamedStyle.Builtin enumeration values.

How to use CopySheet with PivotTables

Problem

When a worksheet with a PivotTable is copied in Excel, the PivotTable data source is not updated. If the worksheet is copied within a workbook, then the data sources of the original and copied PivotTables point to the same area. If the worksheet is copied to a new workbook, the data source of the copied PivotTable will point to the area in the original workbook. For example: “[OldWorkbookName.xlsx]OriginalWorksheet!A1:C10″.

ExcelWriter’s CopySheet follows this behavior. If a worksheet that contains a PivotTable is copied with CopySheet, the data source will not be updated.

Solution

Starting in 8.4, ExcelApplication has the ability to change the data source of a PivotTable. Use PivotTable.ChangeDataSource to update the data source of a copied PivotTable.

Example:

PivotTable pt = ws.PivotTables["PivotTable1"];
Area data_area = ws_new_data.CreateArea("A1:G10");
pt.ChangeDataSource(data_area);

Note

PivotTables are sensitive to the column header names in the data source for a PivotTable. If a column header name is changed, the source field associated with that column will get updated, and any PivotTableFields that were created using that source field will be removed from the PivotTable.

This happens in Excel as well.

When changing the data source of the PivotTable, if the new data source is missing any of the columns from the original data source, then fields created from the missing columns will be removed from the PivotTable.

For example, if PivotTable1 has 3 columns: Col1, Col2, and Col3. Changing the data source to an area that has 3 columns: Col1, Col2, and ColC, will cause any fields that were created from Col3 to be removed from the PivotTable.

How to unmerge a group of cells with ExcelWriter

Problem

Starting in ExcelWriter 6.8.1, the ability to unmerge cells in a worksheet as introduced with the Cell.Unmerge method. The Cell.IsMerged has also been added to determine whether or not a given cell is merged.

This post covers some examples of unmerging an individual cell or an area of cells.

Solution

Unmerging a single cell


Cell cell = wb.Worksheets[0].Cells[0,0];
if(cell.IsMerged) cell.Unmerge();

Unmerging an Area of cells

Although the Merge method is on the Area object, the Unmerge method is only on the Cell object. This is because each cell knows whether it is part of a merged cell, but a given area can be defined to include both merged and unmerged cells.

There are two ways to unmerge an area of cells:

  1. If you know exactly where the merged cells are, you can unmerge any one cell and the entire merged area will be unmerged.
  2. Or, loop through all the cells in an area to find merged cells and unmerge them.

C# example:

Area a = wb.Worksheets[0].CreateArea("A1:C4");
for(int i=0; i<a.RowCount-1; i++)
{
for(int j=0;j<a.ColumnCount-1;j++)
Cell cell = wb.Worksheets[0].Cells[i,j];
if(cell.IsMerged) { cell.Unmerge(); }
}
}

For more information about merging cells, refer to the Area.MergeCells() documentation.