ClosedXML not returning correct cell value

0
votes

I am trying to get the value of a cell. In Excel it is displayed as 0.22 (formatted as number, 0.00). In the underlying Excel sheetXX.xml file the value is

<v>0.21970469798657719</v>

The value is the result of a formula.

When I try to access that cell from ClosedXML in C# I get 0.

sheet.Cell(22, 5).GetValue<double>(); // = 0

Same for

sheet.Cell(22, 5).CachedValue
sheet.Cell(22, 5).Value
sheet.Cell(22, 5).GetString()

etc...

I can see while debugging that the sheet.Cell(22, 5).InnerText property is correct though I don't know how to access that while not debugging.

No Exception is thrown at any point.

Why is ClosedXML not returning the correct value?

1
c#excelclosedxml
What about var testVal = sheet.Cell(22, 5).RichText.ToString(); does the value return or how about var forStr = sheet.Cell(22, 5).GetFormattedString(); – zaggler
sheet.Cell(22 5).HasRichText is true and sheet.Cell(row, 5).RichText.ToString() is "0" – Brad
Though it looks like after I try to access the RichText property the InnerText value changes to "0" – Brad
Interesting, Ill see if I can replicate. – zaggler

1 Answers

0
votes

I've found that trying to access a cell with some .GetValue<T>() method will either work or zero out the value so that subsequent attempts to access it by another means are unsuccessful. If, instead of ever using .GetValue<T>(), you only use

 double.TryParse(sheet.Cell(row, col).CachedValue.ToString(), out var valueOfInterst);

it is much more reliable.