获取单元格值如何在Excel presented [英] Get the cell value as how it was presented in excel

问题描述

我目前工作的一个项目，它读取使用Excel文件的Apache POI。

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 当我这样做 getNumericCellValue（）。当我强迫细胞是一个字符串类型，做 getStringCellValue（）它，然后给我 9000 。我需要的是数据，因为它是如何在Excel psented $ P $。

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 类，但根据我的理解，它需要你的code要知道，细胞具有格式。就我而言，我不知道该细胞可能具有的格式。

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.

所以，我怎么可以检索单元格值，它是如何$ P $在Excel psented？

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有一个类来做到这一点 - <一个href=\"http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DataFormatter.html\">DataFormatter

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

所有你需要做的是一样的东西：

All you need to do is something like:

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