https://poi.apache.org/spreadsheet/eval-devguide.html#Appendix+A
IFERROR seems to be supported. Even if it was not supported (such as a custom VBA macros), you can always write Java code to define how the function behaves and register it with POI. If you're using xlsx or xlsm files, you could edit the XML of the file by hand to see if clearing or deleting the cached formula result element in sheet#.xml resolves your issue. If that strategy works, then your code would need to make the same file changes. You can use POI's XML DOM to edit the XML via the CTCell class, which is loads easier than writing your own XML transform code from scratch. On Aug 7, 2017 01:05, "Hehabr" <[email protected]> wrote: I am trying to update an existing Excel file with many formulas. I can't use evaluate-function ( Excel-file contains a function not supported by Apache POI) How can I delete the cached results from the file, before I save new file? import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.util.CellReference; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.*; public class POITestRB { static String excelFileOrig = "C:/Test/1.xlsm"; static String excelFileNew = "C:/Test/excelFileNew.xlsm"; static FileInputStream fis; static XSSFWorkbook workbook; public static void main(String[] args) throws IOException { fis = new FileInputStream(excelFileOrig); workbook = new XSSFWorkbook(fis); gettingCellContents(workbook, "D5"); updateCell(workbook, 10.0); fis.close(); workbook.close(); fis = new FileInputStream(excelFileNew); workbook = new XSSFWorkbook(fis); gettingCellContents(workbook, "D5"); fis.close(); workbook.close(); } public static void updateCell(XSSFWorkbook workbook, Double newData) { try { XSSFSheet sheet = workbook.getSheetAt(1); CellReference ref = new CellReference("C8"); int row = ref.getRow(); int col = ref.getCol(); Cell cell = sheet.getRow(row).getCell(col); if (cell != null) { cell.setCellValue(newData); } workbook.getCreationHelper().createFormulaEvaluator(). clearAllCachedResultValues(); // workbook.getCreationHelper().createFormulaEvaluator().evaluateAll(); OutputStream os = new FileOutputStream(excelFileNew); workbook.write(os); os.flush(); os.close(); } catch (Exception e) { e.printStackTrace(); } } private static void gettingCellContents(XSSFWorkbook workbook, String cellId) { workbook.setForceFormulaRecalculation(true); XSSFSheet sheet = workbook.getSheetAt(1); CellReference ref = new CellReference(cellId); int row = ref.getRow(); int col = ref.getCol(); Cell cell = sheet.getRow(row).getCell(col); 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("Formula is: " + cell.getCellFormula()); System.out.println("cell.getCachedFormulaResultType(): " + cell.getCachedFormulaResultType()); switch(cell.getCachedFormulaResultType()) { case Cell.CELL_TYPE_NUMERIC: System.out.println("0. case Cell.CELL_TYPE_NUMERIC --> Last evaluated as: " + cell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.println("4. case Cell.CELL_TYPE_STRING --> Last evaluated as \"" + cell.getRichStringCellValue() + "\""); break; case Cell.CELL_TYPE_ERROR: System.out.println("5. case Cell.CELL_TYPE_ERROR --> "); break; } break; case BLANK: System.out.println(); break; default: System.out.println("default"); } } } -- View this message in context: http://apache-poi.1045710.n5. nabble.com/Apache-POI-delete-the-cached-results-from-the-file-tp5728446.html Sent from the POI - User mailing list archive at Nabble.com. --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
