title: EPPlus save two million rows with 200+ columns datatable to multiple excel files
body: <p>I have function that saves all records from SQL table to excel worksheet using EPPlus.
  If I export small amount of data everything works fine, but with 200+ columns and 500 000+ rows I get OutOfMemory exception.</p>
  
  <p>I'd like to modify my code in a way to be able to save 50 000 records per file.</p>
  
  <p>Here is my code that works for small data:</p>
  
  <pre><code>private Task SaveAsync(string tableName)
  {
  
      return Task.Run(() =&gt;
      {
          try
          {
              using (var conn = new SqlConnection(_connectionString))
              {
                  using (var cmd = new SqlCommand(string.Format(DataQuery, tableName), conn))
                  {
                      cmd.CommandType = CommandType.Text;
                      cmd.CommandTimeout = 360;
                      conn.Open();
                      using (SqlDataReader sdr = cmd.ExecuteReader())
                      {
                          var fileName = string.Format(TargetFile, tableName);
                          if (File.Exists(fileName))
                          {
                              File.Delete(fileName);
                          }
  
                          sdr.Read();
                          var numberOfRecordsInTable = sdr.GetInt32(0);
  
                          sdr.NextResult();
  
                          using (ExcelPackage pck = new ExcelPackage(new FileInfo(fileName)))
                          {
                              ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Results");
  
                              int count = sdr.FieldCount;
                              int col = 1, row = 1;
  
                              for (int i = 0; i &lt; count; i++)
                              {
                                  ws.SetValue(row, col++, sdr.GetName(i));
                              }
                              row++;
                              col = 1;
                              while (sdr.Read())
                              {
                                  for (int i = 0; i &lt; count; i++)
                                  {
                                      var val = sdr.GetValue(i);
                                      ws.SetValue(row, col++, val);
                                  }
                                  row++;
                                  col = 1;
                              }
                              //autosize
                              ws.Cells[ws.Dimension.Address].AutoFitColumns();
                              //autofiltr
                              ws.Cells[1, 1, 1, count].AutoFilter = true;
                          }
                      }
                      conn.Close();
                  }
              }
          }
          catch (Exception e)
          {
              Debug.WriteLine("Error at: " + Thread.CurrentThread.ManagedThreadId);
              Debug.WriteLine(e);
          }
      });
  }
  </code></pre>
  
  <p>and my modified code that splits records 50 000 per file:</p>
  
  <pre><code>private Task SaveAsync2(string tableName)
  {
      return Task.Run(() =&gt;
      {
          try
          {
              using (var conn = new SqlConnection(_connectionString))
              {
                  using (var cmd = new SqlCommand(string.Format(DataQuery, tableName), conn))
                  {
                      cmd.CommandType = CommandType.Text;
                      cmd.CommandTimeout = 360;
                      conn.Open();
                      using (SqlDataReader sdr = cmd.ExecuteReader())
                      {
  
                          var fileName = string.Format(TargetFile, tableName,"");
                          if (File.Exists(fileName))
                          {
                              File.Delete(fileName);
                          }
  
                          sdr.Read();
                          var max = sdr.GetInt32(0);
                          int filesCount = 1;
                          if (max &gt; 50000)
                          {
                              fileName = string.Format(TargetFile, tableName, filesCount);
                          }
  
                          sdr.NextResult();
  
                          ExcelPackage pck = new ExcelPackage(new FileInfo(fileName));
                          ExcelWorksheet ws = pck.Workbook.Worksheets.Add("RESULTS");
  
                          int count = sdr.FieldCount;
  
                          int col = 1, row = 1;
  
                          for (int i = 0; i &lt; count; i++)
                          {
                              ws.SetValue(row, col++, sdr.GetName(i));
                          }
                          row++;
                          col = 1;
                          while (sdr.Read())
                          {
                              for (int i = 0; i &lt; count; i++)
                              {
                                  var val = sdr.GetValue(i);
                                  ws.SetValue(row, col++, val);
                              }
                              row++;
                              col = 1;
  
                              if (row &gt; 50000)
                              {
                                  pck.Save();
                                  filesCount++;
                                  fileName = string.Format(TargetFile, tableName, filesCount);
  
                                  pck = new ExcelPackage(new FileInfo(fileName));
                                  ws = pck.Workbook.Worksheets.Add("RESULTS");
  
                                  count = sdr.FieldCount;
  
                                  col = 1;
                                  row = 1;
  
                                  for (int i = 0; i &lt; count; i++)
                                  {
                                      ws.SetValue(row, col++, sdr.GetName(i));
                                  }
                                  row++;
                                  col = 1;
                              }
                          }
  
                          //autosize
                          ws.Cells[ws.Dimension.Address].AutoFitColumns();
                          //autofiltr
                          ws.Cells[1, 1, 1, count].AutoFilter = true;
  
                          pck.Save();
                      }
                  }
                  conn.Close();
  
              }
          }
          catch (Exception e)
          {
              Debug.WriteLine("Error at: " + Thread.CurrentThread.ManagedThreadId);
              Debug.WriteLine(e);
          }
      });
  }
  </code></pre>
  
  <p>basically this works fine, but in first version of my code I was using everything inside <code>using</code> statement, when in second version I'm calling same code twice.</p>
  
  <ol>
  <li>How can I fix my code to remove duplicate code and put everything inside using.</li>
  <li>Can I add next set (50 000 records) as new worksheet instead of creating new file?</li>
  <li>What would be EPPlus limit when saving data to file? <code>rows x columns</code>? I found information that EPPlus should handle more than million rows, but not so much columns as I have. I thinks that I can export million rows with single column, but for 200+ columns for me 50 000 rows is limit. I'm wondering if there is number (rows x columns) that will be limit to which my export will work fine. I want that export function to be universal, so when I pass datatable with 50 columns it will export for example 100 000 rows per file and for 2 columns it will export half million per file.</li>
  </ol>