Solved

C# thowing NullReferenceException when trying to access Excel cells

Posted on 2007-12-06
10
623 Views
Last Modified: 2008-02-01
I am trying to access data in cells from excel and put it into an array in C#. I do this for two different sheets and use the exact same syntax for both. One works perfectly fine and loads all the data correctly. The other throws a NullReferenceException saying that the range value is null. It seems to be getting the sheet assigned correctly and the col and row values are valid that have data in them on the spreadsheet so I hav no idea how one function can work and the other can't. The code is attached below.
public void loadForwardVolArray(ref Microsoft.Office.Interop.Excel.Workbook treeBook)
        {
            Microsoft.Office.Interop.Excel.Worksheet forwardSheet;
            forwardSheet = (Microsoft.Office.Interop.Excel.Worksheet)treeBook.Worksheets.get_Item("Forward Volatility");
            string name = forwardSheet.Name;
            int index = forwardSheet.Index;
            int row = 5;
            int col = 2;
            for (int k = 0; k < 120; k++)
            {
               
                    _forwardVolArray[k].adjusted = Convert.ToDouble(((Microsoft.Office.Interop.Excel.Range)forwardSheet.Cells[row, col + 3]).Value2.ToString());
                    _forwardVolArray[k].choppy = Convert.ToDouble(((Microsoft.Office.Interop.Excel.Range)forwardSheet.Cells[row, col + 1]).Value2.ToString());      //Choppy
                    _forwardVolArray[k].month = Convert.ToInt32(((Microsoft.Office.Interop.Excel.Range)forwardSheet.Cells[row, col]).Value2.ToString(), 10);         //Month
                    _forwardVolArray[k].smoothed = Convert.ToDouble(((Microsoft.Office.Interop.Excel.Range)forwardSheet.Cells[row, col + 2]).Value2.ToString());    //Smoothed
                    
               
                row++;
            }
        }

Open in new window

0
Comment
Question by:kbreaux25
  • 5
  • 5
10 Comments
 
LVL 25

Expert Comment

by:imitchie
ID: 20425167
try this pattern instead

                    _forwardVolArray[k].adjusted = Convert.ToDouble(((Microsoft.Office.Interop.Excel.Range)forwardSheet.Cells[row, col + 3]).Text);
saves you doing a tostring
0
 

Author Comment

by:kbreaux25
ID: 20425265
I tried the changes instead of null showing up when i put the cursor over it with the old syntax the correct value shows up, yet it is still throwing the exception. Any ideas?
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20425339
Actually, the TEXT is being cast to a Range..
try this instead

        _forwardVolArray[k].adjusted = Convert.ToDouble( ( forwardSheet.Cells[row, col + 3] ).Text );
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:kbreaux25
ID: 20425383
When i try that syntax I get an error that 'object' does not contain a definition for 'Text'. I still don't understand my old syntax would work for one sheet but not a different one, is there another issue to consider?
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20425395
>>I tried the changes instead of null showing up when i put the cursor over it with the old syntax the correct value shows up, yet it is still throwing the exception. Any ideas?

Is it still the same exception or another one? I'm assuming that you are making the same change to all 4 statements. Try this one

string dummy;
    dummy = ((Microsoft.Office.Interop.Excel.Range)forwardSheet.Cells[row, col + 3]).Text;
    _forwardVolArray[k].adjusted = dummy != "" ? Convert.ToDouble( dummy ) : 0;

and check dummy after assignment.
0
 

Author Comment

by:kbreaux25
ID: 20439278
the above syntax correctly assigns the value to dummy, but when i try to assign it to the array i get an exception
0
 

Author Comment

by:kbreaux25
ID: 20439318
just substituting in _forwardVolArray[k].adjusted = 5.0  throws the same error so its not a syntax problem. I just can't seem to figure out why this is occurring I've initialized the array and everything.
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20439347
I think your last comment is quite enlightening:
_forwardVolArray[k].adjusted = 5.0  << error
That puts the blame squarely on _forwardVolArray.  I'm guessing k is outside the range of the array.  Check the array length
0
 

Author Comment

by:kbreaux25
ID: 20439371
I wasn't initializing the array correctly.I'm new to c# and didnt' realize I needed to initialize every object in an array separately. Now it is working correctly.
0
 
LVL 25

Accepted Solution

by:
imitchie earned 500 total points
ID: 20439385
good on you
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The purpose of this article is to demonstrate how we can use conditional statements using Python.
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
The viewer will learn how to implement Singleton Design Pattern in Java.
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question