.
...
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.
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.
Cache frequently
If you have large reports which are requested often but whose data changes infrequently, you may want to consider using ExcelApplication 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 at http://support.softartisans.com/kbview_932.aspx which describes how to stream a file to the user that was previously saved to the disk with ExcelApplicationExcelWriter.
Using Styles Effectively
Global styles - see effectively using styles
Set formating on columns/rows instead of cells - see effectively using stylesSee Effetive 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:C VB.NET
| Csharp | ||||
|---|---|---|---|---|
| ||||
//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); |
| Vbnet | ||||
|---|---|---|---|---|
| ||||
'Where you want to insert new rows 'Determine how many rows you'll need to insert 'Insert the desired number of rows. This will be much faster |
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:C VB.NET
| Csharp | ||||
|---|---|---|---|---|
| ||||
//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; |
| Vbnet | ||||
|---|---|---|---|---|
| ||||
'Used to store the maximum length, in characters 'Loop through each row of data 'Get the column properties for the column we want to adjust the width of 'Set the width in characters to the maximum |
While this will not be as accurate as AutoFitWidth, it will be significantly faster.
...
Last version of the product
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.