Page History

...

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.

Apply Styles to

...

Columns and Rows, not

...

Cells and Areas

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

Applying a style to an Area also creates style objects for each individual cell in the Area. However, if you apply a style to a column or row, there will be only one formatting record for the entire column or row, which is much more efficient. Use ColumnProperties.Style or RowProperties.Style

Use InsertRows or InsertColumns instead of InsertRow or InsertColumn

The Worksheet class has both an InsertRows and an InsertRow method, InsertColumns, InsertRow, and InsertColumn methods. 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. The same applies for columns. For example:

Code Block

language	c#

 //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);

...

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:

Code Block

//Used toPOSSIBLE store the maximum length, in characters
int maxChars = 0CORRECT CODE:

DataTable dts = GetData(Page.MapPath(@"data\PersonsInfoV2.csv"));

// Column on which the custom autofit starts, to be set by user.
int startingColumn = int.Parse(dts.Rows[0][9].ToString());

//Loop throughColumn eachon rowwhich ofthe datacustom 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 cellautofit ends, to be set by user.  (Should be set to the row AFTER the last column to be autofit.)
int endColumn = int.Parse(dts.Rows[1][9].ToString());

// Array of the longest variables in each column.
int[] longest;

// Temporary variable for keeping track of current location
int temp = 0;

// The loop to check on the width of each column.  Only start if the given starting and ending places are valid.
if ((startingColumn < dts.Columns.Count)&&(endColumn < dts.Columns.Count)&&(startingColumn < endColumn))
{
    // Find the size of the array, based on the starting location.
    longest =  new int[endColumn - startingColumn];

    // For each row in the DataTable...
    foreach (DataRow row in dts.Rows)
    {
        // For each column, based on that row...
        for (int i = startingColumn; i < endColumn; i++)
        {
            // Find the temporary length of that row.
            temp = row[i].ToString().Length;

            // Is it the longest in the column has
columnProperties.WidthInChars = maxChars;

Code Block

language	vb

 '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( _so far?  If yes, set it as such.  (Longest subtracts startColumn from index to keep starting index at 0.)
            if (temp > longest[i-startingColumn])
            {
                longest[i-startingColumn] = temp;
         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
    // After looping through, set the width of each column to the longest.
    // You can change this function to change where in the output file
    for (int i = startingColumn; i < endColumn; i++)
    {
        ColumnProperties columnProperties;
        columnProperties = ws.GetColumnProperties(i);
        columnProperties.WidthInChars = longest[i-startingColumn];
    }
}

While this will not be as accurate as AutoFitWidth, it will be significantly faster.

...

Space Shortcuts

Page tree

Versions Compared

Old Version 1

New Version Current

Key

Apply Styles to

Columns and Rows, not

Cells and Areas

Use InsertRows or InsertColumns instead of InsertRow or InsertColumn