CODEWITHSUNDEEP
javaexcelexcel-formulaapache-poi
Sooraj ER
Sooraj ER

Reputation: 121

Get Date value of cell with date formula from Excel using Apache POI Java

I was trying to read from excel file everything work good with normal cell values. But when came to formulas, especially Date fields, I am not able to get any Date values.

Date generated using formula in excel

How can i check for date type in Evaluated CellValue?? and how to get date value?

My code is given below. 

Cell cell = nextRow.getCell(cellIndex, Row.CREATE_NULL_AS_BLANK);
if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
    FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    CellValue formulaValue = evaluator.evaluate(cell);
    switch (formulaValue.getCellType()) {
        case Cell.CELL_TYPE_NUMERIC: {
            int numbericValue = formulaValue.getNumericValue(); // 42909.0
            if (isCellDate?) { //What should i check here?
                // DATE THINGS
                // how to convert numeric value 42909.0 to date??
            } else {
               // NUMERIC THINGS 
            }
            break;
        }
        default:
            // STRING - ERROR - BLANK - NUMERIC THINGS 
            break;
    }
}

Upvotes: 1

Views: 1742

Answers (1)

Sooraj ER
Sooraj ER

Reputation: 121

HSSFDateUtil : Helped to solve my issue. my solution is :

case Cell.CELL_TYPE_NUMERIC: {
if (HSSFDateUtil.isCellDateFormatted(cell)) {
    Date date = HSSFDateUtil.getJavaDate(formulaValue.getNumberValue());
    System.out.println(date);
} else {
    System.out.println(cell.getNumericCellValue());
}
break;

}

Upvotes: 2

Related Questions