java - Unable to read date value as a string from excel sheet calculated by formula using Apache POI, What is missing?

Question

So my Java Code is like this to read excel sheet cell values, but I could not be able to get the value of date as string instead it is auto corrected to numbers. Also note that all these values of excel are calculated by formula at their respective cell. What is missing?

public class DatasheetReader {
public static void main(String args[]) throws Exception {
    String path = "D:\Workspace\Analytics\TestDataSheets\IRPTestData.xlsx";
    String sheet = "CompleteSimulationData";
    getCellData(path, sheet);
}

public static Object[][] getCellData(String datSheetPath, String sheetName)throws Exception {

    FileInputStream fis = new FileInputStream(new java.io.File(datSheetPath));
    XSSFWorkbook workbook = new XSSFWorkbook(fis);
    XSSFSheet sheet = workbook.getSheet(sheetName);

    int rowCount = sheet.getPhysicalNumberOfRows();
    int columnCount = sheet.getRow(0).getPhysicalNumberOfCells();

    // System.out.println(rowCount);
    // System.out.println(columnCount);

    Object[][] cellData = new Object[rowCount][columnCount];
    Object[][] dataProviderArray = new Object[rowCount - 1][columnCount];

    for (int i = 0; i < rowCount; i++) {
        System.out.println(" ");
        for (int j = 0; j < columnCount; j++) {
            // IF - for blanks in excel
            if (i != 0) {
                // IF - for not null values
                if (sheet.getRow(i).getCell(j) != null) {
                    Cell cell = sheet.getRow(i).getCell(j);
                    int cellType = cell.getCachedFormulaResultType();
                    if(cellType==Cell.CELL_TYPE_NUMERIC){
                         System.out.println(cell.getNumericCellValue()+" "+cell.getCachedFormulaResultType());
                    }else if(cellType==Cell.CELL_TYPE_STRING){
                         System.out.println(cell.getRichStringCellValue()+" "+cell.getCachedFormulaResultType());
                    }
                } else {
                    cellData[i][j] = "";
                }

            } else {
                continue;
            }
            //System.out.println(cellData[i][j]);
        }
    }

    for (int i = 1; i < rowCount; i++) {
        for (int j = 0; j < columnCount; j++) {
            //System.out.print(cellData[i][j] + " ");
            dataProviderArray[i - 1][j] = cellData[i][j];
        }
    }
    workbook.close();
    return dataProviderArray;
}}

All these values are cached values calculated from formula.
All values are getting as per expectations except the date, it come as 42887.0 instead of Jun-2017 basically all values are not alone values in excel cell rather these are values calculated from formulas, for date POI returns its type as CELL_TYPE_NUMERIC, Don't understand how to handle this, as unable to use DataFormat of POI because then all values which I'm going to read getting as formula?

Answer 1

The following code should be able reading any kind of Excel cell contents. I hope I have not forgotten something.

Mainly changes to Busy Developers' Guide: Getting the cell contents are:

Using DataFormatter having a FormulaEvaluator.

If cell.getCellTypeEnum() is CellType.FORMULA then check again the cell.getCachedFormulaResultTypeEnum() and react dependent of what this is like.

Works using latest stable release Apache POI 3.16. Works not using lower versions.

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;
import org.apache.poi.ss.usermodel.CellType.*;

import java.io.FileInputStream;

class ReadExcelExample {

 public static void main(String[] args) throws Exception {

  Workbook wb  = WorkbookFactory.create(new FileInputStream("ExcelExample.xlsx"));

  DataFormatter formatter = new DataFormatter();
  FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

  Sheet sheet = wb.getSheetAt(0);
  for (Row row : sheet) {
   for (Cell cell : row) {
    CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
    System.out.print(cellRef.formatAsString());
    System.out.print(" - ");

    // get the text that appears in the cell by getting the cell value and applying any data formats (Date, 0.00, 1.23e9, $1.23, etc)
    String text = "";
    try {
     text = formatter.formatCellValue(cell, evaluator);
    } catch (org.apache.poi.ss.formula.eval.NotImplementedException ex) {
     text = "Formula not implemented";
    }
    System.out.println(text);

    // Alternatively, get the value and format it yourself
    switch (cell.getCellTypeEnum()) {
     case STRING:
      System.out.println(cell.getRichStringCellValue().getString());
      break;
     case NUMERIC:
      if (DateUtil.isCellDateFormatted(cell)) {
       System.out.println(cell.getDateCellValue());
      } else {
       System.out.println(cell.getNumericCellValue());
      }
      break;
     case BOOLEAN:
      System.out.println(cell.getBooleanCellValue());
      break;
     case FORMULA:
      System.out.println(cell.getCellFormula());
      switch (cell.getCachedFormulaResultTypeEnum()) {
       case STRING:
        System.out.println(cell.getRichStringCellValue().getString());
        break;
       case NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
         System.out.println(cell.getDateCellValue());
        } else {
         System.out.println(cell.getNumericCellValue());
        }
        break;
       case BOOLEAN:
        System.out.println(cell.getBooleanCellValue());
        break;
       case ERROR:
       System.out.println(cell.getErrorCellValue());
        break;
       default:
        System.out.println("default formula cell"); //should never occur
      }
      break;
     case ERROR:
      System.out.println(cell.getErrorCellValue());
      break;
     case BLANK:
      System.out.println("blank");
      break;
     default:
      System.out.println("default cell"); //should never occur
    }
   }
  }

  wb.close();

 }
}