读取Excel中的Excel数字单元格值

When I am reading excel numeric cell values, i am getting the output with decimals. eg: 79 is reading as 79.0, 0.00 reading as 0.0. The code for my application I have written is:

当我读取excel数字单元格值时，我得到带小数的输出。例如：79读数为79.0，读数为0.00。我写的应用程序的代码是：

int type = cell.getCellType();
if (type == HSSFCell.CELL_TYPE_NUMERIC)
  System.out.println(cell.getNumericCellValue());

4 个解决方案

#1

This question comes up a lot on Stack Overflow, so you'd be well advised to look through many of the similar ones to see there answers!

这个问题在Stack Overflow上出现了很多，所以建议您查看许多类似的问题以查看答案！

Excel stores almost all numbers in the file format as floating point values, which is why POI will give you back a double for a numeric cell as that's what was really there. If you want it to look like it does in Excel, you need to apply the formatting rules defined against the cell to the number. Thus, you want to do exactly the same thing as in my answer here. To quote:

Excel将几乎所有数字都以文件格式存储为浮点值，这就是为什么POI会为数字单元格返回一个双精度数据，因为那就是真正存在的数字单元格。如果您希望它看起来像在Excel中一样，则需要将针对单元格定义的格式设置规则应用于该数字。因此，你想要完成与我在这里的回答完全相同的事情。去引用：

What you want to do is use the DataFormatter class. You pass this a cell, and it does its best to return you a string containing what Excel would show you for that cell. If you pass it a string cell, you'll get the string back. If you pass it a numeric cell with formatting rules applied, it will format the number based on them and give you the string back.

你想要做的是使用DataFormatter类。你传递了一个单元格，它会尽力返回一个字符串，其中包含Excel为该单元格显示的内容。如果你传递一个字符串单元格，你将得到回来的字符串。如果您传递了一个应用了格式规则的数字单元格，它将根据它们格式化数字并返回字符串。

For your case, I'd assume that the numeric cells have an integer formatting rule applied to them. If you ask DataFormatter to format those cells, it'll give you back a string with the integer string in it.

对于您的情况，我假设数字单元格应用了整数格式规则。如果你要求DataFormatter格式化这些单元格，它会返回一个包含整数字符串的字符串。

All you need to do is:

您需要做的就是：

// Only need one of these
DataFormatter fmt = new DataFormatter();

// Once per cell
String valueAsSeenInExcel = fmt.formatCellValue(cell);

#2

I'm not 100% this would work but I believe what you're looking for is DecimalFormat.

我不是100％这会工作，但我相信你正在寻找的是DecimalFormat。

#3

I assume you're using Apache POI.

我假设您正在使用Apache POI。

You should consider playing with DataFormats. Here is some very minimal draft code.

您应该考虑使用DataFormats。这是一些非常小的草案代码。

Otherwise, if the data in your work book is consistent, you might want to round the double returned by getNumericCellValue to int.

否则，如果工作簿中的数据是一致的，您可能希望将getNumericCellValue返回的double舍入为int。

System.out.println((int)Math.round(cell.getNumericCellValue()));

#4

Use the DataFormatter as the following, it will detect the format of the cell automatically and produce the string output

使用DataFormatter，它将自动检测单元格的格式并生成字符串输出

                    DataFormatter formatter = new DataFormatter();
                    String df2 = formatter.formatCellValue(cell);

#1