ExcelPackage.GetAsByteArray() performance

Dec 31, 2015 at 3:15 PM
I'm writing an excel spreadsheet by cloning a template file
            FileInfo templateFile = new FileInfo(templateFilePath);
            using (ExcelPackage package = new ExcelPackage(templateFile, true))
            {...}

and then creating 4 worksheets. Each worksheet represents a category of data for a list of objects. The worksheets are constructed cell by cell like this:
                ws.Cells[rowNumber, (int)GeneralColumns.SpouseFirstName].Value = student.SpouseFirstName;
The worksheets have a varying number of columns, but on overage, say 40 columns. We use quite a few DataValidations, like this:
                IExcelDataValidationList val = ws.DataValidations.AddListValidation(ws.Cells[rowNumber, (int)GeneralColumns.DobMonth].Address);
                val.Formula.ExcelFormula = "=Months";
All of this works fine when the number of rows on each sheet is small, but when we get up to about 100 rows per sheet, we start running into performance problems. Generating the workbook takes between 20 - 30 seconds in that case, but the real killer is the call to GetAsByteArray() to return results to the client. That call takes 50 - 60 seconds.

We upgraded from version 3.1.2 to 4.0.4 but, if anything, the performance got worse after that change.

My question is: are we seeing the expected behavior in terms of performance? Or am I doing something horribly wrong? I'm hoping it's the latter. Any insights or constructive suggestions are welcome.

Thanks,
R.L.
Developer
Jan 3, 2016 at 7:02 AM
I cannot see that this should be a general performance issue. The code below takes approx. 600 ms on my laptop:
var stopwatch = new Stopwatch();
stopwatch.Start();
using (var pck = new ExcelPackage())
{
    for (var ws = 0; ws < 4; ws++)
    {
        var wsName = "worksheet" + ws;
        var currentSheet = pck.Workbook.Worksheets.Add(wsName);
        for (var column = 1; column < 40; column++)
        {
            for (var row = 2; row < 101; row++)
            {
                currentSheet.Cells[row, column].Value = row + column;
            }
        }
        for (var dv = 1; dv < 10; dv++)
        {
            var validation = currentSheet.Cells[1, dv].DataValidation.AddIntegerDataValidation();
            validation.ShowErrorMessage = true;
            validation.Prompt = "Only numeric input here";
            validation.ErrorStyle = ExcelDataValidationWarningStyle.stop;
            validation.Formula.Value = 1;
            validation.Formula2.Value = 10;
        }
                    
    }
    var bytes = pck.GetAsByteArray();
}
stopwatch.Stop();
Console.WriteLine("total milliseconds: {0}", stopwatch.ElapsedMilliseconds);
Console.ReadKey();
Developer
Jan 3, 2016 at 7:07 AM
Does it make any difference if you use the LoadFromCollection method, instead of loading the cells explicitly?
worksheet.Cells["A1"].LoadFromCollection(myCollection, ...);
Jan 4, 2016 at 8:18 PM
Thanks for the reply.

Haven't ever used LoadFromCollection, but I'll look into it. That said, would that change have any impact on the performance of GetAsByteArray()?