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
– Paul Keister, Commented Sep 26, 2012 at 18:08

Paul Keister · Accepted Answer · 2012-09-26 22:13:58Z

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.

answered Sep 26, 2012 at 22:13

Paul Keister

13.1k5 gold badges50 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kobojunkie Over a year ago

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.

Paul Keister Over a year ago

This answer might help: stackoverflow.com/questions/4923359/…

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related