CODEWITHSUNDEEP
javaexcelapache-poi
funkrusher
funkrusher

Reputation: 469

Apache-POI - How do i round numbers like Excel does in Cell-Editor

I use the Apache-POI "XSSF and SAX Event API" for Importing Excel-Files.

Im parsing Raw-Values with Apache-POI and want to ask, how can i round those Raw-Values exactly like the Excel-GUI does for the Cell-Editor. Following example illustrates the problem:

Raw:      1.9210999999999999E-2     (value is stored like this in xlsx-file)
Edit:     1.9211%                   (user sees this value in excel cell-editor)
View:     1.92%                     (user sees this value in excel grid-overview)
Format:   0.00%                     (the cell-style)

How does Excel read the Raw-Value to the Edit-Value ? How does Excel know that it needs to round to a fraction of 4 digits after the decimal-separator. How can Apache POI help me to do it the same, so i can also use the Edit-Value (and not the View-Value) in my Excel-Import?

I have seen a similar Ticket that covers how Excel does it: How does Excel successfully Rounds Floating numbers even though they are imprecise?

In this Ticket i want to ask, how can Apache POI help me so i do not need to reinvent the wheel here, and implement Excels Algorithm.

Upvotes: 1

Views: 2464

Answers (1)

Axel Richter
Axel Richter

Reputation: 61880

Excel gets the double value according to IEEE 754 specification and then it rounds to 15 significant digits before displaying in the sheet.

To do the same one could using BigDecimal rounded according to the MathContext settings of 15 digits precision.

import java.math.BigDecimal;
import java.math.MathContext;

class ReadLongNumbersAsExcel {

 public static void main(String[] args) throws Exception{

  String v = "1.921099999999999E-2";
  double d = Double.parseDouble(v);
  System.out.println("raw: " + v);
  System.out.println("double: " + d);
  BigDecimal bd = new BigDecimal(d);
  v = bd.round(new MathContext(15)).toPlainString();
  System.out.println("like Excel: " + v);

 }
}

The % case is a special case since Excel shows in this case not the value which was really stored but shows this value multiplied by 100 and followed by "%". So if you wants the same then do the same. After getting the raw value rounded to 15 significant digits do multiplying with 100 and append a "%".

But if the need is only getting the formatted value like in Excel, then see How to check a number in a string contains a date and exponential numbers while parsing excel file using apache event model in java

Upvotes: 3

Related Questions