Problem

When using PivotTables in ExcelTemplate in OOXML files (XLSX, XLSM), the drop-down filters show data marker values:

Solution

The default PivotTable behavior is to cache the filter items, even when they are no longer in the data source.

To make sure that this cached information is cleared when the PivotTable refreshes:

1. In your template file, right-click on the PivotTable and select PivotTable options
2. Go to the Data tab.
3. Under Number of items to retain per field select None from the drop-down.

Make sure that Refresh data when opening file is also checked so that the PivotTable refreshes with the imported data.

When you populate the file with data, the data markers should be gone from the drop-down:

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

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...

Problem

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

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:

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

Result sheet should contain:

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