Solved

C# thowing NullReferenceException when trying to access Excel cells

Posted on 2007-12-06
10
634 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

734 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