XSSF Performance: degrade when creating large number of rows

Feb 12, 2015 at 4:23 PM
Noticed a thread on this subject from a couple years back. I have updated our code to use the latest beta release. Still experiencing the same performance degradation as more rows are being created. 700 rows are taking 6 minutes and 40 seconds consistently. Each row only has 6 columns. Any ideas on why it's taking so long to create rows? Here's an example of how the rows are being generated.

using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;

int rowNumber = 0;
int columnNumber = 0;

private IRow CreateRow()
{
var row = sheet.CreateRow(rowNumber);
rowNumber++;
return row;
}

private ICell CreateCell(IRow row, object cellValue, ICellStyle cellStyle, int width = 0)
{
dynamic value = "";

var cell = row.CreateCell(columnNumber);
if (width != 0)
    sheet.SetColumnWidth(columnNumber, width);
else
    sheet.AutoSizeColumn(columnNumber);

if (cellValue is int)
    value = (int)cellValue;
else if (cellValue is string)
    value = (string)cellValue;
else if (cellValue is bool)
    value = (bool)cellValue;
else if (cellValue is double)
    value = (double)cellValue;

cell.SetCellValue(value);
cell.CellStyle = cellStyle;
columnNumber++;

return cell;
}

private void CreateOpportunities(List<Opportunity> opportunities)
{
foreach (var opportunity in opportunities)
{
    columnNumber = 0;
    var row = CreateRow();

    if (opportunity.HasContribution)
        cellStyle = "contentwithyellowbg";
    else
        cellStyle = "content";

    CreateCell(row, opportunity.ActionDate != DateTime.MinValue ? opportunity.ActionDate.ToShortDateString() : string.Empty, styles[cellStyle]);
    CreateCell(row, !string.IsNullOrEmpty(opportunity.Category) ? opportunity.Category : string.Empty, styles[cellStyle]);
    CreateCell(row, !string.IsNullOrEmpty(opportunity.Type) ? opportunity.Type : string.Empty, styles[cellStyle]);
    CreateCell(row, !string.IsNullOrEmpty(opportunity.SourceCode) ? opportunity.SourceCode : string.Empty, styles[cellStyle]);
    CreateCell(row, !string.IsNullOrEmpty(opportunity.Description) ? opportunity.Description : string.Empty, styles[cellStyle]);
    CreateCell(row, !string.IsNullOrEmpty(opportunity.Package) ? opportunity.Package : string.Empty, styles[cellStyle]);
}
}
Feb 12, 2015 at 6:10 PM
Think I figured out our issue. Calling the AutoSizeColumn method during every cell write, instead of waiting till all the data is written and just do it once for all columns.