I am trying to print out a cell from an encrypted Excel workbook. The cell contains a time and when i try to print cell.getNumericCellValue()
, the result is a random number, that seems to be dependend of the time within the cell, in this case 0.5208333333333334
. When I try to just print the cell, the result is always this date: 31-Dez.-1899
, independend of the time within the cell. Here is the cell within the Excel sheet: Screenshot
I am using Windows 11, Eclipse Version: 2021-12 (4.22.0), JDK Version: jdk-17.0.2
This is the code executed:
XSSFWorkbook wb = null;
try (FileInputStream fis = new FileInputStream(excelFilePath);){
wb = ((XSSFWorkbook)WorkbookFactory.create(fis, password));
} catch (IOException | EncryptedDocumentException e) {
e.printStackTrace();
}
XSSFSheet sheet = wb.getSheetAt(0);
XSSFCell cell = sheet.getRow(1).getCell(1);
System.out.println(cell); // results in 31-Dez.-1899
System.out.println(cell.getNumericCellValue()); // results in 0.5208333333333334
CodePudding user response:
To get cell values as String
in the same format as in the Excel sheet, do using DataFormatter
as shown here:
Java POI : How to read Excel cell value and not the formula computing it?
Code excerpt:
...
DataFormatter dataFormatter = new DataFormatter(new java.util.Locale("en", "US"));
...
String value = dataFormatter.formatCellValue(cell);
...
Or, if you want the date cell value, then do get the date cell value instead of the numeric cell value as shown in https://poi.apache.org/components/spreadsheet/quick-guide.html#CellContents:
...
switch (cell.getCellType()) {
...
case CellType.NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
System.out.println(cell.getDateCellValue());
} else {
System.out.println(cell.getNumericCellValue());
}
...