0

I'm trying to read dates formatted like "07/27/1955" from an xlsx file, but I'm getting incorrect dates. Every date that I've tried returns a date of eithe

Here's what I currently have after looking at @Sayantam's answer here: https://stackoverflow.com/a/7261427/9659160

FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

                    CellValue cValue = evaluator.evaluate(currCell);
                    double dv = cValue.getNumberValue();
                    if (DateUtil.isCellDateFormatted(currCell)) {
                        Date date = HSSFDateUtil.getJavaDate(dv);
                        String dateFmt = currCell.getCellStyle().getDataFormatString();
                        String strValue = new CellDateFormatter(dateFmt).format(date);
                        System.out.println(strValue);
                    }

This returns 6/20/04 when 07/27/1955 is in the excel file.

For that same value

currCell.getStringCellValue()

returns 20297

currCell.getDateCellValue()

returns Mon Jun 20 00:00:00 EST 1904

currCell.getNumericCellValue()

returns 1633.0

Chris Whelan
  • 1
  • 1
    No help possible since no [Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve) provided. But what you are describing is not possible. A cell can either have string cell value or numeric cell value but not both the same time. So either `currCell.getStringCellValue()` or `currCell.getNumericCellValue()` must throw exception. And the numeric cell value 1633 is exactly the date cell value 06/20/1904 == 1633 days after 01/01/1900. And **numeric** cell value 20297 would be 07/27/1955 == 20297 days after 01/01/1900. – Axel Richter Mar 08 '19 at 04:44
  • In general try using `DataFormatter` together with `FormulaEvaluator`. See https://stackoverflow.com/questions/54630787/fetch-excel-cell-display-value-using-apache-poi/54630924#54630924 – Axel Richter Mar 08 '19 at 08:19
  • @AxelRichter this helped me a lot. I was able to accomplish what I needed by adding the string value to 01/01/1900. I'll edit my post to make it more useful for others with the same issue. Thank you! – Chris Whelan Mar 08 '19 at 16:39

0 Answers0