Formulas & Evaluation

import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.util.List;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class InventoryFormulas {

    record Book(String title, double price, int stock) { }

    public static void main(String[] args) throws IOException {
        List<Book> books = List.of(
                new Book("Dune", 12.50, 4),
                new Book("Emma", 8.00, 0),
                new Book("Ulysses", 15.25, 7));
        Path target = Path.of("books-inventory.xlsx");

        try (XSSFWorkbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("Inventory");
            Row header = sheet.createRow(0);
            String[] titles = {"Title", "Price", "Stock", "Stock value", "Status"};
            for (int c = 0; c < titles.length; c++) {
                header.createCell(c).setCellValue(titles[c]);
            }

            for (int i = 0; i < books.size(); i++) {
                Book book = books.get(i);
                Row row = sheet.createRow(i + 1);
                int excelRow = i + 2; // formulas use 1-based rows; row 1 is the header
                row.createCell(0).setCellValue(book.title());
                row.createCell(1).setCellValue(book.price());
                row.createCell(2).setCellValue(book.stock());
                // No leading '=', English function names, comma separators
                row.createCell(3).setCellFormula("B" + excelRow + "*C" + excelRow);
                row.createCell(4).setCellFormula(
                        "IF(C" + excelRow + "=0,\"Out of stock\",\"Available\")");
            }

            int last = books.size() + 1; // last data row, 1-based
            Row totals = sheet.createRow(last);
            totals.createCell(0).setCellValue("Total");
            totals.createCell(3).setCellFormula("SUM(D2:D" + last + ")");
            Cell available = totals.createCell(4);
            available.setCellFormula("COUNTIF(E2:E" + last + ",\"Available\")");

            // Store cached results so that viewers that never calculate still show numbers
            XSSFFormulaEvaluator.evaluateAllFormulaCells(workbook);
            // Ask Excel to recalculate everything anyway when the file is opened
            workbook.setForceFormulaRecalculation(true);

            try (var out = Files.newOutputStream(target)) {
                workbook.write(out);
            }
        }
    }
}

<c r="E2" t="str"><f>IF(C2=0,"Out of stock","Available")</f><v>Available</v></c>

import java.io.File;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class ReadFormulaCells {

    public static void main(String[] args) throws IOException {
        File file = new File("books-inventory.xlsx");
        // read-only: steps 3 and 4 change cells, and close() must not save them into the file
        try (Workbook workbook = WorkbookFactory.create(file, null, true)) {
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            Sheet sheet = workbook.getSheet("Inventory");
            Row totals = sheet.getRow(4);

            // 1. Cached value stored in the file: no calculation, no evaluator needed
            Cell total = totals.getCell(3);
            if (total.getCellType() == CellType.FORMULA) {
                System.out.println("Formula: " + total.getCellFormula());
                System.out.println("Cached type: " + total.getCachedFormulaResultType());
                System.out.println("Cached value: " + total.getNumericCellValue());
            }

            // 2. evaluate(): calculates, returns a CellValue, the cell is NOT modified
            CellValue value = evaluator.evaluate(total);
            switch (value.getCellType()) {
                case NUMERIC -> System.out.println("Number: " + value.getNumberValue());
                case STRING -> System.out.println("Text: " + value.getStringValue());
                case BOOLEAN -> System.out.println("Boolean: " + value.getBooleanValue());
                case ERROR -> System.out.println("Error code: " + value.getErrorValue());
                default -> System.out.println("Other: " + value.getCellType());
            }

            // 3. evaluateFormulaCell(): calculates and stores the result as the cell's cached value,
            //    the formula stays; the return value is the type of the result
            CellType resultType = evaluator.evaluateFormulaCell(totals.getCell(4));
            System.out.println("Result type: " + resultType
                    + ", cached: " + totals.getCell(4).getNumericCellValue());

            // 4. evaluateInCell(): calculates and REPLACES the formula by its result
            Cell status = sheet.getRow(1).getCell(4);
            evaluator.evaluateInCell(status);
            System.out.println(status.getCellType() + ": " + status.getStringCellValue());

            // 5. evaluateAll(): every formula cell of the workbook gets a fresh cached value
            evaluator.evaluateAll();
        }
    }
}

import java.io.IOException;
import java.util.Map;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExternalWorkbook {

    public static void main(String[] args) throws IOException {
        try (XSSFWorkbook inventory = new XSSFWorkbook();
                XSSFWorkbook loans = new XSSFWorkbook()) {

            // The referenced workbook: loans-report.xlsx, sheet "Loans", A1 = number of open loans
            loans.createSheet("Loans").createRow(0).createCell(0).setCellValue(42);

            // Register the external workbook under the file name used in the formula
            inventory.linkExternalWorkbook("loans-report.xlsx", loans);

            Sheet sheet = inventory.createSheet("Inventory");
            Cell open = sheet.createRow(0).createCell(0);
            open.setCellFormula("'[loans-report.xlsx]Loans'!A1");

            FormulaEvaluator mine = inventory.getCreationHelper().createFormulaEvaluator();
            FormulaEvaluator other = loans.getCreationHelper().createFormulaEvaluator();
            // Tell the evaluator which evaluator belongs to which workbook name (include itself)
            mine.setupReferencedWorkbooks(Map.of(
                    "books-inventory.xlsx", mine,
                    "loans-report.xlsx", other));

            CellValue value = mine.evaluate(open);
            System.out.println(value.getNumberValue()); // 42.0
        }
    }
}

Collection<String> supported = WorkbookEvaluator.getSupportedFunctionNames();
Collection<String> missing = WorkbookEvaluator.getNotSupportedFunctionNames();
System.out.println(supported.size() + " implemented, " + missing.size() + " not"); // 252 and 161 in 5.5.1
System.out.println(supported.contains("XLOOKUP")); // true

import java.io.IOException;
import org.apache.poi.ss.formula.OperationEvaluationContext;
import org.apache.poi.ss.formula.eval.BoolEval;
import org.apache.poi.ss.formula.eval.EvaluationException;
import org.apache.poi.ss.formula.eval.OperandResolver;
import org.apache.poi.ss.formula.eval.ValueEval;
import org.apache.poi.ss.formula.functions.FreeRefFunction;
import org.apache.poi.ss.formula.udf.DefaultUDFFinder;
import org.apache.poi.ss.formula.udf.UDFFinder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class IsbnUdf {

    /** ISBN_VALID(text): TRUE when the text has 13 digits once hyphens are removed. */
    static final FreeRefFunction ISBN_VALID = (ValueEval[] args, OperationEvaluationContext ctx) -> {
        try {
            ValueEval single = OperandResolver.getSingleValue(args[0], ctx.getRowIndex(), ctx.getColumnIndex());
            String digits = OperandResolver.coerceValueToString(single).replace("-", "");
            return BoolEval.valueOf(digits.length() == 13 && digits.chars().allMatch(Character::isDigit));
        } catch (EvaluationException e) {
            return e.getErrorEval(); // propagate #VALUE!, #REF!, ...
        }
    };

    public static void main(String[] args) throws IOException {
        try (XSSFWorkbook workbook = new XSSFWorkbook()) {
            // Register the function BEFORE parsing formulas that use it and before creating the evaluator
            UDFFinder toolPack = new DefaultUDFFinder(
                    new String[] {"ISBN_VALID"}, new FreeRefFunction[] {ISBN_VALID});
            workbook.addToolPack(toolPack);

            Sheet sheet = workbook.createSheet("Inventory");
            sheet.createRow(0).createCell(0).setCellValue("978-3-16-148410-0");
            Cell check = sheet.getRow(0).createCell(1);
            check.setCellFormula("ISBN_VALID(A1)");

            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            CellValue result = evaluator.evaluate(check);
            System.out.println(result.getBooleanValue()); // true
        }
    }
}

import java.io.IOException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class EvaluatorCache {

    public static void main(String[] args) throws IOException {
        try (XSSFWorkbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("Inventory");
            Row row = sheet.createRow(0);
            Cell price = row.createCell(0);
            price.setCellValue(12.50);
            Cell stock = row.createCell(1);
            stock.setCellValue(4);
            Cell value = row.createCell(2);
            value.setCellFormula("A1*B1");

            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            System.out.println(evaluator.evaluate(value).getNumberValue()); // 50.0

            // The evaluator cached the intermediate results. Changing an input cell is not noticed...
            stock.setCellValue(10);
            System.out.println(evaluator.evaluate(value).getNumberValue()); // still 50.0 (stale)

            // ...unless the evaluator is told about the change
            evaluator.notifyUpdateCell(stock);
            System.out.println(evaluator.evaluate(value).getNumberValue()); // 125.0

            // Changing a formula: notifySetFormula; replacing many cells: clear the whole cache
            value.setCellFormula("A1*B1*2");
            evaluator.notifySetFormula(value);
            System.out.println(evaluator.evaluate(value).getNumberValue()); // 250.0
            evaluator.clearAllCachedResultValues();
        }
    }
}

import java.io.IOException;
import org.apache.poi.ss.formula.FormulaParser;
import org.apache.poi.ss.formula.FormulaRenderer;
import org.apache.poi.ss.formula.FormulaType;
import org.apache.poi.ss.formula.ptg.Ptg;
import org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class FormulaParts {

    public static void main(String[] args) throws IOException {
        try (XSSFWorkbook workbook = new XSSFWorkbook()) {
            workbook.createSheet("Inventory");
            XSSFEvaluationWorkbook evaluationWorkbook = XSSFEvaluationWorkbook.create(workbook);

            // Parse the text into the postfix (RPN) token list that the evaluator walks
            Ptg[] tokens = FormulaParser.parse(
                    "SUM(B2:B4)*2", evaluationWorkbook, FormulaType.CELL, 0);
            for (Ptg token : tokens) {
                System.out.println(token.getClass().getSimpleName());
            }
            // AreaPtg, AttrPtg (the optimised one-argument SUM), IntPtg, MultiplyPtg

            // and back to text
            System.out.println(FormulaRenderer.toFormulaString(evaluationWorkbook, tokens)); // SUM(B2:B4)*2
        }
    }
}

import java.io.IOException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class SafeText {

    /** Prefixes values that a spreadsheet application could interpret as a formula (needed for CSV export). */
    static String neutralise(String text) {
        if (text != null && !text.isEmpty() && "=+-@\t\r".indexOf(text.charAt(0)) >= 0) {
            return "'" + text;
        }
        return text;
    }

    public static void main(String[] args) throws IOException {
        String titleFromUser = "=HYPERLINK(\"http://example.org\",\"Dune\")";
        try (XSSFWorkbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("Inventory");
            Row row = sheet.createRow(0);

            // setCellValue(String) always stores TEXT, never a formula
            Cell text = row.createCell(0);
            text.setCellValue(neutralise(titleFromUser));
            System.out.println(text.getCellType() == CellType.STRING); // true

            // Never do this with user input: setCellFormula(userInput) creates a live formula
            // row.createCell(1).setCellFormula(titleFromUser.substring(1));
        }
    }
}