OpenXML reading wrong cell value if integer

Question

I am reading the cell values using this code;

            var cell = row.Elements<Cell>().FirstOrDefault();
            var stringId = Convert.ToInt32(cell.InnerText);

            var cellValue = workbookPart.SharedStringTablePart.SharedStringTable
                .Elements<SharedStringItem>().ElementAt(stringId).InnerText;

I am reading first cells of rows and get the value. My excel is like this.

     A    B
 1   x    name1
 2   y    name2
 3   1    name3

So when the row is 3, the stringId value is set as 1 and the cellValue is set as "x", but it should be 1.

petelids · Accepted Answer · 2018-11-14 13:14:21Z

You need to check the DataType of the cell as the "1" is stored as an actual number, not a string in the shared strings table.

var cell = row.Elements<Cell>().FirstOrDefault();
string cellValue = null;

if (cell.DataType != null && cell.DataType == CellValues.SharedString)
{
    //it's a shared string so use the cell inner text as the index into the 
    //shared strings table
    var stringId = Convert.ToInt32(cell.InnerText);
    cellValue = workbookPart.SharedStringTablePart.SharedStringTable
        .Elements<SharedStringItem>().ElementAt(stringId).InnerText;
}
else
{
    //it's NOT a shared string, use the value directly
    cellValue = cell.InnerText;
}

There are a couple of things to note: 1) The default type if one is not provided is Number 2) There are other types that are not handled by the code above. Dates are particularly awkward.

Collectives™ on Stack Overflow

OpenXML reading wrong cell value if integer

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related