Here are a few ways to boost the performance of SpreadsheetLight.
Numeric row and column indices
As far as possible, use the numeric row/column index function overload version instead of the cell reference version (such as “B3”). This is because SpreadsheetLight converts the cell reference to the numeric version internally. For small files, this won’t be a problem. If you have millions of cells, this might be significant enough to slow your program.
Finish everything on that worksheet first
SpreadsheetLight is optimised for creating new workbooks and new worksheets. So finish whatever you need to do on that worksheet first, before adding a new worksheet or selecting another worksheet to work on.
The technical explanation is that new worksheets can be written using the OpenXmlWriter, which is fast. Existing worksheets need to be manipulated using the DOM, which is slow. “Existing” applies to worksheets you just added, but you selected another worksheet.
Here’s a bad example:
SLDocument sl = new SLDocument(); sl.AddWorksheet("Sheet2"); sl.AddWorksheet("Sheet3"); sl.AddWorksheet("Sheet4"); sl.SelectWorksheet("Sheet1"); // do something on Sheet1 sl.SelectWorksheet("Sheet2"); // do something on Sheet2 sl.SelectWorksheet("Sheet3"); // do something on Sheet3 sl.SelectWorksheet("Sheet4"); // do something on Sheet4 sl.SaveAs("BadExample.xlsx");
Here’s a better example:
SLDocument sl = new SLDocument(); // do something on Sheet1 // By default, a new SLDocument has a default worksheet added sl.AddWorksheet("Sheet2"); // do something on Sheet2 sl.AddWorksheet("Sheet3"); // do something on Sheet3 sl.AddWorksheet("Sheet4"); // do something on Sheet4 sl.SaveAs("GoodExample.xlsx");
Style it first
If you have a lot of data to write and you also need to style the data (perhaps dates and number formats), style the cells or rows or columns first.
The technical explanation is that the styling functions need to merge any existing styles present in the cells, rows or columns. Setting cell values is fairly straightforward.
On a similar note, if you can style rows or columns instead of styling individual cells, do that.
The technical explanation is that styling a cell effectively sets a cell (with an empty text value) internally. Then when you set a lot of data, existing cells need to be checked for their styles.
Basically, you’re creating a template first. Then you write cell data.