使用OpenXml.Spreadsheet创建大型Excel文件时出现内存问题 [英] Memory problems while creating large excelfile using OpenXml.Spreadsheet

问题描述

我需要能够处理超过200000行！
问题:创建过程占用了所有内存(4GB).说到内存异常.

I need to be able to do over 200000 rows!
The problem: The creation process eats all memory(4GB). says ou of memeory exception.

public static void InsertDataToExcelFile(SpreadsheetDocument sheet, DataTable table) 
        { 
            sheet.AddWorkbookPart(); 
            sheet.WorkbookPart.Workbook = new Workbook(); 
            sheet.WorkbookPart.Workbook.AppendChild(new Sheets()); 
       WorksheetPart newWorksheetPart = sheet.WorkbookPart.AddNewPart<WorksheetPart>(); 
            newWorksheetPart.Worksheet = new Worksheet(); 
        uint worksheetNumber = 1; 
 
SheetData sheetData = newWorksheetPart.Worksheet.AppendChild(new SheetData()); 
 
int numberOfColumns = table.Columns.Count; 
bool[] IsNumericColumn = new bool[numberOfColumns]; 
Row newRow = sheetData.AppendChild(new Row()); 
Cell newCell; 
foreach (DataRow dr in table.Rows) 
            { 
                newRow = sheetData.AppendChild(new Row()); 
          for (int colInx = 0; colInx < numberOfColumns; colInx++) 
                { 
         string cellValue = dr.ItemArray[colInx].ToString(); 
 
         if (IsNumericColumn[colInx] && !string.IsNullOrEmpty(cellValue))    
               {             
                 newCell = CreateNumericCell(cellValue); 
                } 
                else 
               { 
                        newCell = CreateTextCell(cellValue); 
                       }   
 
                  newRow.AppendChild(newCell); 
                } 
            } 
            newWorksheetPart.Worksheet.Save(); 
           sheet.WorkbookPart.Workbook.GetFirstChild<Sheets>().AppendChild(new Sheet() 
            { 
                Id = sheet.WorkbookPart.GetIdOfPart(newWorksheetPart), 
                SheetId = worksheetNumber++, 
                Name = table.TableName 
            }); 
            sheet.WorkbookPart.Workbook.Save(); 
        }

推荐答案

您没有说有多少列，但是200,000行将占用大量空间，因此开始乘以列即可将很快消耗内存，尤其是当您必须同时运行部分excel时.
同样，您用来放入数据的方法似乎也很笨拙.

You have not said how many columns there are, but 200,000 rows will take up a lot of space, so start multiplying by columns and you will eat memory very quickly, especially as you have to run part of excel at the same time.
Also the meth you are using to put in the data seems rather clunky too.

这篇关于使用OpenXml.Spreadsheet创建大型Excel文件时出现内存问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！