Object reference not set while reading cells in Excel file

Question

I am using Excel.dll which is the excel reader available online (not the Microsoft.Office.Intertop.Excel) to read the excel having multiple records

Here is my code-

 Try
 {
       string path = "somelocalpath";
       foreach (var worksheet in Workbook.Worksheets(path))
       {
              //Some code
       }
 }
 catch (Exception e)
 {
       return false;
 }

Here, I am getting exception on first iteration of Workbook.Worksheets(path). There is excel worksheet available at the give local path (The worksheet has three columns and three rows first of which is header row.).

Also, it was working with no errors some time back so as per my understanding, the issue is not with the code but may be the format of worksheet I am not sure.

Can someone please help me on this.

You haven't opened the file yet - use something like `Workbook wb = Workbooks.Open(path)` to return a reference to the opened workbook and then `foreach(var ws in wb.Worksheets)` — Tim Williams, Jan 22 '19 at 05:34
That is a really bad way of doing error handling; catching exception, throwing away the valuable error information and then converting it to legacy C-style return code. — Uwe Keim, Jan 22 '19 at 05:45
@UweKeim This is just a sample code, there is lots of stuff inside catch block. — DevDotNet, Jan 22 '19 at 05:47

score 0 · Answer 1 · answered Jan 22 '19 at 05:39

0

you need to open your file and load the sheet to your range object. Something like this :

Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"myexcelfile.xlsx");
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;

after the initialize you can use your range in foreach

answered Jan 22 '19 at 05:39

Shino Lex

487
6
22

I cant find Application class inside Excel. Just for an update I am using Excel.dll. – DevDotNet Jan 22 '19 at 05:48
What does "Excel.dll" mean? Is this some kind of product by some kind of vendor? – Uwe Keim Jan 22 '19 at 05:51
You have added the Microsoft.Office.Intertop.Excel(Excell.dll) right? – Shino Lex Jan 22 '19 at 05:51
@ShinoLex No this is not Microsoft.Office.Intertop.Excel. This is excel reader available online I have downloaded. This has some extra features which are required in our application so not using the traditional Microsoft.Office.Intertop.Excel. Apologies for not making it clear. – DevDotNet Jan 22 '19 at 06:02
Even so you need a similar code that I've written in my answer. The problem is still same your range is empty, if you set breakpoint to that line and check the variable you'll see it's still null. So fill that variable before you use it and your problem will be solved – Shino Lex Jan 22 '19 at 06:12

Object reference not set while reading cells in Excel file

1 Answers1