EPPlus excel read IndexOutOfRangeException

Question

I want to read data from excel (.xlsx or .xls) file I am using EPPlus but it give that error IndexOutOfRangeException: Worksheet position out of range. in this line

  OfficeOpenXml.ExcelWorksheet workSheet = package.Workbook.Worksheets[0];

here is my all code.Here is my excel file for redad(http://yazilimsozluk.com/a.xlsx ) .Are there any solution for excel read which works with .xlsx and .xls excel file?

if (Request != null) {
  HttpPostedFileBase file = Request.Files["UploadedFile"];
  if ((file != null) && (file.ContentLength > 0) && !string.IsNullOrEmpty(file.FileName)) {
    string fileName = file.FileName;
    string fileContentType = file.ContentType;
    byte[] fileBytes = new byte[file.ContentLength];
    var data = file.InputStream.Read(fileBytes, 0, Convert.ToInt32(file.ContentLength));


    var existingFile = new System.IO.FileInfo(fileName);
    var package = new OfficeOpenXml.ExcelPackage(existingFile);

    OfficeOpenXml.ExcelWorksheet workSheet = package.Workbook.Worksheets[0];

    for (int i = workSheet.Dimension.Start.Column; i <= workSheet.Dimension.End.Column; i++) {
      for (int j = workSheet.Dimension.Start.Row; j <= workSheet.Dimension.End.Row; j++) {
        object cellValue = workSheet.Cells[i, j].Value;
      }
    }
  }
}

If you have a choice, just check for xls / xlsx and inform the user that you don't support xls. If you don't have a choice, you may have to go with some commercial library... honestly the free ones for xls never really worked for me - the support is a lot better for xlsx. Maybe you are lucky and someone has a better idea — grek40, Jun 09 '17 at 10:04
Ok I can inform user but .xlsx does not work,it give same error — user1688401, Jun 09 '17 at 10:07
Just so we are clear: please make sure you can open the specific file in excel without any warning message before you complain about errors when trying to use the same file with other programs. — grek40, Jun 09 '17 at 10:44
I can open without any warning....should I install openoffice to my pc ? — user1688401, Jun 09 '17 at 11:18
Are we still talking about the linked example (http://yazilimsozluk.com/a.xlsx)? That shouldn't be accepted by excel without complaint. — grek40, Jun 09 '17 at 11:19
In my pc it opened without complaint,may be differecen version? — user1688401, Jun 09 '17 at 11:30

Ogglas · Answer 1 · 2017-06-09T11:34:15.537

2

First of all EPPlus can not handle .xls files. See this answer:

Error when trying to read an .xls file using EPPlus

Sample code for reading a file:

var package = new ExcelPackage(new FileInfo("sample.xlsx"));

ExcelWorksheet workSheet = package.Workbook.Worksheets.FirstOrDefault();

for (int i = workSheet.Dimension.Start.Column;
        i <= workSheet.Dimension.End.Column;
        i++)
{
    for (int j = workSheet.Dimension.Start.Row;
            j <= workSheet.Dimension.End.Row;
            j++)
    {
        object cellValue = workSheet.Cells[i, j].Value;
    }
}

edited Jun 09 '17 at 11:34

answered Jun 09 '17 at 10:01

Ogglas

62,132
37
328
418

your code give error( Worksheet position out of range. ).Here is my excell file: http://yazilimsozluk.com/a.xlsx – user1688401 Jun 09 '17 at 10:13
That file is a renamed `.html`-file. ``. Fix: Rename to `.xls`, open it in Excel and then save as `xlsx`. – Ogglas Jun 09 '17 at 10:17
@user1688401 Updated, now it works. Try replacing `Worksheets[0];` with `Worksheets.FirstOrDefault();` in your code. – Ogglas Jun 09 '17 at 11:35
It works but it is null for me...Did you try with this ? yazilimsozluk.com/a.xlsx – user1688401 Jun 09 '17 at 11:53
@user1688401 Use my code, I get values. Got your file, converted it to `.xlsx` and could then read values. Here is the converted file: https://www.dropbox.com/s/30crslyowk6u2cw/a.xlsx?dl=0 – Ogglas Jun 09 '17 at 11:55
I download your file but some code does not work for me..I has office 2003 maybe because of this ? – user1688401 Jun 09 '17 at 12:04
@user1688401 Could give you an error if you try to open the file but if you run my code it should work. Anyway 2003 will remove some XML-tags I think even if you have the Microsoft Office Compatibility Pack. If you need to handle modern files you really should upgrade. https://www.microsoft.com/en-us/download/details.aspx?id=3 – Ogglas Jun 09 '17 at 12:11
I am sure your path for the file is wrong because i faced the same issue and unfortunately i was providing wrong path of the excel file to Read. When i gave the correct path the issue fixed. – Naveed Khan Oct 26 '21 at 12:03

EPPlus excel read IndexOutOfRangeException

1 Answers1