Category Archives: ExcelWriter

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

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
}


ExcelWriter, Knowledge Base

How to create a chart with different chart types

Problem

ExcelWriter supports creating custom charts, including charts that contain several different chart types. For example, a chart wtih two area series and one line series.

Solution

This code snippet creates a chart with two area series and one line series:

Chart chart = ws.Charts.CreateChart(ChartType.Area.StandardArea,ws.CreateAnchor(5, 0, 0, 0));
chart.SeriesCollection.CreateSeries("A1:A3");
chart.SeriesCollection.CreateSeries("B1:B3", ChartType.Area.StandardArea, AxisType.Primary);
chart.SeriesCollection.CreateSeries("C1:C3", ChartType.Line.StandardLine, AxisType.Primary);

ExcelWriter, Knowledge Base

Optimizing ExcelApplication performance to reduce memory or time usage

Problem

Your program, which uses the ExcelApplication object, is using more memory than you would like or is taking too long to generate a report.

Solution

The Best Pratices with Large Reports article in our documentation discusses how to optimize for large ExcelApplication reports by reducing memory or run time. It includes, but is not limited to:

  • What to avoid when referencing cells, inserting rows or columns, and applying styles
  • Certain methods are known to be memory intensive, such as AutoFitWidth
  • Ways to improve performance by changing how and when data is imported

There are also code examples that compare inefficient code and code that was improved by the recommendations in the article. These code examples are split up into Memory Related Performance Issues and Time Related Performance Issues.

ExcelWriter, Knowledge Base

How to apply auto filters with ExcelApplication

Details

Auto filters are an Excel feature that can be applied to a column of data that allows users to select what rows of data they want visible for a given area on a worksheet.

The ability to apply auto filters programmatically with ExcelApplication in XLSX and XLSM files was added in OfficeWriter 8.3. This post covers how to add auto filters with the ExcelWriter API.

Solution

  1. Define the Area that the auto filter will be applied to.
  2. Get a handle on the AutoFilter object for the Worksheet that will have the auto filter.
  3. Set the worksheet’s AutoFilter.Area to the area that was defined earlier.

Here is the code:

Area filter_area = ws.PopulatedCells; //Returns a continuous area of all the populated cells
AutoFilter filter = ws.AutoFilter;
filter.Area = filter_area;
Here is the code in a single line:
ws.AutoFilter.Area = ws.CreateArea("A1:H1");

Additional Resources

To remove the auto filter from a worksheet, use AutoFilter.Clear():
ws.AutoFilter.Clear();
A few things to bear in mind:

  • A worksheet can only contain one area with auto filters. If you set Worksheet.AutoFilter.Area, you will overwrite any existing auto filters.
  • You must specify the entire area that you want to apply the filters to. ExcelWriter will not automatically detect blank rows and columns. We recommend Worksheet.PopulatedCells for getting a handle on all the populated cells in a worksheet.

ExcelWriter, Knowledge Base

How to use ASP.NET MVC with OfficeWriter

Problem

A number of customers have asked about support for ASP.NET MVC. With the introduction of ASP.NET MVC, the method to send OfficeWriter-generated files to the user requires a few more lines of code. In ASP.NET WebForms, OfficeWriter can write directly to the HttpResponse stream via the Save method, allowing for a one-line implementation. In MVC, all the functions from a Controller return an ActionResult, and it requires a different approach to return the file to the user.

Solution

To facilitate file downloads, we need an ActionResult to return the file generated by OfficeWriter. For use in ASP.NET MVC, we need to return our file via an ActionResult for the Controller’s action; the specific type used below for doing that is the FileStreamResult by using OfficeWriter to get a stream of the file.

The overview of what we need to do is summarized in the algorithm below:

  1. Create the appropriate OfficeWriter object.
  2. Get the data into the file, using the various data binding methods like ExcelTemplate.BindData and WordTemplate.SetDataSource.
  3. Process the file.
  4. Save the file to an in-memory Stream (using MemoryStream); check out ExcelTemplate.Save, ExcelApplication.Save, WordTemplate.Save, and WordApplication.Save.
  5. Return the FileStreamResult, set to use the in-memory Stream.

The following is a C# sample that uses ExcelTemplate. This general approach also works for ExcelApplication, WordTemplate, and WordApplication.

public ActionResult Download()
{
if (ModelState.IsValid)
{
var excelTemplate = new ExcelTemplate();
excelTemplate.Open(Server.MapPath("~/Templates/Download_Template.xlsx"));

// get the data into the file
excelTemplate.Process();
var stream = new MemoryStream();
excelTemplate.Save(stream);

// make sure to reset the position because we will be reading it again
stream.Position = 0;
var actionResult = new FileStreamResult( stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" );
actionResult.FileDownloadName = "Download.xlsx"; return actionResult;
} return View();
}
The above code would be placed inside a Controller for the Download action. When this action is activated, a file download prompt will appear for the user with the file name that was given to the FileStreamResult.

ExcelWriter, Knowledge Base

How to create a dual axis chart from bar charts

Problem

With the ExcelApplication object, it is possible to create and manipulate charts, including bar charts with series on both the primary and secondary axes. In this example, we will cover how to use OfficeWriter to create a dual axis bar chart.

Note: The ExcelApplication object is only available in OfficeWriter Enterprise Edition (EE).

Solution

This example assumes that you are familiar with creating charts with ExcelApplication. If you’re not, we recommend that you check out Charts in ExcelApplication.

Let’s start with given data in worksheet Sheet1:

The following code snippet creates a bar chart with two series from that data:

Worksheet wkst = wb.Worksheets["Sheet1"];
Anchor anchr = wkst.CreateAnchor(5, 5, 0, 0);

//Create the chart
Chart chrt = wkst.Charts.CreateChart(ChartType.Bar.Clustered, anchr);

//Set the category axis data for the chart
chrt.SeriesCollection.CategoryData = "A2:A4";

//Create a cluster bar series on the primary axis
Series series1 = chrt.SeriesCollection.CreateSeries("B2:B4", ChartType.Bar.Clustered, AxisType.Primary);

//Create a cluster bar series on the secondary axis
Series series2 = chrt.SeriesCollection.CreateSeries("C2:C4", ChartType.Bar.Clustered, AxisType.Secondary);

If you are working with an existing chart, you can set the Series.AxisType property to AxisType.Secondary.

 series2.AxisType = AxisType.Secondary 
This will produce a chart that looks like the following:

The reason why only one series appears on the chart is because of an issue with how Excel displays bar and column charts on two axes.

To get around this, you should pad your data with zeroed values which act as placeholders for the bar or column chart series. Peltier Tech has a blog post with details on how to show a Column Chart on Two Axes. These principles also apply to bar charts.

This workaround can also be applied to OfficeWriter charts.

Dual Axis Chart Workaround

1. Pad the data to include the zeroed columns.

2. Add code for the place holder series on the primary and secondary axes


//Create a cluster bar series on the primary axis
Series series1 = chrt.SeriesCollection.CreateSeries("B2:B4", ChartType.Bar.Clustered, AxisType.Primary);

//Create the zeroed placeholder series for the primary axis
Series series2_pad = chrt.SeriesCollection.CreateSeries("C2:C4", ChartType.Bar.Clustered, AxisType.Primary);

//Create the zeroed placeholder series for the secondary axis
Series series1_pad = chrt.SeriesCollection.CreateSeries("D2:D4", ChartType.Bar.Clustered, AxisType.Secondary);

//Create a cluster bar series on the secondary axis
Series series2 = chrt.SeriesCollection.CreateSeries("E2:E4", ChartType.Bar.Clustered, AxisType.Secondary);
3. Add code to hide the legend entries for the placeholder series:
//Hide the placeholder legend entries
series2_pad.ShowLegendEntry = false;
series1_pad.ShowLegendEntry = false;
The final chart should display both series correctly on the separate axes:

Note: The change in color is because there are technically 4 series in the chart and Excel’s automatic coloring reflects this.

ExcelWriter, Knowledge Base

How to format alternating color rows with ExcelApplication

Problem

A common way to display data in Excel is to alternate the background color of every other row when displaying a large table of data. With ExcelWriter there are multiple ways to accomplish this. This post covers some possible ways to apply alternating row colors with ExcelApplication.

There is another post that discusses how to do this with ExcelTemplate.

Solution

In versions of ExcelApplication prior to 6.5.1, the only way to set alternating background colors on a table was to loop over all the rows you wanted to format and set their background colors one at a time. This can take a long time for large amounts of data, and requires quite a bit of code. With the introduction of the ConditionalFormat object in ExcelWriter 6.5.1, alternating row colors in a Range with the ExcelApplication object is much simpler and faster.

The first step is to choose colors for the odd and even rows. In this example I’ll color even-numbered rows a light blue and odd-numbered rows white. You can use any colors you like, as long as they exist in the Palette or you add them yourself.

The second step is to create the ConditionalFormat object and the conditions within it. Each Condition has a comparison rule that gets set up in the constructor and a Style object that will be applied to each cell for which that rule is true. To alternate row colors we need two Conditions: one that will format the odd-numbered rows and one for the even-numbered rows.

To do this we use the FormulaEvaluation comparison type and create formulas that evaluate to true depending on the numbering of the row. The MOD() function returns the remainder left after dividing its first argument by its second, and the ROW() function returns the 1-indexed number of the current cell’s row. Thus, MOD(ROW(), 2)=1 will be true for all odd-numbered cells, and MOD(ROW(), 2)=0 will be true for odd-numbered cells.

After creating the conditions, we change the formatting of the Style objects associated with them. For this example all we’ll do is change the background color, but we could apply any formatting that can be set with through the Style object. As in the example below, we suggest that you modify the existing Condition.Style object rather than creating a new Style object and setting Condition.Style to it, as some attributes are not preserved properly with the second method.

The final step is to create the Range this formatting should be applied to and associate the ConditionalFormat with the Range. The following code illustrates these steps and produces the screenshot below.


// Create the colors we'll use on alternating rows. In Excel you can only
// use colors in the Palette, so we need to create a Palette object and use
// that to get the colors.
Palette p = wb.Palette;
Color evenColor = p.GetClosestColor(153, 204, 255);
Color oddColor = p.GetClosestColor(255, 255, 255);

// Create the conditional format and its conditions. Each condition
// has a formula that can evaluate to true or false.
ConditionalFormat oddEvenColors = wb.CreateConditionalFormat();
Condition evenRows;
evenRows = oddEvenColors.CreateCondition(Condition.Comparison.FormulaEvaluation, "=MOD(ROW(), 2)=0");
Condition oddRows; oddRows = oddEvenColors.CreateCondition(Condition.Comparison.FormulaEvaluation, "=MOD(ROW(), 2)=1");

// Change the background color for each condition. This is what will change
// for the cells where the condition is true.
evenRows.Style.BackgroundColor = evenColor;
oddRows.Style.BackgroundColor = oddColor;

// Create the range we want to format and set the formatting.
Range toFormat = ws.CreateRange("A1:F10");
toFormat.SetConditionalFormat(oddEvenColors);

Note that the conditional formatting is applied rather than set. This means that only the differences between any existing styles and the conditional formatting will take effect. For example, if some of the cells in the range had a border or had changed the font size, those changes will not be affected when we change the background color. See our documentation for more on setting and applying styles.

ExcelWriter, Knowledge Base

How to autofit all cells in a workbook when using the ExcelTemplate object

Problem

When using the ExcelTemplate object to populate a spreadsheet with data, you may want to AutoFit the row heights or column widths to better display the data in the cells. The AutoFit needs to be done after all the data has been populated.

ExcelTemplate is a small, light-weight object model that is designed to populate data very quickly. ExcelTemplate does not provide AutoFit functionality, because that would require parsing the entire spreadsheet and creating objects for every single cell, the way the ExcelApplication object does with its much larger object model.

Solution

One solution is to pass the workbook to the ExcelApplication object after it has been populated with data, and then perform the AutoFit (using ColumnProperties.AutoFitWidth or RowProperties.AutoFitHeight). However, this can have an impact on performance, particularly if the workbook contains a lot of data.

If you want to avoid reopening the workbook on the server with the ExcelApplication object, another solution is to use a macro that will run when the user opens the file on the client machine. The following VBA code can be placed in your template spreadsheet to AutoFit all of the cells in a workbook. The code should be placed in the ThisWorkbook object in the VBA Project for the spreadsheet.


Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In Me.Worksheets ws.Cells.Columns.AutoFit ws.Cells.Rows.AutoFit Next End Sub

ExcelWriter, Knowledge Base

How to hide a worksheet with ExcelApplication

Problem

A worksheet may contain proprietary data or algorithms that developers wish to hide from end-users. Different methods of hiding your data exist, but some methods are more secure than others.

Solution

An ExcelWriter workheet can be hidden by setting the Worksheet.Visibility property to Worksheet.SheetVisibility.Hidden. However, the worksheet can be un-hidden by users in Excel.

To make the worksheet more hidden, set Worksheet.Visibility to Worksheet.SheetVisibility.VeryHidden, in which case the worksheet will remain hidden unless the end-user writes a new VBA macro to unhide it.

Therefore, to keep a worksheet hidden, the developer must prevent end-users from creating new macros for the workbook as well. To do this, open the workbook’s macro editor and add a password to lock the project.

Password Protecting VBA in Excel 2007/2010

  1. Go to the Developer tab in the ribbon
  2. Go to Visual Basic
  3. Go to Tools > VBAProject Properties > Protection
  4. Check Lock project for viewing and add a password

Password Protecting VBA in Excel 2003

  1. Go to Tools > Macro > Macros
  2. Edit an existing macro
  3. In the macro editor, right-click the VBA project.
  4. Go to VBA Project Properties > Protection.
  5. Check the checkbox and add a password.

This will make it impossible for the user to look at your existing macros or to add a macro to unhide a sheet.