http://www.codeproject.com/KB/XML/ooxml ... ctive.aspx
Since we have had several cases logged for this, I wanted to send out a note to everyone. If you are experiencing rounding issues when you are loading data, unfortunately, this an Excel bug, not a Workday issue. The only workaround we have is to edit the data in Notepad. Keep in mind it is only an issue in certain cases. Here is an excerpt from a website with additional details:
Entered versus stored values
We all take for granted that when we type a value such as 1234.1234 in a cell of a spreadsheet, that's what actually gets stored. Excel has this auto-number
format matching capability where it tries to make sense of what is manually entered in order to deduce if that's a string, a number, a boolean or a
date and applies a number
format accordingly, but what's being stored as a value is what is entered. By the way, if you hit Alt+F11 in Excel, and enter something like Range("C3").Value and run the macro, you'll get the entered value in cell C3 ; if you enter something like Range("C3").Text, you'll get the formatted value in cell C3, where the number
format has been applied to the value. Note that the return value takes advantage of the locale and number formatting which means you may get "1234,1234" (note the comma) instead of "1234.1234".
Is this storage neutrality true with the new formats?
To reproduce the scenario :
* start Excel 2007 and create a new spreadsheet
* type value 123456.123456
* resize the column manually so that the value entirely shows
* hit return and type 12345.12345
* hit return and type 1234.1234
* hit return and type 123.123
* hit return and type 12.12
* save the spreadsheet (xlsx file)
* close it, unzip it
Here is a screenshot of what you should see at this point :
http://www.codeproject.com/KB/XML/ooxml ... esign3.gif
Typing a few numeric values in Excel 2007
The corresponding XML in the main part xl/worksheets/sheet1.xml is :
http://www.codeproject.com/images/minus.gifCollapse
<sheetData>
<row r="2" spans="4:4">
<c r="D2">
<v>123456.123456</v>
</c>
</row>
<row r="3" spans="4:4">
<c r="D3">
<v>12345.123449999999</v>
</c>
</row>
<row r="4" spans="4:4">
<c r="D4">
<v>1234.1233999999999</v>
</c>
</row>
<row r="5" spans="4:4">
<c r="D5">
<v>123.123</v>
</c>
</row>
<row r="6" spans="4:4">
<c r="D6">
<v>12.12</v>
</c>
</row>
</sheetData>
The problem is that Excel 2007 does not store what we entered. If we read the XML, we are going to grab numbers that have rounding errors compared to the actual numbers we typed. Let's see how far the problem goes :
Entered value
Stored value
Rounding error
123456.123456
123456.123456
0
12345.12345
12345.123449999999
o(1e-5)
1234.1234
1234.1233999999999
o(1e-4)
123.123
123.123
0
12.12
12.12
0
Not only there is a rounding error, but its order of magnitude changes depending on the value. Ironically enough, if you entered 4321.4321, it would be stored as is, with no rounding error.
It is absolutely lost on me how implementers are expected to deal with this mess. The spreadsheet does not reflect the proper values, and you can easily see where it goes. Imagine non-Microsoft applications used in healthcare and critical systems relying on the spreadsheet data. Not only the rounding error seems arbitrary (one would have to go back and study the artefacts of IEEE floating-point values, several decades of work), but it changes. There is no way we can possibly take advantage of this, with one notable exception : if we are able to be in an execution environment for which reading those floating-point values does not produce those artefacts, and returns the proper entered values, then we are good. Problem : Microsoft does not document the execution environment. We can fairly assume its Windows, but what else? And if I am using Linux, how do I work with this?
It's important to understand that if we open the spreadsheet in Excel 2007, we see the proper values. No loss (based on the values entered) seem to have occured, the problem is that the data in XML just cannot be used as is.
As an aside, the stored value does not use the locale (it always uses the dot as decimal separator), therefore we have to assume this is all US English. If we wrote software in Excel VBA that grabs the value in cells, then processes it, there is no way we could migrate our VBA code to work with this XML part without substantial rework. We are left with Excel's own international implementation artefacts, undocumented.
