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

The question would be a lot easier if you post the exception message and the message of the inner exception, if there is one. — Paul Keister, Sep 26 '12 at 18:08

score 1 · Accepted Answer · answered Sep 26 '12 at 22:13

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.

do you have any idea if there is a way to FORCE the row cells to remain in the same order they appear in the Excel document? I ask because when I read some of my rows, the cells are not in order so I end up reading the wrong data to the wrong database column as a result. — Kobojunkie, Oct 05 '12 at 00:22
This answer might help: http://stackoverflow.com/questions/4923359/how-do-i-have-open-xml-spreadsheet-uncollapse-cells-in-a-spreadsheet — Paul Keister, Oct 05 '12 at 04:30

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

1 Answers1