Improvement for generating excel with many data

Question

I have the problem, my methods become slower and slower after many data. My methods can generate 100 rows with 20 columns around 1-3s, but after that my methods become slower and slower.

And for another information, i'm still using NPOI ver 2.4.1 in ASP.NET Web Form (Target framework 4.6.1)

Here's my code:

this is the extensions for generating the excel

public static class ExcelHelper
{
    public static void GenerateReportExcel<T>(this IWorkbook workbook, List<T> dataList, string sheetName = "Sheet1")
        where T : IReportModel
    {
        try
        {
            // Create Sheet
            var sheet = workbook.CreateSheet(sheetName);

            // Set Header Style
            var headerCellStyle = workbook.CreateCellStyle();
            headerCellStyle.BorderTop = BorderStyle.Thin;
            headerCellStyle.BorderBottom = BorderStyle.Thin;
            headerCellStyle.BorderLeft = BorderStyle.Thin;
            headerCellStyle.BorderRight = BorderStyle.Thin;
            var headerFontStyle = workbook.CreateFont();
            headerFontStyle.Boldweight = (short)FontBoldWeight.Bold;
            headerCellStyle.SetFont(headerFontStyle);
            headerCellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
            headerCellStyle.FillPattern = FillPattern.SolidForeground;

            // Generate Header Row
            var props = typeof(T).GetProperties();
            var headerRow = sheet.CreateRow(0);
            int columnIndex = 0;
            foreach (var prop in props.Where(p => p.GetSetMethod() != null).OrderBy(x => x.GetAttributeValue<ExcelColumnAttribute, int>(y => y.Order)))
            {
                var name = prop.GetAttributeValue<ExcelColumnAttribute, string>(x => x.Name);
                if (!string.IsNullOrEmpty(name))
                {
                    headerRow.CreateCell(columnIndex).SetValue(workbook, name);
                    headerRow.Cells[columnIndex].CellStyle = headerCellStyle;
                    sheet.AutoSizeColumn(columnIndex);
                    columnIndex++;
                }
            }

            // Generate Item Row
            if (dataList != null && dataList.Any())
            {
                int rowNumber = 1;
                foreach (var item in dataList)
                {
                    item.RowNumber = rowNumber;
                    var row = sheet.CreateRow(rowNumber);

                    columnIndex = 0;
                    foreach (var prop in props.Where(p => p.GetSetMethod() != null).OrderBy(x => x.GetAttributeValue<ExcelColumnAttribute, int>(y => y.Order)))
                    {
                        var name = prop.GetAttributeValue<ExcelColumnAttribute, string>(x => x.Name);
                        if (!string.IsNullOrEmpty(name))
                        {
                            var value = prop.GetValue(item);
                            if (value != null)
                            {
                                row.CreateCell(columnIndex).SetValue(workbook, value);
                                sheet.AutoSizeColumn(columnIndex);
                            }
                            columnIndex++;
                        }
                    }

                    rowNumber++;
                }
            }
        }
        finally
        {
            ExcelExtensions.ClearStyles();
        }
    }
}

this is the extensions to help set value to the cell

public static class ExcelExtensions
{
    private static Dictionary<string, ICellStyle> cellStyles = new Dictionary<string, ICellStyle>();

    public static void ClearStyles()
    {
        cellStyles.Clear();
    }

    public static void SetValue(this ICell cell, IWorkbook workbook, object value)
    {
        if (value != null)
        {
            var type = value.GetType();
            if (type == typeof(bool))
            {
                var boolValue = Convert.ToBoolean(value);
                cell.SetCellValue(boolValue);
                cell.SetCellType(CellType.Boolean);
            }
            else if (type == typeof(int) || type == typeof(long))
            {
                var longValue = Convert.ToInt64(value);
                cell.SetCellValue(longValue);
                cell.SetCellType(CellType.Numeric);
                cell.CellStyle = GetCellStyleForFormat(workbook, "#,##0");
            }
            else if (type == typeof(double) || type == typeof(decimal))
            {
                var doubleValue = Convert.ToDouble(value);
                cell.SetCellValue(doubleValue);
                cell.SetCellType(CellType.Numeric);
                cell.CellStyle = GetCellStyleForFormat(workbook, "#,##0.00");
            }
            else if (type == typeof(DateTime) || type == typeof(DateTime?))
            {
                var dateValue = (DateTime?)value;
                if (dateValue != null && dateValue != DateTime.MinValue)
                {
                    cell.SetCellValue(dateValue.Value);
                    cell.CellStyle = GetCellStyleForFormat(workbook, "dd/MM/yyyy HH:mm:ss");
                }
                else
                {
                    cell.SetCellValue("");
                }
            }
            else
            {
                var stringValue = value.ToString();
                cell.SetCellValue(stringValue);
                cell.SetCellType(CellType.String);
            }
        }
    }

    private static ICellStyle GetCellStyleForFormat(IWorkbook workbook, string dataFormat)
    {
        if (!cellStyles.ContainsKey(dataFormat))
        {
            var style = workbook.CreateCellStyle();

            // check if this is a built-in format
            var builtinFormatId = HSSFDataFormat.GetBuiltinFormat(dataFormat);

            if (builtinFormatId != -1)
            {
                style.DataFormat = builtinFormatId;
            }
            else
            {
                // not a built-in format, so create a new one
                var newDataFormat = workbook.CreateDataFormat();
                style.DataFormat = newDataFormat.GetFormat(dataFormat);
            }

            cellStyles[dataFormat] = style;
        }

        return cellStyles[dataFormat];
    }
}

this is the Attribute to determine the column name and column index

public class ExcelColumnAttribute : Attribute
{
    public ExcelColumnAttribute(string name, int order)
    {
        Name = name;
        Order = order;
    }

    public string Name { get; }
    public int Order { get; }
}

and the last one is extensions to get attribute value

public static class AttributeExtensions
{
    public static TValue GetAttributeValue<TAttribute, TValue>(this PropertyInfo prop, Func<TAttribute, TValue> valueSelector)
        where TAttribute : Attribute
    {
        var att = prop.GetCustomAttributes(typeof(TAttribute), false).FirstOrDefault() as TAttribute;
        if (att != null)
        {
            return valueSelector(att);
        }
        return default(TValue);
    }
}

I put stopwatch each 100 data in Generate Item Row loop (because I think the problem in this loop), here's the result:

Row 100 : 2980ms
Row 200 : 8283ms
Row 300 : 13836ms
Row 400 : 19654ms
Row 500 : 25237ms
Row 600 : 30673ms
Row 700 : 37554ms
Row 800 : 42548ms
Row 900 : 47646ms
Row 1000 : 52820ms
Row 1100 : 59280ms

Thanks in advance.

Answer 1

I already found the problem, in the generate item row loop here

// Generate Item Row
if (dataList != null && dataList.Any())
{
    int rowNumber = 1;
    foreach (var item in dataList)
    {
        item.RowNumber = rowNumber;
        var row = sheet.CreateRow(rowNumber);

        columnIndex = 0;
        foreach (var prop in props.Where(p => p.GetSetMethod() != null).OrderBy(x => x.GetAttributeValue<ExcelColumnAttribute, int>(y => y.Order)))
        {
            var name = prop.GetAttributeValue<ExcelColumnAttribute, string>(x => x.Name);
            if (!string.IsNullOrEmpty(name))
            {
                var value = prop.GetValue(item);
                if (value != null)
                {
                    row.CreateCell(columnIndex).SetValue(workbook, value);
                    sheet.AutoSizeColumn(columnIndex);
                }
                columnIndex++;
            }
        }

        rowNumber++;
    }
}

this AutoSizeColumn is taking so much time.

sheet.AutoSizeColumn(columnIndex);

My Solution is complete the document first, and do AutoSizeColumn later with separate loop after the document is ready.

Also thanks to @Moch Yusup, I'm using Dictionary for getting the attribute, It also increase the time.