Background

ExcelTemplate was designed for importing large amounts of data into Excel workbooks. ExcelApplication was designed for the purpose of creating more dynamic content, but as a consequence it is more memory intensive. To minimize the amount of memory that's being consumed when using ExcelApplication, we recommend:

1. Create a partial template
2. Use ExcelApplication to dynamically modify the template
3. Use ExcelTemplate to import large amounts of data
4. Only postprocess when you need to

For example, you can create a Workbook with ExcelApplication and insert the formatting, charts, or dynamic content that you want to appear in the final workbook, along with data markers where you want the data to appear. You can then open this Workbook with ExcelTemplate to populate the data.

When should I be postprocessing?

Postprocessing should only be done when there are changes that need to be made based on the data that was imported, or when preprocessing is not available.

An example of formatting changes that depend on the data is using AutoFitWidth() on a column of data. AutoFitWidth needs to have the data in the column in order to calculate the column width. Calling AutoFitWidth before the data is imported will not have the desired effect.

An example of when preprocessing is not available is combining multiple existing files into a single workbook. This is common when using OfficeWriter for Reporting Services because the only way to access the reports with ExcelApplication is after they have already been rendered with Reporting Services and are full of imported data.

In general, it's best to avoid post-processing as a report with all of its imported data can be much larger than the original template file. This means that ExcelApplication may consume a much larger amount of memory compared to the same operation performed on the original template. There is more information on how to reduce memory usage in Best Practices with Large Reports.