You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2009/11/04 11:43:23 UTC
DO NOT REPLY [Bug 48124] New: The Precision is lossed when i prasing
excel used poi
https://issues.apache.org/bugzilla/show_bug.cgi?id=48124
Summary: The Precision is lossed when i prasing excel used poi
Product: POI
Version: 3.5-FINAL
Platform: PC
OS/Version: Linux
Status: NEW
Severity: critical
Priority: P5
Component: HSSF
AssignedTo: dev@poi.apache.org
ReportedBy: xunwei@csii.com.cn
hi
I am so sorry ,my english is not very well.But i have some trouble in using
POI to parse excel,the precision is lossed.
For exampe,the right amount is:1860.68,but the result is 1860.6800000000001
when i using poi in Linux 64-bit and the poi version is poi-3.5-FINAL.
If I run the project in Linux 32-bit,the result is right.But I can't change
the os because it's a production.
Pls give me some suggestions and i must solve it quickly because many users
can't use our product.
This is the testing code in Linux RatHat5.0 64-bit :
/**
*This function return 1860.6800000000001 which i see in the log,but the
* amount is 1860.68 in the excel
**/
public double getNumericCellValue(HSSFSheet hssfsheet, int i, int j)
{
HSSFCell hssfcell = getCell(hssfsheet, i , j );
double value=hssfcell.getNumericCellValue();
return vaule;
}
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- 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
Re: DO NOT REPLY [Bug 48124] The Precision is lossed when i prasing
excel used poi
Posted by MSB <ma...@tiscali.co.uk>.
Thanks for that David; I was certain this question had been asked recently
and looked for your previous response but could not find it. Might be worth
pointing out to the OP that if all they are doing is logging the value then
they could simply return the result from
HSSFDataFormatter.convertCellValue(Cell);
Yours
Mark B
Bugzilla from bugzilla@apache.org wrote:
>
> https://issues.apache.org/bugzilla/show_bug.cgi?id=48124
>
> David Fisher <df...@jmlafferty.com> changed:
>
> What |Removed |Added
> ----------------------------------------------------------------------------
> Status|NEW |NEEDINFO
>
> --- Comment #1 from David Fisher <df...@jmlafferty.com> 2009-11-04
> 10:26:39 UTC ---
> I am going to answer you in two parts.
>
> (1) How floating point numbers work.
>
> This is not a POI issue. It is how doubles work. Double precision numbers
> are
> atored in a certain binary format and on 64 bit architectures you get more
> bits
> in your mantissa. This is the mathematics of numbers represented by the
> form of
> mantissa ** exponential, like for instance 1 / 3 = 0.3333333333333333. 64
> bits
> gives you more. Some hardware even uses extra bits beyond the 64.
>
> I suggest that you try these google searches and learn about how floating
> point
> works.
>
> (a) 32 bit jvm on 64 bit linux
>
> (b) ieee-754
>
> (c) java double precision
>
> If you want further explanation then you will need to tell us about your
> hardware and jvm versions, and we might have an insight.
>
> You might be better off on a linux or redhat list for this issue.
>
> (2) A question.
>
> In your program how are you using the result of getNumericCellValue? How
> are
> you writing the value to your log?
>
> Regards,
> Dave
>
> --
> Configure bugmail:
> https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
> ------- 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 this message in context: http://old.nabble.com/DO-NOT-REPLY--Bug-48124--New%3A-The-Precision-is-lossed-when-i-prasing-excel-used-poi-tp26194123p26202070.html
Sent from the POI - Dev mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 48124] The Precision is lossed when i prasing
excel used poi
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=48124
David Fisher <df...@jmlafferty.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |NEEDINFO
--- Comment #1 from David Fisher <df...@jmlafferty.com> 2009-11-04 10:26:39 UTC ---
I am going to answer you in two parts.
(1) How floating point numbers work.
This is not a POI issue. It is how doubles work. Double precision numbers are
atored in a certain binary format and on 64 bit architectures you get more bits
in your mantissa. This is the mathematics of numbers represented by the form of
mantissa ** exponential, like for instance 1 / 3 = 0.3333333333333333. 64 bits
gives you more. Some hardware even uses extra bits beyond the 64.
I suggest that you try these google searches and learn about how floating point
works.
(a) 32 bit jvm on 64 bit linux
(b) ieee-754
(c) java double precision
If you want further explanation then you will need to tell us about your
hardware and jvm versions, and we might have an insight.
You might be better off on a linux or redhat list for this issue.
(2) A question.
In your program how are you using the result of getNumericCellValue? How are
you writing the value to your log?
Regards,
Dave
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- 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
DO NOT REPLY [Bug 48124] The Precision is lossed when i prasing
excel used poi
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=48124
Josh Micich <jo...@gildedtree.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEEDINFO |RESOLVED
Resolution| |INVALID
--- Comment #3 from Josh Micich <jo...@gildedtree.com> 2009-11-04 18:08:41 UTC ---
This problem is almost certainly independent of POI. The underlying floating
point values are being stored/retrieved properly and any numerical calculations
you perform on those will be fine (within normal IEEE double constraints).
Your problem seems to be related to text conversion, in particular the method
"public static String toString(double d)" on class java.lang.Double in your
JRE.
If you are concerned about the apparent error in the converted text value, you
can attempt to get around these problems by using the POI class
NumberToTextConverter (which uses Excel rules instead of JRE rules for text
conversion). Alternatively, if you have specified an number format in Excel
(e.g. "2 decimal places") you can try HSSFDataFormatter.convertCellValue(Cell)
to reproduce the same formatting.
-- -- -- --
Below I have added some details to help explain the source of your error.
Your value "1860.68" is represented by the double with raw bit value
0x409D12B851EB851FL.
The following code shows this double value and its two neighbours:
System.out.println(Double.longBitsToDouble(0x409D12B851EB851EL));
System.out.println(Double.longBitsToDouble(0x409D12B851EB851FL));
System.out.println(Double.longBitsToDouble(0x409D12B851EB8520L));
On my JREs (Sun 1.5/WinXP and Sun 1.6/Ubuntu) I get this:
1860.6799999999998
1860.68
1860.6800000000003
I suspect you'll get 1860.6800000000001 for the second line
Hopefully it's clear that POI *is* reading the correct double value from your
Excel file (since 1860.68+1e-13 is still closer to 1860.68 than 1860.68+3e-13).
To dig a little deeper, you can see the exact (over precise) decimal
representation of these same three IEEE double values with the following code:
BigDecimal x = new BigDecimal(new BigInteger("2").pow(42));
System.out.println(new BigDecimal(Long.toString(0x1D12B851EB851EL)).divide(x));
System.out.println(new BigDecimal(Long.toString(0x1D12B851EB851FL)).divide(x));
System.out.println(new BigDecimal(Long.toString(0x1D12B851EB8520L)).divide(x));
Which gives:
1860.67999999999983629095368087291717529296875
1860.680000000000063664629124104976654052734375
1860.6800000000002910383045673370361328125
Now from this you might conclude that your JRE is actually doing the correct
thing, since the true value (~1860.68+6.366e-14) is closer to 1860.68+1e-13
than 1860.68. However, if you read the javadoc of Double.toString(double), you
will see the following:
"How many digits must be printed for the fractional part of m or a? There must
be at least one digit to represent the fractional part, and beyond that as
many, but only as many, more digits as are needed to uniquely distinguish the
argument value from adjacent values of type double"
http://java.sun.com/j2se/1.5.0/docs/api/java/lang/Double.html#toString(double)
So, since "1860.68" still uniquely identifies double value 0x409D12B851EB851FL
(i.e. there is no other double value closer to 1860.68), only 2 decimal places
are required. Perhaps you should log a bug with your JRE provider.
BTW if you look for 0x409D12B851EB851FL in the hex dump of your XLS file you
won't find it. This is because 1860.68 has been encoded as an RKNum with value
1090959009 (A1 B6 06 41) which Excel/POI automatically translate into a double
with value 0x409D12B851EB851FL.
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- 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
Re: DO NOT REPLY [Bug 48124] New: The Precision is lossed when i
prasing excel used poi
Posted by MSB <ma...@tiscali.co.uk>.
I cannot promise that this WILL work but it ought to even though it is a very
convoluted way to solve the problem and I can only apologise that I cannot
think of anything better. Try using the
org.apache.poi.hssf.usermodel.HSSFDataFormatter class to convert the number
into a String but a String that is formatted as the cell would be, then
convert the String back into a double value. Something like this;
public double getNumericCellValue(HSSFSheet hssfsheet, int i, int j)
{
HSSFCell hssfcell = getCell(hssfsheet, i , j );
String strValue = HSSFDataFormatter.formatCellValue(hssfCell);
double value = Double.parseDouble(strValue);
return value;
}
Another possible solution would have been to recover the data formatting
String applied to the cell, counted how may digits followed the decimal
separator and then truncated the double value at the appropriate point.
The real villain it seems to me is that old problem; accurately representing
numerical values.
Hope this helps a little.
Yours
Mark B
Bugzilla from bugzilla@apache.org wrote:
>
> https://issues.apache.org/bugzilla/show_bug.cgi?id=48124
>
> Summary: The Precision is lossed when i prasing excel used poi
> Product: POI
> Version: 3.5-FINAL
> Platform: PC
> OS/Version: Linux
> Status: NEW
> Severity: critical
> Priority: P5
> Component: HSSF
> AssignedTo: dev@poi.apache.org
> ReportedBy: xunwei@csii.com.cn
>
>
> hi
> I am so sorry ,my english is not very well.But i have some trouble in
> using
> POI to parse excel,the precision is lossed.
> For exampe,the right amount is:1860.68,but the result is
> 1860.6800000000001
> when i using poi in Linux 64-bit and the poi version is poi-3.5-FINAL.
> If I run the project in Linux 32-bit,the result is right.But I can't
> change
> the os because it's a production.
> Pls give me some suggestions and i must solve it quickly because many
> users
> can't use our product.
>
> This is the testing code in Linux RatHat5.0 64-bit :
>
> /**
> *This function return 1860.6800000000001 which i see in the log,but
> the
> * amount is 1860.68 in the excel
> **/
> public double getNumericCellValue(HSSFSheet hssfsheet, int i, int j)
> {
> HSSFCell hssfcell = getCell(hssfsheet, i , j );
> double value=hssfcell.getNumericCellValue();
> return vaule;
>
> }
>
> --
> Configure bugmail:
> https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
> ------- 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 this message in context: http://old.nabble.com/DO-NOT-REPLY--Bug-48124--New%3A-The-Precision-is-lossed-when-i-prasing-excel-used-poi-tp26194123p26202022.html
Sent from the POI - Dev mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 48124] The Precision is lossed when i prasing
excel used poi
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=48124
--- Comment #2 from Josh Micich <jo...@gildedtree.com> 2009-11-04 17:57:36 UTC ---
*** Bug 48081 has been marked as a duplicate of this bug. ***
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- 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