Solved

C# thowing NullReferenceException when trying to access Excel cells

Posted on 2007-12-06
10
617 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
 

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
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: 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
groupSumClump challenge 9 98
Not showing page correctly 3 29
Angular JS Route 3 45
Change Format on Data when exporting to Excel 4 22
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
The viewer will learn how to implement Singleton Design Pattern in Java.
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

932 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now