...
Table of Contents
Table of Contents |
---|
Introduction
ExcelWriter is a powerful tool for generating and manipulating Excel files. Files can be saved to disk or streamed 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. |
Any cell loaded by the ExcelApplication object 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.
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 |
Best Practices for Large Reports
...
If you have a complex workbook with many sheets and you aren't sure how large it is, here's a simple macro you can use for calculating the total number of cells and approximate memory required:
Code Block | ||||
---|---|---|---|---|
| ||||
Dim cellcount As Double
Dim Sheet
cellcount = 0
For Each Sheet In ActiveWorkbook.Worksheets
cellcount = cellcount + Sheet.UsedRange.Cells.Count
Next
Dim message As String
message = "Total cells in workbook: " & vbCrLf & FormatNumber(Round(cellcount), 0) & vbCrLf
message = message & "Approximate max memory required (at 400 bytes per cell): " & vbCrLf
message = message & FormatNumber(Round(cellcount * 400), 0) & " bytes " & vbCrLf
message = message & Math.Round(((cellcount / 1024 / 1024 / 1024) * 400), 2) & " gigabytes"
MsgBox (message)
End Sub |
Best Practices
Avoid Referenceing 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 ExcelApplication before ExcelTemplate
...
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.Postprocessing
Cache
...
Frequently
If you have large reports which are requested often but whose data changes infrequently, you may want to consider using ExcelWriter to generate the document once and then stream it to multiple users. This may be appropriate if the data is changed on a predictable schedule, or it is easy to check to see if the data has been changed. If you want the user to see the most recent data, then you will need to know when new data is available so you can regenerate the report.
...
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 which describes how to stream a file to the user that was previously saved to the disk with ExcelWriter.
...
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.
Use InsertRows instead of InsertRow
The Worksheet class has both an InsertRows and an InsertRow method. If you are only inserting one row, then you should use InsertRow; however, if you are inserting multiple rows, you should make one call to InsertRows and pass the number of rows that you want to insert. For example:
Csharpcode | ||
---|---|---|
| ||
//Where you want to insert new rows int atRow = 10; //Determine how many rows you'll need to insert int numRowsToInsert = 4; //Insert the desired number of rows. This will be much faster //than multiple calls to InsertRow. ws.InsertRows(atRow, numRowsToInsert); |
Vbnetcode | ||
---|---|---|
| ||
'Where you want to insert new rows Dim atRow As Integer = 10 'Determine how many rows you'll need to insert Dim numRowsToInsert As Integer = 4 'Insert the desired number of rows. This will be much faster 'than multiple calls to InsertRow. ws.InsertRows(atRow, numRowsToInsert) |
Avoid calling AutoFitWidth on a lot of data
...
If you have a large area that you want to autofit, and do not know beforehand how wide the cell contents might be, it will be much faster to find the longest string and then set the width of the columns in characters. For example, if you have several thousand rows, then you could say:
Csharp | 3 | 3 |
---|
Code Block |
//Used to store the maximum length, in characters int maxChars = 0; //Loop through each row of data for (int row = 0; row < lastRow; row++) { //If this cell contains more characters than //our previous maximum, then update maxChars maxChars = Math.Max( ws.Cells[row, columnNumber].Value.ToString().Length, maxChars); } //Get the column properties for the column we want to adjust the width of ColumnProperties columnProperties = ws.GetColumnProperties(columnNumber); //Set the width in characters to the maximum //number of characters a cell in the column has columnProperties.WidthInChars = maxChars; |
Vbnetcode | ||
---|---|---|
| ||
'Used to store the maximum length, in characters Dim maxChars As Integer = 0 'Loop through each row of data For row As Integer = 0 To lastRow 'If this cell contains more characters than 'our previous maximum, then update maxChars maxChars = Math.Max(_ _ ws.Cells(row, columnNumber).Value.ToString().Length,_ _ maxChars) Next 'Get the column properties for the column we want to adjust the width of Dim columnProperties As ColumnProperties = ws.GetColumnProperties(columnNumber) 'Set the width in characters to the maximum 'number of characters a cell in the column has columnProperties.WidthInChars = maxChars |
While this will not be as accurate as AutoFitWidth, it will be significantly faster.
...
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 OfficeWriter Change Log for more details.
Example Code
When streamlining a report, it is very important to know which performance issues tend to be the most problematic and the best practices for dealing with those issues.
Below is a collection of common errors that may negatively affect performance, as well as examples of best practices for each situation. There are a number of issues that can slow performance. The examples mention which aspect of performance they are most related to:
Time based issues, where a report is taking longer to process than would seem normal, yet otherwise runs fine.
Memory based issues, where the report seems to be using more of the systems resources then necessary.
Overlap between these two issues is very common; when experiencing performance issues, it is recommended that all of the samples be considered.
Section | ||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|