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/10/16 21:31:27 UTC
(poi) branch trunk updated: CellFormat: '?' and '#' show an integer part of zero as insignificant (#1422)
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 3b7dfeb5f6 CellFormat: '?' and '#' show an integer part of zero as insignificant (#1422)
3b7dfeb5f6 is described below
commit 3b7dfeb5f66973e5609da37d3f146cf70be4e617
Author: PJ Fanning <pj...@users.noreply.github.com>
AuthorDate: Fri Oct 16 22:31:21 2026 +0100
CellFormat: '?' and '#' show an integer part of zero as insignificant (#1422)
Bug 62242: CellNumberFormatter always wrote the units digit, so "??"
showed 0 as " 0" and the zero section of accounting formats, "-"??, showed
"- 0" instead of "- ". An integer part of zero is now insignificant like
any other leading zero: '?' shows a space, '#' shows nothing, and '0' still
shows the 0. Fractions and scientific notation are unchanged, as Excel shows
the 0 in them (checked by TestDataFormatterAgainstExcel).
The tests are moved from TestUnfixedBugs to TestCellFormat, and the
commented-out zero assertions in testAccountingFormats are enabled.
Co-authored-by: Claude Opus 5.5 <no...@anthropic.com>
---
.../apache/poi/ss/format/CellNumberFormatter.java | 6 ++++
.../apache/poi/hssf/usermodel/TestUnfixedBugs.java | 30 --------------------
.../org/apache/poi/ss/format/TestCellFormat.java | 32 ++++++++++++++++++++--
3 files changed, 35 insertions(+), 33 deletions(-)
diff --git a/poi/src/main/java/org/apache/poi/ss/format/CellNumberFormatter.java b/poi/src/main/java/org/apache/poi/ss/format/CellNumberFormatter.java
index 5595e63a75..b4559f72ca 100644
--- a/poi/src/main/java/org/apache/poi/ss/format/CellNumberFormatter.java
+++ b/poi/src/main/java/org/apache/poi/ss/format/CellNumberFormatter.java
@@ -839,6 +839,12 @@ public class CellNumberFormatter extends CellFormatter {
break;
}
}
+ // an integer part of zero is insignificant too: only a '0' placeholder shows it,
+ // e.g. "??" shows 0 as two spaces, and "#.00" shows 0.5 as ".50"
+ // (but not in fractions or scientific notation, where Excel shows the 0)
+ if (exponent == null && slash == null && strip == pos && pos >= 0 && result.charAt(pos) == '0') {
+ strip++;
+ }
ListIterator<Special> it = numSpecials.listIterator(numSpecials.size());
Special lastOutputIntegerDigit = null;
diff --git a/poi/src/test/java/org/apache/poi/hssf/usermodel/TestUnfixedBugs.java b/poi/src/test/java/org/apache/poi/hssf/usermodel/TestUnfixedBugs.java
index ac1ed44f1b..17df09a9ac 100644
--- a/poi/src/test/java/org/apache/poi/hssf/usermodel/TestUnfixedBugs.java
+++ b/poi/src/test/java/org/apache/poi/hssf/usermodel/TestUnfixedBugs.java
@@ -24,7 +24,6 @@ import java.io.IOException;
import org.apache.poi.hssf.HSSFTestDataSamples;
import org.apache.poi.hssf.util.HSSFColor;
-import org.apache.poi.ss.format.CellFormat;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
@@ -105,33 +104,4 @@ final class TestUnfixedBugs {
assertEquals("0, 128, 128", fontColorStr);
wb.close();
}
-
- @Test
- void testBug62242() {
- CellFormat cfUK = CellFormat.getInstance("_ * #,##0.00_ ;_ * \\-#,##0.00_ ;_ * \"-\"??_ ;_ @_");
- assertEquals(" - ", cfUK.apply((double) 0).text);
- }
-
- @Test
- void testDataFormattingWithQuestionMarkBug62242() {
- // The question mark in the format should be replaced by blanks, but
- // this is currently not handled when producing the Java formatting and
- // so we end up with a trailing zero here
- CellFormat cfUK = CellFormat.getInstance("??");
- assertEquals(" ", cfUK.apply((double) 0).text);
- }
-
- @Test
- void testDataFormattingWithQuestionMarkAndPoundBug62242() {
- char pound = '\u00A3';
-
- // The question mark in the format should be replaced by blanks, but
- // this is currently not handled when producing the Java formatting and
- // so we end up with a trailing zero here
- CellFormat cfUK = CellFormat.getInstance("_-[$£-809]* \"-\"??_-");
- assertEquals(" "+pound+" - ", cfUK.apply((double) 0).text);
-
- cfUK = CellFormat.getInstance("_-[$£-809]* \"-\"??_-a");
- assertEquals(" "+pound+" - a", cfUK.apply((double) 0).text);
- }
}
diff --git a/poi/src/test/java/org/apache/poi/ss/format/TestCellFormat.java b/poi/src/test/java/org/apache/poi/ss/format/TestCellFormat.java
index 09c09f257f..cbf06f92cd 100644
--- a/poi/src/test/java/org/apache/poi/ss/format/TestCellFormat.java
+++ b/poi/src/test/java/org/apache/poi/ss/format/TestCellFormat.java
@@ -1002,9 +1002,11 @@ class TestCellFormat {
// For zero, should be Space + currency symbol + spaces + Minus + spaces
assertEquals(" $ - ", cfDft.apply((double) 0).text);
assertEquals(" $ - ", cfUS.apply((double) 0).text);
- // TODO Fix these to not have an incorrect bonus 0 on the end
- //assertEquals(" "+pound+" - ", cfUK.apply((double) 0).text);
- //assertEquals(" - "+euro+" ", cfFR.apply((double) 0).text);
+ // "-"?? is the minus and a space for each '?' (bug 62242)
+ assertEquals(" "+pound+" - ", cfUK.apply((double) 0).text);
+ assertEquals(" - "+euro+" ", cfFR.apply((double) 0).text);
+ assertEquals(" R - ", cfSA.apply((double) 0).text);
+ assertEquals(" USD - ", cfUSD.apply((double) 0).text);
}
@Test
@@ -1049,6 +1051,30 @@ class TestCellFormat {
assertEquals("ca. 5", cf.apply((double) 5).text);
}
+ /**
+ * Bug 62242: '?' shows a space for an insignificant zero, including an integer part of zero
+ */
+ @Test
+ void testBug62242() {
+ CellFormat cf = CellFormat.getInstance("_ * #,##0.00_ ;_ * \\-#,##0.00_ ;_ * \"-\"??_ ;_ @_");
+ assertEquals(" - ", cf.apply((double) 0).text);
+
+ assertEquals(" ", CellFormat.getInstance("??").apply((double) 0).text);
+ assertEquals(" 5", CellFormat.getInstance("??").apply((double) 5).text);
+
+ cf = CellFormat.getInstance("_-[$£-809]* \"-\"??_-");
+ assertEquals(" £ - ", cf.apply((double) 0).text);
+ cf = CellFormat.getInstance("_-[$£-809]* \"-\"??_-a");
+ assertEquals(" £ - a", cf.apply((double) 0).text);
+
+ // '#' shows nothing for it, '0' shows the 0
+ assertEquals("", CellFormat.getInstance("#").apply((double) 0).text);
+ assertEquals(".50", CellFormat.getInstance("#.00").apply(0.5).text);
+ assertEquals(" .5 ", CellFormat.getInstance("?.??").apply(0.5).text);
+ assertEquals("0.50", CellFormat.getInstance("0.00").apply(0.5).text);
+ assertEquals("0", CellFormat.getInstance("0").apply((double) 0).text);
+ }
+
@Test
void testNamedColors() {
// Make sure we have all standard named colors defined
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org