How to write on multiple worksheets using EPPlus

Question

I'm using the following code snippet to write some data into an excel file using EPPlus. My application does some big data processing and since excel has a limit of ~1 million rows, space runs out time to time. So what I am trying to achieve is this, once a System.ArgumentException : row out of range is detected or in other words.. no space is left in the worksheet.. the remainder of the data will be written in the 2nd worksheet in the same workbook. I have tried the following code but no success yet. Any help will be appreciated!

  try
                            {

                                for (int i = 0; i < data.Count(); i++)
                                {
                                    var cell1 = ws.Cells[rowIndex, colIndex];
                                    cell1.Value = data[i];
                                    colIndex++;
                                }
                                rowIndex++;
                            }
                            catch (System.ArgumentException)
                            {
                                for (int i = 0; i < data.Count(); i++)
                                {
                                    var cell2 = ws1.Cells[rowIndex, colIndex];
                                    cell2.Value = data[i];
                                    colIndex++;

                                }
                                rowIndex++;
                            }

Answer 1

You shouldnt use a catch to handle that kind of logic - it is more for a last resort. Better to engineer you code to deal with your situation since this is very predictable.

The excel 2007 format has a hard limit of 1,048,576 rows. With that, you know exactly how many rows you should put before going to a new sheet. From there it is simple for loops and math:

[TestMethod]
public void Big_Row_Count_Test()
{
    var existingFile = new FileInfo(@"c:\temp\temp.xlsx");
    if (existingFile.Exists)
        existingFile.Delete();

    const int maxExcelRows = 1048576;

    using (var package = new ExcelPackage(existingFile))
    {
        //Assume a data row count
        var rowCount = 2000000;

        //Determine number of sheets
        var sheetCount = (int)Math.Ceiling((double)rowCount/ maxExcelRows);

        for (var i = 0; i < sheetCount; i++)
        {
            var ws = package.Workbook.Worksheets.Add(String.Format("Sheet{0}", i));
            var sheetRowLimit = Math.Min((i + 1)*maxExcelRows, rowCount);

            //Remember +1 for 1-based excel index
            for (var j = i * maxExcelRows + 1; j <= sheetRowLimit; j++)
            {
                var cell1 = ws.Cells[j - (i*maxExcelRows), 1];
                cell1.Value = j;
            }
        }

        package.Save();
    }
}