Apache POI DataFormatter 返回科学记数法

问题描述

我有一个 xlsx 文件，我正在 java 6 中使用 apache poi 3.17 读取.在一个实例中，我有一个值为 123456789011 的单元格.它作为 NUMERIC CellTypeEnum Cell 读入 java.当我使用 DataFormatter 像这样获取单元格的值时:

I have an xlsx file I'm reading with apache poi 3.17 in java 6. In one instance I have a cell with the value, 123456789011. This is read into java as a NUMERIC CellTypeEnum Cell. When I use DataFormatter to get the value of the cell like this:

DataFormatter formatter = new DataFormatter(Locale.US);String strVal = formatter.formatCellValue(cell);

字符串值显示为1.234567+11".我需要单元格中的实际值是123456789011".我怎样才能得到它?

The String value comes out as "1.234567+11". I need the real value in the cell which is "123456789011". How can I get that?

我已经尝试在 Java 中使用 BigDecimal 来转换字符串，但它返回123456700000"，因为这是对给定信息所能做的最好的事情；这意味着我需要从实际的单元格对象中获取正确的值.我也尝试使用 cell.getNumericCellValue() 但它返回一个双精度值，它的限制太小而无法处理原始值，因此它被检索为1.234567E11"，它与其他检索方法具有相同的问题.

I already tried using BigDecimal in Java to convert the String, but that returns "123456700000" because that's the best it can do with the given information; meaning I need to get the correct value from the actual cell object. I also tried using cell.getNumericCellValue() but that returns a double, which has a limit too small to handle the original value, so it is retrieved as "1.234567E11" which has the same issue as the other method of retrieval.

在 xlsx 中输入时，有没有办法从原始单元格中获取字符串形式的值?我对原来的 xlsx 没有任何权力.

Is there a way to get the value as a String from the original Cell as it is entered in the xlsx? I have no power over the original xlsx.

谢谢.

推荐答案

使用 BigDecimal 的 toPlainString() 方法.它接受科学记数法并将其转换为相应的数字字符串值.

use toPlainString() method of BigDecimal. It takes in the scientific notation and converts it to its corresponding numerical String value.

我尝试这样做:(我的工作表中单元格 A1 的编号为 123456789011，单元格类型为 NUMERIC)

I tried doing this: (I have the number 123456789011 at cell A1 in my sheet, with cell type as NUMERIC)

Row row = sheet.getRow(0);
Object o = getCellValue(row.getCell(0));
System.out.println(new BigDecimal(o.toString()).toPlainString());

getCellValue 方法是我读取单元格的通用方法:

getCellValue method is my generic method that reads the cell:

public Object getCellValue(Cell cell) {
        if (cell != null) {
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                return cell.getStringCellValue();

            case Cell.CELL_TYPE_BOOLEAN:
                return cell.getBooleanCellValue();

            case Cell.CELL_TYPE_NUMERIC:
                return cell.getNumericCellValue();
            }
        }
        return null;
    }

希望这会有所帮助！

这篇关于Apache POI DataFormatter 返回科学记数法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！