Avoid Opening Workbooks Populated with Large Quantities of Data with ExcelApplication

Why this might happen:

• Result of an attempt to get dynamic formatting based on user choices in asp.net.
• Attempts to autofit column widths after the data has been populated.
• To add special formatting to cells based on their values.

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

...

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

Issues with Empty Cells

1.) Referencing cells that don't exist in the input file

Why this might happen:

• Looping through and referencing a large amount of cells, some of which are empty, while looking for something.
• A search might go past the end of a column or row if the extent of the data is not known.
• Creating large areas which extend past where the data is located.

...

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

2.) Empty cells in the input file

Why this might happen:

• Sometimes people create template files using previously populated reports by clearing out values.
• Templates with large numbers of blank cells can be identified by either the scrollbar, which may be suspiciously small for having no cells with data, or by the size of the file, which may be larger than a normal empty template.

...

• Identify blank cells.  This can be done by hitting ctrl+f, then pressing the button 'Find All'.  This will return a list of all cells which currently exist in the file. (If there is a large number of cells, this may take a while.)
• Delete any rows/columns which contain empty cells.  Note: The scrollbars may not change after deleting the rows, the best way to check is by saving and re-opening the file.
• Don't apply formatting to ranges of blank cells; instead apply it to either just cells with data markers or to column and row headers.
• If all else fails, start over and create a new, blank, template file. 

Apply Styles to Row/Columns, Not Cells/Areas

Why this might happen:

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

...