Worksheet position out of range. Connection Closed. When using EPPLUS

Question

I am trying to open an XLSX file as a template (I have even used a blank XLSX file), using EPPLUS 4.0.3.

If I do not open the template file (blank or the real one) and just create a new workbook and create a sheet, it works fine. OR if I open the template file, and create a NEW worksheet, then it works fine. It is only when I try to access the FIRST sheet in the template that I get the error: Worksheet position out of range.

Accessing the first worksheet like this: workBook.Worksheets.First() DOES NOT WORK.

First is no longer a definition.

So I tried accessing the first worksheet by name and by this method workBook.Worksheets[1] using both 0 and 1 to try to get the first sheet.

MY CODE:

    var existingTemplate = new FileInfo(_ExcelTemplateFilePath);
    using (ExcelPackage p = new ExcelPackage(existingTemplate)) {
    // Get the work book in the file
    ExcelWorkbook workBook = p.Workbook;
    ExcelWorksheet ws = workBook.Worksheets[1];
    // MY OTHER EXCEL CELL CODE HERE    
}}

Does anyone know how to access the first sheet of and Excel file?

Answer 1

I used

var currentSheet = package.Workbook.Worksheets;
var workSheet = currentSheet.First();

This is because the sheet might not have a known name and now you are interest in the first sheet

Answer 2

I was able to get around this issue by referring to the worksheet by name, rather than index.

var oSheet = package.Workbook.Worksheets["My Worksheet Name"];

Answer 3

to get the first sheet just use the below code

    var xlWorkbook = new ExcelPackage(new FileInfo(@"C:\ESD\EXCELDATAREADTEST.xlsx"));

ExcelWorksheet workSheet = xlWorkbook.Workbook.Worksheets[1];

just make sure you're pointing to the correct location for your workbook

Answer 4

I just had this same problem and the trouble is that EPPlus chokes on spreadsheets with named ranges.

Here's what I did (using LibreOffice Calc) to enable reading the spreadsheet:

1. Create a copy of the spreadsheet
2. Open in LibreOffice
3. List item
4. Click the drodpwon on the upper left corner for defining ranges. Typically reads "A1"
5. Select "Manage Names"
6. Highlight the entire list
7. Click "Delete"

Once I completed those steps, I saved/closed the spreadsheet and was able to open it with EPPlus.