13

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?

user-44651
  • 3,924
  • 6
  • 41
  • 87

7 Answers7

15

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"];
Thomas Johnson
  • 400
  • 2
  • 10
7

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

Mpilo Z
  • 119
  • 1
  • 7
1

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.

John Hurrell
  • 350
  • 4
  • 11
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

israel
  • 181
  • 1
  • 6
0

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

ExcelWorksheet ws = workBook.Worksheets[0];

PANKAJ KUMAR MEENA
  • 1
0

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.
EhsanAlam
  • 1
  • 3
0

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
John Wright
  • 1