内存不足异常 - 使用 SqlDataReader 和 OpenXML

我想到了如何将xmlreader/xmlwriter与sqldatareader一起使用sqldatareader，它使用很少的内存。 这在十分钟内生成了一个 300,000 行的 xlsx 文件，几乎不使用任何内存！

截至 2012 年

            String strExtract2012 = "WHERE client.typecode = 'I' AND Policy.UniqAgency IN(65536,65537,65538,65539,65540) AND Line.ExpirationDate < '1/1/2013' " +
                "ORDER BY polagencycode, polbranch, clientlookup, policynumber, lineeff, linetypecode";

            String strWham = strExtract + strExtract2012;
            System.Data.SqlClient.SqlCommand objCmd = new System.Data.SqlClient.SqlCommand(strWham, objConn);
            objCmd.CommandTimeout = 3000;
            System.Data.SqlClient.SqlDataReader objReader;
            objReader = objCmd.ExecuteReader();

            string path = @"''filepath'ExpThrough201212.xlsx";
            string blankpath = @"''filepath'blank.xlsx"; - put this blank xlsx file in the *filepath*
            File.Copy(blankpath, path, true);


            if (objReader.Read())
            {
                using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(path, true))
                {                      
                    WorkbookPart workbookPart = myDoc.WorkbookPart;
                    WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
                    string origninalSheetId = workbookPart.GetIdOfPart(worksheetPart);
                    WorksheetPart replacementPart = workbookPart.AddNewPart<WorksheetPart>();
                    string replacementPartId = workbookPart.GetIdOfPart(replacementPart);

                    OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
                    OpenXmlWriter writer = OpenXmlWriter.Create(replacementPart);
                    while (reader.Read())
                    {
                        if (reader.ElementType == typeof(SheetData))
                        {
                            if (reader.IsEndElement)
                                continue;
                            writer.WriteStartElement(new SheetData());
                            Row rr = new Row();
                            writer.WriteStartElement(rr);
                            //Add Header          
                            for (int count = 0; count < objReader.FieldCount; count++)
                            {
                                String FieldName = objReader.GetName(count);

                                DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                                cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                                cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(objReader.GetName(count));
                                //headerRow.AppendChild(cell);
                                writer.WriteElement(cell);
                            }
                            writer.WriteEndElement();

                            //writer.WriteEndElement();
                            //sheetData.AppendChild(headerRow);

                            while (objReader.Read())
                            {
                                //DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
                                Row r = new Row();
                                writer.WriteStartElement(r);
                                for (int col = 0; col < objReader.FieldCount; col++)
                                {
                                    String FieldValue = objReader.GetValue(col).ToString();
                                    //columns.Add(FieldValue);
                                    DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                                    cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                                    cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(FieldValue);
                                    //newRow.AppendChild(cell);
                                    writer.WriteElement(cell);
                                }
                                //.AppendChild(newRow);
                                writer.WriteEndElement();                                    
                            }
                            writer.WriteEndElement();
                        }
                        else
                        {
                            if (reader.IsStartElement)
                            {
                                writer.WriteStartElement(reader);
                            }
                            else if (reader.IsEndElement)
                            {
                                writer.WriteEndElement();
                            }
                        }
                    }
                    reader.Close();
                    writer.Close();
                    Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Id.Value.Equals(origninalSheetId)).First();
                    sheet.Id.Value = replacementPartId;
                    workbookPart.DeletePart(worksheetPart);

                }
            }
            objReader.Close();

您为objReader中的每一行创建一个新文档; while (objReader.Read())行应该在文件创建代码中移动，但这不会导致您当前看到的问题。

看到的问题是，您用作基础的示例读取现有文件(使用 SpreadsheetDocument.Open (并将数据复制到其中，但您正在从头开始创建一个全新的文件(使用 SpreadsheetDocument.Create (。这就是为什么他的文档已经包含workbookpart而你的文档没有。由于您的没有，您会收到对象引用错误，因为您的workbookPart变量null 。

Vincent Tan有一篇关于如何使用纯SAX方法编写大型Excel文件的精彩文章。他提到了使用您提供的链接中的代码可能会遇到的一些问题，因此我强烈建议您阅读一下。

以此为基础，你可以通过这样的东西或多或少地实现你所追求的目标：

String strWham = strExtract + strExtract2012;
System.Data.SqlClient.SqlCommand objCmd = new System.Data.SqlClient.SqlCommand(strWham, objConn);
objCmd.CommandTimeout = 3000;
System.Data.SqlClient.SqlDataReader objReader;
objReader = objCmd.ExecuteReader();
string path = @"''wsi'userdata'pterrazas'AccountingReports'ExpThrough201212.xlsx";
using (SpreadsheetDocument myDoc = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook))
{
    List<OpenXmlAttribute> xmlAttributes;
    OpenXmlWriter writer;
    //add a workbookpart manually
    myDoc.AddWorkbookPart();
    WorksheetPart worksheetpart = myDoc.WorkbookPart.AddNewPart<WorksheetPart>();
    //create an XML writer for the worksheetpart
    writer = OpenXmlWriter.Create(worksheetpart);
    writer.WriteStartElement(new Worksheet());
    writer.WriteStartElement(new SheetData());
    int rowNumber = 1;
    while (objReader.Read())
    {
        xmlAttributes = new List<OpenXmlAttribute>();
        // this is the row index
        xmlAttributes.Add(new OpenXmlAttribute("r", null, rowNumber.ToString()));
        //write the row start element with the attributes added above
        writer.WriteStartElement(new Row(), xmlAttributes);
        for (int columnNumber = 1; columnNumber < objReader.FieldCount; columnNumber++)
        {
            //reset the attributes
            xmlAttributes = new List<OpenXmlAttribute>();
            // this is the data type ("t"), with CellValues.String ("str")
            // you might need to change this depending on your source data
            // you might also consider using the Shared Strings table instead
            xmlAttributes.Add(new OpenXmlAttribute("t", null, "str"));
            //add the cell reference (A1, B1, A2... etc)
            xmlAttributes.Add(new OpenXmlAttribute("r", null, GetExcelColumnName(rowNumber, columnNumber)));
            //write the start of the cell element with the type and cell reference attributes
            writer.WriteStartElement(new Cell(), xmlAttributes);
            //write the cell value
            writer.WriteElement(new CellValue(objReader[columnNumber].ToString()));
            //write the cell end element
            writer.WriteEndElement();
        }
        //write the row end element
        writer.WriteEndElement();
        rowNumber++;
    }
    //write the sheetdata end element
    writer.WriteEndElement();
    //write the worksheet end element
    writer.WriteEndElement();
    writer.Close();
    //create a writer for the workbookpart
    writer = OpenXmlWriter.Create(myDoc.WorkbookPart);
    //write the start element of a workbook to the workbook part
    writer.WriteStartElement(new Workbook());
    //write the start element of a sheets item to the workbook part
    writer.WriteStartElement(new Sheets());
    //write the whole element of a sheet to the workbook part
    //note we link it to the id of the worksheetpart populated above
    writer.WriteElement(new Sheet()
    {
        Name = "Sheet1",
        SheetId = 1,
        Id = myDoc.WorkbookPart.GetIdOfPart(worksheetpart)
    });
    //write the sheets end element
    writer.WriteEndElement();
    //write the workbook end element
    writer.WriteEndElement();
    writer.Close();
    myDoc.Close();
}
objConn.Close();

我使用的方法GetExcelColumnName改编自这个StackOverflow问题。

private static string GetExcelColumnName(int rowNumber, int columnNumber)
{
    int dividend = columnNumber;
    string columnName = String.Empty;
    int modulo;
    while (dividend > 0)
    {
        modulo = (dividend - 1) % 26;
        columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
        dividend = (int)((dividend - modulo) / 26);
    }
    return columnName + rowNumber.ToString();
}

请注意，您可能希望根据源数据的类型更改根据代码中的注释输出的类型。您可能还想查看共享字符串概念，即字符串写入单独的文件，然后将该字符串的索引写入单元格值而不是字符串本身。

就编写Excel文件的原始性能而言，我认为以上可能是使用OpenXML SDK的最快方法。然而，这当然是以复杂性为代价的。

我希望这有所帮助。