You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Daniel Noll <da...@nuix.com> on 2007/03/01 07:00:13 UTC
Re: Numeric Cell Type not consistent
Takahiro Sasaki wrote:
> I created the HSSFWorkbook with Microsoft Excel.
> The numeric cell value gets converted into the wrong value in the
> resultant HSSFCell in the numeric type.
> The value 0.769 in the file is converted to 0.7689999999999999 by
> LittleEndian in the fillFields(RecordInputStream in) method in
> NumberRecord class.
> If I save the file as a csv file and then save back as a xls file, this
> problem doesn't happen.
> Does any body know what is causing this problem? If this is a known
> bug, is there any work around?
It's not a bug but a misunderstanding. Floating point numbers will do
this from time to time. The workaround is that if you know you want
three decimal places, you can format it to three decimal places instead
of using String.valueOf/Double.toString.
Daniel
--
Daniel Noll
Nuix Pty Ltd
Suite 79, 89 Jones St, Ultimo NSW 2007, Australia Ph: +61 2 9280 0699
Web: http://nuix.com/ Fax: +61 2 9212 6902
This message is intended only for the named recipient. If you are not
the intended recipient you are notified that disclosing, copying,
distributing or taking any action in reliance on the contents of this
message or attachment is strictly prohibited.
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
Re: Numeric Cell Type not consistent
Posted by Daniel Noll <da...@nuix.com>.
David Fisher wrote:
> Ah yes, the difference between being rational and precise :-D
>
> In programming fortran for IBM 370s you couldn't say:
>
> IF ( A.EQ.1.0 )
>
> You had to say something like:
>
> IF ( ABS(A-1.0).LT.EPSILON )
>
> where EPSILON = 0.0001
Yup.
And this is also why in JUnit, assertEquals for floats and doubles has
an error parameter.
Daniel
--
Daniel Noll
Nuix Pty Ltd
Suite 79, 89 Jones St, Ultimo NSW 2007, Australia Ph: +61 2 9280 0699
Web: http://nuix.com/ Fax: +61 2 9212 6902
This message is intended only for the named recipient. If you are not
the intended recipient you are notified that disclosing, copying,
distributing or taking any action in reliance on the contents of this
message or attachment is strictly prohibited.
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
Re: Numeric Cell Type not consistent
Posted by David Fisher <df...@jmlafferty.com>.
Ah yes, the difference between being rational and precise :-D
In programming fortran for IBM 370s you couldn't say:
IF ( A.EQ.1.0 )
You had to say something like:
IF ( ABS(A-1.0).LT.EPSILON )
where EPSILON = 0.0001
REALly
My Finance Professor Father who compiled the first statistically
reliable database of NYSE stock prices (Monthly 1925 - 1964 - the
CRSP monthly database still maintained by the GSB at the University
of Chicago ) has always remarked about the trouble he had when he had
to convert to the IBM 360 in the early 60's. A REAL number went from
being in a Word of 6 bytes of 6 bits to Words of 4 bytes of 8 bits.
His dataset had to be converted from SINGLE to DOUBLE because the
loss of precision of going from 36 bits to 32 bits adversely effected
his results.
A good computer scientist must know how precise their calculations
are and make the trade offs.
Takahiro is encountering the fact that different implementations have
made their own decisions about numeric representations and it can be
hard to know how POI's are different from Open Office or Microsoft's.
For information on floating point: http://en.wikipedia.org/wiki/
IEEE_754 and follow some of the external links.
Regards,
Dave
On Mar 1, 2007, at 11:37 AM, Andrew C. Oliver wrote:
> You do realize that floating point numbers are always
> approximates? Both of the numbers below
> are "correct" ;-)
>>>
>>>
>> Are you sure this is not a bug of POI? This is not a random
>> problem. It
>> always does so for the file. Also I can't enforce the decimal
>> places. Also when I save as CSV and then back to XLS, this doesn't
>> happen.
>>
>> I debugged a little and figure out that the LittleEndian.getDouble
>> (final
>> byte[] data, final int offset) method returns different long bits
>> value
>> (one last digit is different), 4562254508917369340 (Corrent) and
>> 4605101755772172238 (Wrong), which is pass to the Double to get the
>> double value.
>>
>> I have attached the sample file so that the problem can be
>> reproduced. This file is created with the open office 2 by copying
>> paste cells from
>> the problematic ms excel xp file.
>>
>> Takahiro
>>
>> ---------------------------------------------------------------------
>> ---
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
>> Mailing List: http://jakarta.apache.org/site/mail2.html#poi
>> The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
>
>
> --
> No PST Files Ever Again
> Buni Meldware Communication Suite
> Email, Calendaring, ease of configuration/administration
> http://buni.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> Mailing List: http://jakarta.apache.org/site/mail2.html#poi
> The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
Re: Numeric Cell Type not consistent
Posted by "Andrew C. Oliver" <ac...@buni.org>.
You do realize that floating point numbers are always approximates?
Both of the numbers below
are "correct" ;-)
>>
>>
> Are you sure this is not a bug of POI? This is not a random problem. It
> always does so for the file. Also I can't enforce the decimal places.
> Also when I save as CSV and then back to XLS, this doesn't happen.
>
> I debugged a little and figure out that the LittleEndian.getDouble(final
> byte[] data, final int offset) method returns different long bits value
> (one last digit is different), 4562254508917369340 (Corrent) and
> 4605101755772172238 (Wrong), which is pass to the Double to get the
> double value.
>
> I have attached the sample file so that the problem can be reproduced.
> This file is created with the open office 2 by copying paste cells from
> the problematic ms excel xp file.
>
> Takahiro
>
> ------------------------------------------------------------------------
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> Mailing List: http://jakarta.apache.org/site/mail2.html#poi
> The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
--
No PST Files Ever Again
Buni Meldware Communication Suite
Email, Calendaring, ease of configuration/administration
http://buni.org
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
Re: Numeric Cell Type not consistent
Posted by Takahiro Sasaki <ts...@i2rd.com>.
Daniel Noll wrote:
> Takahiro Sasaki wrote:
>> I created the HSSFWorkbook with Microsoft Excel.
>> The numeric cell value gets converted into the wrong value in the
>> resultant HSSFCell in the numeric type.
>> The value 0.769 in the file is converted to 0.7689999999999999 by
>> LittleEndian in the fillFields(RecordInputStream in) method in
>> NumberRecord class.
>> If I save the file as a csv file and then save back as a xls file, this
>> problem doesn't happen.
>> Does any body know what is causing this problem? If this is a known
>> bug, is there any work around?
>
> It's not a bug but a misunderstanding. Floating point numbers will do
> this from time to time. The workaround is that if you know you want
> three decimal places, you can format it to three decimal places
> instead of using String.valueOf/Double.toString.
>
> Daniel
>
>
Are you sure this is not a bug of POI? This is not a random problem. It
always does so for the file. Also I can't enforce the decimal places.
Also when I save as CSV and then back to XLS, this doesn't happen.
I debugged a little and figure out that the LittleEndian.getDouble(final
byte[] data, final int offset) method returns different long bits value
(one last digit is different), 4562254508917369340 (Corrent) and
4605101755772172238 (Wrong), which is pass to the Double to get the
double value.
I have attached the sample file so that the problem can be reproduced.
This file is created with the open office 2 by copying paste cells from
the problematic ms excel xp file.
Takahiro