How to access worksheets in EPPlus?

Question

I'm using the 3.1 release of EPPlus library to try to access a worksheet in an Excel file. When I try either of the following methods I get a System.ArgumentException : An item with the same key has already been added.

using (ExcelPackage package = new ExcelPackage(new FileInfo(sourceFilePath)))
{
   var worksheet = package.Workbook.Worksheets[0];

   // OR

   foreach (var excelWorksheet in package.Workbook.Worksheets)
   ...
}

Exception Stack:

System.ArgumentException : An item with the same key has already been added.
   at System.ThrowHelper.ThrowArgumentException(ExceptionResource resource)
   at System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add)
   at System.Collections.Generic.Dictionary`2.Add(TKey key, TValue value)
   at OfficeOpenXml.ExcelNamedRangeCollection.Add(String Name, ExcelRangeBase Range)
   at OfficeOpenXml.ExcelWorkbook.GetDefinedNames()
   at OfficeOpenXml.ExcelPackage.get_Workbook()

This seems like very basic functionality to have be so broken.. am I doing something wrong?

Coroner

Is it possible that the excel file was a xls file originally? EPPlus does only understand xlsx. Try to save it as xlsx manually first. – Sundried

Answer 1

The workbook in question had named ranges defined. These were causing problems so I created a new xlsx file with just the data I needed and it was able to open fine.

Coroner

I know this is 2 years old, but if this solved your problem, you should accept your own answer to help highlight it for others who may have the same issue – Rebecarebecca

this was the only probable solution for me, among all answers. I deleted Named Ranges; it didn't help. Point is that Worksheets object by itself throws the exception. – Caia

Answer 2

I believe that excel does worksheets from index 1 not index 0

 var worksheet = package.Workbook.Worksheets[0]; 

should be

var worksheet = package.Workbook.Worksheets[1];

to read the first worksheet.

Martial

In excel and pretty everything that connects to it, indexes start with 1, not 0. Nice reminder. Thanks. – Uncovenanted

Answer 3

Also, you can manage them by referencing the name:

var worksheet = package.Workbook.Worksheets["Sheet1"];

Prudi

Answer 4

At least with Epplus 3.1.3.0 you can simply use the following to access the first worksheet.

ExcelWorksheet workSheet = excel.Workbook.Worksheets.First();

Exsect

Answer 5

Make sure the document created or saved with MS Excel (not OpenOffice, Libre Office, etc.)

Parterre

Answer 6

To access/find all worksheets, I used:

 var file = new FileInfo(filename);

 ExcelPackage pck = new ExcelPackage(file);
           
 foreach (var excelWorksheet in pck.Workbook.Worksheets) 
 {
     Console.WriteLine("found sheet: " + excelWorksheet.Name);
 }

Disfranchise