CODEWITHSUNDEEP
c#excelopenxmlopenxml-sdk
barteloma
barteloma

Reputation: 6875

OpenXML reading wrong cell value if integer

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.

Upvotes: 1

Views: 2244

Answers (1)

petelids
petelids

Reputation: 12815

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.

Upvotes: 3

Related Questions