none
How to read /process numbers that have a "General" format (NumberFormatId = 0) RRS feed

  • Question

  • I found this on stackoverflow that describes the question I have, but the "answer" doesn't really address how to determine "what" the displayed value in Excel is.  Maybe it can't be determined.  If that's the case, that's okay because I can tell customers there's no way we can accurately process numeric fields with a "General" format.

    http://stackoverflow.com/questions/5859496/inconsistent-visualization-of-numerical-values-in-excel-2007-vs-the-underlying-x

    I have a spread sheet where I've left all the cell formats at "General".  All the values I entered have 1 position after the decimal place:

    As an example, here are how some of the values are retrieved in my test program:

    Displayed value     Actual value from file
    2.1                       2.2000000000000002
    2.2                       2.2000000000000002
    3.1                       3.1
    3.2                       3.2
    3.7                       3.7
    4.1                       4.0999999999999996

    Again, I don't care "why" they are stored the way they're stored, but am trying to determine if there's a way to determine the "displayed" format, when a true numbering format isn't assigned.

    Thanks.

    • Moved by Caillen Friday, November 7, 2014 6:49 AM
    Tuesday, November 4, 2014 7:46 PM

Answers

  • I disagree.  The question is about development of a program that is using the OpenXML SDK, not Excel features.  Evidently, there is no way under the circumstances that I described to "know" in the OpenXML objects what the "display" value is.

    Unless someone can offer a solution, I think that we would simply inform customers that this won't be supported or that an agreed way of formatting the columns, rows, etc. would be needed.  E.g. All cells in column "A" that are of "General" format will be formatted to one decimal place or two decimal places, etc.

    Wednesday, November 5, 2014 1:43 PM

All replies

  • Hello,

    This is actually about the feature of how Excel application display the number, but not about OpenXML SDK. The more suitable forum is Excel IT Pro Forum or Office XML, ODF, and Binary File Formats forum.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Edited by Caillen Friday, November 7, 2014 6:49 AM
    Wednesday, November 5, 2014 10:31 AM
  • I disagree.  The question is about development of a program that is using the OpenXML SDK, not Excel features.  Evidently, there is no way under the circumstances that I described to "know" in the OpenXML objects what the "display" value is.

    Unless someone can offer a solution, I think that we would simply inform customers that this won't be supported or that an agreed way of formatting the columns, rows, etc. would be needed.  E.g. All cells in column "A" that are of "General" format will be formatted to one decimal place or two decimal places, etc.

    Wednesday, November 5, 2014 1:43 PM