Search code examples
c#excelepplus

C# EPPlus create many tabs causes a null reference exception

I found a strange error with generating an Excel file using EPPlus library. The scenario is simple - I need to have many worksheets in a single excel file. But, when invoking the GetAsByteArray() method, I get the null reference exception

using (ExcelPackage xml = new ExcelPackage())
{
    foreach (var mainValueItem in values)
    {
        using (ExcelWorksheet worksheet = xml.Workbook.Worksheets.Add($"sheet {mainValueItem.ID}"))
        {
            worksheet.Cells[1, 1].Value = "Date";
        }
    }
    return ctr.File(xml.GetAsByteArray(), MediaTypeNames.Application.Octet);
}

I can see in both Worksheets, the Cells property is not loaded as you can see here: enter image description here

so, how to create many worksheets ?

Solution

  • I've found an answer - we shouldn't use 

    using (ExcelWorksheet worksheet = xml.Workbook.Worksheets.Add($"sheet {mainValueItem.ID}"))

    in that scenario. instead, just declare a variable

    var worksheet = xml.Workbook.Worksheets.Add($"sheet {mainValueItem.ID}");

    and now it works, I can see multiple tabs in the generated file.

    Happy coding !