使用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 ();
  }