BigDecimal and Exponential-CodePudding

Im 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 Im 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 its a valid value(ie. 
   not String), it should still go in.
   */

}

CodePudding user response：

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());

CodePudding user response：

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
                Math.max(0, value.scale() ) <= 3; //non-negative scale <= 3, i.e. 3 decimal digits at most