Apache POI DataFormatter返回科学计数法 [英] Apache POI DataFormatter Returns Scientific Notation

问题描述

我有一个xlsx文件，我正在用Java 6中的apache poi 3.17读取.在一个实例中，我有一个值为123456789011的单元格.将其作为NUMERIC CellTypeEnum单元格读入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);

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来转换String，但是返回"123456700000"，因为这是对给定信息的最佳处理.这意味着我需要从实际的单元格对象中获取正确的值.我也尝试使用cell.getNumericCellValue()，但是返回一个double，它的限制太小而无法处理原始值，因此它被检索为"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()方法.它采用科学计数法，并将其转换为相应的数字String值.

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返回科学计数法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！