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

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:

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

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

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"];

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

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

Related