从excel单元格中读取日期值作为字符串 [英] Reading date value from excel cell as a String

问题描述

我正在使用 apache poi 库来读取 excel 文件.我在阅读密码单元格时卡住了.如果用户在密码单元格中提供日期作为密码，即 16/05/2012.我将此值读为41045"，而值应为16/05/2012".这是我的代码:

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 :

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:
}

有人可以帮忙吗?

谢谢.

推荐答案

您在 POI 中寻找的课程是 DataFormatter

The class you're looking for in POI is DataFormatter

当 Excel 写入文件时，某些单元格存储为文字字符串，而其他单元格存储为数字(包括日期).对于后者，表示单元格的浮点值存储在文件中，因此当您向 POI 询问单元格的值时，这就是它实际拥有的值.

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 中一样.并非总是可以在字符串中准确获取它(例如，非全空间填充)，但 DataFormatter 类会让您接近.

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 中，自 ~1900 年以来，日期存储为浮点数，这就是为什么您看到的是数字而不是日期.使用 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单元格中读取日期值作为字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！