title: Write large amount of data to excel c#
body: <p>I need to export lots of data from database table to excel (xls/xlsx) file.
  It could be easily 10million rows and more. </p>
  
  <p>I need open source solution which does not require Office to be installed (SpreadsheetGear and interop solutions will not work for me).</p>
  
  <p>I am checking two libraries: OpenXML SDK and EPPlus. </p>
  
  <p>For OpenXML SDK I found this method:</p>
  
  <pre><code>  private static void Write(string fileName, int numRows, int numCols)
          {
              using (var spreadsheetDocument = SpreadsheetDocument.Open(fileName, true))
              {
                  WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
                  WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
  
                  string origninalSheetId = workbookPart.GetIdOfPart(worksheetPart);
  
                  WorksheetPart replacementPart = workbookPart.AddNewPart&lt;WorksheetPart&gt;();
                  string replacementPartId = workbookPart.GetIdOfPart(replacementPart);
  
                  using (OpenXmlReader reader = OpenXmlReader.Create(worksheetPart))
                  {
                      using (OpenXmlWriter writer = OpenXmlWriter.Create(replacementPart))
                      {
                          Row row = new Row();
                          Cell cell = new Cell();
                          //CellFormula cellFormula = new CellFormula();
                          //cellFormula.CalculateCell = true;
                          //cellFormula.Text = "RAND()";
                          //cell.Append(cellFormula);
                          CellValue cellValue = new CellValue("val val");
                          cell.Append(cellValue);
  
                          while (reader.Read())
                          {
                              if (reader.ElementType == typeof(SheetData))
                              {
                                  if (reader.IsEndElement)
                                      continue;
                                  writer.WriteStartElement(new SheetData());
  
                                  for (int rowNumber = 0; rowNumber &lt; numRows; rowNumber++)
                                  {
                                      writer.WriteStartElement(row);
                                      for (int col = 0; col &lt; numCols; col++)
                                      {
                                          writer.WriteElement(cell);
                                      }
                                      writer.WriteEndElement();
                                  }
  
                                  writer.WriteEndElement();
                              }
                              else
                              {
                                  if (reader.IsStartElement)
                                  {
                                      writer.WriteStartElement(reader);
                                  }
                                  else if (reader.IsEndElement)
                                  {
                                      writer.WriteEndElement();
                                  }
                              }
                          }
                      }
                  }
  
                  Sheet sheet = workbookPart.Workbook.Descendants&lt;Sheet&gt;().First(s =&gt; s.Id.Value.Equals(origninalSheetId));
                  sheet.Id.Value = replacementPartId;
                  workbookPart.DeletePart(worksheetPart);
              }
          }
  </code></pre>
  
  <p>But it throws <code>Out of memory</code> exception. 
  I need <code>batch oriented</code> approach and to be able to <code>append</code> data to the end of excel document. 
  Unfortunately I did not find how to append rows with <code>OpenXML SDK</code>.</p>
  
  <p>Also, I checked <a href="http://techbrij.com/export-excel-xls-xlsx-asp-net-npoi-epplus" rel="nofollow">EPPlus soluion</a> with <code>LoadFromCollection</code> method. 
  It does support <code>IDataReader</code> with <code>LoadFromDataReader</code> but I dont have datareader at that point in code.</p>
  
  <p>The question: is there a way to append data to existing sheet xls/xlsx file with kind of writer? Like <code>OpenXMLWrite</code>r in <code>OpenXML SDK</code>.</p>
  
  <p>UPD. Excel clearly does not support 10 million rows. Lets stick with 1m rows and lost of columns without out of memory exception.</p>
  
  <p>UPD. Added EPPlus sample. 200k rows exports in 6 minutes and takes up to 1GB of RAM.</p>
  
  <pre><code> private const string TempFile = @"C:\Users\vnechyp\Desktop\temp.xlsx";
  
  private static void EPPlusExport()
  {
      var random = new Random();
  
      var dt = new System.Data.DataTable();
      for (int i = 0; i &lt; 15; i++)
      {
          dt.Columns.Add($"column_{i}");
      }
  
      var values = Enumerable.Range(0, 15).Select(val =&gt; random.Next().ToString()).ToArray();
  
      for (int i = 0; i &lt; 10000; i++)
      {
          dt.Rows.Add(values);
      }
  
      using (ExcelPackage excelPackage = new ExcelPackage())
      {
          var workSheet = excelPackage.Workbook.Worksheets.Add("sheet");
          workSheet.Cells[1, 1].LoadFromDataTable(dt, true);
          excelPackage.SaveAs(new FileInfo(TempFile));
      }
  
  
      for (int i = 1; i &lt; 50; i++)
      {
          Console.WriteLine($"Iteration: {i}");
  
          var updateRow = i*10000;
          Console.WriteLine($"Rows: {updateRow}");
  
          FileInfo existingFile = new FileInfo(TempFile);
          using (ExcelPackage excelPackage = new ExcelPackage(existingFile))
          {
              // get the first worksheet in the workbook
              ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets[1];
              worksheet.Cells[updateRow, 1].LoadFromDataTable(dt, true);
              excelPackage.SaveAs(new FileInfo(TempFile));
          }
      }
  }
  </code></pre>