Apache POI incorrectly printing cell that is auto formatted to time

Question

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

Axel Richter · Accepted Answer · 2022-04-30T05:34:39.927

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:

import org.apache.poi.ss.usermodel.CellType;
...
    switch (cell.getCellType()) {
...
        case NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                System.out.println(cell.getDateCellValue());
            } else {
                System.out.println(cell.getNumericCellValue());
            }
...

Apache POI incorrectly printing cell that is auto formatted to time

1 Answers1