使用NPOI构建excel工作表缓慢

Question

我正在使用NPOI库在我的主管想要在Linux服务器上托管的应用程序中构建Excel工作表。该应用程序可以很好地处理1000行以下的文件，但偶尔我有大文件，它需要比我预期的长得多。有人知道我怎样才能加快处理这些大文件的速度吗?谢谢。

代码如下:

foreach (DataRow row in dataMalformed.Rows)
            {
                IWorkbook workbook = new XSSFWorkbook();
                //Console.WriteLine(row[0]);
                if (dataMalformed.Rows.Count > 0)
                {
                    strFileName = strMalformed + "\MVPSMalformedProd" + "-";
                    strFileName2 = strFileName + strDate2 + ".xls";
                }
                var fs = new FileStream(strFileName, FileMode.Create, FileAccess.ReadWrite);
                var fs2 = new FileStream(strFileName2, FileMode.Create, FileAccess.ReadWrite);
                strSheetName = "Malformed Messages - " + ", " + strDate2;
                
                using (fs)
                {
                    ISheet excelSheet = workbook.CreateSheet(strSheetName);
                    intRow = 2;
                    List<String> columns = new List<string>();
                    IRow row1 = excelSheet.CreateRow(0);
                    int columnIndex = 0;
                    foreach (System.Data.DataColumn column in dataMalformed.Columns)
                    {
                        columns.Add(column.ColumnName);
                        row1.CreateCell(columnIndex).SetCellValue(column.ColumnName);
                        columnIndex++;
                    }
                    foreach (DataRow row2 in dataMalformed.Rows)
                    {
                        //Console.WriteLine(row2[0]);
                        row1 = excelSheet.CreateRow(intRow);
                        int cellIndex = 0;
                        foreach (String col in columns)
                        {
                            row1.CreateCell(cellIndex).SetCellValue(row2[col].ToString());
                            excelSheet.AutoSizeColumn(cellIndex);
                            cellIndex++;
                        }
                        
                        if (dictSenders.ContainsKey(row2[3].ToString()))
                        {
                            row1.CreateCell(--cellIndex).SetCellValue(dictSenders[row2[3].ToString()]);
                        }
                        intRow++;
                    }
                }

Answer 1

你确定NPOI是问题所在吗?我刚刚尝试创建一个10000行50列的excel文件。

  var workbook = new XSSFWorkbook ();
  var sheet = workbook.CreateSheet ("Sheet1");
  for (var rowNum = 0; rowNum < 10000; rowNum++)
  {
    var rowData = sheet.CreateRow (rowNum);
    for (var columnNum = 0; columnNum < 50; columnNum++)
    {
      rowData.CreateCell (columnNum).SetCellValue ($"Row {rowNum + 1}, Column {columnNum + 1}");
    }
  }
  using (FileStream fileStream = File.Create ("D:\Temp\test.xlsx"))
  {
    workbook.Write (fileStream);
    fileStream.Close ();
  }