...

• Someone is adding multiple rows into a template file yet is unaware that both InsertRow and InsertRows are legitimate functions (same with InsertColumn(s)).

  Code Block
  /* INCORRECT CODE: for (int i = 1; i < 50; i++) { ws.InsertRow(i + 5); ws.InsertColumn(i + 5); } */

  Why this method causes issues:
• Every time a call is sent for InsertRow, InsertRows, InsertColumn, DeleteRow, etc., it requires the updating of the entire grid to calculate the new position of all rows/columns beyond the adjusted ones.
• Grid updates also require that any formulas that point to something beyond the adjusted position have to be updated to compensate.

...

• If adding more than just one or two rows, use InsertRows/InsertColumns instead of looping through InsertRow/Column multiple times.
• InsertRow/Column can still be used, but as grid updates are expensive, they should be retained only for calls of one or two new lines.

  Code Block
  // POSSIBLE CORRECT CODE: // Example inserting a single row and column to split up data. ws.InsertRow(1); ws.InsertColumn(1); // Example inserting multiple rows and columns to move data down. ws.InsertRows(0, 10); ws.InsertColumns(0, 10);

Avoid Calling AutoFitWidth on a Large Amount of Data

...

• As AutoFitWidth sets the width of a column to just beyond the length of the longest cell, and as such can be seen as a very simple way to format items.

  Code Block
  /* INCORRECT CODE: ColumnProperties columnProperties; for (int i = 0; i < 3; i++) { columnProperties = ws.GetColumnProperties(i); columnProperties.AutoFitWidth(); } */

Why this method causes issues:

...

• AutoFitWidth can still be called on very small amounts of data, but if it's even debatable whether a data might be too large, it should be avoided.
• Determine a likely possible max length for populated cells, and adjust column widths beforehand (may not look as nice, but runs much better).
• You can loop through the DataTable manually.  Although you are still looping through all the data, DataTables are simpler to loop through than entire worksheets.
• Be wary of the fact that datamarkers allow the order of columns in the data source and in the resulting worksheet to be different.  This method only workers if you are sure of a column's location in both the original DataTable and in the output file.
• If estimating, remember to account for any formatting that may be applied, such as currency formatting.

  Code Block

  // POSSIBLE CORRECT 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()); // Column on which the custom autofit 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 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; } } } // 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]; } }

Cache Frequently

What might cause this to happen:

...

• Check to see if there is new info or not; if there is, update; if not, use last version.
• Save a copy of the Output each time it receives new data to be used until more new data is added.

Downloads

You can download the code for the best performance samples as a Visual Studio solution.

• (Link to code goes here.)

Memory Related Performance Issues

...