用Apache-POI解析Excel格式到BigDecimal

Question

我想在Apache (XSSF和SAX事件API)的帮助下导入XLSX文件。

因为Excel将数字存储为浮点数，所以在java中有必要将其格式化为它们在Excel中的最初外观。这可以通过读取单元格格式来实现：

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

上面的代码对我很有用..。但是它给了我一些数字，就像它们最初是在Excel中格式化的，而在德国地区运行Excel。例如，这些数字：

10,30
100.00.00,43

现在，我如何重新格式化这些数字，以便将它们输入Java和？

Apache似乎没有为这种情况提供实用程序类，但是如何在java中处理这些数字呢？

为了让这一切发生，我已经黑进了poi，但没有别的办法吗？

// 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

在@AxelRichter的帮助下，下面的解决方案现在解决了我的问题：

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