0

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.

rnd
  • 321
  • 3
  • 13
  • 1
    If you use `getNumericCellValue()`, you are getting the raw double which you can then parse/round however you want. You might have to be more specific with what you're trying to do because you may be over-complicating. – Trevor Bye Feb 20 '18 at 16:48
  • 1
    "Edit: 1.9211 (user sees this value in excel cell-editor)": No, my Excel shows 1.9211% in this case. – Axel Richter Feb 21 '18 at 06:52
  • @AxelRichter you are correct about "1.9211%" in the Cell-Editor. I fixed it in my example now. – rnd Feb 21 '18 at 08:04

1 Answers1

3

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

Axel Richter
  • 56,077
  • 6
  • 60
  • 87
  • this answers my question. Thanks for explaining the % case also. In a naive way i would think that apache poi should provide a utility-function for this. – rnd Feb 21 '18 at 09:17
  • @funkrusher: "i would think that apache poi should provide a utility-function for this": Why? There is [DataFormatter](https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DataFormatter.html) to get the contents as Excel shows them in the cell. What is the background of the requirement for getting the contents as Excel is showing them in editing mode? – Axel Richter Feb 21 '18 at 10:48
  • because poi does provide a DataFormatter to get the contents as shown in the cell, poi could as well provide one for the content of editing-mode, because it would also be helpful for import/export scenarios where it is necessary to process data in a way that is not influenced by display-styles. – rnd Feb 21 '18 at 11:49
  • i want to say, i will use the DataFormatter to solve my problem, because i don't want to reimplement something which a framwork should solve for me. I see how far i can come with that way, and fall back to your solution if its not good enough. – rnd Feb 21 '18 at 12:05
  • 1
    @funkrusher: "process data in a way that is not influenced by display-styles": Then do using the raw stored values having in mind the floating point problems which are always present while using floating point values in IT. Thats the disadvantage of using numeral systems having different bases - binary versus decimal. – Axel Richter Feb 21 '18 at 12:14