Get all worksheets

user_1676889 0
Viewed 290

i've got this very strange error while trying to get the worksheets (just worksheet names) from excel (xlsx) file with EPPlus. 

using (var ep = new ExcelPackage(new FileInfo(filePath)) {
    foreach (var ws in ep.Workbook.Worksheets)
        ....
}

This works flawlesly (why shouldn't it right), but from time to time i find excel file that just throws System.NullReferenceException while trying to get worksheets.

Exception thrown: 'System.NullReferenceException' in EPPlus.dll

The thing that baffles me the most is that when i debug this and go step by step everything works fine.

This is the StackTrace:

   at OfficeOpenXml.ExcelRangeBase..ctor(ExcelWorksheet xlWorksheet, String address)
   at OfficeOpenXml.ExcelNamedRangeCollection.Add(String Name, ExcelRangeBase Range)
   at OfficeOpenXml.ExcelWorkbook.GetDefinedNames()
   at OfficeOpenXml.ExcelPackage.get_Workbook()
   at isef.winformapp.Helpers.ExcelUtilities.GetWorksheets(String filePath) ...

Im just wondering whether someone has encountered a similar error

You can download and try one of those 'not working' xlsx files here if you want to try it yourself.

It's an empty xlsx file with two empty worksheets.

epplus excel .net c#
1 Answers

The error occurs because there are defined names in the workbook that refer to a sheet/table/range that no longer exists. Referenced from here

First I was able to duplicate your error. Second, I deleted these Names and your code worked fine.

enter image description here

Unfortunately, the exception happens anytime you reference the workbook so I see no clear way to solve this outside the package.

var wb = ep.Workbook;  //also throws error when wb has invalid names
user_25210040
Related