Not able to open Worksheet

Jun 8, 2011 at 10:23 AM

Hi, 

I am not able to open up an excel sheet using EPPlus. Below is my code I am just trying to get reference of Worksheet so that I can play on that:

     string filePath = txtboxSelectedFile.Text;
            FileInfo file = new FileInfo(filePath);
            ExcelPackage package = new ExcelPackage(file);   
        ExcelWorksheet sheet = package.Workbook.Worksheets[0];

 

 

Exception details with stack trace is :

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

   at System.ThrowHelper.ThrowArgumentOutOfRangeException()   at System.Collections.Generic.List`1.get_Item(Int32 index)   at OfficeOpenXml.ExcelStyleCollection`1.get_Item(Int32 PositionID)   at OfficeOpenXml.Style.ExcelStyle..ctor(ExcelStyles styles, ChangedEventHandler ChangedEvent, Int32 positionID, String Address, Int32 xfsId)   at OfficeOpenXml.Style.XmlAccess.ExcelNamedStyleXml..ctor(XmlNamespaceManager NameSpaceManager, XmlNode topNode, ExcelStyles styles)   at OfficeOpenXml.ExcelStyles.LoadFromDocument()   at OfficeOpenXml.ExcelStyles..ctor(XmlNamespaceManager NameSpaceManager, XmlDocument xml, ExcelWorkbook wb)   at OfficeOpenXml.ExcelWorkbook.get_Styles()   at OfficeOpenXml.ExcelCell.get_Style()   at OfficeOpenXml.ExcelWorksheet.GetValueFromXml(ExcelCell cell, XmlTextReader xr)   at OfficeOpenXml.ExcelWorksheet.LoadCells(XmlTextReader xr)   at OfficeOpenXml.ExcelWorksheet.CreateXml()   at OfficeOpenXml.ExcelWorksheet..ctor(XmlNamespaceManager ns, ExcelPackage excelPackage, String relID, Uri uriWorksheet, String sheetName, Int32 sheetID, Int32 positionID, eWorkSheetHidden hide)   at OfficeOpenXml.ExcelWorksheets..ctor(ExcelPackage xlPackage)   at OfficeOpenXml.ExcelWorkbook.get_Worksheets()   at TimeSheetTracker.Main.ProcessInputs() in D:\TryOut Projects\TimeSheetTracker\TimeSheetTracker\Main.cs:line 39   at TimeSheetTracker.Main.btnProcess_Click(Object sender, EventArgs e) in D:\TryOut Projects\TimeSheetTracker\TimeSheetTracker\Main.cs:line 25

 

The Excel sheet that I am trying to open is of .xlsx type and contains only one worksheet with some data.

The thing is when I copy my data into one of the sample .xlsx files of EPPlus it works fine.   

Does anybody knows that what can be the problem??

 

Thanks in advance

Regards

Sumit

Jun 8, 2011 at 10:41 PM

Same problem here. Workbook.Worksheets container is null. It does not happen with every file - I've created a test file and it opened without problems. Problematic file is huge, lots of sheets were created, deleted, moved, etc... Tried fixing it manually editing sheet ids after unziping xlsx file so that they would be in sequence from 1, it didn't help.

Btw, I think you should use index of 1, not zero:

ExcelWorksheet sheet = package.Workbook.Worksheets[1];

Sheets are numbered starting from 1.

Coordinator
Jun 9, 2011 at 11:44 AM

I think this is the same problem as issue 13639.

Try the latest source and see if it solves your problem.

Jan

Jun 9, 2011 at 9:41 PM
jankallman wrote:

I think this is the same problem as issue 13639.

Try the latest source and see if it solves your problem.

Jan

I'm using latest version, I think (I've downloaded it two or three days ago).

Developer
Jun 10, 2011 at 6:28 AM
semanthos wrote:
jankallman wrote:

I think this is the same problem as issue 13639.

Try the latest source and see if it solves your problem.

Jan

I'm using latest version, I think (I've downloaded it two or three days ago).


Jan means that you should get the latest source code and build it yourself. Eventually there will be a 2.9.0.2, but until then bugfixes are only available in the source code.

Jul 11, 2011 at 1:00 PM

Have the same trouble with one file. It is not big, or something - looks normal.

Using release from May 31.

Jul 11, 2011 at 1:00 PM
Edited Jul 11, 2011 at 1:11 PM

Have the same trouble with one file. It is not big, or something - looks normal.

Using release from May 31.

Update: it looks working with the latest source.

Sep 27, 2011 at 12:05 AM

I have the same issue.  I downloaded the most recent source code and built it but still getting the error...

'package.Workbook.Worksheets' threw an exception of type 'System.ArgumentOutOfRangeException'
"Index was out of range. Must be non-negative and less than the size of the collection.\r\nParameter name: index"

The problem workbook is very large (6MB with hundreds of worksheets and takes about 20-30 seconds to load in Excel).  As soon as I load the file, which happens very quickly, _worksheets is null.  Same code works for a smaller workbook (3 worksheets) that is generated by the same system.

FileInfo fiExcel = new FileInfo(txtFilename.Text);
using (ExcelPackage package = new ExcelPackage(fiExcel))

I don't have control over the source as it's generated by another system.  I need to split it up into multiple workbooks but can't get it to open successfully.

Any workarounds to this?

Thanks,
Jim

Sep 27, 2011 at 1:29 AM
May be like this:

 

     string filePath = txtboxSelectedFile.Text;
            FileInfo file = new FileInfo(filePath);
            ExcelPackage package = new ExcelPackage(file);   
        ExcelWorksheet sheet = package.Workbook.Worksheets[1];//start from 1

 

Sep 27, 2011 at 12:22 PM
hekucn wrote:
May be like this:

 

     string filePath = txtboxSelectedFile.Text;
            FileInfo file = new FileInfo(filePath);
            ExcelPackage package = new ExcelPackage(file);   
        ExcelWorksheet sheet = package.Workbook.Worksheets[1];//start from 1

 


Thanks. I did try that, as well referencing the first sheet by name, but it doesn't work.  I also tried...

MessageBox.Show(package.Workbook.Worksheets.Count.ToString());

... just to get the sheet count but the source file completely fails to load so that errors out too.

 

Coordinator
Sep 27, 2011 at 2:45 PM

Hard to tell what the problem is.

Create an issue and upload xlsx file and your code (or mail it to epplusdocs@gmail.com).

Jan

Sep 28, 2011 at 2:01 AM

The source file is generated by a financial reporting system so I opened it in Excel to cleanse it of any sensitive info before uploading it.  Before uploading it I tested it again and of course it loaded successfully. :o)  I had also removed some worksheets in that process so I thought that may have been what fixed the issue so I tried another cleansed version with all worksheets and that also worked.

It looks like it's something with the format of the file being generated by the reporting system.  The original file size is 7.9 MB.  If I open it in Excel, do nothing but just save it again and close, the file becomes 8.1 MB.  That version then loads properly in EPPlus.

Thanks for the feedback, it looks like I'll need to address the issue on the source side.  Worst case scenario they need to open the workbook, save, and close again before processing.

Oh, and thanks for producing this library, it works great.

-Jim

Nov 15, 2011 at 7:16 AM

I have the same problem. So what is the last solve? I can not make it until I copy the file to a new one. So is there any other way?

Coordinator
Nov 17, 2011 at 5:44 AM

You have to upload your file (or mail it to epplusdocs@gmail.com).

Nov 29, 2011 at 2:03 PM
Edited Nov 29, 2011 at 2:04 PM

I had the same problem, now its working (I was using release 2.9.0.1, latest release by now).

I went to http://epplus.codeplex.com/SourceControl/list/changesets, downloaded the latest source code and compiled the project \trunk\ExcelPackage. I updated the reference uin my project with the generated EPPLUS.dll and now its working OK.

Thanks a lot for this excelente tool, I've been using it for a while and this is the first issue I have with it.

 

Daniel.

Dec 5, 2011 at 11:45 PM
JanKallman wrote:

You have to upload your file (or mail it to epplusdocs@gmail.com).


I have sent my file to your email. but you never answer me. this paoblem is really confused me and my work has many files like that to deal with,so if I must copy each of them to a new file,it can be dead to me.I need your help.please answer me,thank you.

Feb 14, 2012 at 11:29 AM

I had the same error, And managed to make it work by using following code

ExcelPackage excelPackage = new ExcelPackage();

excelPackage.Load(new FileStream(excelFile, FileMode.Open));

 ExcelWorksheet workSheet = excelPackage.Workbook.Worksheets.["Sheet1"];

Feb 4, 2014 at 1:00 AM
In my case, the file name is too long.

I change to "text.xlsx" then working
Sep 26, 2014 at 12:13 PM
Did anyone solve this issue ?

I have some .xlsx files which are working and some which are not. They all look ok.

LAST LINE FAILS ------------------------------------------------------------------------

using (ExcelPackage xlPackage = new ExcelPackage(fi))
{
    // get the first worksheet in the workbook
    ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[1];
EXCEPTION ----------------------------------------------------------------------------

An unhandled exception of type 'System.NullReferenceException' occurred in EPPlus.dll

Additional information: Object reference not set to an instance of an object.

However if i open the failing file, and press save, it's working next time !

any suggestions ?
Sep 26, 2014 at 1:00 PM
Updated to version 4.0.0.1 and initial testing seems to show the problem has disappeared