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?

Literatim

Answer 1

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

Synaesthesia

Answer 2

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

Gerrit

Provides access to all the worksheets in the workbook. Note: Worksheets index // either starts by 0 or 1 depending on the Excelpackage.Compatibility.IsWorksheets1Based // property. Default is 1 for .Net 3.5 and .Net 4 and 0 for .Net Core. – Downtoearth

Answer 3

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.

Algesia

Answer 4

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

Henkel

Answer 5

You can start the index with ZERO, it will work.

ExcelWorksheet ws = workBook.Worksheets[0];

Siphon

Answer 6

Solution : This is because you are not providing correct excel file path which you have called or placed in _ExcelTemplateFilePath parameter. Reasons : either you have placed your file in wrong directory and your are calling from your desired path but unfortunately not.

1. Kindly just see example, if you have placed your excel file in current directory of project or solution (Note :Current directory of Vs studio will always be \bin\debug.netcoreapp public static FileInfo getfile = new FileInfo(Directory.GetCurrentDirectory() + @"\Login_Credentials.xlsx");
2. if your excel file path is static and hardcoded e-g (@"F:\task1 \TestTask\abc.xlsx") then your path must also be correct in given format then your file will be accessable.

Bullish

Answer 7

The problem is the file type is not exactly what EPPLUS desires. It opens the xlsx, sees the workbooks but not the worksheets. The solution is to save as the format it wants.

  Dim fiOriginal As System.IO.FileInfo
    Dim fiTemp As System.IO.FileInfo
    Dim strOriginalName As String
    Dim strTempName As String

Rem Get a temp name 
    strTempName = IO.Path.GetTempFileName()
    strTempName = strTempName & ".xlsx"

Rem Have EPPLUS save the original as a temp in its format
    fiOriginal = New System.IO.FileInfo(strOriginalName)
    Using pckSrc As New ExcelPackage(fiOriginal)
        pckSrc.SaveAs(fiTemp)
    End Using
    
Rem Process the temp or replace the original and see EPPLUS happy.
    fiOriginal = New System.IO.FileInfo(strTempName)
    Using pckSrc As New ExcelPackage(fiOriginal)
        Dim wBookSrc As ExcelWorkbook = pckSrc.Workbook
        Dim wkShtSrc As ExcelWorksheet = wBookSrc.Worksheets(0)
    
    End Using

Dipsomaniac