Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

C# thowing NullReferenceException when trying to access Excel cells

Posted on 2007-12-06
10
Medium Priority
?
655 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
Quick Start: DOCKER

Sometimes you just need a Quick Start on a topic in order to begin using it.. this is just what you need to know to get up and running with Docker!

 

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 1500 total points
ID: 20439385
good on you
0

Featured Post

Quiz: What Do These Organizations Have In Common?

Hint: Their teams ended up taking quizzes, too.

Question has a verified solution.

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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This video teaches viewers about errors in exception handling.
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…

704 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