CODEWITHSUNDEEP
c#.netexcelepplus
eren
eren

Reputation: 810

Get all worksheets

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.

Upvotes: 4

Views: 509

Answers (1)

Automate This
Automate This

Reputation: 31364

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

Upvotes: 1

Related Questions