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?