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

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.

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.

SoftArtisans at SharePoint Saturday The Conference

Mr. Jones Goes to Washington

Plus three more of us, but Ben’s surname is most conducive to Capra-homage. Anyways, this Wednesday through Saturday, the SA Crew is storming das Kapital/Annendale, VA with the lofty intentions of a) getting to know the people behind the avatars, b) talking shop, which in our case translates to demonstrating some of the things you can do when you integrate OfficeWriter into SharePoint, c) learning about everything from Office365 to migration to branding to unicorns, d) blasting our speaker-sourced SPSTCDC playlist all day, urry day. If you have some downtime between sessions, please stop by booth 320 to hang out/bust some moves/ ogle some schmancy SharePoint-to-Office reports with us. Also, if you’re a developer, admin or BI analyst, you should definitely check out Ben’s two sessions on  custom workflow actions and advanced document processing with OfficeWriter in SharePoint: Continue reading SoftArtisans at SharePoint Saturday The Conference

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.

Blogged