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-unsubscr...@poi.apache.org For additional commands, e-mail: user-h...@poi.apache.org
