POI读取Excel数值转科学计数法致BigDecimal精度异常如何解决

cell.getNumericCellValue()返回的是Excel存储的原始双精度浮点数，本身不携带单元格格式定义的展示规则：

• Excel的数值存储和界面展示是完全分离的，你看到的19,000,000.00是单元格按格式规则渲染后的结果，底层存储的原始值就是19000000，直接读取原始值自然会出现末尾0丢失、大数自动转科学计数法的问题
• 把Double类型的原始值直接转BigDecimal，会继承浮点数的精度特性，调用scale()、precision()得到的结果自然和界面展示的数值属性不匹配
• 你之前尝试的Double.parseDouble()、转double/float后再转BigDecimal的方案，本质都是经过双精度浮点数中转，浮点数本身不保留格式定义的末尾小数0，也不携带单元格展示精度信息，不可能解决问题

要获取和Excel界面展示完全一致的数值、正确完成精度校验，不要直接读取原始数值，要结合单元格的格式化规则拿到渲染后的字符串，再转BigDecimal处理：

实现逻辑 #

• 使用POI内置的DataFormatter类，该类会按照单元格配置的格式规则，把单元格值渲染成和界面展示完全一致的字符串，自动处理千分位、科学计数法、小数位补0等格式逻辑
• 拿到格式化后的字符串，去除千分位分隔符后转成BigDecimal，此时得到的BigDecimal的标度、精度属性和Excel展示的数值完全一致
• 基于转换后的BigDecimal做长度、精度校验即可

修正后的代码示例 #

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import java.math.BigDecimal;
import java.util.Iterator;

// DataFormatter初始化一次即可复用，不需要每次读单元格都新建
DataFormatter dataFormatter = new DataFormatter();
Object theObject = new Object();

Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext() && !hasError) {
    Cell cell = cellIterator.next();
    theColumnIndex = cell.getColumnIndex();

    // 省略其他分支逻辑，数值单元格处理替换为以下代码
    // 获取和Excel界面展示完全一致的单元格字符串值，例如会返回"19,000,000.00"而非原始double值
    String cellDisplayStr = dataFormatter.formatCellValue(cell);
    // 去除千分位分隔符后转BigDecimal
    BigDecimal amount = new BigDecimal(cellDisplayStr.replace(",", ""));
    theObject.setAmount(amount);

    // 按规则做校验，以Numeric(15,3)约束为例：
    // 1. 小数位长度（scale()返回值）不能超过3
    // 2. 整数位长度=总精度(precision())-小数位长度，不能超过12，否则总长度超过15
    if (amount.scale() > 3) {
        // 小数位超限，记录错误日志
    }
    if (amount.precision() - amount.scale() > 12) {
        // 整数位超限，总长度不符合要求，记录错误日志
    }
}

注意事项 #

• 禁止经过double/float类型做中转，双精度浮点数本身存在精度丢失问题，只要经过浮点数中转，就无法拿到和展示完全一致的精度结果
• 如果单元格内容是公式计算得到的数值，可以给formatCellValue方法传入FormulaEvaluator实例，工具会自动计算公式结果后再做格式化，适配公式场景
• DataFormatter对Excel支持的所有数值格式（常规、数值、货币、自定义数值格式等）都能正确渲染，返回结果和Excel界面看到的内容完全一致

内容的提问来源于stack exchange，提问作者Erik