Out Of Memory Exception - Using SqlDataReader and OpenXML

Question

Edit:

Due to memory issues, I am attempting to now use: http://blogs.msdn.com/b/brian_jones/archive/2010/06/22/writing-large-excel-files-with-the-open-xml-sdk.aspx

But still running into some problems.

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";

while (objReader.Read())
{         
    using (var myDoc = SpreadsheetDocument.Create(path, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
    {               
        WorkbookPart workbookPart = myDoc.WorkbookPart;
        /* The next line causes the error:
           ** Error: Object Reference not set to an instance of an object**
        */
        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);

        Row r = new Row();
        Cell c = new Cell();
        CellFormula f = new CellFormula();
        f.CalculateCell = true;
        f.Text = "RAND()";
        c.Append(f);
        CellValue v = new CellValue();
        c.Append(v);
        int numRows = 1;
        int numCols = 1;
        while (reader.Read())
        {
            if (reader.ElementType == typeof(SheetData))
            {
                if (reader.IsEndElement)
                    continue;
                writer.WriteStartElement(new SheetData());

                for (int row = 0; row < numRows; row++)
                {
                    writer.WriteStartElement(r);
                    for (int col = 0; col < numCols; col++)
                    {
                         writer.WriteElement(c);
                         numCols++;
                    }
                    writer.WriteEndElement();
                    numRows++;
                }

                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);

     }
}    

objConn.Close();

}
catch (Exception ex)

Can't get it configured properly to run without exception!

Thanks for any help!

Answer 1

I figured how to use xmlreader/xmlwriter with sqldatareader from a sql db string and it uses very little memory. This produced a 300,000 row xlsx file in ten minutes using hardly any memory!

//Exp Through 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();