You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Andreas Reichel <an...@manticore-projects.com> on 2019/10/18 02:34:41 UTC
Floating Point Arithmetics, reading 0.1066913 gives
0.10669129999999999
Dear All,
compliments of the day.
We face some challenges with reading values from Spread Sheets.
Example: the numeric cell has the value = 0.1066913 and when reading
that value with POI we receive the double = 0.10669129999999999.
This turns into a problem, when writing these figures into a database,
when the Precision/Scale of the field can't hold that double value,
e.g.
JdbcSQLDataException: Value too long for column """BASE_RATE""
VARCHAR(12)": "'0.10669129999999999' (19)"; SQL statement:
insert /*+PARALLEL APPEND_VALUES*/ into CFE.INTEREST_PERIOD
(ID_INSTRUMENT ,ID_FIXINGMODE ,FIXING_DATE ,CHANGE_DATE ,BASE_RATE
,BASE_MARGIN_RATE ,PAR_RATE ,PAR_MARGIN_RATE ,ID_PAYMENT_CONVENTION
,ID_DAY_COUNT_CONVENTION ,ID_DAY_INCL_CONVENTION ,FIX_AMOUNT
,ID_CURRENCY_FIX_AMOUNT ,ID_SCRIPT) VALUES (? ,? ,? ,? ,? ,? ,? ,? ,?
,? ,? ,? ,? ,?) [22001-199]+
Excel itself does not seem to have a problem, because multiplying the
value = 0.1066913 with a large multiplicant 1E15 gives the correct
amount: 0.1066913 x 1E15 = 106691300000000
Furthermore, "similar" values like 0.0813613 or 0.0716913 work well and
will fit.
My question is: How does Excel know the correct value, but POI does
not? And would it not be very very usefull to have a method returning
cell values as BigDecimals instead of Doubles.
At the moment, we work around by advising the users to provide numbers
as Text/String. Although that is counter intuitive and always yields in
having egg on the face.
Thank you already for advise and best regards
Andreas
Re: Floating Point Arithmetics, reading 0.1066913 gives
0.10669129999999999 --> Problem only with Excel, NOT with LibreOffice
Posted by Andreas Reichel <an...@manticore-projects.com>.
Apologies, wrong file.
Correction, Excel wrote:
<row r="10" spans="1:15">
<c r="B10" s="7" t="s">
<v>26</v>
</c>
<c r="C10" s="1" t="s">
<v>376</v>
</c>
<c r="D10" s="6">
<v>43622</v>
</c>
<c r="F10" s="12">
<v>0.10669129999999999</v>
</c>
<c r="J10" s="1" t="s">
<v>377</v>
</c>
<c r="K10" s="1" t="s">
<v>378</v>
</c>
</row>
Looks to me like Excel is to blame.
Although I still wonder: How does it know internally, that the actual
value is 0.1066913 but not 0.10669129999999999?!
Best regards
Andreas
Re: Floating Point Arithmetics, reading 0.1066913 gives
0.10669129999999999 --> Problem only with Excel, NOT with LibreOffice
Posted by Andreas Reichel <an...@manticore-projects.com>.
Libre Office writes:
<row r="10" customFormat="false" ht="12.75"
hidden="false" customHeight="false" outlineLevel="0" collapsed="false">
<c r="B10" s="1" t="s">
<v>48</v>
</c>
<c r="C10" s="1" t="s">
<v>40</v>
</c>
<c r="D10" s="5" t="n">
<v>43622</v>
</c>
<c r="F10" s="6" t="n">
<v>0.1066913</v>
</c>
<c r="J10" s="1" t="s">
<v>41</v>
</c>
<c r="K10" s="1" t="s">
<v>42</v>
</c>
</row>
while Excel wrote:
<row r="10" customFormat="false" ht="12.8"
hidden="false" customHeight="false" outlineLevel="0" collapsed="false">
<c r="B10" s="1" t="s">
<v>26</v>
</c>
<c r="C10" s="1" t="s">
<v>507</v>
</c>
<c r="D10" s="6" t="n">
<v>43622</v>
</c>
<c r="F10" s="10" t="s">
<v>510</v>
</c>
<c r="J10" s="1" t="s">
<v>508</v>
</c>
<c r="K10" s="1" t="s">
<v>509</v>
</c>
</row>
Sorry, I am lost here?! How to interprete that "510"?!
On Sat, 2019-10-19 at 13:53 +0700, Andreas Reichel wrote:
> All,
>
> it gets interesting: I actually did not want to send the original
> file and so I edited that file in LibreOffice, removed all the
> unneeded worksheets and saved it.
> Guess what?! The test output looks correct now:
>
> 0.0813613
> 0.089153
> 0.10929
> 0.097758
> 0.0716913
> 0.1066913
> 0.082126
> 0.0866913
> 0.0966913
> 0.0966913
> 0.0866913
> 0.0861688
> 0.0966913
> 0.0966913
> 0.0966913
> 0.0985188
> 0.09845
> 0.0866913
> 0.0866913
> 0.0954438
> 0.0766913
> 0.09845
>
> (I have no MS Excel and can't play with it. I believe, the client who
> sent the original files uses MS Excel 2013, but I do not know for
> sure.)
>
> Cheers
> Andreas
>
> -------------------------------------------------------------------
> --To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
Re: Floating Point Arithmetics, reading 0.1066913 gives
0.10669129999999999 --> Problem only with Excel, NOT with LibreOffice
Posted by Andreas Reichel <an...@manticore-projects.com>.
All,
it gets interesting: I actually did not want to send the original file
and so I edited that file in LibreOffice, removed all the unneeded
worksheets and saved it.
Guess what?! The test output looks correct now:
0.0813613
0.089153
0.10929
0.097758
0.0716913
0.1066913
0.082126
0.0866913
0.0966913
0.0966913
0.0866913
0.0861688
0.0966913
0.0966913
0.0966913
0.0985188
0.09845
0.0866913
0.0866913
0.0954438
0.0766913
0.09845
(I have no MS Excel and can't play with it. I believe, the client who
sent the original files uses MS Excel 2013, but I do not know for
sure.)
Cheers
Andreas
Re: Floating Point Arithmetics, reading 0.1066913 gives
0.10669129999999999
Posted by Andreas Reichel <an...@manticore-projects.com>.
Dear David and Team,
please see a reproducible test case attached:
import java.io.File;
import java.io.IOException;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.ss.usermodel.*;
public class ExcelTest {
public static void main(String[] args) {
File file = new File ("/tmp/ifrsbox_DataCapture.xlsx");
try {
Workbook wb = WorkbookFactory.create(file);
Sheet sheet = wb.getSheet("INTEREST_PERIOD");
for (int r=4; r<100; r++) {
Row row = sheet.getRow(r);
Cell cell = row.getCell(5);
if (cell.getCellType().equals(CellType.NUMERIC)) {
double value = cell.getNumericCellValue();
System.out.println(value);
}
}
} catch (IOException ex) {
Logger.getLogger(ExcelTest.class.getName()).log(Level.SEVERE,
null, ex);
} catch (EncryptedDocumentException ex) {
Logger.getLogger(ExcelTest.class.getName()).log(Level.SEVERE,
null, ex);
}
}
}
Output:
0.0813613
0.089153
0.10929
0.097758
0.0716913
0.10669129999999999
0.082126
0.08669129999999999
0.0966913
0.0966913
0.08669129999999999
0.0861688
0.0966913
0.0966913
0.0966913
0.09851880000000002
0.09845000000000001
0.08669129999999999
0.08669129999999999
0.09544380000000001
0.0766913
...
On Fri, 2019-10-18 at 22:47 +0200, David Law wrote:
> Hi Andreas,
> I tried to create a Spreadsheet to reproduce this but failed.(it
> returned double = 0.1066913)
> Could you share an example.xslsx with us to demonstrate the
> behaviour?
> All the best,DaveLaw
> On 18/10/2019 04:34, Andreas Reichel wrote:
> > Dear All,
> > compliments of the day.We face some challenges with reading values
> > from Spread Sheets.
> > Example: the numeric cell has the value = 0.1066913 and when
> > readingthat value with POI we receive the double =
> > 0.10669129999999999.
> > This turns into a problem, when writing these figures into a
> > database,when the Precision/Scale of the field can't hold that
> > double value,e.g.
> > JdbcSQLDataException: Value too long for column
> > """BASE_RATE""VARCHAR(12)": "'0.10669129999999999' (19)"; SQL
> > statement:insert /*+PARALLEL APPEND_VALUES*/ into
> > CFE.INTEREST_PERIOD(ID_INSTRUMENT ,ID_FIXINGMODE ,FIXING_DATE
> > ,CHANGE_DATE ,BASE_RATE,BASE_MARGIN_RATE ,PAR_RATE ,PAR_MARGIN_RATE
> > ,ID_PAYMENT_CONVENTION,ID_DAY_COUNT_CONVENTION
> > ,ID_DAY_INCL_CONVENTION ,FIX_AMOUNT,ID_CURRENCY_FIX_AMOUNT
> > ,ID_SCRIPT) VALUES (? ,? ,? ,? ,? ,? ,? ,? ,?,? ,? ,? ,?
> > ,?) [22001-199]+
> > Excel itself does not seem to have a problem, because multiplying
> > thevalue = 0.1066913 with a large multiplicant 1E15 gives the
> > correctamount: 0.1066913 x 1E15 = 106691300000000Furthermore,
> > "similar" values like 0.0813613 or 0.0716913 work well andwill
> > fit.My question is: How does Excel know the correct value, but POI
> > doesnot? And would it not be very very usefull to have a method
> > returningcell values as BigDecimals instead of Doubles.
> > At the moment, we work around by advising the users to provide
> > numbersas Text/String. Although that is counter intuitive and
> > always yields inhaving egg on the face.
> > Thank you already for advise and best regardsAndreas
>
> -------------------------------------------------------------------
> --To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
Re: Floating Point Arithmetics, reading 0.1066913 gives
0.10669129999999999
Posted by David Law <da...@apconsult.de>.
What we IBM Mainframers call "packed decimal".
Rounding is still an issue: try dividing 2 by 3.
But that doesn't help us here:
Excel calculates using Floating Point
so we just need to learn to live with its advantages & disadvantages.
All the best,
DaveLaw
On 18/10/2019 22:55, Schene, Chris wrote:
> I think a lot of financial institutions used BCD (Binary Coded Decimal) because it is accurate without round off like floating point.
>
> Christopher Schene
> Field Engineer
> Fraud & Identity Client Implementations
> 16260 N. 71st Street, Suite 400 | Scottsdale, Arizona 85254
> www.experian.com<http://www.experian.com/>
> experian
>
>
> From: David Law <da...@apconsult.de>
> Reply-To: POI Users List <us...@poi.apache.org>
> Date: Friday, October 18, 2019 at 1:47 PM
> To: "user@poi.apache.org" <us...@poi.apache.org>
> Subject: Re: Floating Point Arithmetics, reading 0.1066913 gives 0.10669129999999999
>
> Hi Andreas,
>
> I tried to create a Spreadsheet to reproduce this but failed.
> (it returned double = 0.1066913)
>
> Could you share an example.xslsx with us to demonstrate the behaviour?
>
> All the best,
> DaveLaw
>
> On 18/10/2019 04:34, Andreas Reichel wrote:
> Dear All,
>
> compliments of the day.
> We face some challenges with reading values from Spread Sheets.
>
> Example: the numeric cell has the value = 0.1066913 and when reading
> that value with POI we receive the double = 0.10669129999999999.
>
> This turns into a problem, when writing these figures into a database,
> when the Precision/Scale of the field can't hold that double value,
> e.g.
>
> JdbcSQLDataException: Value too long for column """BASE_RATE""
> VARCHAR(12)": "'0.10669129999999999' (19)"; SQL statement:
> insert /*+PARALLEL APPEND_VALUES*/ into CFE.INTEREST_PERIOD
> (ID_INSTRUMENT ,ID_FIXINGMODE ,FIXING_DATE ,CHANGE_DATE ,BASE_RATE
> ,BASE_MARGIN_RATE ,PAR_RATE ,PAR_MARGIN_RATE ,ID_PAYMENT_CONVENTION
> ,ID_DAY_COUNT_CONVENTION ,ID_DAY_INCL_CONVENTION ,FIX_AMOUNT
> ,ID_CURRENCY_FIX_AMOUNT ,ID_SCRIPT) VALUES (? ,? ,? ,? ,? ,? ,? ,? ,?
> ,? ,? ,? ,? ,?) [22001-199]+
>
> Excel itself does not seem to have a problem, because multiplying the
> value = 0.1066913 with a large multiplicant 1E15 gives the correct
> amount: 0.1066913 x 1E15 = 106691300000000
> Furthermore, "similar" values like 0.0813613 or 0.0716913 work well and
> will fit.
> My question is: How does Excel know the correct value, but POI does
> not? And would it not be very very usefull to have a method returning
> cell values as BigDecimals instead of Doubles.
>
> At the moment, we work around by advising the users to provide numbers
> as Text/String. Although that is counter intuitive and always yields in
> having egg on the face.
>
> Thank you already for advise and best regards
> Andreas
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org<ma...@poi.apache.org>
> For additional commands, e-mail: user-help@poi.apache.org<ma...@poi.apache.org>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Floating Point Arithmetics, reading 0.1066913 gives
0.10669129999999999
Posted by "Schene, Chris" <Ch...@experian.com.INVALID>.
I think a lot of financial institutions used BCD (Binary Coded Decimal) because it is accurate without round off like floating point.
Christopher Schene
Field Engineer
Fraud & Identity Client Implementations
16260 N. 71st Street, Suite 400 | Scottsdale, Arizona 85254
www.experian.com<http://www.experian.com/>
experian
From: David Law <da...@apconsult.de>
Reply-To: POI Users List <us...@poi.apache.org>
Date: Friday, October 18, 2019 at 1:47 PM
To: "user@poi.apache.org" <us...@poi.apache.org>
Subject: Re: Floating Point Arithmetics, reading 0.1066913 gives 0.10669129999999999
Hi Andreas,
I tried to create a Spreadsheet to reproduce this but failed.
(it returned double = 0.1066913)
Could you share an example.xslsx with us to demonstrate the behaviour?
All the best,
DaveLaw
On 18/10/2019 04:34, Andreas Reichel wrote:
Dear All,
compliments of the day.
We face some challenges with reading values from Spread Sheets.
Example: the numeric cell has the value = 0.1066913 and when reading
that value with POI we receive the double = 0.10669129999999999.
This turns into a problem, when writing these figures into a database,
when the Precision/Scale of the field can't hold that double value,
e.g.
JdbcSQLDataException: Value too long for column """BASE_RATE""
VARCHAR(12)": "'0.10669129999999999' (19)"; SQL statement:
insert /*+PARALLEL APPEND_VALUES*/ into CFE.INTEREST_PERIOD
(ID_INSTRUMENT ,ID_FIXINGMODE ,FIXING_DATE ,CHANGE_DATE ,BASE_RATE
,BASE_MARGIN_RATE ,PAR_RATE ,PAR_MARGIN_RATE ,ID_PAYMENT_CONVENTION
,ID_DAY_COUNT_CONVENTION ,ID_DAY_INCL_CONVENTION ,FIX_AMOUNT
,ID_CURRENCY_FIX_AMOUNT ,ID_SCRIPT) VALUES (? ,? ,? ,? ,? ,? ,? ,? ,?
,? ,? ,? ,? ,?) [22001-199]+
Excel itself does not seem to have a problem, because multiplying the
value = 0.1066913 with a large multiplicant 1E15 gives the correct
amount: 0.1066913 x 1E15 = 106691300000000
Furthermore, "similar" values like 0.0813613 or 0.0716913 work well and
will fit.
My question is: How does Excel know the correct value, but POI does
not? And would it not be very very usefull to have a method returning
cell values as BigDecimals instead of Doubles.
At the moment, we work around by advising the users to provide numbers
as Text/String. Although that is counter intuitive and always yields in
having egg on the face.
Thank you already for advise and best regards
Andreas
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org<ma...@poi.apache.org>
For additional commands, e-mail: user-help@poi.apache.org<ma...@poi.apache.org>
Re: Floating Point Arithmetics, reading 0.1066913 gives
0.10669129999999999
Posted by David Law <da...@apconsult.de>.
Hi Andreas,
I tried to create a Spreadsheet to reproduce this but failed.
(it returned double = 0.1066913)
Could you share an example.xslsx with us to demonstrate the behaviour?
All the best,
DaveLaw
On 18/10/2019 04:34, Andreas Reichel wrote:
> Dear All,
>
> compliments of the day.
> We face some challenges with reading values from Spread Sheets.
>
> Example: the numeric cell has the value = 0.1066913 and when reading
> that value with POI we receive the double = 0.10669129999999999.
>
> This turns into a problem, when writing these figures into a database,
> when the Precision/Scale of the field can't hold that double value,
> e.g.
>
> JdbcSQLDataException: Value too long for column """BASE_RATE""
> VARCHAR(12)": "'0.10669129999999999' (19)"; SQL statement:
> insert /*+PARALLEL APPEND_VALUES*/ into CFE.INTEREST_PERIOD
> (ID_INSTRUMENT ,ID_FIXINGMODE ,FIXING_DATE ,CHANGE_DATE ,BASE_RATE
> ,BASE_MARGIN_RATE ,PAR_RATE ,PAR_MARGIN_RATE ,ID_PAYMENT_CONVENTION
> ,ID_DAY_COUNT_CONVENTION ,ID_DAY_INCL_CONVENTION ,FIX_AMOUNT
> ,ID_CURRENCY_FIX_AMOUNT ,ID_SCRIPT) VALUES (? ,? ,? ,? ,? ,? ,? ,? ,?
> ,? ,? ,? ,? ,?) [22001-199]+
>
> Excel itself does not seem to have a problem, because multiplying the
> value = 0.1066913 with a large multiplicant 1E15 gives the correct
> amount: 0.1066913 x 1E15 = 106691300000000
> Furthermore, "similar" values like 0.0813613 or 0.0716913 work well and
> will fit.
> My question is: How does Excel know the correct value, but POI does
> not? And would it not be very very usefull to have a method returning
> cell values as BigDecimals instead of Doubles.
>
> At the moment, we work around by advising the users to provide numbers
> as Text/String. Although that is counter intuitive and always yields in
> having egg on the face.
>
> Thank you already for advise and best regards
> Andreas
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Floating Point Arithmetics, reading 0.1066913 gives 0.10669129999999999
Posted by Dave Fisher <wa...@comcast.net>.
Hi,
You need to format your double when convert to a string. This is basic to using floating point numbers.
Regards,
Dave
Sent from my iPhone
> On Oct 17, 2019, at 7:34 PM, Andreas Reichel <an...@manticore-projects.com> wrote:
>
> Dear All,
>
> compliments of the day.
> We face some challenges with reading values from Spread Sheets.
>
> Example: the numeric cell has the value = 0.1066913 and when reading
> that value with POI we receive the double = 0.10669129999999999.
>
> This turns into a problem, when writing these figures into a database,
> when the Precision/Scale of the field can't hold that double value,
> e.g.
>
> JdbcSQLDataException: Value too long for column """BASE_RATE""
> VARCHAR(12)": "'0.10669129999999999' (19)"; SQL statement:
> insert /*+PARALLEL APPEND_VALUES*/ into CFE.INTEREST_PERIOD
> (ID_INSTRUMENT ,ID_FIXINGMODE ,FIXING_DATE ,CHANGE_DATE ,BASE_RATE
> ,BASE_MARGIN_RATE ,PAR_RATE ,PAR_MARGIN_RATE ,ID_PAYMENT_CONVENTION
> ,ID_DAY_COUNT_CONVENTION ,ID_DAY_INCL_CONVENTION ,FIX_AMOUNT
> ,ID_CURRENCY_FIX_AMOUNT ,ID_SCRIPT) VALUES (? ,? ,? ,? ,? ,? ,? ,? ,?
> ,? ,? ,? ,? ,?) [22001-199]+
>
> Excel itself does not seem to have a problem, because multiplying the
> value = 0.1066913 with a large multiplicant 1E15 gives the correct
> amount: 0.1066913 x 1E15 = 106691300000000
> Furthermore, "similar" values like 0.0813613 or 0.0716913 work well and
> will fit.
> My question is: How does Excel know the correct value, but POI does
> not? And would it not be very very usefull to have a method returning
> cell values as BigDecimals instead of Doubles.
>
> At the moment, we work around by advising the users to provide numbers
> as Text/String. Although that is counter intuitive and always yields in
> having egg on the face.
>
> Thank you already for advise and best regards
> Andreas
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Floating Point Arithmetics, reading 0.1066913 gives 0.10669129999999999
Posted by Dominik Stadler <do...@gmx.at>.
Hi,
Biggest con: BigInteger are slower by an order of magnitude and use up much
more CPU, think processing files with millions of lines, which users of POI
do.
Also it is more complex to handle and you still need to decide how you
round numbers when you decide to store them as strings.
Dominik
On Fri, Oct 18, 2019, 05:08 Andreas Reichel <an...@manticore-projects.com>
wrote:
> Hi Andi and Team,
>
> thank you for prompt response.
>
> On Thu, 2019-10-17 at 20:11 -0700, kiwiwings wrote:
>
> > this issue pops up every now and then. Please have a look at
> >
> http://apache-poi.1045710.n5.nabble.com/Floating-point-behaviour-difference-between-POI-and-Excel-td5715765.html
>
> I will read through that.
>
> > So Excel rounds to 15 digits. I'm undecided about the BigDecimal API,
> > so Ileave it to the others to decide.
>
> Pardon my curiosity: what are the Cons? I might be biased by my
> profession, but I believe floating point arithmetic sucks.
>
> > Regarding the DB inserts, why do you store numbers in varchars(12)?
> > ... Iwould expect the jdbc API to truncate/round the doubles and use
> > the maximumnumber type.
>
> Well, we hit an exceptional field here: Base Rate can be a number (like
> 5% p.a.) or a reference to a yield curve like LIBOR or EURIBOR.
> Would we not hit exactly the same problem with NUMBER(3,9) or
> DECIMAL(3,9) when 0.10669129999999999 exceeds the precision/scale?
>
> Thank you again and cheers
> Andreas
>
Re: Floating Point Arithmetics, reading 0.1066913 gives
0.10669129999999999
Posted by Andreas Reichel <an...@manticore-projects.com>.
Hi Andi and Team,
thank you for prompt response.
On Thu, 2019-10-17 at 20:11 -0700, kiwiwings wrote:
> this issue pops up every now and then. Please have a look at
> http://apache-poi.1045710.n5.nabble.com/Floating-point-behaviour-difference-between-POI-and-Excel-td5715765.html
I will read through that.
> So Excel rounds to 15 digits. I'm undecided about the BigDecimal API,
> so Ileave it to the others to decide.
Pardon my curiosity: what are the Cons? I might be biased by my
profession, but I believe floating point arithmetic sucks.
> Regarding the DB inserts, why do you store numbers in varchars(12)?
> ... Iwould expect the jdbc API to truncate/round the doubles and use
> the maximumnumber type.
Well, we hit an exceptional field here: Base Rate can be a number (like
5% p.a.) or a reference to a yield curve like LIBOR or EURIBOR.
Would we not hit exactly the same problem with NUMBER(3,9) or
DECIMAL(3,9) when 0.10669129999999999 exceeds the precision/scale?
Thank you again and cheers
Andreas
Re: Floating Point Arithmetics, reading 0.1066913 gives
0.10669129999999999
Posted by kiwiwings <ki...@apache.org>.
Hello Andreas
this issue pops up every now and then. Please have a look at
http://apache-poi.1045710.n5.nabble.com/Floating-point-behaviour-difference-between-POI-and-Excel-td5715765.html
So Excel rounds to 15 digits. I'm undecided about the BigDecimal API, so I
leave it to the others to decide.
Regarding the DB inserts, why do you store numbers in varchars(12)? ... I
would expect the jdbc API to truncate/round the doubles and use the maximum
number type. If the schema is not in your hands, you have to do the rounding
... for being consistent with Excel, you probably need to do the rounding
anyways or use the DataFormatter.
Andi
--
Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Floating Point Arithmetics, reading 0.1066913 gives 0.10669129999999999
--> GNUMERIC vs LibreOffice vs Excel
Posted by Nick Burch <ap...@gagravarr.org>.
On Sun, 20 Oct 2019, Andreas Reichel wrote:
> Opening the file in an XML Text Editor, I get 0.10669129999999999.
> Opening the file in GNUMERIC, I get 0.10669129999999999. (Both the
> shown cell content as well as the editable text box).
> Opening the file in LibreOffice, I get 0.1066913. (Same file, I have
> tried 3 times. Both the shown cell content as well as the editable text
> box).
> Opening the file in MS Excel, I get 0.1066913.
>
> So, while it is still an Excel problem, I wonder how/why LibreOffice
> and Excel know/decide about interpreting the value as 0.1066913?!
If you ask POI to format the value to a string based on the formatting
rules applied to the cell, using DataFormatter or similar, do you get the
value you expect?
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Floating Point Arithmetics, reading 0.1066913 gives
0.10669129999999999 --> GNUMERIC vs LibreOffice vs Excel
Posted by Andreas Reichel <an...@manticore-projects.com>.
Guys, I am sorry: just one more for the fun.
Opening the file in an XML Text Editor, I get 0.10669129999999999.
Opening the file in GNUMERIC, I get 0.10669129999999999. (Both the
shown cell content as well as the editable text box).
Opening the file in LibreOffice, I get 0.1066913. (Same file, I have
tried 3 times. Both the shown cell content as well as the editable text
box).
Opening the file in MS Excel, I get 0.1066913.
I can guarantee that the end-user has captured 0.1066913 (because its a
nominal interest rate, nobody would negotiate or type
0.10669129999999999).
So, while it is still an Excel problem, I wonder how/why LibreOffice
and Excel know/decide about interpreting the value as 0.1066913?!
And as annoying as this all is, could POI follow that interpretation,
simply because that is what a simple end-user would have expected? At
least LibreOffice seems to know and do something, that POI does not.
Cheers
Andreas
Re: Floating Point Arithmetics, reading 0.1066913 gives
0.10669129999999999
Posted by Andreas Reichel <an...@manticore-projects.com>.
Good Morning David and All,
thank you so much for your time and effort! I appreciate.
At this point, I believe it is only MS Excel to blame for:
On Sat, 2019-10-19 at 18:45 +0200, David Law wrote:
> I think if you check the original XML of your *.xlsx,
> you'll find that it contains something like:
> <v>0.10669129999999999</v>
>
> This value can be mapped EXACTLY to a double in Java.
> So that's what you're getting.
(Minor objection here: Edit this spreadsheet and multiply the
0.10669129999999999 with 1E15 and you will see that Excel actually uses
0.1066913 internally instead.)
>
> In other cases you may find XML values like:
> <v>8.6168800000000004E-2</v>
>
It obviously writes different values like 0.10669129999999999 into the
file then it shown/used for its own calculations (like 0.1066913). I
only wonder how Excel itself knows the difference.
Using LibreOffice fixed the problem immediately. Perhaps newer versions
of MS Excel also fixed that, although I am not even keen to try.
As for the JDBC/database issue, I believe it is a complete different
topic. The definition of VARCHAR(12) is there for a good reason
(because both TEXT and or NUMBER can be written into that field).
Same problem would arise with any other column type when precision was
set to less than 15 digits.
It is actully more like a feature, because the solution was to deliver
the figures as text in the spreadsheet (capturing text '0.1066913
instead of number 0.1066913).
We can close this case as it is not a POI related challenge.
Thank you again and cheers
Andreas
Re: Floating Point Arithmetics, reading 0.1066913 gives
0.10669129999999999
Posted by David Law <da...@apconsult.de>.
Hello Andreas,
I think if you check the original XML of your *.xlsx,
you'll find that it contains something like:
<v>0.10669129999999999</v>
This value can be mapped EXACTLY to a double in Java.
So that's what you're getting.
In other cases you may find XML values like:
<v>8.6168800000000004E-2</v>
There is NO exact Java double which matches that!!
(it actually has greater precision than is possible with a Java double)
The closest is 0.0861688
Which raises the questions:
Q) What floating-point convention does Microsoft use in Excel?
Q) Why is Excel displaying it as 0.1066913?
Maybe the answer to that is here:
https://en.wikipedia.org/wiki/Extended_precision#x86_extended_precision_format
I've enclosed some output from a little test proggy (see later)
showing some possible Test values from an XML Excel
and the closest Java doubles to them.
Working with Spreadsheets & Floating-Point you will always get such values.
I would suggest, depending on the application,
Varchar(12) may not an appropriate Datatype for storage of this value in
the DB?
Its also not cheap to convert backwards & forwards between String & Numeric.
(parsing a String to numeric is particularly costly)
Maybe Numeric(nn.6, say) might be more appropriate?
You'll obviously need to round or truncate as necessary.
Either with some proprietary code (via int?) or via BigDecimal?
(but remember, as Dominik Stadler pointed out, BigDecimal is pretty slow)
Not sure what your options are getting your value into JDBC.
You might well find BigDecimal under the bonnet in JDBC anyway.
Anyway, as promised, some Test-Cases, showing the original Excel values
and the closest Java doubles to them with the internal raw hex of the
doubles:
Excel value (XML)..: 0.10669129999999999
----------------------------------------------------------------------
Java double (Init).: 0.10669129999999998 3fbb501efc44899b (raw)
Java double (Next).: 0.10669129999999999 3fbb501efc44899c (raw)
Java double (Next).: 0.1066913 3fbb501efc44899d (raw)
Excel value (XML)..: 0.086168800000000004 (8.6168800000000004E-2)
----------------------------------------------------------------------
Java double (Init).: 0.08616879999999999 3fb60f2891ef812c (raw)
Java double (Next).: 0.0861688 3fb60f2891ef812d (raw)
Java double (Next).: 0.08616880000000002 3fb60f2891ef812e (raw)
Excel value (XML)..: 0.086691299999999985 (8.6691299999999985E-2)
----------------------------------------------------------------------
Java double (Init).: 0.08669129999999997 3fb63166aa59047c (raw)
Java double (Next).: 0.08669129999999999 3fb63166aa59047d (raw)
Java double (Next).: 0.0866913 3fb63166aa59047e (raw)
Excel value (XML)..: 0.096691299999999994 (9.6691299999999994E-2)
----------------------------------------------------------------------
Java double (Init).: 0.09669129999999998 3fb8c0c2d34ec70c (raw)
Java double (Next).: 0.0966913 3fb8c0c2d34ec70d (raw)
Java double (Next).: 0.09669130000000001 3fb8c0c2d34ec70e (raw)
All the best,
DaveLaw
On 18/10/2019 04:34, Andreas Reichel wrote:
> Dear All,
>
> compliments of the day.
> We face some challenges with reading values from Spread Sheets.
>
> Example: the numeric cell has the value = 0.1066913 and when reading
> that value with POI we receive the double = 0.10669129999999999.
>
> This turns into a problem, when writing these figures into a database,
> when the Precision/Scale of the field can't hold that double value,
> e.g.
>
> JdbcSQLDataException: Value too long for column """BASE_RATE""
> VARCHAR(12)": "'0.10669129999999999' (19)"; SQL statement:
> insert /*+PARALLEL APPEND_VALUES*/ into CFE.INTEREST_PERIOD
> (ID_INSTRUMENT ,ID_FIXINGMODE ,FIXING_DATE ,CHANGE_DATE ,BASE_RATE
> ,BASE_MARGIN_RATE ,PAR_RATE ,PAR_MARGIN_RATE ,ID_PAYMENT_CONVENTION
> ,ID_DAY_COUNT_CONVENTION ,ID_DAY_INCL_CONVENTION ,FIX_AMOUNT
> ,ID_CURRENCY_FIX_AMOUNT ,ID_SCRIPT) VALUES (? ,? ,? ,? ,? ,? ,? ,? ,?
> ,? ,? ,? ,? ,?) [22001-199]+
>
> Excel itself does not seem to have a problem, because multiplying the
> value = 0.1066913 with a large multiplicant 1E15 gives the correct
> amount: 0.1066913 x 1E15 = 106691300000000
> Furthermore, "similar" values like 0.0813613 or 0.0716913 work well and
> will fit.
> My question is: How does Excel know the correct value, but POI does
> not? And would it not be very very usefull to have a method returning
> cell values as BigDecimals instead of Doubles.
>
> At the moment, we work around by advising the users to provide numbers
> as Text/String. Although that is counter intuitive and always yields in
> having egg on the face.
>
> Thank you already for advise and best regards
> Andreas
>
Re: Floating Point Arithmetics, reading 0.1066913 gives
0.10669129999999999
Posted by David Law <da...@apconsult.de>.
Hi Folks,
I've written a little proggy to demonstrate how to get the Raw Cell
Value into a BigDecimal.
Please find attached, together with a (new!!) Excel with Testdata.
This seems to workaround the problem fairly well.
Best regards,
DaveLaw
On 18/10/2019 04:34, Andreas Reichel wrote:
> Dear All,
>
> compliments of the day.
> We face some challenges with reading values from Spread Sheets.
>
> Example: the numeric cell has the value = 0.1066913 and when reading
> that value with POI we receive the double = 0.10669129999999999.
>
> This turns into a problem, when writing these figures into a database,
> when the Precision/Scale of the field can't hold that double value,
> e.g.
>
> JdbcSQLDataException: Value too long for column """BASE_RATE""
> VARCHAR(12)": "'0.10669129999999999' (19)"; SQL statement:
> insert /*+PARALLEL APPEND_VALUES*/ into CFE.INTEREST_PERIOD
> (ID_INSTRUMENT ,ID_FIXINGMODE ,FIXING_DATE ,CHANGE_DATE ,BASE_RATE
> ,BASE_MARGIN_RATE ,PAR_RATE ,PAR_MARGIN_RATE ,ID_PAYMENT_CONVENTION
> ,ID_DAY_COUNT_CONVENTION ,ID_DAY_INCL_CONVENTION ,FIX_AMOUNT
> ,ID_CURRENCY_FIX_AMOUNT ,ID_SCRIPT) VALUES (? ,? ,? ,? ,? ,? ,? ,? ,?
> ,? ,? ,? ,? ,?) [22001-199]+
>
> Excel itself does not seem to have a problem, because multiplying the
> value = 0.1066913 with a large multiplicant 1E15 gives the correct
> amount: 0.1066913 x 1E15 = 106691300000000
> Furthermore, "similar" values like 0.0813613 or 0.0716913 work well and
> will fit.
> My question is: How does Excel know the correct value, but POI does
> not? And would it not be very very usefull to have a method returning
> cell values as BigDecimals instead of Doubles.
>
> At the moment, we work around by advising the users to provide numbers
> as Text/String. Although that is counter intuitive and always yields in
> having egg on the face.
>
> Thank you already for advise and best regards
> Andreas
>