BigDecimal and Exponential

Question

I'm trying to read the following value from an Excel file cell:

19,000,000.00
19,000,000.10
19,000,000.01
19,000,000.101

What I see in the Excel file are also those values.

However, my output respectively, when using cell.getNumericValue():

1.9E+7
19,000,000.1
19,000,000.01
19,000,000.101

The conversion to the exponential values makes it hard to manipulate and obtain all the information I need from the value, because when calling the .scale() and .precision() methods, the value is completely off. (the exponential value in question gives me precision: 2; scale: -6 )

How do I make it so that I get what I see instead of the conversion? My end-goal, basically, is to ensure the length of the value does not exceed my settings (eg. Numeric(15, 3) )

I've tried:

Double.parseDouble()
BigDecimal.valueOf().doubleValue()
BigDecimal.valueOf().floatValue()

But everything keeps returning me back the exponential value.

Edit

Due to request, portion of the code I'm doing, modified as to not show the whole thing and clutter:

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;

Object theObject = new Object();

Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext() && !hasError) {
   Cell cell = cellIterator.next();
   theColumnIndex = cell.getColumnIndex();

  (switch statement omitted)
    numericValue = cell.getNumericCellValue();
    //numericValue will return values like 1.9E+7, 19000000.10
    // 19000000.01, 19000000.101
    // afaik, the return type is Double

    theObject.setAmount( BigDecimal.valueOf(numericValue) );
    //SetAmount function expects a BigDecimal input, hence the use


   /*
   Additional checking for length and precision here to try and catch and 
   log it. Put after and not before because so long as it's a valid value (ie. 
   not String), it should still go in.
   */

}

Note that `1.9E+7` is more of a text representation and I'd not use this for any checks or calculations unless the formatting is relevant. Otherwise shouldn't `19,000,000.101` and `19000000.101` have the same length? So what you need to do is check the values against _numeric_ boundaries, e.g. `x > BigDecimal.valueOf("999999999999.999")` for a max value. — Thomas, Jun 13 '22 at 09:31
"But everything keeps returning me back the exponential value" - well it's returning a `double` value (or a `float` value). How that value is then converted *into a string* is an entirely separate matter. It sounds like really you just want `BigDecimal` without any conversion to `float` or `double`... — Jon Skeet, Jun 13 '22 at 09:31
@JonSkeet While writing this question, I did wonder for a while whether the output is because it's part of a ```System.out```, but as mentioned, my ```.scale()``` and ```.precision()``` does not correspond with the value Im expecting, but in fact matches the exponential value. Basically, what I want is the actual value, not it converted to exponential value. — Erik, Jun 13 '22 at 09:53
You can refer to this post: https://stackoverflow.com/questions/13563747/converting-exponential-value-in-java-to-a-number-format — Venkatesh, Jun 13 '22 at 09:55
@Thomas The other values are more of additional examples. The most important one is the ```19000000.00``` value, which returns as ```1.9E+7```. I dont want this; I want the ```19000000.00``` value. — Erik, Jun 13 '22 at 09:55
"what I want is the actual value" - the `BigDecimal` you get _is_ the value, `scale` and `precision` are more for other purposes and describe the internal representation of the value (i.e. `1000` could be represented as `value 1 + scale -3`, `value 10 + scale -2` etc. - note the JavaDoc on `scale()`). Just work with the `BigDecimal` directly and the internal representation shouldn't matter). — Thomas, Jun 13 '22 at 09:55
You can refer to this post: https://stackoverflow.com/questions/13563747/converting-exponential-value-in-java-to-a-number-format — Venkatesh, Jun 13 '22 at 09:56
@Venkatesh I originally added a PS in my OP to indicate that I've seen a few other threads before posting as none of their answers fit. That link was one of them; the top 2 most voted answers didnt fit my need. — Erik, Jun 13 '22 at 10:01
I don't think your question is complete. You need to include the way you're creating the BigDecimal, and the way that you're comparing the BigDecimal and why you consider the results incorrect. — matt, Jun 13 '22 at 10:16
`1.9E7` is not the value. It is the result of some conversion to `String` which you haven't shown. You need to investigate `java.text.DecimalFormat`. — user207421, Jun 13 '22 at 10:28
"// afaik, the return type is Double" You're assigning it to a variable, `numericValue` you should absolutely include the type. "//numericValue will return values like 1.9E+7, 19000000.10 " doubles don't work like that. — matt, Jun 14 '22 at 12:03

score 3 · Answer 1 · answered Jun 13 '22 at 10:05

You should avoid the BigDecimal constructor with a double as floating point has no precision (scale). From a String the BigDecimal can determine the precision (scale -2 below).

BigDecimal n = new BigDecimal("19000000.00");

So one should not get double values from Excel.

Then for text presentation without scientific exponent notation:

System.out.println(n.toPlainString());

Thomas · Answer 2 · 2022-06-13T14:10:27.337

As already commented, you should not do numerical constraint checks based on a text representation of a number.

If I remember correctly Numberic(15,3) basically means "15 digits, 3 of which are decimal places". This means you'd have the following constraints:

min value: -999,999,999,999.999
max value: 999,999,999,999.999
max 3 decimal places

To check this you could do the following:

//construct the boundary values once and cache them
BigDecimal max = new BigDecimal("999999999999.999");
BigDecimal min = max.negate();

//get the value and strip trailing zeros to get `x.01` instead of `x.01000` etc.
BigDecimal value = cell.getNumericValue().stripTrailingZeros();

//check validity
boolean valid = min.compareTo(value) < 0 &&  //value not smaller than min which would mean more digits
                max.compareTo(value) > 0 &&  //value not larger than max which would mean more digits
                value.scale() <= 3; //scale <= 3, i.e. 3 decimal digits at most

Note on scale(): a negative value basically defines the number of zeros that would be added to the internally stored unscaled integer value, e.g. value = 123 and scale = -5 would actually mean "123 + 5 zeros, i.e. 12,300,000". A negative scale thus also implies that there are no fraction digits while a positive scale would indicate the number of fraction digits ("value = 123 and scale 2" would mean "move the decimal point 2 places to the left" so the actual value would be 1.23 - which means 2 fraction digits).

@user16320675 true and since scale should only be negative for integers this means there are no decimal digits in this case. Will correct it. — Thomas, Jun 13 '22 at 14:05

BigDecimal and Exponential

Edit

2 Answers2