Extracting data from excel using interop - ignore certain cells where no data is contained.

Question

Hi guys, I'm using c# and visual studio to extract data from an excel sheet, put it into an array (adding commas to the end of each string) and then output it to a .CSV file.

Now I've run into a snag that occurs when a cell has either no data, or not the data expected (i.e. an image).

I am assuming that this is on the reading end of the excel sheet, rather than the output to .CSV as when I get this error, I'm not getting any data put into the array.

Here's a snippet of the code that I'm using which has been adapted from a Microsoft example on accessing Excel data using interop.

    //Get a range of data.
            range = objSheet.get_Range(ranges1, ranges2);

            //Retrieve the data from the range.
            Object[,] saRet;
            saRet = (System.Object[,])range.get_Value(Missing.Value);

            //Determine the dimensions of the array.
            long iRows;
            long iCols;
            iRows = saRet.GetUpperBound(0);
            iCols = saRet.GetUpperBound(1);

            //Build a string that contains the data of the array.
            String valueString;
            valueString = "Prices";

            System.IO.StreamWriter OutWrite = new System.IO.StreamWriter("h:\\out.csv");

            for (long rowCounter = 1; rowCounter <= iRows; rowCounter++)
            {
                for (long colCounter = 1; colCounter <= iCols; colCounter++)
                {

                    //Write the next value into the string.
                    valueString = String.Concat(valueString,
                       saRet[rowCounter, colCounter].ToString() + ", ");
                }


                //Write in a new line.
                valueString = String.Concat(valueString, "\n");


            }

The error that I recieve is to do with the system.object line is:

Error: Object reference not set to an instance of an object, Line: WindowsFormApplication2

(No I haven't gotten around to renaming my project yet :P )

Cheers, Andy.

Answer 1

This line is dangerous:

valueString = String.Concat(valueString, saRet[rowCounter, colCounter].ToString() + ", ");

...as the result from saRet[rowCounter, colCounter] could be null. If an empty string is acceptable for an empty cell, use Convert.ToString(), which will interpret nulls as empty strings:

valueString = String.Concat(valueString, Convert.ToString(saRet[rowCounter, colCounter]) + ", ");

I suspect this is why you are getting the NullReferenceException.