Clever Geek Handbook

Worksheet position out of range. The connection is closed. When using EPPLUS

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

If I don't open the template file (empty or real), but 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's only when I try to access the FIRST sheet in the template, I get the error: Worksheet position out of range.

Accessing the first table looks like this: workBook.Worksheets.First()

DOES NOT WORK.

This is not a definition at first.

So I tried to access the first sheet by name and this method workBook.Worksheets[1]

using both 0 and 1 to try and 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 and Excel file?

+3


source to share


3 answers


I managed to work around this issue by referring to the sheet by name rather than index.



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


source


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 point to the correct location for your book

+2


source


I had the same problem and the problem is EPPlus is choking on spreadsheets with named ranges.

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

  • Create a copy of the spreadsheet
  • Open in LibreOffice
  • List item
  • Click the drodpwon button in the upper left corner to define the ranges. Usually reads "A1"
  • Select "Manage Names"
  • Highlight the entire list
  • Click "Delete"

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

0


source






More articles:


All Articles