.
ExcelWriter is a powerful tool for generating and manipulating Excel files. The files are generated in memory then 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. 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 the ExcelApplication object , every cell "hydrated" by our object model requires 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.
Here is a table showing approximately how much memory is required for large reports with different column/row combinations:
rows |
columns |
cells |
memory (bytes) |
memory (MB) |
|---|---|---|---|---|
1,000 |
10 |
10,000 |
4,000,000 |
4 |
20,000 |
20 |
400,000 |
160,000,000 |
153 |
50,000 |
20 |
1,000,000 |
400,000,000 |
381 |
50,000 |
30 |
1,500,000 |
600,000,000 |
572 |
Referencing Cells
Last version of the product
ExcelTemplate with ExcelApplication - see Preprocessing/postprocessing for more information
Cache frequently
Global styles - see effectively using styles
Set formating on columns/rows instead of cells - see effectively using styles
Use InsertRows instead of InsertRow
Avoid calling AutoFitWidth on a lot of data
Use DataReaders instead of DataTables