在 Java 中读取 Excel 数字单元格值 [英] Read Excel Numeric cell values as they are in Java

问题描述

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

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:

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

推荐答案

这个问题在 Stack Overflow 上经常出现，因此建议您浏览许多类似的问题以找到答案！

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!

Excel 将文件格式中的几乎所有数字都存储为浮点值，这就是为什么 POI 会为您返回数字单元格的双精度值，因为它确实存在.如果您希望它看起来像在 Excel 中那样，您需要将针对单元格定义的格式规则应用于数字.因此，您要与我在此处的回答完全相同.引用:

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:

你想要做的是使用 DataFormatter类.您将这个单元格传递给它，它会尽最大努力返回一个字符串，其中包含 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 格式化这些单元格，它会返回一个包含整数字符串的字符串.

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.

您需要做的就是:

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

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

这篇关于在 Java 中读取 Excel 数字单元格值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！