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]