title: OutOfMemory errors using large datatables with ClosedXML
body: <p>I have an issue creating worksheets when adding large DataTables. I am getting out of memory errors. I turned off the event tracking as suggested to improve performance and memory consumption but I am still experiencing these errors.</p>
  
  <p>Here is some sample code:</p>
  
  <pre><code>public void SaveWorkBook(string xlFileName, DataSet dataSet)
  {
      using (var xlWorkBook = new XLWorkbook(XLEventTracking.Disabled))
      {
          xlWorkBook.AddWorksheet(dataSet);
          xlWorkBook.SaveAs(xlFileName);
      }
  }
  
  public void SaveWorkBook(string xlFileName, params DataTable[] dataTables)
  {           
      using (var xlWorkBook = new XLWorkbook(XLEventTracking.Disabled))
      {
          foreach (var dataTable in dataTables) xlWorkBook.AddWorksheet(dataTable);
          xlWorkBook.SaveAs(xlFileName);
      }
  }
  </code></pre>
  
  <p>The issue of course is when the DataTables are huge. Like thousands of records or more. I tried using an alternative method to large data tables like using a temporary one with smaller records counts, then flushing them to the Worksheet, clearing the data table then refilling it with new data. Fill, flush, clear, repeat. That or the <code>InsertData</code> method()</p>
  
  <p>The issue is that every time I try to write the data table using the options available, only the first column is getting written. Then lower down the file I am seeing the other columns of data, but in the first cell of the worksheet instead of spanning the columns in a single row as they are suppose to. And only for 1 row. Probably for the last record that was written.</p>
  
  <p>Can someone help me understand how this works?</p>
  
  <p>Here is a code snippet using the <code>InsertData</code> method. I create the worksheet using a dataTable as a template since it has all the columns defined. Then I get that worksheet and try to add to it.</p>
  
  <pre><code>public void SaveWorkBook(string xlFileName, DataTable dataTable, string dataFileName, char delimitor)
  {
      using (var xlWorkBook = new XLWorkbook(XLEventTracking.Disabled))
      {
          xlWorkBook.AddWorksheet(dataTable);
  
          var workSheet = xlWorkBook.Worksheets.First(ws =&gt; ws.Name == dataTable.TableName);                    
  
          using (var sr = new StreamReader(dataFileName))
          {
              var rowIndex = 1;
  
              sr.ReadLine();
  
              while (!sr.EndOfStream)
              {
                  var line = sr.ReadLine() + string.Empty;
                  workSheet.Cell(++rowIndex, 1).InsertData(line.Split(delimitor));
              }
          }
  
          xlWorkBook.SaveAs(xlFileName);
      }
  }
  </code></pre>
  
  <p>As you can see I am using a <code>StreamReader</code> to read 1 line at a time of the CSV file I created earlier. Loading the entire file into the datable causes out of memory errors. So I read the file 1 line at a time and try to add it after creating the worksheet.</p>
  
  <p>Can anyone share some insight into this?</p>
  
  <p>Thanks</p>