I am using SpreadsheetLight to write log files from a WinForms project. My intent is to write log entries to three worksheets in the same file, and I really want to avoid using Interop if I can avoid it.
I start with a template file made in Excel which has the three worksheets pre-populated with row titles, and since each worksheet has the same basic properties (which can vary independently), I encapsulate each sheet in a class, the basics of which look like this:
/// <summary>
/// Encapsulate the info we need to know about each worksheet in order to populate properly
/// </summary>
public class LogSheet
{
public SLDocument data;
public SLWorksheetStatistics stats;
public int RowCount;
public int ColumnCount;
public int currentColumn; //indicates what column you want to be writing to
public List<string> rowNames = new List<string>(); //used to make sure you're writing new data to the right row
public List<string> columnNames = new List<string>(); //used by GetLatestRun() to check if data already exists for a given serial number
public LogSheet(string sheet)
{
this.data = new SLDocument(_path, sheet);
this.stats = this.data.GetWorksheetStatistics();
this.RowCount = this.stats.EndRowIndex;
this.ColumnCount = this.stats.EndColumnIndex;
currentColumn = GetLatestRun();
for (int i = 1; i < RowCount + 1; i++)
{
this.rowNames.Add(this.data.GetCellValueAsString(i, 1));
}
for (int i = 1; i < ColumnCount + 1; i++)
{
this.columnNames.Add(this.data.GetCellValueAsString(1, i));
}
}
}
There are also some methods not shown in the LogSheet class that handle writing data to the right places.
This all seems to work fine, and when debugging, I can see that each of the three worksheets instantiated with new LogSheet(<sheetName>) contain the data they are supposed to after I've written things to them.
The problem is that when I want to save the data, I can get away with this.data.Save(), but it only saves one worksheet, and the other two are now left in limbo because the Save() method is terminal and closes the Excel file. trying the Save() method on either of the other sheets two ends up with an Exception "Object reference not set to an object" because, of course, Save() killed my spreadsheet, and the sheets no longer have anything to reference. The resulting file only has data for the first time I saved it.
My best guess for how to get around this is to not instantiate a new SLDocument for each sheet and instead use SLDocument.SelectWorksheet() each time I want to write to a specific worksheet, but I still want to keep things encapsulated in the LogSheet class because everything else in there is still relevant.
Any other suggestions?
The recommended and efficient way is to store all the logs to be written in memory first (with a List<> or something). Then when writing, you select the worksheet, write everything from the first List<>, select the second worksheet, write everything from the second List<>, select the third worksheet, write everything from the third List<>.
If memory is an issue, then select first worksheet, write log chunk into cell value, select second worksheet, write log chunk into cell value (will be in second worksheet because second worksheet is currently selected), select third worksheet, write log chunk. Then iterate over every log chunk with the above.
The latter method takes less memory at any one time, but takes more CPU cycles because you keep going back and forth between the worksheets. The going back and forth thing is equivalent to loading up one worksheet, unloading it, then load another worksheet and so on.