java 从excel单元格中读取日期值作为字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10616935/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Reading date value from excel cell as a String
提问by Nandkumar Tekale
I am using apache poi library to read excel file. I am stuck while reading password cell. If user gives date as a password in password cell i.e. 16/05/2012. I am reading this value as "41045" while value should be "16/05/2012". This is my code :
我正在使用 apache poi 库来读取 excel 文件。我在阅读密码单元格时卡住了。如果用户在密码单元格中提供日期作为密码，即 16/05/2012。我将此值读为“41045”，而值应为“16/05/2012”。这是我的代码：
cell = row.getCell(); // date in the cell password '16/05/2012'
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
cellValue = cell.getRichStringCellValue().getString();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
if(cellCount == TEMPLATE_PASSWORD) {// if cell is password
cell.setCellType(Cell.CELL_TYPE_STRING);
cellValue = cell.getRichStringCellValue().getString(); // value read is 41045 and not "16/05/2012"
}
break;
default:
}
can anyone help on this?
有人可以帮忙吗？
Thanks.
谢谢。
回答by Gagravarr
The class you're looking for in POI is DataFormatter
您在 POI 中寻找的课程是DataFormatter
When Excel writes the file, some cells are stored as literal Strings, while others are stored as numbers (including dates). For the latter, a floating point value representing the cell is stored in the file, so when you ask POI for the value of the cell that's what it actually has.
当 Excel 写入文件时，某些单元格存储为文字字符串，而其他单元格存储为数字（包括日期）。对于后者，表示单元格的浮点值存储在文件中，因此当您向 POI 询问单元格的值时，这就是它实际拥有的值。
Sometimes though, especially when doing Text Extraction (but not always), you want to make the cell value look like it does in Excel. It isn't always possible to get that exactly in a String (non full space padding for example), but the DataFormatter class will get you close.
但有时，尤其是在进行文本提取时（但并非总是如此），您希望单元格值看起来像在 Excel 中一样。并非总是可以在字符串中准确获取它（例如，非全空间填充），但 DataFormatter 类会让您接近。
Also note that in Excel, dates are stored as floating point numbers since ~1900, which is why you're seeing a number not a date. Use DataFormatter (or similar) to have it rendered as a date
另请注意，在 Excel 中，日期自 1900 年起存储为浮点数，这就是为什么您看到的是数字而不是日期。使用 DataFormatter（或类似的）将其呈现为日期
回答by user3044855
Use the below code to get the exact date formate as u entered in excel sheet.
使用以下代码获取您在 Excel 工作表中输入的确切日期格式。
DataFormatter df = new DataFormatter();
stringCellValue = df.formatCellValue(cell);
回答by nunchuckNinja
Prefixing the cell value with a ' while saving it into xls renders it into a string cell. So, whatever you read from that cell will be treated as a string and read as is.
在将单元格值保存到 xls 的同时用 ' 前缀将其呈现为字符串单元格。因此，您从该单元格读取的任何内容都将被视为字符串并按原样读取。
