...

• If the workbook formatting needs to be modified dynamically at runtime, the changes should be made in the unpopulated template before populating the data, rather than to the populated file.
• Design the template file, load it in ExcelApplication and make any other customizations desired, then pass it on to ExcelTemplate to load in the data.

  Code Block
  // POSSIBLE CORRECT CODE: ExcelApplication xla = new ExcelApplication(); Workbook wb = xla.Open(Page.MapPath(@"path\to\code.xlsx")); /*...*/ ExcelTemplate xlt = new ExcelTemplate(); xlt.Open(xla, wb); xlt.Process(); xlt.Save(Page.Response, "Completed.xlsx", false);

...

• Don't vaguely guess how much area a data set might take, try to narrow down the area as much as possible towards only populated data.
• Start with the range of populated cells so you are only looping through theirs.

  Code Block
  // POSSIBLE CORRECT CODE: Area testArea = ws.CreateArea(1,1,20,4); // Create areas that are as accurately sized to the data as possible, as opposed to vastly overscaled.

...

• Loop through a large number of cells, setting styles on each cell individually.
• Set group of cells to an area, then apply that style to the area.

  Code Block

  /* INCORRECT CODE: Style randDataStyle = wb.CreateStyle(); randDataStyle.BackgroundColor = wb.Palette.GetClosestColor(162, 221, 139); randDataStyle.HorizontalAlignment = Style.HAlign.Center; ColumnProperties columnProperties; System.Random rand = new System.Random(); for (int iRow = 1; iRow < 50; iRow++) { for (int iCol = 0; iCol < 3; iCol++) { ws.Cells[iRow, iCol].Value = rand.Next(100); ws.Cells[iRow, iCol].ApplyStyle(randDataStyle); } } */

Why this method causes issues:

...

• Apply styles to large numbers of cells by setting the style to either rows/columns or to singular data marker cells.
• If more than one style is needed in a row/column, and it can't be handled by switching the style for the other (ie, setting style on a row to change part of a column), try to keep the style that takes a majority of the row/column as the one set by that row/column.

  Code Block

  // POSSIBLE CORRECT CODE: Style randDataStyle = wb.CreateStyle(); randDataStyle.BackgroundColor = wb.Palette.GetClosestColor(162, 221, 139); randDataStyle.HorizontalAlignment = Style.HAlign.Center; ColumnProperties columnProperties; // Loop through the 2-4 columns to apply formatting for (int i = 1; i < 4; i++) { columnProperties = ws.GetColumnProperties(i); columnProperties.Width = 100; columnProperties.ApplyStyle(randDataStyle); }

When Possible, Use DataReaders Instead of DataTables

...

• DataTables create an in-memory copy of all the data.  They are useful for manipulating data in memory or updating the database, but tends to be rather inefficient for just pulling data out of a database.
• Also results in a hit May have a negative impact on Time based performance.

...

• DataReader is more efficient at pulling out data and displaying it, as it just reads one row at a time out of the database and doesn't keep anything in memory.
• DataTables still have uses beyond those provided by DataReader, so sometimes they are still needed to filter, sort or otherwise manipulate the data before populating the workbook.

Downloads

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

• (Link to code goes here.)

Time Related Performance Issues

...