This is an automated email from the ASF dual-hosted git repository.

pjfanning pushed a commit to branch 6.0.x
in repository https://gitbox.apache.org/repos/asf/poi.git


The following commit(s) were added to refs/heads/6.0.x by this push:
     new d86b0b984d [6.0.x] DataFormatter percent section fixes (#1528)
d86b0b984d is described below

commit d86b0b984ded61b440cf5c5dff64a9d42bc70e21
Author: PJ Fanning <[email protected]>
AuthorDate: Tue Nov 3 20:53:48 2026 +0100

    [6.0.x] DataFormatter percent section fixes (#1528)
    
    * DataFormatter: percent format with a negative section multiplied by 100 
twice (#1483)
    
    DataFormatter: only the positive section's percent signs scale the value
    
    A percent format with a negative section, e.g. "0.00%;[Red]-0.00%", was
    multiplied by 100 twice since #1404, because the percent signs of both
    sections were counted. Count them per section.
    
    Fixes #1482
    
    Co-authored-by: Claude Opus 5.5 <[email protected]>
    (cherry picked from commit 5de26e846918e4fbbeadce96ecdef0c92548a8b9)
    
    * DataFormatter: scale each section by its own percent signs (#1484)
    
    DecimalFormat applies the positive section's multiplier to negative
    values, so "0%%;-0%" showed -0.25 as "-2500%" and "0%;-0" showed -5 as
    "-500". Negative values now use a copy of the DecimalFormat with the
    negative section's multiplier, as CellFormat does.
    
    Co-authored-by: Claude Opus 5.5 <[email protected]>
    (cherry picked from commit a45e77ce7986fd7172eb0f7759de587a9a13ceb5)
    
    ---------
    
    Co-authored-by: Claude Opus 5.5 <[email protected]>
---
 .../org/apache/poi/ss/usermodel/DataFormatter.java | 46 +++++++++++++++++-----
 .../apache/poi/ss/usermodel/TestDataFormatter.java | 31 +++++++++++++++
 2 files changed, 67 insertions(+), 10 deletions(-)

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 6f9370c455..1bf70c8fc2 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
@@ -1251,6 +1251,8 @@ public class DataFormatter {
         private final BigDecimal divider;
         private static final BigDecimal ONE_THOUSAND = 
BigDecimal.valueOf(1000);
         private final DecimalFormat df;
+        /** formats negative values: the same as df, apart from the multiplier 
when the sections have different percent signs */
+        private final DecimalFormat negativeDf;
         /** the integer and fraction digit placeholders of the positive 
section, e.g. "??0" and "0?" */
         private final String integerPlaceholders;
         private final String fractionPlaceholders;
@@ -1286,20 +1288,32 @@ public class DataFormatter {
             if (padsWithSpaces) {
                 pattern = pattern.replace('?', '#');
             }
-            // each '%' multiplies by 100, but DecimalFormat only allows one
-            int percents = countUnquoted(pattern, '%');
-            if (percents > 1) {
+            // each '%' multiplies by 100, but DecimalFormat only allows one 
in each section,
+            // and it scales negative values by the percent signs of the 
positive section
+            int percents = countUnquotedInSection(pattern, '%', 0);
+            int negativePercents = countUnquotedInSection(pattern, '%', 1);
+            if (negativePercents < 0) {
+                negativePercents = percents;
+            }
+            boolean quotePercents = percents > 1 || negativePercents > 1 || 
percents != negativePercents;
+            if (quotePercents) {
                 pattern = quoteUnquoted(pattern, '%');
             }
             df = new DecimalFormat(trimTrailingCommas(pattern), symbols);
-            if (percents > 1) {
-                df.setMultiplier((int) Math.pow(100, Math.min(percents, 4)));
+            if (quotePercents) {
+                df.setMultiplier(percentMultiplier(percents));
             }
             setExcelStyleRoundingMode(df);
             // Excel shows the decimal point even when there are no digits 
after it, e.g. "#.#" shows 1 as "1."
             if (hasDecimalPoint(pattern)) {
                 df.setDecimalSeparatorAlwaysShown(true);
             }
+            if (negativePercents == percents) {
+                negativeDf = df;
+            } else {
+                negativeDf = (DecimalFormat) df.clone();
+                negativeDf.setMultiplier(percentMultiplier(negativePercents));
+            }
             Matcher endsWithCommasMatcher = endsWithCommas.matcher(pattern);
             if (endsWithCommasMatcher.find()) {
                 String commas = (endsWithCommasMatcher.group(1));
@@ -1313,18 +1327,29 @@ public class DataFormatter {
             }
         }
 
-        private static int countUnquoted(String pattern, char ch) {
+        private static int percentMultiplier(int percents) {
+            return (int) Math.pow(100, Math.min(percents, 4));
+        }
+
+        /**
+         * counts the unquoted {@code ch} in a section of the pattern: 0 is 
the positive section, 1 the negative one;
+         * -1 when the pattern does not have the section
+         */
+        private static int countUnquotedInSection(String pattern, char ch, int 
section) {
             int count = 0;
+            int currentSection = 0;
             boolean quoted = false;
             for (int i = 0; i < pattern.length(); i++) {
                 char c = pattern.charAt(i);
                 if (c == '\'') {
                     quoted = !quoted;
-                } else if (!quoted && c == ch) {
+                } else if (!quoted && c == ';') {
+                    currentSection++;
+                } else if (!quoted && c == ch && currentSection == section) {
                     count++;
                 }
             }
-            return count;
+            return currentSection < section ? -1 : count;
         }
 
         /** quotes each run of unquoted {@code ch}, so that DecimalFormat 
shows them as text */
@@ -1389,10 +1414,11 @@ public class DataFormatter {
         @Override
         public StringBuffer format(Object obj, StringBuffer toAppendTo, 
FieldPosition pos) {
             obj = scaleInput(obj);
+            DecimalFormat format = obj instanceof Number && ((Number) 
obj).doubleValue() < 0 ? negativeDf : df;
             if (!padsWithSpaces) {
-                return df.format(obj, toAppendTo, pos);
+                return format.format(obj, toAppendTo, pos);
             }
-            return toAppendTo.append(padWithSpaces(df.format(obj)));
+            return toAppendTo.append(padWithSpaces(format.format(obj)));
         }
 
         /**
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 6de9ac274e..cc327ddec1 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
@@ -1401,6 +1401,37 @@ class TestDataFormatter {
                 formatter.formatRawCellContents(-12.5, -1, 
"0.0\\%;\\-0.0\\%"));
     }
 
+    /**
+     * See https://github.com/apache/poi/issues/1482
+     */
+    @Test
+    void percentWithNegativeSection() {
+        DataFormatter formatter = new DataFormatter();
+        assertEquals("20.23%", formatter.formatRawCellContents(0.202345, -1, 
"0.00%;[Red]-0.00%"));
+        assertEquals("-20.23%", formatter.formatRawCellContents(-0.202345, -1, 
"0.00%;[Red]-0.00%"));
+        assertEquals("1,020.23%", formatter.formatRawCellContents(10.202345, 
-1, "#,##0.00%;[Red]-#,##0.00%"));
+        assertEquals("-1,020.23%", formatter.formatRawCellContents(-10.202345, 
-1, "#,##0.00%;[Red]-#,##0.00%"));
+        assertEquals("2023.45%%", formatter.formatRawCellContents(0.202345, 
-1, "0.00%%;-0.00%%"));
+        assertEquals("-2023.45%%", formatter.formatRawCellContents(-0.202345, 
-1, "0.00%%;-0.00%%"));
+    }
+
+    /**
+     * Each section is scaled by its own percent signs
+     */
+    @Test
+    void percentDiffersBetweenSections() {
+        DataFormatter formatter = new DataFormatter();
+        assertEquals("2500%%", formatter.formatRawCellContents(0.25, -1, 
"0%%;-0%"));
+        assertEquals("-25%", formatter.formatRawCellContents(-0.25, -1, 
"0%%;-0%"));
+        assertEquals("25%", formatter.formatRawCellContents(0.25, -1, 
"0%;-0%%"));
+        assertEquals("-2500%%", formatter.formatRawCellContents(-0.25, -1, 
"0%;-0%%"));
+        assertEquals("25%", formatter.formatRawCellContents(0.25, -1, 
"0%;-0"));
+        assertEquals("-5", formatter.formatRawCellContents(-5, -1, "0%;-0"));
+        assertEquals("5", formatter.formatRawCellContents(5, -1, "0;-0%"));
+        assertEquals("-500%", formatter.formatRawCellContents(-5, -1, 
"0;-0%"));
+        assertEquals("(5.0)", formatter.formatRawCellContents(-5, -1, 
"0.0%;(0.0)"));
+    }
+
     @Test
     void testAdjustTo4DigitYearsIfConfigured() {
         DataFormatter dataFormatter = new DataFormatter();


---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]

Reply via email to