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 3a96efcacd DataFormatter: default useCachedValuesForFormulaCells to
true (#1259)
3a96efcacd is described below
commit 3a96efcacdfec7c10b8122739c782850c5612575
Author: PJ Fanning <[email protected]>
AuthorDate: Tue Sep 8 13:39:25 2026 +0100
DataFormatter: default useCachedValuesForFormulaCells to true (#1259)
When no FormulaEvaluator is supplied, DataFormatter now returns the cached
value of a formula cell (when one is available) instead of the formula text.
This was already opt-in via setUseCachedValuesForFormulaCells(true); POI
6.0.0
makes it the default. Set the flag to false to restore the POI 5.x
behaviour.
Co-authored-by: Claude Opus 5 (1M context) <[email protected]>
---
.../org/apache/poi/xssf/usermodel/TestXSSFDataFormat.java | 3 +++
.../java/org/apache/poi/ss/usermodel/DataFormatter.java | 14 ++++++++++----
.../apache/poi/hssf/usermodel/TestHSSFDataFormatter.java | 12 +++++++++---
.../org/apache/poi/ss/usermodel/TestDataFormatter.java | 8 ++++++--
src/documentation/content/xdocs/changes.xml | 2 ++
5 files changed, 30 insertions(+), 9 deletions(-)
diff --git
a/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFDataFormat.java
b/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFDataFormat.java
index 5c290eb85b..835de2cc4a 100644
---
a/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFDataFormat.java
+++
b/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFDataFormat.java
@@ -117,6 +117,9 @@ public final class TestXSSFDataFormat extends
BaseTestDataFormat {
XSSFSheet sheet = wb.getSheetAt(0);
XSSFRow row = sheet.getRow(0);
XSSFCell d1 = row.getCell(3);
+ // the cached value is used by default since POI 6.0.0
+ assertEquals("6.75", formatter.formatCellValue(d1));
+ formatter.setUseCachedValuesForFormulaCells(false);
assertEquals("SUM(A1:C1)", formatter.formatCellValue(d1));
formatter.setUseCachedValuesForFormulaCells(true);
assertEquals("6.75", formatter.formatCellValue(d1));
diff --git a/poi/src/main/java/org/apache/poi/ss/usermodel/DataFormatter.java
b/poi/src/main/java/org/apache/poi/ss/usermodel/DataFormatter.java
index 0fe6345f46..10c6042a77 100644
--- a/poi/src/main/java/org/apache/poi/ss/usermodel/DataFormatter.java
+++ b/poi/src/main/java/org/apache/poi/ss/usermodel/DataFormatter.java
@@ -212,9 +212,10 @@ public class DataFormatter {
private boolean use4DigitYearsInAllDateFormats = false;
/**
- * if set to true, avoid recalculating the values if there is a cached
value available (default is false)
+ * if set to true, avoid recalculating the values if there is a cached
value available
+ * (default is true since POI 6.0.0, was false in earlier releases)
*/
- private boolean useCachedValuesForFormulaCells = false;
+ private boolean useCachedValuesForFormulaCells = true;
/** stores the locale set by updateLocale method */
private Locale locale;
@@ -297,7 +298,9 @@ public class DataFormatter {
* @param useCachedValuesForFormulaCells if set to true, when you do not
provide a {@link FormulaEvaluator},
* for cells with formulas, we will
return the cached value for the cell (if available),
* otherwise - we return the formula
itself.
- * The default is false and this
means we return the formula itself.
+ * Since POI 6.0.0, the default is
true, so the cached value is used when it is
+ * available. In POI 5.x, the
default was false and the formula itself was returned.
+ * Set this to false to restore the
POI 5.x behaviour.
* @since 5.2.0
*/
public void setUseCachedValuesForFormulaCells(boolean
useCachedValuesForFormulaCells) {
@@ -308,7 +311,8 @@ public class DataFormatter {
* @return useCachedValuesForFormulaCells if set to true, when you do not
provide a {@link FormulaEvaluator},
* for cells with formulas, we will
return the cached value for the cell (if available),
* otherwise - we return the
formula itself.
- * The default is false and this
means we return the formula itself.
+ * Since POI 6.0.0, the default is
true, so the cached value is used when it is
+ * available. In POI 5.x, the
default was false and the formula itself was returned.
* @since 5.2.0
*/
public boolean useCachedValuesForFormulaCells() {
@@ -1058,6 +1062,8 @@ public class DataFormatter {
* <p>When passed a null or blank cell, this method will return an empty
* String (""). Formulas in formula type cells will not be evaluated.
* {@link #setUseCachedValuesForFormulaCells} controls how these cells are
evaluated.
+ * Since POI 6.0.0, that setting defaults to true, so the cached value of
a formula cell is
+ * returned when one is available (POI 5.x returned the formula itself by
default).
* </p>
*
* @param cell The cell
diff --git
a/poi/src/test/java/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java
b/poi/src/test/java/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java
index 642e7d3e7d..0430ee9ca7 100644
--- a/poi/src/test/java/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java
+++ b/poi/src/test/java/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java
@@ -341,14 +341,20 @@ public final class TestHSSFDataFormatter {
HSSFCell cell = row.getCell(0);
log("\n==== FORMULA CELL ====");
- // first without a formula evaluator
+ char decimalSeparator =
DecimalFormatSymbols.getInstance(LocaleUtil.getUserLocale()).getDecimalSeparator();
+
+ // first without a formula evaluator - since POI 6.0.0, the cached
value is used by default
log(formatter.formatCellValue(cell) + "\t (without evaluator)");
- assertEquals("SUM(12.25,12.25)/100", formatter.formatCellValue(cell));
+ assertEquals(decimalSeparator + "00%",
formatter.formatCellValue(cell));
+
+ // the formula itself is returned if the pre-POI 6.0.0 behaviour is
restored
+ HSSFDataFormatter formulaFormatter = new HSSFDataFormatter();
+ formulaFormatter.setUseCachedValuesForFormulaCells(false);
+ assertEquals("SUM(12.25,12.25)/100",
formulaFormatter.formatCellValue(cell));
// now with a formula evaluator
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
log(formatter.formatCellValue(cell, evaluator) + "\t\t\t (with
evaluator)");
- char decimalSeparator =
DecimalFormatSymbols.getInstance(LocaleUtil.getUserLocale()).getDecimalSeparator();
assertEquals("24" + decimalSeparator + "50%",
formatter.formatCellValue(cell,evaluator));
}
diff --git
a/poi/src/test/java/org/apache/poi/ss/usermodel/TestDataFormatter.java
b/poi/src/test/java/org/apache/poi/ss/usermodel/TestDataFormatter.java
index 32e01e8cc9..e56532bbf3 100644
--- a/poi/src/test/java/org/apache/poi/ss/usermodel/TestDataFormatter.java
+++ b/poi/src/test/java/org/apache/poi/ss/usermodel/TestDataFormatter.java
@@ -106,11 +106,12 @@ class TestDataFormatter {
@Test
void useCachedValuesForFormulaCells() {
DataFormatter dataFormatter = new DataFormatter();
- assertFalse(dataFormatter.useCachedValuesForFormulaCells());
- dataFormatter.setUseCachedValuesForFormulaCells(true);
+ // the default changed to true in POI 6.0.0
assertTrue(dataFormatter.useCachedValuesForFormulaCells());
dataFormatter.setUseCachedValuesForFormulaCells(false);
assertFalse(dataFormatter.useCachedValuesForFormulaCells());
+ dataFormatter.setUseCachedValuesForFormulaCells(true);
+ assertTrue(dataFormatter.useCachedValuesForFormulaCells());
}
@Test
@@ -874,6 +875,9 @@ class TestDataFormatter {
assertEquals(formula, cell.getCellFormula());
DataFormatter formatter = new DataFormatter();
+ // the cached value is used by default since POI 6.0.0
+ assertEquals("5.6789", formatter.formatCellValue(cell));
+ formatter.setUseCachedValuesForFormulaCells(false);
assertEquals(formula, formatter.formatCellValue(cell));
formatter.setUseCachedValuesForFormulaCells(true);
assertEquals("5.6789", formatter.formatCellValue(cell));
diff --git a/src/documentation/content/xdocs/changes.xml
b/src/documentation/content/xdocs/changes.xml
index 6625821aed..f7cf0052bf 100644
--- a/src/documentation/content/xdocs/changes.xml
+++ b/src/documentation/content/xdocs/changes.xml
@@ -73,6 +73,7 @@
<release version="6.0.0" date="TBD">
<summary>
<summary-item>Minimum Java version is now JDK 17 (was JDK 8 in POI
5.x)</summary-item>
+ <summary-item>DataFormatter now uses cached values for formula
cells by default (see setUseCachedValuesForFormulaCells)</summary-item>
<summary-item>Upgrade bouncycastle dependency to
1.85</summary-item>
<summary-item>Upgrade commons-codec dependency to
1.22.1</summary-item>
<summary-item>Upgrade commons-compress dependency to
1.28.0</summary-item>
@@ -175,6 +176,7 @@
<action type="add" fixes-bug="github-1174" context="XSLF">Support
theme colors as highlight color in pptx</action>
<action type="fix" fixes-bug="github-1179" context="XSLF">Prevent
rendering a bitmap when its effective size is 0</action>
<action type="fix" fixes-bug="github-1187" context="XSLF">Adjust
XSLFDrawing's slide shape tree scan in constructor to prevent shape ID
warnings</action>
+ <action type="update" breaks-compatibility="true"
context="SS_Common">DataFormatter: setUseCachedValuesForFormulaCells(boolean)
now defaults to true (was false in POI 5.x)</action>
</actions>
</release>
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]