POI 读取 excel 字符串作为数字

问题描述

我正在使用 Apache POI 来读取 excel 文件.在阅读它时，我注意到它将字符串作为浮点值.

I am using Apache POI for reading excel file. And while reading it I have noticed that it takes strings as float values.

如果我的单元格包含 1，那么它将获取它为 1.0

If my cell contains 1 then it will fetch it as 1.0

我从前面的问题中得到了一些提示并修改了代码，但浮点表示仍然保持原样.

I took some hints from previous questions here and modified the code but still the float representation remains as it is.

如何正确读取字符串和日期的数据?

How would I read correctly the data for strings and dates?

DataFormatter df = new DataFormatter();

        for (Row row : sheet) {

            for(int cn=0; cn<row.getLastCellNum(); cn++) {
                   // If the cell is missing from the file, generate a blank one
                   // (Works by specifying a MissingCellPolicy)
                   Cell cell = row.getCell(cn, Row.CREATE_NULL_AS_BLANK);
                   // Print the cell for debugging
                   cell.setCellType(Cell.CELL_TYPE_STRING);

                   System.out.println("CELL: " + cn + " --> " + df.formatCellValue(cell));

                   if (row.getRowNum() == 0) {

                        sheetColumnNames.add(cell.getRichStringCellValue().getString());
                    }

            }

        }

推荐答案

将评论提升为答案

问题在于通话

cell.setCellType(Cell.CELL_TYPE_STRING);

这样做是要求 POI 尝试将单元格从当前的任何内容(例如数字)转换为字符串.用于尝试执行此操作的转换是一个相当简单的转换，这就是您丢失格式的原因

What that is doing is asking POI to try to convert the cell from whatever it is currently (eg a number) into a string. The conversion applied to try to do this is a fairly simple one, which is why you're loosing the formatting

如果您只想取回包含 Excel 中显示的单元格值的字符串，只需直接调用 DataFormatter，它会尽力而为.玩弄单元格类型只会混淆事物，并有丢失格式的风险

If you just want to get back a String that contains the Cell Value as shown in Excel, just call DataFormatter directly, and it'll do its best. Playing around with the Cell Type will only confuse things, and will risk loosing formatting

这篇关于POI 读取 excel 字符串作为数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！