Spreadsheetgear in parallel

Question

I am trying to generate an excel file using spreadsheetgear, along with Parallel.ForEach, for writing in cells and I get sometimes, null reference exception. Is there any setup that I need to do, or config?

Answer 1

If you intend to do simultaneous work on the same workbook across multiple threads, each of your threads must acquire/release a lock via IWorkbookSet.GetLock() and ReleaseLock() on the corresponding IWorkbookSet while doing this work on the workbook. Otherwise, you could access the workbook in an unexpected state and all sorts of seemingly random exceptions like you are seeing can occur. Example:

// Create a new "workbook set".
IWorkbookSet wbs = Factory.GetWorkbookSet();

// Add a workbook under this "workbook set".
IWorkbook workbook = wbs.Workbooks.Add();
IWorksheet worksheet = workbook.ActiveWorksheet;

// Do your task (here I just insert single characters into cells in the sheet).
Parallel.ForEach("ABCDEFG", (char c) => {
    // Must acquire a workbook set lock before doing anything to the workbook!
    wbs.GetLock();
    try
    {
        // Get next row to add character.
        int nextRow = worksheet.UsedRange.Row + worksheet.UsedRange.RowCount;

        // Add character to cell.
        worksheet.Cells[nextRow, 0].Value = c;
    }
    finally
    {
        // Must release the workbook set lock.
        wbs.ReleaseLock();
    }
});

At least for this trivial example, such an approach provides no performance benefit because each thread must wait its turn to access the workbook and add the character to the sheet. In fact, this approach most certainly hurts performance compared to doing this task in a more linear, non-multi-threaded fashion.

So depending on the specifics of your task and how much work it does on the workbook vs on other things that can more successfully be done simultaneously, such an approach may or may not provide a performance benefit compared to a more linear approach.