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

user-44651 picture user-44651 · Apr 21, 2015 · Viewed 14.4k times · Source

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

Thomas Johnson picture Thomas Johnson · Apr 11, 2016

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