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

Answer 1

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/src/examples/src/org/apache/poi/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.

Answer 2

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.]", "");