OpenXML内存泄漏

Question

我使用OpenXML将数据表导出到Excel文件中。

例如，我使用一个大约有250,000行和大约10列的表。在将其导出到Excel时，需要大约1.2 GB的内存，有时还会抛出OutOfMemoryException。

我的问题有什么解决方案吗？

这是我的代码

        ExcelDocument excelDocument = new ExcelDocument();
        excelDocument.CreatePackage(exportFile);

        //populate the data into the spreadsheet
        using (SpreadsheetDocument spreadsheet =
            SpreadsheetDocument.Open(exportFile, true))
        {
            WorkbookPart workbook = spreadsheet.WorkbookPart;
            //create a reference to Sheet1
            WorksheetPart worksheet = workbook.WorksheetParts.Last();
            SheetData data = worksheet.Worksheet.GetFirstChild<SheetData>();

            //add column names to the first row
            Row header = new Row();
            header.RowIndex = (UInt32) 5;

            foreach (DataColumn column in table.Columns)
            {
                Stylesheet styleSheet = workbook.WorkbookStylesPart.Stylesheet;

                //build the formatted header style
                UInt32Value headerFontIndex =
                    createFont(
                        styleSheet,
                        "Arial",
                        9,
                        true,
                        System.Drawing.Color.White);
                //set the background color style
                UInt32Value headerFillIndex =
                    createFill(
                        styleSheet,
                        System.Drawing.Color.SlateGray);
                //create the cell style by combining font/background
                UInt32Value headerStyleIndex =
                    createCellFormat(
                        styleSheet,
                        headerFontIndex,
                        headerFillIndex,
                        null);
                Cell headerCell = createTextCell(
                    table.Columns.IndexOf(column) + 1,
                    5,
                    column.ColumnName, headerStyleIndex);

                header.AppendChild(headerCell);
            }
            data.AppendChild(header);

            //loop through each data row
            DataRow contentRow;
            for (int i = 0; i < table.Rows.Count; i++)
            {
                contentRow = table.Rows[i];
                data.AppendChild(createContentRow(contentRow, i + 6));
            }
        }

Answer 1

问题是在处理SpreadsheetDocument时，它花费了太多的时间，似乎在处理的时候会将所有的数据都刷新到excel表中，所以我吐出了excel表，每个文件只有30,000条记录，并强制垃圾收集器运行，解决了我的问题