Page History

Pre-processing uses a lot less memory and takes a lot less time than post-processing

From KB Article

Using ExcelTemplate alongside ExcelApplication
As discussed in the introduction, ExcelApplication Preprocessing refers to using the ExcelApplication object to modify an existing partial template file before passing the template file to ExcelTemplate for importing data.
Postprocessing refers to using the ExcelApplication object to modify a workbook that has already been populated with data.

ExcelApplication was designed for the purpose of creating more dynamic content, but as a consequence it is more memory intensive. ExcelTemplate, on the other hand, was designed for the purpose of importing large amounts of data, and much less memory is required per cell for an ExcelTemplate object. If you are experiencing memory problems with ExcelApplication because you have a large amount of data, the best option is often to use ExcelApplication to create a template, and then use the ExcelTemplate object to actually import the data.To improve the amount of memory that's being consumed, 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 needing to combine 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, avoid post-processing because the larger a report is, the more memory ExcelApplication will consume. There is more information on how to reduce memory usage in Best Practices with Large Reports.