获取单元格值作为它在excel中的显示方式 [英] Get the cell value as how it was presented in excel

问题描述

我目前正在开发一个使用 Apache POI 读取 excel 文件的项目.

I am currently working on a project that reads an excel file using Apache POI.

我的任务似乎很简单，我只需要获取显示在 excel 文件中的单元格值.我知道根据单元格的单元格类型执行 switch 语句.但是如果数据是这样的

My task seems to be simple, I just need to get the cell value as it was display in the excel file. I am aware of performing a switch statement based on the cell type of a cell. But if the data is something like

9,000.00

POI 给我 9000.0.当我将单元格强制为字符串类型并执行 getStringCellValue() 时，它会给我 9000.我需要的是数据在 excel 中的显示方式.

POI gives me 9000.0 when I do getNumericCellValue(). When I force the cell to be a string type and do getStringCellValue() it then gives me 9000. What I need is the data as how it was presented in excel.

我发现一些帖子告诉使用 DataFormat 类，但根据我的理解，它要求您的代码知道单元格的格式.就我而言，我不知道单元格可能具有的格式.

I found some post telling to use DataFormat class but as per my understanding, it requires your code to be aware of the format that the cell has. In my case, I am not aware of the format that the cell might have.

那么，如何检索单元格值，就像它在 excel 中的显示方式一样?

So, how can I retrieve the cell value as how it was presented in excel?

推荐答案

Excel 将某些单元格存储为字符串，但大多数存储为应用了特殊格式规则的数字.您需要做的是针对数字单元格运行这些格式规则，以生成与 Excel 中类似的字符串.

Excel stores some cells as strings, but most as numbers with special formatting rules applied to them. What you'll need to do is have those formatting rules run against the numeric cells, to produce strings that look like they do in Excel.

幸运的是，Apache POI 有一个类可以做到这一点 - 数据格式化程序

Luckily, Apache POI has a class to do just that - DataFormatter

您需要做的就是:

 Workbook wb = WorkbookFactory.create(new File("myfile.xls"));
 DataFormatter df = new DataFormatter();

 Sheet s = wb.getSheetAt(0);
 Row r1 = s.getRow(0);
 Cell cA1 = r1.getCell(0);

 String asItLooksInExcel = df.formatCellValue(cA1);

不管单元格类型是什么，DataFormatter 都会使用 Excel 中应用的规则尽可能为您设置格式

Doesn't matter what the cell type is, DataFormatter will format it as best it can for you, using the rules applied in Excel

这篇关于获取单元格值作为它在excel中的显示方式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！