CODEWITHSUNDEEP
javaexcelapache-poinumeric
RinksM
RinksM

Reputation: 1

Numeric cell values in excel are read abnormally with extra digits appended after decimal point

Please find below the code snippet. The implementation reads cell values for a particular column in excel sheet.

Numeric values such as 459000.00 are being read by code as 459000.00000000006. It is working fine for few of the numbers, but is failing for some. 

 try
                {
                String AmountColumn="C";
                File FS = new File(FileLocation);
                FileInputStream FileStream = new FileInputStream(FS);
                XSSFWorkbook FileWorkbook = new XSSFWorkbook(FileStream);
                Sheet FileWorksheet = FileWorkbook.getSheetAt(0);
                double[] AmountArray = new double[FileWorksheet.getPhysicalNumberOfRows() - 1];
                Iterator<Row> iterator2 = FileWorksheet.iterator();
                int i2 = 0;

                while (iterator2.hasNext())
                {
                if (i2 == 0)
                {
                iterator2.next();
                }
                else
                {
                AmountArray[i2 - 1] = iterator2.next().getCell(CellReference.convertColStringToIndex(AmountColumn)).getNumericCellValue();
                System.out.println("Amount is: " + AmountArray[i2 - 1]);
                }
                i2++;
                }
                Amount = AmountArray;
                }
                catch (Exception e)
                {
                e.printStackTrace();
                }

Upvotes: 0

Views: 357

Answers (1)

Peter Lawrey
Peter Lawrey

Reputation: 533432

Excel does more rounding of the result than Java does. The true value stored might be 459000.00000000006 and yet display as 459000.0

If I input 459000.00000000006 into Excel it displays 459000

A simple solution is to use a little rounding of the name you get.

e.g. Round to 6 decimal places

/**
 * Performs a round which is accurate to within 1 ulp. i.e. for values very close to 0.5 it
 * might be rounded up or down. This is a pragmatic choice for performance reasons as it is
 * assumed you are not working on the edge of the precision of double.
 *
 * @param d value to round
 * @return rounded value
 */
public static double round6(double d) {
    final double factor = 1e6;
    return d > WHOLE_NUMBER / factor || d < -WHOLE_NUMBER / factor ? d :
            (long) (d < 0 ? d * factor - 0.5 : d * factor + 0.5) / factor;
}

https://github.com/OpenHFT/Chronicle-Core/blob/master/src/main/java/net/openhft/chronicle/core/Maths.java#L121

Upvotes: 1

Related Questions