Index was out of range. Must be non-negative and less than the size of the collection.

Mar 1, 2012 at 2:47 AM

When I am opening xlsx file it is throwing exception "Index was out of range. Must be non-negative and less than the size of the collection."

Help is appreciated.

Mar 15, 2012 at 1:26 AM

Hi Sunil. I'm running into the same error with xls files created in Excel 2007 (xls files created in older versions of Excel or using third party tools like Crystal Reports work just fine).

This error happens when the IsFirstRowAsColumnNames attribute is set to true, so i know that isn't the problem. So it looks like the column name collection isn't being populated correctly when the file is generated in newer versions of Excel. Possibly it's failing to read the file stream entirely, so the reader object collection is entirely empty. Hopefully this information helps you in some way.

Have you found a fix for your issue? Or any additional information?

Cheers.

Mar 27, 2012 at 4:48 PM

I found a possible issue in ExcelOpenXmlReader.cs.

After the initial call CreateOpenXmlreader in my C# code-->

Trying to call "while (excelReader.Read()"... calls the Read() method in ExcelOpenXmlReader.cs.

This immediately does a boolean check and calls InitializeSheetRead().

This procedure makes use of the _resultIndex variable, which has been left at 1 by the CreateOpenXmlreader process. If I'm seeing things right, it should begin at zero for the Read() procedure.

I created a module-level variable called "private int firstRead = 0;"

I then changed the Read() procedure as follows:

public bool Read()
{
 if (!_isValid) return false;
 if (firstRead == 0) { _resultIndex = 0; firstRead = 1; }
 if (_isFirstRead && !InitializeSheetRead())
 {
  return false;
 }

 return ReadSheetRow(_workbook.Sheets[_resultIndex]);
}

This resets the _resultIndex variable and all went fine.

This is NOT thoroughly tested, but in my usage it all worked fine after this.

If your error is not happening in the InitializeSheetRead() procedure then you probably are seeing a different problem.

Bob Graham

 

 

 

Aug 20, 2012 at 2:33 PM

getting the same error: Index was out of range. Must be non-negative and less than the size of the collection.
here is the code i'm using... I'm using 2010 excel

            FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);

            //1. Reading from a binary Excel file ('97-2003 format; *.xls)
            //IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
            //...
            //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
            IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
            //...
            //3. DataSet - The result of each spreadsheet will be created in the result.Tables
            DataSet result1 = excelReader.AsDataSet();
            //...
            //4. DataSet - Create column names from first row

            //excelReader.IsFirstRowAsColumnNames = true;
            DataSet result = excelReader.AsDataSet();

           
            //5. Data Reader methods
            while (excelReader.Read())
            {
                //excelReader.GetInt32(0);
            }

            //6. Free resources (IExcelDataReader is IDisposable)
            excelReader.Close();