Problem with FormulaEvaluator cell - Apache POI

Viewed 32

I have this strange situation and I need some tips on how to resolve it.

I have a column ( lets call it column K ) with values that are result of a FORMULA ( the values of this column are taken from another sheet). All the values on column K are set as String.

I use all the guidelines from the website: https://poi.apache.org/components/spreadsheet/eval.html but I have a real problem to extract numbers ( example: 12345 ) and data ( 08/09/2022). When i extract the number 12345 on java i have 12.34.5 and when i extract the date (08/09/2022) it gives me a value: 44813.0

A pseudocode that I was using is this one:

    FileInputStream fis = new FileInputStream("/somepath/test.xls");
Workbook wb = new HSSFWorkbook(fis); //or new XSSFWorkbook("/somepath/test.xls")
Sheet sheet = wb.getSheetAt(0);
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
// suppose your formula is in B3
CellReference cellReference = new CellReference("B3"); 
Row row = sheet.getRow(cellReference.getRow());
Cell cell = row.getCell(cellReference.getCol()); 
if (cell!=null) {
    switch (evaluator.evaluateFormulaCell(cell)) {
        case Cell.CELL_TYPE_BOOLEAN:
            System.out.println(cell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            System.out.println(cell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            System.out.println(cell.getStringCellValue());
            break;
        case Cell.CELL_TYPE_BLANK:
            break;
        case Cell.CELL_TYPE_ERROR:
            System.out.println(cell.getErrorCellValue());
            break;
        // CELL_TYPE_FORMULA will never occur
        case Cell.CELL_TYPE_FORMULA: 
            break;
    }
}

Can someone give me some tips on how to resolve it?

1 Answers

Resolved: Guys, as always some tools has their own logic that if you never work with them you will never know. The solution was really easy and crazy :)

I select all the content of the column with the formula and other stuff in sheet 1, and i simply paste everything in another sheet 2.

At this moment from all the strange logic about formula, cached stuff, or real content on the cell,... after pasting in another sheet ( sheet 2 ) everything was visibile as a String without any Formula. So just by doing cell.toString() i get the string value of everything.

Sometimes the easiest solutions are the most hardest thing to reason about lol.

Related