Hi David, I can not overstate you help, thank you a lot. Although this time I do not fully agree with you because I see two use cases:
1) some end-user captures more digits than the DB field would expect. Solution: Blame the user and do not care. 2) the end-user captured a very valid (short) decimal figure and only Excel turned it into something different. Your suggestion and and the derived implementation shall address only use case number 2 and I have tested it immediately with a rather large set of real life data and it improved the situation dramatically, even when other corner cases might exist. (We can fix that, when they arrive.) Furthermore, unfortunately SQLSheet seems to be rather unmaintained. Who knows if they even accept my pull request. However, if anyone else stumbles over a similar challenge, even the documentation by the pull request might help. Cheers Andreas On Mon, 2019-10-21 at 13:21 +0200, David Law wrote: > Hello Andreas, > maybe we should put that Commit to SqlSheet on HOLD? > Whilst this solution solves your immediate problem,there are > implications for other possible values. > Please see the attached proggy. > BigDecimal precision determines how many digits the numberwill > contain after stripping off leading & trailing zeros.(irrespective of > the decimal-point position) > So the result may well exceed the SqlSheet length limit. > I'm sorry, I didn't realise you were working with a generic Open > Source Offering.(but I did include a TODO that we needed to > understand Precision & Scale better) :-) > Best regards,DaveLaw > On 21/10/2019 02:13, Andreas Reichel wrote: > > Dear All. > > with David's help I have been able to read that XLSX correctly and > > Iwill incorporate the following lines in SQLSHEET (XLSX JDBC > > driver) inorder to avoid such problems in the future: > > // @author David Law <david....@apconsult.de>private static final > > MathContext CTX_NN_15_EVEN = new > > MathContext(15,RoundingMode.HALF_EVEN);final String rawValue = > > xssfCell.getRawValue();final BigDecimal rawBig = new > > BigDecimal(rawValue, CTX_NN_15_EVEN); > > double result = rawBig.doubleValue(); > > // returns 0.1066913 as expected > > Best regardsAndreas > > On Sun, 2019-10-20 at 21:50 +0100, Nick Burch wrote: > > > On Sun, 20 Oct 2019, David Law wrote: > > > > the Cells have no Format. Take a look at the attached File, > > > > cellF10. Andreas tells me it was entered as 0.1066913 & that's > > > > how itsdisplayed too, although it has no format. > > > Numeric cells have a default format if nothing else is applied, > > > itcould be that perhaps?I know that David North did some work a > > > few years ago on trying tounderstand + match the Excel floating > > > point rules, it might be worthhaving a look at some of his > > > mailing list posts for more details. Heisn't involved much in POI > > > at the moment (day job priority changes),but we can always ping > > > him to chime in if needed!Nick----------------------------------- > > > ----------------------------------To unsubscribe, e-mail: > > > user-unsubscr...@poi.apache.org > > > For additional commands, e-mail: user-h...@poi.apache.org > > ------------------------------------------------------------------- > --To unsubscribe, e-mail: user-unsubscr...@poi.apache.org > For additional commands, e-mail: user-h...@poi.apache.org