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 <[email protected]> > 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: [email protected] For additional commands, e-mail: [email protected]
