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.
props.Where(p => p.GetSetMethod() != null).OrderBy(x => x.GetAttributeValue<ExcelColumnAttribute, int>(y => y.Order))
, andprop.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\$