0
\$\begingroup\$

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.

\$\endgroup\$
7
  • 3
    \$\begingroup\$ The current question title, which states your concerns about the code, is too general to be useful here. Please edit to the site standard, which is for the title to simply state the task accomplished by the code. Please see How to get the best value out of Code Review: Asking Questions for guidance on writing good question titles. \$\endgroup\$ Apr 26, 2021 at 9:40
  • \$\begingroup\$ @TobySpeight thank you for the information, i'll try to improve the question title. \$\endgroup\$
    – James
    Apr 26, 2021 at 9:43
  • \$\begingroup\$ Could you provide a test case example? \$\endgroup\$
    – aepot
    Apr 26, 2021 at 11:03
  • \$\begingroup\$ props.Where(p => p.GetSetMethod() != null).OrderBy(x => x.GetAttributeValue<ExcelColumnAttribute, int>(y => y.Order)), and prop.GetAttributeValue<ExcelColumnAttribute, string>(x => x.Name), you could create dictionary/cache to prevent redundant enumerable and get attribute operation: Dictionary<string, (PropertyInfo prop, ExcelColumnAttribute attr)>. I saw couple of things that can be improved, but I suggest start from there. \$\endgroup\$
    – Moch Yusup
    Apr 26, 2021 at 13:54
  • \$\begingroup\$ @aepot I'm not exactly know how to test case example, but I put some stopwatch test for Generate Item Row loop. I already added in the description, is that enough? \$\endgroup\$
    – James
    Apr 27, 2021 at 2:11

1 Answer 1

1
\$\begingroup\$

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.

\$\endgroup\$
3
  • \$\begingroup\$ You could just wait until the document is complete and THEN autosize all or specific columns. \$\endgroup\$
    – JeffC
    Apr 27, 2021 at 18:34
  • \$\begingroup\$ @JeffC yes, that what I do. that's what I mean when I said move to another loop. I'm gonna edit the answer \$\endgroup\$
    – James
    Apr 28, 2021 at 2:14
  • \$\begingroup\$ You have sheet.AutoSizeColumn(columnIndex); in the deepest loop... that's the line I was talking about. I would move that out of the outermost loop. \$\endgroup\$
    – JeffC
    Apr 28, 2021 at 2:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.