.
...
ExcelWriter is a powerful tool for generating and manipulating Excel files. The files are generated in memory then Files can be saved to disk or streamed them to the user over HTTP. In order to provide maximum flexibility for manipulating every element of a spreadsheet, ExcelWriter (in particular ExcelApplication) has a rich object model that must be populated at runtime and requires sufficient memory.
| Excerpt |
|---|
It is important to understand that the memory required to process a large report is much greater than the size of eventual output file. |
In Any cell loaded by the ExcelApplication object , every cell "hydrated" by our object model requires model may require up to 400 bytes of memory for the various objects associated with each cell for values, formulas, formatting, etc. The number of cells equals the number of rows times the number of columns. So if you have a large report with 50,000 rows by 20 columns, that's 1 million cells and it can use up 300-400 MB of memory to process just one request for that report.
...
Best Practices for Large Reports
Avoid Referencing Empty Cells
As noted previously, this cell will occupy up to 400 bytes of memory because several other objects have to be instantiated to hold the cell's attributes. Consequently, you should avoid looping through cells that might be empty, since doing so will create Cell objects that you may not need.
Use ExcelTemplate with ExcelApplication
To conserve memory, we recommend pre-processing a partial template with the ExcelApplication before passing the file to ExcelTemplate for importing data. See Preprocessing vs. PostProcessing.
...
If the report contains sensitive data, you will want to take security precautions when using this approach. Since the report will already be pregenerated generated and saved to disk, it could potentially be easier for an intruder to gain access to it. You may want to use a second server that is not public facing to act as a file server. This file server would only grant access to the report when it is requested by your web application.
When a user requests an Excel document, you can first check to see if there is new data. If there is, you can generate a new report and save it to disk. You can then stream that file to the user. We have a KB article which describes how to stream a file to the user that was previously saved to the disk with ExcelWriter.
...
Effective Use of Styles
...
Every time Cell.Style is accessed, ExcelWriter instantiates a separate Style object. To reduce file size bloating, we recommend using Global styles and setting styles on groups of cells. For details, see Effective Use of Styles.
...
The Worksheet.ImportData method takes several different kinds of data types. DataReaders will read data directly from your data source into ExcelApplication's object model, and consequently use less memory and time than other data types. DataTables, DataViews, and arrays must all create an object in memory which contains the data before you can pass that object to ExcelApplication. ExcelApplication will then import the data into its object model. As a result, the data is stored in memory twice for these data types, as opposed to the DataReader where it is stored only once.
DataTables, DataViews, and arrays all use approximately the same amount of memory; however, there is a slight difference in speed. Two dimensional and jagged object arrays will be imported slightly faster, while DataTables and DataViews take approximately 10% longer to import. However, DataReaders remain the fastest, since they do not need to read the data into an object before importing it into ExcelApplication.
...
Use the newest version OfficeWriter
We are always working to improve the efficiency of ExcelWriter. Make sure to use the latest version of the product to take advantage of these improvements. See the ExcelWriter Change Log for more details.