使用ApachePOI将Excel DecimalFormat解析为Java BigDecimal

Question

我想在ApachePOI（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 Double和Java BigDecimal

ApachePOI似乎没有为这种情况提供实用程序类，但是如何在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

从Apache POI docs

细胞。getNumericCellValue（）

对于其他格式，请使用数据格式化程序类：

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.

当使用XSSF SAX事件API时，您没有这种访问权限，但幸运的是，这里有一个代码示例

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

这显示了如何通过实现SheetContentsHandler接口并覆盖其单元格、startRow、endRow等方法来检索单元格的数字/格式化字符串值（在本例中，查找XLSX2CSV.SheetToCSV.cell（…）方法

希望这有帮助

Answer 2

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