You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by fa...@apache.org on 2026/09/20 13:54:18 UTC
(poi) branch trunk updated: XSSF: cache parsed formula tokens per evaluator; one parse per shared formula group (#1318)
This is an automated email from the ASF dual-hosted git repository.
pjfanning pushed a commit to branch trunk
in repository https://gitbox.apache.org/repos/asf/poi.git
The following commit(s) were added to refs/heads/trunk by this push:
new 76a9f3ea08 XSSF: cache parsed formula tokens per evaluator; one parse per shared formula group (#1318)
76a9f3ea08 is described below
commit 76a9f3ea08b55a8d6596a7dda97f86fee3aac0ff
Author: PJ Fanning <pj...@users.noreply.github.com>
AuthorDate: Sun Sep 20 14:54:13 2026 +0100
XSSF: cache parsed formula tokens per evaluator; one parse per shared formula group (#1318)
* XSSF: cache parsed formula tokens per evaluator; derive shared formula cells from one parse of the master
XSSFEvaluationWorkbook.getFormulaTokens parsed the formula text on every
call, so a formula was re-parsed each time the evaluator recomputed it after
a notifyUpdateCell. XSSFEvaluationSheet now keeps the parsed tokens per cell
for the life of the evaluator; clearAllCachedResultValues drops them all and
the new EvaluationSheet.notifyUpdateCell hook (propagated from
WorkbookEvaluator.notifyUpdateCell, like notifyDeleteCell) drops the entry
of a changed cell.
A cell of a shared formula group used to cost parse master, shift, render,
parse again (XSSFCell.getCellFormula produces the text). The sheet now parses
the master once per group, keyed by the master CTCellFormula object it holds,
and hands each cell the tokens shifted to its position. Formulas with
structured table references keep the old path, as their parse depends on
the row.
New HSSF/XSSFFormulaReevaluationBenchmark: one evaluator kept, one input
changed and notified, every formula read again.
Co-Authored-By: Claude Opus 5 <no...@anthropic.com>
* TestXSSFFormulaTokenCache: exercise the new-master path via removal, independent of setCellFormula semantics
Co-Authored-By: Claude Opus 5 <no...@anthropic.com>
* BaseXSSFFormulaEvaluator: skip the external-workbook scan when the workbook has no external links
cacheExternalWorkbookCells re-parsed every evaluated formula, after every
evaluate call, to look for references into other workbooks - even when the
workbook has none. With the token cache the second parse is a lookup; with
no external links tables there is nothing to look for at all.
Co-Authored-By: Claude Opus 5 <no...@anthropic.com>
---------
Co-authored-by: Claude Opus 5 <no...@anthropic.com>
---
.../hssf/HSSFFormulaReevaluationBenchmark.java | 99 +++++++++++++++
.../xssf/XSSFFormulaReevaluationBenchmark.java | 99 +++++++++++++++
.../xssf/usermodel/BaseXSSFFormulaEvaluator.java | 5 +-
.../poi/xssf/usermodel/XSSFEvaluationSheet.java | 102 +++++++++++++++
.../poi/xssf/usermodel/XSSFEvaluationWorkbook.java | 12 +-
.../xssf/usermodel/TestXSSFFormulaTokenCache.java | 137 +++++++++++++++++++++
.../org/apache/poi/ss/formula/EvaluationSheet.java | 13 ++
.../apache/poi/ss/formula/WorkbookEvaluator.java | 3 +
8 files changed, 465 insertions(+), 5 deletions(-)
diff --git a/poi-benchmark/src/jmh/java/org/apache/poi/benchmark/hssf/HSSFFormulaReevaluationBenchmark.java b/poi-benchmark/src/jmh/java/org/apache/poi/benchmark/hssf/HSSFFormulaReevaluationBenchmark.java
new file mode 100644
index 0000000000..76a341f504
--- /dev/null
+++ b/poi-benchmark/src/jmh/java/org/apache/poi/benchmark/hssf/HSSFFormulaReevaluationBenchmark.java
@@ -0,0 +1,99 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
+package org.apache.poi.benchmark.hssf;
+
+import java.io.IOException;
+import java.util.List;
+import java.util.concurrent.TimeUnit;
+
+import org.apache.poi.benchmark.ss.InvoiceWorkbookBuilder;
+import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.Sheet;
+import org.openjdk.jmh.annotations.Benchmark;
+import org.openjdk.jmh.annotations.BenchmarkMode;
+import org.openjdk.jmh.annotations.Fork;
+import org.openjdk.jmh.annotations.Level;
+import org.openjdk.jmh.annotations.Measurement;
+import org.openjdk.jmh.annotations.Mode;
+import org.openjdk.jmh.annotations.OutputTimeUnit;
+import org.openjdk.jmh.annotations.Param;
+import org.openjdk.jmh.annotations.Scope;
+import org.openjdk.jmh.annotations.Setup;
+import org.openjdk.jmh.annotations.State;
+import org.openjdk.jmh.annotations.TearDown;
+import org.openjdk.jmh.annotations.Threads;
+import org.openjdk.jmh.annotations.Warmup;
+import org.openjdk.jmh.infra.Blackhole;
+
+/**
+ * The what-if loop: one evaluator is kept for the life of the workbook, an input cell changes,
+ * the evaluator is told ({@code notifyUpdateCell}) and every formula is read again. Only the
+ * formulas that depend on the changed cell are actually recomputed — the row's own chain, the
+ * running total from that row down and the summary block — the rest come out of the result
+ * cache. This is what {@link HSSFFormulaEvaluationBenchmark} does not measure: the cost of a
+ * re-evaluation, including whatever the evaluator has to re-derive for a formula it has already
+ * seen.
+ * <p>
+ * The workbook is the one from {@link InvoiceWorkbookBuilder}; each invocation bumps the quantity
+ * of the next data row. Single thread, as the evaluator is not thread-safe.
+ */
+@BenchmarkMode(Mode.AverageTime)
+@OutputTimeUnit(TimeUnit.MILLISECONDS)
+@Warmup(iterations = 3, time = 2)
+@Measurement(iterations = 5, time = 2)
+@Fork(1)
+@Threads(1)
+@State(Scope.Thread)
+public class HSSFFormulaReevaluationBenchmark {
+
+ @Param({"100", "200"})
+ public int rows;
+
+ private HSSFWorkbook wb;
+ private HSSFFormulaEvaluator fe;
+ private List<Cell> formulaCells;
+ private Sheet orders;
+ private int nextRow;
+
+ @Setup(Level.Trial)
+ public void setUp() {
+ wb = new InvoiceWorkbookBuilder(rows, 42L).build(new HSSFWorkbook());
+ formulaCells = InvoiceWorkbookBuilder.formulaCells(wb);
+ orders = wb.getSheet("Orders");
+ fe = HSSFFormulaEvaluator.create(wb, null, null);
+ fe.evaluateAll();
+ }
+
+ @TearDown(Level.Trial)
+ public void tearDown() throws IOException {
+ wb.close();
+ }
+
+ @Benchmark
+ public void changeOneInputAndReevaluate(Blackhole bh) {
+ Cell qty = orders.getRow(1 + nextRow).getCell(0);
+ nextRow = (nextRow + 1) % rows;
+ qty.setCellValue(qty.getNumericCellValue() + 1);
+ fe.notifyUpdateCell(qty);
+ for (Cell cell : formulaCells) {
+ bh.consume(fe.evaluate(cell));
+ }
+ }
+}
diff --git a/poi-ooxml-benchmark/src/jmh/java/org/apache/poi/benchmark/xssf/XSSFFormulaReevaluationBenchmark.java b/poi-ooxml-benchmark/src/jmh/java/org/apache/poi/benchmark/xssf/XSSFFormulaReevaluationBenchmark.java
new file mode 100644
index 0000000000..a7ba0a6ef1
--- /dev/null
+++ b/poi-ooxml-benchmark/src/jmh/java/org/apache/poi/benchmark/xssf/XSSFFormulaReevaluationBenchmark.java
@@ -0,0 +1,99 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
+package org.apache.poi.benchmark.xssf;
+
+import java.io.IOException;
+import java.util.List;
+import java.util.concurrent.TimeUnit;
+
+import org.apache.poi.benchmark.ss.InvoiceWorkbookBuilder;
+import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
+import org.apache.poi.xssf.usermodel.XSSFWorkbook;
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.Sheet;
+import org.openjdk.jmh.annotations.Benchmark;
+import org.openjdk.jmh.annotations.BenchmarkMode;
+import org.openjdk.jmh.annotations.Fork;
+import org.openjdk.jmh.annotations.Level;
+import org.openjdk.jmh.annotations.Measurement;
+import org.openjdk.jmh.annotations.Mode;
+import org.openjdk.jmh.annotations.OutputTimeUnit;
+import org.openjdk.jmh.annotations.Param;
+import org.openjdk.jmh.annotations.Scope;
+import org.openjdk.jmh.annotations.Setup;
+import org.openjdk.jmh.annotations.State;
+import org.openjdk.jmh.annotations.TearDown;
+import org.openjdk.jmh.annotations.Threads;
+import org.openjdk.jmh.annotations.Warmup;
+import org.openjdk.jmh.infra.Blackhole;
+
+/**
+ * The what-if loop: one evaluator is kept for the life of the workbook, an input cell changes,
+ * the evaluator is told ({@code notifyUpdateCell}) and every formula is read again. Only the
+ * formulas that depend on the changed cell are actually recomputed — the row's own chain, the
+ * running total from that row down and the summary block — the rest come out of the result
+ * cache. This is what {@link XSSFFormulaEvaluationBenchmark} does not measure: the cost of a
+ * re-evaluation, including whatever the evaluator has to re-derive for a formula it has already
+ * seen.
+ * <p>
+ * The workbook is the one from {@link InvoiceWorkbookBuilder}; each invocation bumps the quantity
+ * of the next data row. Single thread, as the evaluator is not thread-safe.
+ */
+@BenchmarkMode(Mode.AverageTime)
+@OutputTimeUnit(TimeUnit.MILLISECONDS)
+@Warmup(iterations = 6, time = 3)
+@Measurement(iterations = 5, time = 3)
+@Fork(1)
+@Threads(1)
+@State(Scope.Thread)
+public class XSSFFormulaReevaluationBenchmark {
+
+ @Param({"100", "200"})
+ public int rows;
+
+ private XSSFWorkbook wb;
+ private XSSFFormulaEvaluator fe;
+ private List<Cell> formulaCells;
+ private Sheet orders;
+ private int nextRow;
+
+ @Setup(Level.Trial)
+ public void setUp() {
+ wb = new InvoiceWorkbookBuilder(rows, 42L).build(new XSSFWorkbook());
+ formulaCells = InvoiceWorkbookBuilder.formulaCells(wb);
+ orders = wb.getSheet("Orders");
+ fe = XSSFFormulaEvaluator.create(wb, null, null);
+ fe.evaluateAll();
+ }
+
+ @TearDown(Level.Trial)
+ public void tearDown() throws IOException {
+ wb.close();
+ }
+
+ @Benchmark
+ public void changeOneInputAndReevaluate(Blackhole bh) {
+ Cell qty = orders.getRow(1 + nextRow).getCell(0);
+ nextRow = (nextRow + 1) % rows;
+ qty.setCellValue(qty.getNumericCellValue() + 1);
+ fe.notifyUpdateCell(qty);
+ for (Cell cell : formulaCells) {
+ bh.consume(fe.evaluate(cell));
+ }
+ }
+}
diff --git a/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/BaseXSSFFormulaEvaluator.java b/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/BaseXSSFFormulaEvaluator.java
index c7589bfc54..e65d5e72ff 100644
--- a/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/BaseXSSFFormulaEvaluator.java
+++ b/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/BaseXSSFFormulaEvaluator.java
@@ -102,7 +102,10 @@ public abstract class BaseXSSFFormulaEvaluator extends BaseFormulaEvaluator {
* @param evalCell sourceCell
*/
private void cacheExternalWorkbookCells(XSSFEvaluationCell evalCell) {
- //
+ if (evalCell.getXSSFCell().getSheet().getWorkbook().getExternalLinksTables().isEmpty()) {
+ // nothing in this workbook can refer to another one
+ return;
+ }
Ptg[] formulaTokens = getEvaluationWorkbook().getFormulaTokens(evalCell);
for (Ptg ptg : formulaTokens) {
if (ptg instanceof Area3DPxg area3DPxg) {
diff --git a/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFEvaluationSheet.java b/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFEvaluationSheet.java
index ca0b352016..725fe6ce30 100644
--- a/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFEvaluationSheet.java
+++ b/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFEvaluationSheet.java
@@ -18,13 +18,22 @@
package org.apache.poi.xssf.usermodel;
import java.util.HashMap;
+import java.util.IdentityHashMap;
import java.util.Map;
+import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.formula.EvaluationCell;
import org.apache.poi.ss.formula.EvaluationSheet;
+import org.apache.poi.ss.formula.FormulaParser;
+import org.apache.poi.ss.formula.FormulaType;
+import org.apache.poi.ss.formula.SharedFormula;
+import org.apache.poi.ss.formula.ptg.Ptg;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
+import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.Internal;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellFormulaType;
/**
* XSSF wrapper for a sheet under evaluation
@@ -34,6 +43,13 @@ final class XSSFEvaluationSheet implements EvaluationSheet {
private final XSSFSheet _xs;
private Map<CellKey, EvaluationCell> _cellCache;
+ /** parsed formula tokens by cell; a cell is parsed once per evaluator unless it is notified */
+ private Map<XSSFCell, Ptg[]> _formulaTokens;
+ /**
+ * parsed tokens of shared formula masters, keyed by the master {@link CTCellFormula} the sheet
+ * currently holds for the group (a new master object is installed when the group changes)
+ */
+ private Map<CTCellFormula, Ptg[]> _sharedFormulaTokens;
public XSSFEvaluationSheet(XSSFSheet sheet) {
_xs = sheet;
@@ -69,15 +85,101 @@ final class XSSFEvaluationSheet implements EvaluationSheet {
@Override
public void clearAllCachedResultValues() {
_cellCache = null;
+ _formulaTokens = null;
+ _sharedFormulaTokens = null;
}
@Override
public void notifyDeleteCell(int rowIndex, int columnIndex) {
+ forgetFormulaTokens(rowIndex, columnIndex);
if (_cellCache != null) {
_cellCache.remove(new CellKey(rowIndex, columnIndex));
}
}
+ /**
+ * @since 6.0.0
+ */
+ @Override
+ public void notifyUpdateCell(int rowIndex, int columnIndex) {
+ forgetFormulaTokens(rowIndex, columnIndex);
+ }
+
+ private void forgetFormulaTokens(int rowIndex, int columnIndex) {
+ if (_formulaTokens == null) {
+ return;
+ }
+ // prefer the wrapper: it still knows the cell after it was removed from the sheet
+ EvaluationCell wrapper = _cellCache == null ? null : _cellCache.get(new CellKey(rowIndex, columnIndex));
+ XSSFCell cell;
+ if (wrapper != null) {
+ cell = ((XSSFEvaluationCell) wrapper).getXSSFCell();
+ } else {
+ XSSFRow row = _xs.getRow(rowIndex);
+ cell = row == null ? null : row.getCell(columnIndex);
+ }
+ if (cell != null) {
+ _formulaTokens.remove(cell);
+ }
+ }
+
+ /**
+ * @return the parsed formula of the cell, from the cache when it was parsed before
+ * @since 6.0.0
+ */
+ /* package */ Ptg[] getFormulaTokens(XSSFCell cell, BaseXSSFEvaluationWorkbook fpb, int sheetIndex) {
+ if (_formulaTokens == null) {
+ _formulaTokens = new IdentityHashMap<>();
+ }
+ Ptg[] ptgs = _formulaTokens.get(cell);
+ if (ptgs == null) {
+ ptgs = parseFormulaTokens(cell, fpb, sheetIndex);
+ _formulaTokens.put(cell, ptgs);
+ }
+ return ptgs;
+ }
+
+ private Ptg[] parseFormulaTokens(XSSFCell cell, BaseXSSFEvaluationWorkbook fpb, int sheetIndex) {
+ CTCellFormula f = cell.getCTCell().getF();
+ if (f != null && f.getT() == STCellFormulaType.SHARED && !cell.isPartOfArrayFormulaGroup()) {
+ Ptg[] shared = sharedFormulaTokens(cell, Math.toIntExact(f.getSi()), fpb, sheetIndex);
+ if (shared != null) {
+ return shared;
+ }
+ }
+ return FormulaParser.parse(cell.getCellFormula(fpb), fpb, FormulaType.CELL, sheetIndex, cell.getRowIndex());
+ }
+
+ /**
+ * A cell of a shared formula group takes the master's tokens, shifted to its own position.
+ * Parsing the master once per group replaces the per-cell parse, shift, render and re-parse
+ * that {@link XSSFCell#getCellFormula()} has to do to produce the formula text.
+ *
+ * @return {@code null} when the group cannot be handled this way (no master, or a formula
+ * whose parse depends on the row, i.e. one with a structured table reference)
+ */
+ private Ptg[] sharedFormulaTokens(XSSFCell cell, int si, BaseXSSFEvaluationWorkbook fpb, int sheetIndex) {
+ CTCellFormula master = _xs.getSharedFormula(si);
+ if (master == null) {
+ return null;
+ }
+ String formula = master.getStringValue();
+ if (formula == null || formula.indexOf('[') >= 0) {
+ return null;
+ }
+ if (_sharedFormulaTokens == null) {
+ _sharedFormulaTokens = new IdentityHashMap<>();
+ }
+ Ptg[] masterPtgs = _sharedFormulaTokens.get(master);
+ if (masterPtgs == null) {
+ masterPtgs = FormulaParser.parse(formula, fpb, FormulaType.CELL, sheetIndex, cell.getRowIndex());
+ _sharedFormulaTokens.put(master, masterPtgs);
+ }
+ CellRangeAddress ref = CellRangeAddress.valueOf(master.getRef());
+ return new SharedFormula(SpreadsheetVersion.EXCEL2007).convertSharedFormulas(masterPtgs,
+ cell.getRowIndex() - ref.getFirstRow(), cell.getColumnIndex() - ref.getFirstColumn());
+ }
+
@Override
public EvaluationCell getCell(int rowIndex, int columnIndex) {
// shortcut evaluation if reference is outside the bounds of existing data
diff --git a/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java b/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java
index b78bf1f74d..d722204f91 100644
--- a/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java
+++ b/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java
@@ -22,8 +22,6 @@ import java.util.Map;
import org.apache.poi.ss.formula.EvaluationCell;
import org.apache.poi.ss.formula.EvaluationSheet;
-import org.apache.poi.ss.formula.FormulaParser;
-import org.apache.poi.ss.formula.FormulaType;
import org.apache.poi.ss.formula.ptg.Ptg;
import org.apache.poi.util.Internal;
@@ -77,11 +75,17 @@ public final class XSSFEvaluationWorkbook extends BaseXSSFEvaluationWorkbook {
return _sheetCache.computeIfAbsent(sheet, rows -> new XSSFEvaluationSheet(sheet));
}
+ /**
+ * The parsed tokens are cached by the {@link XSSFEvaluationSheet} the cell belongs to, so a
+ * formula is parsed once per evaluator rather than every time it is (re-)evaluated; the cache
+ * is kept in step by {@link #clearAllCachedResultValues()} and the sheet's
+ * {@code notifyUpdateCell}/{@code notifyDeleteCell}.
+ */
@Override
public Ptg[] getFormulaTokens(EvaluationCell evalCell) {
final XSSFCell cell = ((XSSFEvaluationCell)evalCell).getXSSFCell();
final int sheetIndex = _uBook.getSheetIndex(cell.getSheet());
- return FormulaParser.parse(cell.getCellFormula(this), this,
- FormulaType.CELL, sheetIndex, cell.getRowIndex());
+ final XSSFEvaluationSheet sheet = (XSSFEvaluationSheet) getSheet(sheetIndex);
+ return sheet.getFormulaTokens(cell, this, sheetIndex);
}
}
diff --git a/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFFormulaTokenCache.java b/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFFormulaTokenCache.java
new file mode 100644
index 0000000000..defd07f27c
--- /dev/null
+++ b/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFFormulaTokenCache.java
@@ -0,0 +1,137 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
+package org.apache.poi.xssf.usermodel;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertNotSame;
+import static org.junit.jupiter.api.Assertions.assertSame;
+
+import org.apache.poi.ss.formula.EvaluationCell;
+import org.apache.poi.ss.formula.EvaluationSheet;
+import org.apache.poi.ss.formula.FormulaRenderer;
+import org.apache.poi.ss.formula.ptg.Ptg;
+import org.apache.poi.ss.usermodel.BaseTestFormulaEvaluatorFixture;
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.FormulaEvaluator;
+import org.apache.poi.ss.usermodel.Workbook;
+import org.apache.poi.xssf.XSSFITestDataProvider;
+import org.junit.jupiter.api.Test;
+
+/**
+ * {@link XSSFEvaluationSheet} keeps the parsed tokens of every formula it has evaluated, so an
+ * evaluator parses a formula once and not on every re-evaluation, and derives the cells of a
+ * shared formula group from one parse of the master. Runs against the fixture workbook with
+ * {@code D2:D4} and {@code A6:C6} stored as shared formulas.
+ */
+final class TestXSSFFormulaTokenCache extends BaseTestFormulaEvaluatorFixture {
+
+ TestXSSFFormulaTokenCache() {
+ super(XSSFITestDataProvider.instance);
+ }
+
+ @Override
+ protected Workbook finishWorkbook(Workbook built) {
+ return XSSFSharedFormulaFixture.shareFillDownFormulasAndReload(built);
+ }
+
+ private XSSFEvaluationWorkbook evaluationWorkbook() {
+ return XSSFEvaluationWorkbook.create((XSSFWorkbook) wb);
+ }
+
+ private static EvaluationCell evalCell(XSSFEvaluationWorkbook ew, Workbook wb, Cell cell) {
+ EvaluationSheet sheet = ew.getSheet(wb.getSheetIndex(cell.getSheet()));
+ return sheet.getCell(cell.getRowIndex(), cell.getColumnIndex());
+ }
+
+ @Test
+ void tokensAreParsedOnceAndKeptUntilTheCellIsNotified() {
+ XSSFEvaluationWorkbook ew = evaluationWorkbook();
+ Cell a7 = cell("A7");
+ EvaluationCell ec = evalCell(ew, wb, a7);
+ Ptg[] first = ew.getFormulaTokens(ec);
+ assertSame(first, ew.getFormulaTokens(ec), "the same parse is handed out again");
+ assertEquals("A6*2", FormulaRenderer.toFormulaString(ew, first));
+
+ // a change the evaluator is not told about is not seen ...
+ a7.setCellFormula("A6*3");
+ assertSame(first, ew.getFormulaTokens(ec));
+
+ // ... a notified one is
+ ec.getSheet().notifyUpdateCell(a7.getRowIndex(), a7.getColumnIndex());
+ Ptg[] second = ew.getFormulaTokens(ec);
+ assertNotSame(first, second);
+ assertEquals("A6*3", FormulaRenderer.toFormulaString(ew, second));
+ assertSame(second, ew.getFormulaTokens(ec));
+
+ // and everything goes with the cache
+ ew.clearAllCachedResultValues();
+ assertNotSame(second, ew.getFormulaTokens(evalCell(ew, wb, a7)));
+ }
+
+ @Test
+ void notifiedThroughTheEvaluatorTheNewFormulaIsUsed() {
+ // the whole chain: FormulaEvaluator -> WorkbookEvaluator -> EvaluationSheet.notifyUpdateCell
+ assertEquals(120, num("A7"), DELTA);
+ cell("A7").setCellFormula("A6*3");
+ fe.notifySetFormula(cell("A7"));
+ assertEquals(180, num("A7"), DELTA);
+ assertEquals(201, num("B7"), DELTA);
+
+ // an input change re-evaluates the formula from its cached tokens
+ cell("A2").setCellValue(40);
+ fe.notifyUpdateCell(cell("A2"));
+ assertEquals(270, num("A7"), DELTA);
+ }
+
+ @Test
+ void sharedFormulaCellsGetTheMasterTokensShiftedToTheirPosition() {
+ XSSFEvaluationWorkbook ew = evaluationWorkbook();
+ XSSFSharedFormulaFixture.assertSharedFormulas(wb);
+ for (String ref : new String[]{"D2", "D3", "D4", "A6", "C6"}) {
+ Cell cell = cell(ref);
+ Ptg[] tokens = ew.getFormulaTokens(evalCell(ew, wb, cell));
+ // the tokens derived from the master render to the formula the cell reports
+ assertEquals(cell.getCellFormula(), FormulaRenderer.toFormulaString(ew, tokens), ref);
+ }
+ assertEquals("VLOOKUP(B3,Prices!$A$2:$B$4,2,FALSE)", cell("D3").getCellFormula());
+ assertEquals("SUM(C2:C4)", cell("C6").getCellFormula());
+ }
+
+ @Test
+ void aGroupWithANewMasterIsParsedAgain() {
+ // clearing the master D2 makes POI promote D3 to master of D3:D4 (a new master object for
+ // the group); cells parsed from then on are derived from it
+ FormulaEvaluator fresh = wb.getCreationHelper().createFormulaEvaluator();
+ cell("D2").setBlank();
+ fe.notifyUpdateCell(cell("D2"));
+ assertEquals("D3:D4", ((XSSFCell) cell("D3")).getCTCell().getF().getRef());
+ assertEquals("VLOOKUP(B4,Prices!$A$2:$B$4,2,FALSE)", cell("D4").getCellFormula());
+
+ // the notified cell is blank now, the others keep their cached results
+ assertEquals(2.5, num("D3"), DELTA);
+ assertEquals(3.5, num("D4"), DELTA);
+ assertEquals(2.5 * 20 + 3.5 * 30, num("D6"), DELTA);
+
+ // an evaluator that parses the group afresh takes the tokens from the new master
+ assertEquals(2.5, fresh.evaluate(cell("D3")).getNumberValue(), DELTA);
+ assertEquals(3.5, fresh.evaluate(cell("D4")).getNumberValue(), DELTA);
+ XSSFEvaluationWorkbook ew = evaluationWorkbook();
+ assertEquals("VLOOKUP(B4,Prices!$A$2:$B$4,2,FALSE)",
+ FormulaRenderer.toFormulaString(ew, ew.getFormulaTokens(evalCell(ew, wb, cell("D4")))));
+ }
+}
diff --git a/poi/src/main/java/org/apache/poi/ss/formula/EvaluationSheet.java b/poi/src/main/java/org/apache/poi/ss/formula/EvaluationSheet.java
index 3830343932..3d7f5a8f64 100644
--- a/poi/src/main/java/org/apache/poi/ss/formula/EvaluationSheet.java
+++ b/poi/src/main/java/org/apache/poi/ss/formula/EvaluationSheet.java
@@ -64,4 +64,17 @@ public interface EvaluationSheet {
default void notifyDeleteCell(int rowIndex, int columnIndex) {
// nothing cached by default
}
+
+ /**
+ * Propagated from {@link WorkbookEvaluator#notifyUpdateCell(EvaluationCell)} so that an
+ * implementation which caches anything derived from a cell (such as its parsed formula
+ * tokens) can drop what it holds for the changed cell. The default implementation does nothing.
+ *
+ * @param rowIndex zero-based row of the changed cell
+ * @param columnIndex zero-based column of the changed cell
+ * @since 6.0.0
+ */
+ default void notifyUpdateCell(int rowIndex, int columnIndex) {
+ // nothing cached by default
+ }
}
diff --git a/poi/src/main/java/org/apache/poi/ss/formula/WorkbookEvaluator.java b/poi/src/main/java/org/apache/poi/ss/formula/WorkbookEvaluator.java
index 608b8b9df3..96363d23d8 100644
--- a/poi/src/main/java/org/apache/poi/ss/formula/WorkbookEvaluator.java
+++ b/poi/src/main/java/org/apache/poi/ss/formula/WorkbookEvaluator.java
@@ -184,6 +184,9 @@ public final class WorkbookEvaluator {
public void notifyUpdateCell(EvaluationCell cell) {
int sheetIndex = getSheetIndex(cell.getSheet());
_cache.notifyUpdateCell(_workbookIx, sheetIndex, cell);
+ // cell.getSheet() may be a throw-away wrapper created just for this call, so tell the
+ // sheet instance the evaluator actually reads from (it may cache the parsed formula)
+ _workbook.getSheet(sheetIndex).notifyUpdateCell(cell.getRowIndex(), cell.getColumnIndex());
}
/**
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org