Accessing Cell values in Excel Row using OpenXML - Object reference not set to an instance of an object

Question

I have a requirement to read values from an excel spreadsheet and save the values to the database. Problem I seem to be having at this time is accessing the individual values in the columns in my row object

http://msdn.microsoft.com/en-us/library/office/documentformat.openxml.spreadsheet.cellvalue.aspx

var cellValues = from cell in row.Descendants<Cell>()
                                                     select (cell.DataType != null && cell.DataType.HasValue && cell.DataType == CellValues.SharedString
                                                       && int.Parse(cell.CellValue.InnerText) < sharedString.ChildElements.Count 
                                                        ? sharedString.ChildElements[int.Parse(cell.CellValue.InnerText)].InnerText 
                                                        : cell.CellValue.InnerText);

Unfortunately, the code above does not seem to do the job as it throws an exception when run so looking for best ideas on how to access the values that are contained in the Excel Row object using OpenXML.

+$exception {"Object reference not set to an instance of an object."} System.Exception {System.NullReferenceException}

Resulting StackTrace

   at MOC.Import.Products.<ProcessRows>b__0(Cell cell) in c:\Development\CFrontEnd\MOC.Import\Products.cs:line 37
   at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
   at System.Linq.Enumerable.Count[TSource](IEnumerable`1 source)
   at MOC.Import.Products.ProcessRows(IEnumerable`1 dataRows, SharedStringTable sharedString) in c:\Development\CFrontEnd\MOC.Import\Products.cs:line 45

Answer 1

One of the objects that you're referencing is null. The easiest way to find the specific object that's causing the problem is to take the filtering and formatting code out of your LINQ expression and just iterate through the cells:

var cellValues = from cell in row.Descendants<Cell>()
                    select cell;

foreach (var cell in cellValues)
{
    if(cell.DataType != null 
        && cell.DataType.HasValue 
        && cell.DataType == CellValues.SharedString
        && int.Parse(cell.CellValue.InnerText) < sharedString.ChildElements.Count)
    {
        DoSomething(sharedString.ChildElements[int.Parse(cell.CellValue.InnerText)].InnerText);
    }
    else
    {
        DoSomething(cell.CellValue.InnerText);
    }
}

Using this structure, it will be easy to detect the problem in the debugger. You can also unwind this code even more and add more guards against nulls to make your code more robust. Your problem in a nutshell is that you are making invalid assumptions about the structure of the document you're reading. As a general rule assumptions are bad, especially when they are assumptions about input over which you don't have full control.