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
