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++;
                            }

score 3 · Answer 1 · answered Jul 09 '15 at 15:40

3

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();
    }
}

answered Jul 09 '15 at 15:40

Ernie S

13,902
4
52
79

How can i assume a value for rowcount in my case? It can be anything based on the data – sparta93 Jul 09 '15 at 17:01
Its a variable. So get it from your data source and then plug it into the sheetCount formula. What is your data source exactly? – Ernie S Jul 09 '15 at 17:29
little off topic: for inserting data from database you can use `LoadFromDataTable` function to quickly load data which is quicker than looping through rows and columns. – Techie Jul 12 '15 at 13:35
1

@Nimesh The thing with `LoadFromDataTable` is that it can get very slow with very large datasets due to the overhead it adds. See this post: http://stackoverflow.com/questions/30709328/slow-loading-of-csv-files-using-epplus/30710690 In this case it is actually faster to use a for loop. – Ernie S Jul 13 '15 at 12:12
@Ernie My initial code was using loops as I was not aware about `LoadfromDataTable` but I noticed quite improvement in my case, maybe the datasets were not that huge. Will have to experiment and see. Thanks. – Techie Jul 13 '15 at 16:00

How to write on multiple worksheets using EPPlus

1 Answers1