I've been scouring for days and I can't seem find what should be an easy answer. The displayed value is not necessarily the value that from cell.InnerText. I see post after post that requires you to look at the cell.StyleIndex property & cell.DataType.value to determine how to get the displayed value. And while I have plugged in some of this code and it works I hit another case I didnt handle where the cell StyleIndex is null which I can only assume the cell is formatted as "General". The on screen value "-39718.96" while the cell.InnerText returns "-39718.959999999999". I realize there is a easy fix to this by Double.Parse(value).ToString() but there seems to be an endless supply of formats https://stackoverflow.com/a/4655716/1713000 and that's a shortlist and dates are another problem. It seems we are left to handle each different style ourselves. Did Microsoft really not create a public api that returns the formatted displayed value and has no one really written a complete solution to handle this cluster of a mess?
Asked
Active
Viewed 59 times
0
-
1Have you tried the Nuget package ClosedXML? It makes things so much easier in Excel/OpenXML – Jan 21 '22 at 16:57
-
@JohnDaPleaserofWimmin Damn it, I completely forgot about that lib and i already use it in one of my other projects, thx! – medic Jan 21 '22 at 17:00
-
You're welcome. I hope it helps you get the fix you wanted. – Jan 21 '22 at 17:01
-
@JohnDaPleaserofWimmin just tested this and ClosedXML returned the displayed value, thx again! – medic Jan 21 '22 at 17:23
-
If I add it as an answer would you be kind enough to up vote it and mark it as an answer for me please? (I'm trying to improve me cred) – Jan 21 '22 at 19:40
-
@JohnDaPleaserofWimmin Sure – medic Jan 22 '22 at 17:05
-
Thanks a million @medic. If I can return the favour anytime just ask – Jan 22 '22 at 17:30