Load large amount of excel data with EPPlus

Question

I have a basic winforms app that a user can upload an excel file (.xlsx) and I want to read the contents of this file so I am using EPPlus.

The problem is, I am trying to load the contents of a very large excel file, it has 7 tabs, with one tab haveing more then 200k rows, and another tab with 70k. The other 5 total to about 50k.

These file will also only continue to get bigger.(The end goal) Since I want to import the data, read the data, and depending on the rules/data I have, I need to write data back on the excel file and export it.

I am having issues though with EPPlus in achieving the loading of the data.

here is code

var file = new FileInfo(filePath);
using (var package = new ExcelPackage(file))
{
    try
    {
        // Get the work book in the file
        ExcelWorkbook workBook = package.Workbook; //Hangs here for about 2 mins
        if (workBook != null)
        {
            if (workBook.Worksheets.Count > 0)
            {
                // Get the first worksheet
                ExcelWorksheet currentWorksheet = workBook.Worksheets.First();
                // gets the currentWorksheet but doesn't evaluate anything...

            }
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

The code hangs on the package.workbook line for roughly 2 minutes. Then it gets down into the if where it gets the currentWorkSheet, and if I look at the contents of that variable in my watch, nothing is really loaded since it displays:

Function evaluation disabled because a previous function evaluation timed out. You must continue execution to reenable function evaluation.

I looked at this link from EPPlus and it shows the only issue with loading large files is load from top to bottom and left to right, and they are saying more than 5,000. I have way more than though so I am just wondering if this could even be possible with EPPlus?

Additionally, I have did some google searching and most issues are they can't open the large excel files on server where they could locally...

I have also started looking at Open XML SDK and it seems to be better performance wise, but also much more difficult to use code wise.

Solution

Those sounds like pretty big datasets so you might want to read this:

EPPlus Large Dataset Issue with Out of Memory Exception

Basically, you can run out of RAM with "larger" dataset. But its not just the number of rows that adds to the size but also the columns AND the content of each cell. Strings will generally take up a lot more room than numbers so it is sometime difficult to predict when EPP will start to have memory issues. Rumor has it the newest version of EPP is better but I havent tested it myself.

Seems like you got it working based on your comments and thats good but keep the memory limitations in mind. I aggree with you - doing it in Open XML would not be a simple exercise.

Comments

[Robin]

Thanks for this, fortunately I did find out the issue and was able to resolve it but I will keep this in mind. Thanks!

[Dan S.]

Is it possible to get around the memory limitation by loading and processing a file in chunks, using something similar to Linq's Skip(x) and Take(x) methods, or does it load the entire workbook into memory regardless?

[Ernie S]

@DanS. I tried that (might have mentioned it in my answer in that linked post). No luck - once you open the file in EPP it all gets loaded no matter what. Like I said, maybe they figured it out in the latest version which is still in beta - be nice if they did.

[Dan S.]

@Ernie You did mention it. My reading comprehension tanks on Fridays.

[xaisoft]

@Ernie - I got around the memory issue by reading loading the data in chunks. I had 90K+ records and loaded 10K at a time with no out of memory.