Parse Excel Decimal-Format with Apache-POI to Java BigDecimal

Question

I want to Import an XLSX-File with Help of Apache-POI (XSSF and SAX Event API).

Because Excel stores numbers as floating-point numbers it is necessary in java to format them back to the way they originally look in Excel. This is possible by reading the cell-format:

String cellStyle = sheetReader.getAttributeValue(null, "s");
if (cellStyle != null) {
  // save the format of the cell for later use.
  int styleIndex = Integer.parseInt(cellStyle);
  XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
  formatIndex = style.getDataFormat();
  formatString = style.getDataFormatString();
  if (formatString == null) {
    // formatString could not be found, so it must be a builtin format.
    formatString = BuiltinFormats.getBuiltinFormat(formatIndex);
  }
}
...
// format the floating-point value
String xlsxValue = formatter.formatRawCellContents(
  Double.parseDouble(value),
  formatIndex,
  formatString);

The above code does work well for me... but it gives me the numbers like they are originally formatted in Excel while running Excel in Germany Locale. Example for such numbers:

10,30
100.00.00,43

Now how can i reformat those numbers so that they can be fed to Java Double and Java BigDecimal?

Apache-POI seemingly does not provide Utility-Classes for this case, but how can the numbers be processed in java then?

I have hacked my way into poi to make this happen like this, but is there no other way?

// hack apache-poi classes that are private, so we can retrieve the 'format'
// which helps us to transform the formated value to the expected java-format
CellStyle style = new CellStyleHack(formatIndex, formatString);
Cell cell = new CellHack(Double.parseDouble(xlsxValue), style);

java.text.Format format = formatter.createFormat(cell);
if (format instanceof DecimalFormat) {
  DecimalFormat decimalFormat = ((DecimalFormat) format);
  char dSep = decimalFormat.getDecimalFormatSymbols().getDecimalSeparator();
  char gSep = decimalFormat.getDecimalFormatSymbols().getGroupingSeparator();
  String cSymbol = decimalFormat.getDecimalFormatSymbols().getCurrencySymbol();

  // java always expects '.' as decimal seperator for BigDecimal and Double.
  xlsxValue = xlsxValue.replace("" + gSep, "");
  xlsxValue = xlsxValue.replace(dSep, '.');
  if (cSymbol != null) {
    xlsxValue = xlsxValue.replace(cSymbol, "").trim();
  }
}

I would simply working with the doubles having in mind the floating point problems. But what you could doing is getting the double values and then doing the same as Excel does, rounding to 15 significant digits using BigDecimal.round. I have shown this here : stackoverflow.com/questions/41067328/…. — Axel Richter
– Axel Richter, Commented Feb 9, 2018 at 10:05
@AxelRichter yes i could do it this way, thanks for pointing out. Problem is we have a convention in our software that our BigDecimals need to be max 9 digits before fraction and max. 7 digits after fraction, so they can be stored in database and we can be sure they are not larger than our database field allows. The Value of Excel is for example: 62.474099999999993 which is larger than our 7 digits after fraction. I dont want to cut off the rest of the number, and i also dont want to lose precision for price-values. My above code-example (which is a hack) does not have this problem. — funkrusher
– funkrusher, Commented Feb 9, 2018 at 10:31
Can't test at the moment. But could you try using DataFormatter(java.util.Locale locale) having Locale.US? Or setting LocaleUtil.setUserLocale to Locale.US before using the DataFormatter? — Axel Richter
– Axel Richter, Commented Feb 9, 2018 at 10:57
@AxelRichter good idea. I have tryed it like follow: formatter = new DataFormatter(Locale.US). And i have tryed using the formatRawCellContents --> With value: "1333", format-string: "#,##0" result: "1,333". With value: "1", format-string: "0.00", result: "1.00". So it really does change something, but it still produces "," in result. But thanks this helps. It would be a solution if with Locale.US it would never produce a comma. — funkrusher
– funkrusher, Commented Feb 9, 2018 at 12:42
@AxelRichter hey now that i think about it. If the result now always conforms to the Locale.US it will always have "." as decimal separator. Cool Thx. I think this could solve my problem — funkrusher
– funkrusher, Commented Feb 9, 2018 at 12:50

funkrusher · Accepted Answer · 2018-02-09 13:03:07Z

3

With help of @AxelRichter following solution now solves my problem:

// we must use Locale.US, because we want to make sure that the DataFormatter will
// always product "." as decimal-separator and "," as thousands-separator.
this.formatter = new DataFormatter(Locale.US);

// format the floating-point value
String xlsxValue = formatter.formatRawCellContents(
        Double.parseDouble(value),
        formatIndex,
        formatString);

// xlsxValue may contain format-symbols, which we need to remove...
xlsxValue = xlsxValue.replaceAll("[^\\d.]", "");

answered Feb 9, 2018 at 13:03

funkrusher

4771 gold badge5 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nicola Isotta · Accepted Answer · 2022-10-30 05:25:04Z

3

From Apache POI docs:

Cell.getNumericCellValue() should already return a double value.

For other formats, use DataFormatter class:

DataFormatter contains methods for formatting the value stored in an Cell. This can be useful for reports and GUI presentations when you need to display data exactly as it appears in Excel. Supported formats include currency, SSN, percentages, decimals, dates, phone numbers, zip codes, etc.

When using XSSF SAX Event API, you don't have that kind of access but fortunately there's a code sample at

https://svn.apache.org/repos/asf/poi/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/eventusermodel/XLSX2CSV.java

that shows how to retrieve the numeric / formatted string value of a cell by implementing the SheetContentsHandler interface and overriding its cell, startRow, endRow, etc. methods (in the example, look for the XLSX2CSV.SheetToCSV.cell(...) method.

Hope this helps.

edited Oct 30, 2022 at 5:25

Nicola Isotta

2163 silver badges13 bronze badges

answered Feb 9, 2018 at 8:38

Repoker

2123 silver badges13 bronze badges

4 Comments

funkrusher Over a year ago

Because i use the "XSSF and SAX Event API" (for Performance-Reasons) i can not use "Cell". Cell is only available with the SXSSF API. Even if i could use Cell. Excel saves numbers like this: 123.43500000009 if i just use those double values in java i would get floating-point problems and the original formatting of excel would get lost. (Original number was: "123.435" (3 digits after decimal point). I just want that Apache-POI gives me the number as BigDecimal oder as Double simulating the original Excel-Number that has been shown to the user in excel, so i can work with it in java

Repoker Over a year ago

Even in XSSF Cell classyou can get a double numeric value directly from cell (poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/…) and if you need the value exactly as it appears, then poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/… should be used. In case this wouldn't be enough, you can also access the underlying CTCell with XSSFCell.getCTCell() and work your way from there (raw data)

funkrusher Over a year ago

you are right about XSSF Cell. But please see following link about "XSSF and SAX Event API": poi.apache.org/spreadsheet/how-to.html#xssf_sax_api XSSF Cell is only used for the "User API (HSSF and XSSF)" ? I have no Cell-Object when i use the XMLStreamReader to parse the sheet.

Repoker Over a year ago

Then check the sample code at: svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/… XLSX2CSV.SheetToCSV.cell() overriden method has what you need (cell value parsing for doubles and other formats)

Collectives™ on Stack Overflow

Parse Excel Decimal-Format with Apache-POI to Java BigDecimal

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related