c# - How to write on multiple worksheets using EPPlus -

i'm using following code snippet write data excel file using epplus. application big data processing , since excel has limit of ~1 million rows, space runs out time time. trying achieve this, once system.argumentexception : row out of range detected or in other words.. no space left in worksheet.. remainder of data written in 2nd worksheet in same workbook. have tried following code no success yet. appreciated!

  try                             {                                  (int = 0; < data.count(); i++)                                 {                                     var cell1 = ws.cells[rowindex, colindex];                                     cell1.value = data[i];                                     colindex++;                                 }                                 rowindex++;                             }                             catch (system.argumentexception)                             {                                 (int = 0; < data.count(); i++)                                 {                                     var cell2 = ws1.cells[rowindex, colindex];                                     cell2.value = data[i];                                     colindex++;                                  }                                 rowindex++;                             } 

you shouldnt use catch handle kind of logic - more last resort. better engineer code deal situation since predictable.

the excel 2007 format has hard limit of 1,048,576 rows. that, know how many rows should put before going new sheet. there simple loops , 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 data row count         var rowcount = 2000000;          //determine number of sheets         var sheetcount = (int)math.ceiling((double)rowcount/ maxexcelrows);          (var = 0; < sheetcount; i++)         {             var ws = package.workbook.worksheets.add(string.format("sheet{0}", i));             var sheetrowlimit = math.min((i + 1)*maxexcelrows, rowcount);              //remember +1 1-based excel index             (var j = * maxexcelrows + 1; j <= sheetrowlimit; j++)             {                 var cell1 = ws.cells[j - (i*maxexcelrows), 1];                 cell1.value = j;             }         }          package.save();     } }