title: Out Of Memory Exception - Using SqlDataReader and OpenXML
body: <p>Edit:</p>
  
  <p>Due to memory issues, I am attempting to now use: <a href="http://blogs.msdn.com/b/brian_jones/archive/2010/06/22/writing-large-excel-files-with-the-open-xml-sdk.aspx" rel="nofollow">http://blogs.msdn.com/b/brian_jones/archive/2010/06/22/writing-large-excel-files-with-the-open-xml-sdk.aspx</a></p>
  
  <p>But still running into some problems.</p>
  
  <pre><code>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&lt;WorksheetPart&gt;();
          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 &lt; numRows; row++)
                  {
                      writer.WriteStartElement(r);
                      for (int col = 0; col &lt; 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&lt;Sheet&gt;().Where(s =&gt; s.Id.Value.Equals(origninalSheetId)).First();
          sheet.Id.Value = replacementPartId;
          workbookPart.DeletePart(worksheetPart);
  
       }
  }    
  
  objConn.Close();
  
  }
  catch (Exception ex)
  </code></pre>
  
  <p>Can't get it configured properly to run without exception!</p>
  
  <p>Thanks for any help!</p>