3

I want to disable all formulas on my excel sheet. I wrote this code as a test for only one cell. Although everything seen fine, my B3 cell remains as formula cell. I searched a lot but did not find solution. What is problem of my code or what you recommend?

public static void formulaDisabler() throws IOException, InvalidFormatException {
    FileInputStream file = new FileInputStream("C:\\Users\\Farid\\Desktop\\test.xlsx");
    Workbook workbook = new XSSFWorkbook(file);
    Sheet sheet = workbook.getSheetAt(0);
    FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    CellReference cellReference = new CellReference("B3");
    Row row = sheet.getRow(cellReference.getRow());
    Cell cell;
    cell = row.getCell(cellReference.getCol());
    if (cell != null) {
        switch (evaluator.evaluateInCell(cell).getCellType()) {
            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;
        }
    } else {
        System.out.println("cell is null");
    }
}
Farid Orucov
  • 41
  • 4
  • Theoretically this should work (it is also the same as the examle here: https://poi.apache.org/spreadsheet/eval.html). This page has a section called "Formula Evaluation Debugging". You can try to enable logging and see what it prints. – DVarga Jul 08 '16 at 06:01
  • I tried but it shows only step by step formula calculation on excel. I want evaluateInCell to work which evaluate formula and sets its value on the cell. – Farid Orucov Jul 08 '16 at 06:26
  • Your code reads a `test.xlsx` file into memory as `XSSFWorkbook`. Then it does `evaluateInCell` in `B3`. After this `B3` will no more be a formula cell **within memory**. The file `test.xlsx` is not changed until now. – Axel Richter Jul 08 '16 at 10:06
  • Thanks. Problem Solved. I needed to use **workbook.write(new FileOutputStream("C:\\Users\\Farid\\Desktop\\newExcelFile.xlsx")); workbook.close();** to save output as you say. thanks a lot my friend :) – Farid Orucov Jul 08 '16 at 16:35
  • POI cannot evaluate all formulas, such as formulas containing user-defined functions (VBA macros). It might be better and faster to convert formulas using the cached formula result instead. – IceArdor Jul 12 '16 at 02:20

0 Answers0