Use InsertRows/Columns instead of InsertRow/Column

Why this might happen:

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

Issues with this method:

• 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.
• Performance affected: Time

Solutions:

• 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.

Avoid calling AutoFitWidth on a large amount of data

Why this might happen:

• 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.

Issue with this method:

• Since AutoFitWidth has to loop through all of the values in a column to determine which has the longest length, it often becomes very time consuming to call it on larger data sets.
• Performance affected: Time

Solutions:

• 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.

Cache Frequently

Why this might happen:

• Most documents are set up this way by default, as is in many cases it is not much of an issue.

Issue with this method:

• A document that is called a lot and is set to run whenever someone calls it may result in issues if it gets to be large enough in size, since it has to retrieve the information each time.
• Performance affected: Time

Solutions:

• 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.)