Hi Andreas,
did you try my example program?
Some of the String values are rather long.
I thought you were constrained to wasn't it VarChar(12)?
All the best,
Dave
On 21/10/2019 13:43, Andreas Reichel wrote:
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
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscr...@poi.apache.org
For additional commands, e-mail: user-h...@poi.apache.org
