I am using apache-poi to modify an excel cell (with setCellValue) and retrieve the value of cellM14 whose value depends on the modified cell.
When I manually open excel cellM14 is updated properly but when I try to print it
System.out.println("Updated Value at M14: " + cellM14.getNumericCellValue());
the old value of cellM14 is printed. I cannot use Formula Evaluator since there is a bug with naming, see:
FormulaEvaluatorFormulaParseException Dotted range (full row or column) expression 'C.aRG' must have exactly 2 dots.
Can anyone help so that I can print the updated cellM14 please?
String filePath = "file";
try (FileInputStream fileInputStream = new FileInputStream(new File(filePath));
XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream)) {
Sheet sheet = workbook.getSheet("input");
workbook.setForceFormulaRecalculation(true);
// Check if Row 13 exists and print original value if the cell exists
Row row13 = sheet.getRow(13);
if (row13 != null) {
Cell cellM14 = row13.getCell(11);
if (cellM14 != null) {
System.out.println("Original Value at M14: " + cellM14.getNumericCellValue());
} else {
System.out.println("Cell M14 is null.");
}
} else {
System.out.println("Row 13 is null.");
}
// Change the value in cell H5 (row index 4, column index 7)
Row row4 = sheet.getRow(4);
if (row4 == null) {
row4 = sheet.createRow(4);
}
Cell sub = row4.getCell(7);
if (sub == null) {
sub = row4.createCell(7);
}
sub.setCellValue("nanoparticle");
// Save workbook
try (FileOutputStream fileOutputStream = new FileOutputStream(new File(filePath))) {
workbook.write(fileOutputStream);
}
} catch (Exception e) {
e.printStackTrace();
}
try (FileInputStream fileInputStream = new FileInputStream(new File(filePath));
XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream)) {
workbook.setForceFormulaRecalculation(true);
try (FileOutputStream fileOutputStream = new FileOutputStream(new File(filePath))) {
workbook.write(fileOutputStream);
}
Sheet sheet = workbook.getSheet("input");
// Check if Row 13 exists and print original value if the cell exists
Row row13 = sheet.getRow(13);
if (row13 != null) {
Cell cellM14 = row13.getCell(11);
if (cellM14 != null) {
System.out.println("Updated Value at M14: " + cellM14.getNumericCellValue());
} else {
System.out.println("Cell M14 is null.");
}
} else {
System.out.println("Row 13 is null.");
}
} catch (Exception e) {
e.printStackTrace();
}
I eventually found a way, by using a VB SCRIPT to save the .xslx file and the formulas are now evaluated properly when accessing the M14 cell (even though it's not the most optimal solution it does the job).