poi-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bugzi...@apache.org
Subject [Bug 54636] getNumericCellValue return different result for visually equal numbers
Date Wed, 06 Mar 2013 15:28:14 GMT

--- Comment #7 from Mark B <markbrdsly@tiscali.co.uk> ---
Now I understand the question completely - I thought you were asking why POI
returned a numeric value for one of these cells rather than a nicely formatted
sting similar to the one the user sees when viewing the workbook using Excel.
Nick's response should, I think, be accorded greater weight. To my mind, this
problem is caused by the difficulties encountered representing a floating point
number and I will explain why I do think this.
The older binary file format is quite hard to interrogate without using a tool
like POI. As a result, I used Excel to convert your test file into OOXML and
saved it with the extension.xlsx. These are simply zipped archives of various
xml files and it is quite trivial to look at their contents directly.
Opening the OOXML based Excel archive and drilling down to the xml markup for
the first sheet reveals this fragment;
   <row r="1" spans="1:3">
      <c r="A1" s="1"><v>0.97915999999999992</v></c>
      <c r="B1" t="s"><v>0</v></c>
      <c r="C1" s="2"/>
   <row r="2" spans="1:3">
      <c r="A2" s="2"><v>0.97916000000000003</v></c>
      <c r="B2" t="s"><v>1</v></c>
      <c r="C2" s="2"/>
      <row r="3" spans="1:3">
      <c r="A3" s="2"><v>0.97916234499999999</v></c>
      <c r="B3" t="s"><v>2</v></c>

As you can no dobt intuit, this fragment contains the data for the sheet
arranged into rows. Each row contains child elements that define the markup for
the cells on the row and the key here are the numerical values contained within
the v - value - elements. As you can see, these are all flaoting point values
with a significant number of digits following the decimal point but all
display, when the sheet is viewed using Excel, exactly as you originally
described (I will attach the converted .xlsx workbook for you to see for
This suggests - to me at least - that you are seeing some sort of interaction
between the way Excel itself stores floating point values and the way they are
handled/represented internally. Consequently, I do not regard this as being a
bug within POI as it is simply reporting the values it finds within the
workbook files. All in my opinion of course and I could be wrong.

You are receiving this mail because:
You are the assignee for the bug.

To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org

View raw message