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 number
will 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 I
will incorporate the following lines in SQLSHEET (XLSX JDBC driver) in
order to avoid such problems in the future:
// @author David Law <[email protected]>
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 regards
Andreas
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, cell
F10. Andreas tells me it was entered as 0.1066913 & that's how its
displayed too, although it has no format.
Numeric cells have a default format if nothing else is applied, it
could be that perhaps?
I know that David North did some work a few years ago on trying to
understand + match the Excel floating point rules, it might be worth
having a look at some of his mailing list posts for more details. He
isn'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: [email protected]
For additional commands, e-mail: [email protected]
import java.math.BigDecimal;
import java.math.MathContext;
import java.math.RoundingMode;
import java.util.Arrays;
public class SqlSheetBigDecimal {
private static final String dots;
/**/ static {
final String pad = " ";
final String dots4 = "....";
final String dots9 = dots4 + '+' + dots4;
/**/ dots = dots9 + '1' + dots9 + '2' + dots9 + '3' +
pad;
}
private static final MathContext CTX_NN_14_EVEN = new MathContext(14,
RoundingMode.HALF_EVEN);
private static final MathContext CTX_NN_15_EVEN = new MathContext(15,
RoundingMode.HALF_EVEN);
public static void printValue(final String value) {
System.out.println("Dots.........: " + dots
+ dots);
System.out.println("Precision 14.: " + rPad(value,
dots.length()) + new BigDecimal(value, CTX_NN_14_EVEN).doubleValue());
System.out.println("Precision 15.: " + rPad(value,
dots.length()) + new BigDecimal(value, CTX_NN_15_EVEN).doubleValue());
System.out.println("Raw Value....: " + rPad(value,
dots.length()) + new BigDecimal(value ).doubleValue());
System.out.println();
}
public static void main(final String[] args) {
printValue("0.1066913");
printValue("0.10669129999999999");
printValue("0.086691299999999985");
printValue( "0.001234567890123456789");
printValue( "0.0001234567890123456789");
printValue("0.00001234567890123456789");
printValue( "123456789012345678900");
printValue( "012345678901234567890");
// printValue("001234567890123456789"); // TODO Bug in BigDecimal
Precision?? -> 1.23456789012350003E18 !!
printValue("0.01234567890123456789");
printValue("0.01234567890123456789e+9");
printValue("0.01234567890123456789e+8");
printValue("0.01234567890123456789e-2");
printValue("0.01234567890123456789e-1");
printValue( "8.6691299999999985E-2");
printValue( "8.6691299999999985E-13");
printValue( "8.6691299999999985E+7");
printValue( "8.6691299999999985E+6");
printValue( "8.6691299999999985E-3");
printValue( "8.6691299999999985E-4");
}
public static final String rPad(final CharSequence value, final int
padLength) {
final char[] padChars = new char[Math.max(0, padLength -
value.length())];
Arrays.fill (padChars, ' ');
return value.toString().concat(String.valueOf(padChars));
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
