java - Java 中 Excel 的低值与 %

Question

当我们有低数字时如何从 Excel 中读取数字数据？
例如，我有


一个
乙
C
D
乙
F


123456789012
0.1%
0.02%
0.003%
0.0004%
0.0005%


当我阅读我的数据时

BigDecimal bd = new BigDecimal(String.valueOf(((XSSFCell)cell).getRawValue()));

我明白了

1234567
0.001
0.00020000000000000001
0.000030000000000000001
0.0000039999999999999998
4.9999999999999998E-8

但是，如果我将线路更改为该 View

BigDecimal bd = new BigDecimal(cell.getNumericValue());

1234567
0.001000000000000000000020816681711....
0.000200000000000000000009584347204....
and etc

所以，我的问题是如何以正确的格式解析该行？
如何获得 0.1 和 0.02 等？在字符串或数字中都没有关系。虽然毕竟我必须对这些数字进行一些数学运算，但无论如何，我首先必须得到正确的数字。

Answer 1

如果需要以与 Excel 在其 GUI 中显示它们的格式相同的格式获取所有单元格值，则可以使用 DataFormatter。使用当前的 apache poi 版本 5.2.2 这就像使用 DataFormatter.formatCellValue(Cell cell) 一样简单，它返回一个 String，以与 Excel 显示的相同格式显示单元格值。当设置 DataFormatter.setUseCachedValuesForFormulaCells(true) 时，这也适用于公式单元格。
但是，如果单元格值的使用取决于它们的类型，例如日期作为日期，数字作为数字， boolean 值作为 boolean 值，......并且只有字符串作为字符串，那么按单元格类型读取是必要的。然后需要知道 Excel 如何存储数字单元格值。
Excel 将所有数值存储为 double 值。
在 Excel 中，日期/时间值也是数值。值 1.0 表示一天，从 1900-01-01 开始。所以 1.0 是 1900-01-01，2.0 是 1900-01-02，依此类推。今天，2022-06-07，是数值 44,719.0。时间值是介于 0.00 和 1.00 之间的十进制值。一小时是 1.0/24.0，一分钟是 1.0/24.0/60.0，一秒是 1.0/24.0/60.0/60.0。这就是为什么只有数字单元格值 one 无法确定这是否是日期/时间。仅具有来自单元格样式的单元格数据格式，这是可以检测到的。这就是为什么 apache poi 提供 DateUtil.isCellDateFormatted(Cell cell) 来检测单元格是否为日期格式的原因。
百分比格式也是一种特殊情况。在 Excel 中，值 1.0 表示 100%，0.5 表示 50%，依此类推。因此，如果在没有进一步了解的情况下获得 0.5 的数字单元格值，则无法检测到这是否是百分比值。这里还需要来自单元格样式的单元格数据格式。但是对于这种情况，apache poi 不提供任何实用方法。所以需要自己去检测。
下面的代码展示了这一切。

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.CellType;

import java.io.FileInputStream;

class ReadExcel {
    
 static void readAsStrings(Workbook workbook) {
  DataFormatter dataFormatter = new DataFormatter(java.util.Locale.US);
  dataFormatter.setUseCachedValuesForFormulaCells(true);
  String cellValue = "";

  for (Sheet sheet: workbook) {
   System.out.println("Sheet " + sheet.getSheetName());
   for (Row row : sheet) {
    for (Cell cell : row) {
     cellValue = dataFormatter.formatCellValue(cell);
     System.out.println(cell.getAddress() + ":" + cellValue);
     // do something with cellValue
    }
   }
  }
 } 
 
 static boolean isCellPercentFormatted(Cell cell) {
  if (cell.getCellType() == CellType.NUMERIC || cell.getCachedFormulaResultType() == CellType.NUMERIC) {
   CellStyle cellStyle = cell.getCellStyle();
   if (cellStyle.getDataFormat() == (short)0x9 || cellStyle.getDataFormat() == (short)0xA) return true;
   String dataFormatString = cellStyle.getDataFormatString();
   String[] posNegZeroTextFormat = dataFormatString.split(";");
   for (int i = 0; i < posNegZeroTextFormat.length; i++) {
    if (posNegZeroTextFormat[i].endsWith("%")) return true; 
   }
   if (dataFormatString.endsWith("%")) return true;
  }
  return false;
 }
 
 static double getPercentCellValue(Cell cell) {
  double cellValue = cell.getNumericCellValue();
  double percentValue = cellValue * 100d;
  return percentValue;
 }
 
 static void handleCellByType(CellType cellType, Cell cell) {
  switch (cellType) {
   case STRING:
    System.out.println(cell.getRichStringCellValue().getString());
    break;
   case NUMERIC:
    if (DateUtil.isCellDateFormatted(cell)) {
     System.out.println(cell.getDateCellValue());
    } else if (isCellPercentFormatted(cell)) {
     System.out.println(getPercentCellValue(cell));
    } else {
     System.out.println(cell.getNumericCellValue());
    }
    break;
   case BOOLEAN:
    System.out.println(cell.getBooleanCellValue());
    break;
   case FORMULA:
    System.out.println(cell.getCellFormula());
    //probably evaluate the formula first
    handleCellByType(cell.getCachedFormulaResultType(), cell);
    break;
   case ERROR:
    System.out.println(cell.getErrorCellValue());
    break;
   case BLANK:
    System.out.println();
    break;
   default:
    System.out.println();
  } 
 }
 
 static void readByType(Workbook workbook) { 
  for (Sheet sheet: workbook) {
   System.out.println("Sheet " + sheet.getSheetName());
   for (Row row : sheet) {
    for (Cell cell : row) {
     handleCellByType(cell.getCellType(), cell);
    }
   }
  } 
 }
    
 public static void main(String[] args) throws Exception {
  
  try (
   Workbook workbook = WorkbookFactory.create(new FileInputStream("ExcelExample.xlsx"));
   //Workbook workbook = WorkbookFactory.create(new FileInputStream("ExcelExample.xls"));
   ) {
   
   readAsStrings(workbook);
   
   readByType(workbook);
   
  }  
 }
}

有了您的样本表， readAsStrings 将打印:

Sheet Sheet1
A1:123456789012
B1:0.1%
C1:0.02%
D1:0.003%
E1:0.0004%
F1:0.0005%

readByType 打印:

Sheet Sheet1
1.23456789012E11
0.1
0.02
0.003
3.9999999999999996E-4
5.0E-4