This is an automated email from the ASF dual-hosted git repository.
fanningpj 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 d81592022d feat: Full color support in CellFormat expressions (#797)
d81592022d is described below
commit d81592022dc186d20290c74da92b671113e780e8
Author: Patrik Lindström <[email protected]>
AuthorDate: Mon Aug 4 18:19:45 2025 +0300
feat: Full color support in CellFormat expressions (#797)
* feat: Full color support in cellformat expressions
This change adds full support for named and indexed color in cell format
expressions by adding in the standard color palette to CellFormatPart, from
where the formatting information is free to propagate down the stack.
For strict format compatibility, only the 8 basic named colors and 56
indexed colors should be allowed, but this change retains the assumed intended
feature of the original code to also support the full set of colors specified
in HSSFColor.HSSFColorPredefined. If this is undesirable, the dependency on
HSSFColor should be dropped entirely.
This change is required in order to properly support named and indexed
colors as part of cell format expressions in products using POI.
* Add some sanity tests
* Rename testNamedColorsExist to testNamedColors
* fix rebase corruption in CellFormatPart
* fix indentation in TestCellFormat
..by converting tabs to four spaces
* fix: replace .length() == 0 with .isEmpty for color name string
* fix Javadoc name parameter in CellFormatPart.getColor()
* chore: Tidy up CellFormatPart
---
.../org/apache/poi/ss/format/CellFormatPart.java | 106 +++++++++++++----
.../org/apache/poi/ss/format/TestCellFormat.java | 126 ++++++++++++++++++++-
2 files changed, 206 insertions(+), 26 deletions(-)
diff --git a/poi/src/main/java/org/apache/poi/ss/format/CellFormatPart.java
b/poi/src/main/java/org/apache/poi/ss/format/CellFormatPart.java
index ea8eaa37b4..d8f59b70a7 100644
--- a/poi/src/main/java/org/apache/poi/ss/format/CellFormatPart.java
+++ b/poi/src/main/java/org/apache/poi/ss/format/CellFormatPart.java
@@ -26,8 +26,10 @@ import javax.swing.*;
import java.awt.*;
import java.util.*;
+import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
+import java.util.stream.Collectors;
import static org.apache.poi.ss.format.CellFormatter.quote;
@@ -50,29 +52,13 @@ public class CellFormatPart {
private static final Logger LOG =
PoiLogManager.getLogger(CellFormatPart.class);
static final Map<String, Color> NAMED_COLORS;
+ static final List<Color> INDEXED_COLORS;
private final Color color;
private final CellFormatCondition condition;
private final CellFormatter format;
private final CellFormatType type;
- static {
- NAMED_COLORS = new TreeMap<>(
- String.CASE_INSENSITIVE_ORDER);
-
- for (HSSFColor.HSSFColorPredefined color :
HSSFColor.HSSFColorPredefined.values()) {
- String name = color.name();
- short[] rgb = color.getTriplet();
- Color c = new Color(rgb[0], rgb[1], rgb[2]);
- NAMED_COLORS.put(name, c);
- if (name.indexOf('_') > 0)
- NAMED_COLORS.put(name.replace('_', ' '), c);
- if (name.indexOf("_PERCENT") > 0)
- NAMED_COLORS.put(name.replace("_PERCENT", "%").replace('_',
- ' '), c);
- }
- }
-
/** Pattern for the color part of a cell format part. */
public static final Pattern COLOR_PAT;
/** Pattern for the condition part of a cell format part. */
@@ -103,6 +89,50 @@ public class CellFormatPart {
public static final int SPECIFICATION_GROUP;
static {
+ // Build indexed color list, in order, from 1 to 56
+ Integer[] indexedColors = new Integer[] {
+ 0x000000, 0xFFFFFF, 0xFF0000, 0x00FF00, 0x0000FF, 0xFFFF00,
0xFF00FF, 0x00FFFF,
+ 0x800000, 0x008000, 0x000080, 0x808000, 0x800080, 0x008080,
0xC0C0C0, 0x808080,
+ 0x9999FF, 0x993366, 0xFFFFCC, 0xCCFFFF, 0x660066, 0xFF8080,
0x0066CC, 0xCCCCFF,
+ 0x000080, 0xFF00FF, 0xFFFF00, 0x00FFFF, 0x800080, 0x800000,
0x008080, 0x0000FF,
+ 0x00CCFF, 0xCCFFFF, 0xCCFFCC, 0xFFFF99, 0x99CCFF, 0xFF99CC,
0xCC99FF, 0xFFCC99,
+ 0x3366FF, 0x33CCCC, 0x99CC00, 0xFFCC00, 0xFF9900, 0xFF6600,
0x666699, 0x969696,
+ 0x003366, 0x339966, 0x003300, 0x333300, 0x993300, 0x993366,
0x333399, 0x333333
+ };
+ INDEXED_COLORS = Collections.unmodifiableList(
+ Arrays.asList(indexedColors)
+ .stream().map(Color::new)
+ .collect(Collectors.toList()));
+
+ // Build initial named color map
+ Map<String, Color> namedColors = new TreeMap<>(
+ String.CASE_INSENSITIVE_ORDER);
+
+ // Retain compatibility with original implementation
+ for (HSSFColor.HSSFColorPredefined color :
HSSFColor.HSSFColorPredefined.values()) {
+ String name = color.name();
+ short[] rgb = color.getTriplet();
+ Color c = new Color(rgb[0], rgb[1], rgb[2]);
+ namedColors.put(name, c);
+ if (name.indexOf('_') > 0)
+ namedColors.put(name.replace('_', ' '), c);
+ if (name.indexOf("_PERCENT") > 0)
+ namedColors.put(name.replace("_PERCENT", "%").replace('_',
+ ' '), c);
+ }
+
+ // Add missing color values and replace incorrectly defined standard
colors
+ // used in Excel, Google Sheets, etc. The first eight indexed colors
correspond
+ // exactly to named colors.
+ namedColors.put("black", INDEXED_COLORS.get(0));
+ namedColors.put("white", INDEXED_COLORS.get(1));
+ namedColors.put("red", INDEXED_COLORS.get(2));
+ namedColors.put("green", INDEXED_COLORS.get(3));
+ namedColors.put("blue", INDEXED_COLORS.get(4));
+ namedColors.put("yellow", INDEXED_COLORS.get(5));
+ namedColors.put("magenta", INDEXED_COLORS.get(6));
+ namedColors.put("cyan", INDEXED_COLORS.get(7));
+
// A condition specification
String condition = "([<>=]=?|!=|<>) # The operator\n" +
" \\s*(-?([0-9]+(?:\\.[0-9]*)?)|(\\.[0-9]*))\\s* # The
constant to test against\n";
@@ -110,8 +140,16 @@ public class CellFormatPart {
// A currency symbol / string, in a specific locale
String currency = "(\\[\\$.{0,3}(-[0-9a-f]{3,4})?])";
- String color =
- "\\[(black|blue|cyan|green|magenta|red|white|yellow|color
[0-9]+)]";
+ // Build the color code matching expression. We should match any named
color
+ // in the set as well as a string in the form of "Color 8" or "Color
15".
+ String color = "\\[(";
+ for (String key : namedColors.keySet()) {
+ // Escape special characters in the color name
+ color += key.replaceAll("([^a-zA-Z0-9])", "\\\\$1") + "|";
+ }
+ // Match the indexed color table (accept both e.g. COLOR2 and COLOR 2)
+ // Both formats are accepted as input in other products
+ color += "color\\s*[0-9]+)\\]";
// A number specification
// Note: careful that in something like ##, that the trailing comma is
not caught up in the integer part
@@ -159,6 +197,17 @@ public class CellFormatPart {
CONDITION_OPERATOR_GROUP = findGroup(FORMAT_PAT, "[>=1]@", ">=");
CONDITION_VALUE_GROUP = findGroup(FORMAT_PAT, "[>=1]@", "1");
SPECIFICATION_GROUP = findGroup(FORMAT_PAT, "[Blue][>1]\\a ?", "\\a
?");
+
+ // Once patterns have been compiled, add indexed colors to
+ // namedColors so they can be easily picked up by getColor().
+ for (int i = 0; i < INDEXED_COLORS.size(); ++i) {
+ namedColors.put("color" + (i + 1), INDEXED_COLORS.get(i));
+ // Also support space between "color" and number.
+ namedColors.put("color " + (i + 1), INDEXED_COLORS.get(i));
+ }
+
+ // Store namedColors as NAMED_COLORS
+ NAMED_COLORS = Collections.unmodifiableMap(namedColors);
}
interface PartHandler {
@@ -250,12 +299,23 @@ public class CellFormatPart {
* @return The color specification or {@code null}.
*/
private static Color getColor(Matcher m) {
- String cdesc = m.group(COLOR_GROUP);
- if (cdesc == null || cdesc.isEmpty())
+ return getColor(m.group(COLOR_GROUP));
+ }
+
+ /**
+ * Get the Color object matching a color name, or {@code null} if the
+ * color name is not recognized.
+ *
+ * @param cname Color name, such as "red" or "Color 15"
+ *
+ * @return a Color object or {@code null}.
+ */
+ static Color getColor(String cname) {
+ if (cname == null || cname.isEmpty())
return null;
- Color c = NAMED_COLORS.get(cdesc);
+ Color c = NAMED_COLORS.get(cname);
if (c == null) {
- LOG.warn("Unknown color: {}", quote(cdesc));
+ LOG.warn("Unknown color: {}", quote(cname));
}
return c;
}
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 d9572117f4..feb56716a7 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
@@ -20,6 +20,7 @@ import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertNotNull;
import static org.junit.jupiter.api.Assertions.assertTrue;
+import java.awt.Color;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
@@ -33,6 +34,7 @@ import javax.swing.JLabel;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
@@ -1041,11 +1043,129 @@ class TestCellFormat {
@Test
void testNamedColors() {
- assertTrue(CellFormatPart.NAMED_COLORS.size() >=
HSSFColor.HSSFColorPredefined.values().length);
- Stream.of("GREEN", "Green", "RED", "Red", "BLUE", "Blue", "YELLOW",
"Yellow")
- .map(CellFormatPart.NAMED_COLORS::get)
+ // Make sure we have all standard named colors defined
+ // and are returned as non-null regardless of case
+ Stream.of("black", "white", "red", "green", "blue", "yellow",
"magenta", "cyan",
+ "Black", "White", "Red", "Green", "Blue", "Yellow", "Magenta",
"Cyan",
+ "BLACK", "WHITE", "RED", "GREEN", "BLUE", "YELLOW", "MAGENTA",
"CYAN")
+ .map(CellFormatPart::getColor)
.forEach(Assertions::assertNotNull);
}
+
+ @Test
+ void testIndexedColorsExist() {
+ // Make sure the standard indexed colors are returned correctly and
regardless of case
+ for (int i = 0; i < 56; ++i) {
+ assertNotNull(CellFormatPart.getColor("Color " + (i + 1)));
+ assertNotNull(CellFormatPart.getColor("COLOR" + (i + 1)));
+ assertNotNull(CellFormatPart.getColor("color" + (i + 1)));
+ }
+ }
+
+ @Test
+ void verifyNamedColors() {
+ assertEquals(CellFormatPart.getColor("Black"), new Color(0x000000));
+ assertEquals(CellFormatPart.getColor("white"), new Color(0xFFFFFF));
+ assertEquals(CellFormatPart.getColor("RED"), new Color(0xFF0000));
+ assertEquals(CellFormatPart.getColor("Green"), new Color(0x00FF00));
+ assertEquals(CellFormatPart.getColor("blue"), new Color(0x0000FF));
+ assertEquals(CellFormatPart.getColor("YELLOW"), new Color(0xFFFF00));
+ assertEquals(CellFormatPart.getColor("Magenta"), new Color(0xFF00FF));
+ assertEquals(CellFormatPart.getColor("cyan"), new Color(0x00FFFF));
+ }
+
+ @Test
+ void verifyIndexedColors() {
+ assertEquals(CellFormatPart.getColor("Color1"),
CellFormatPart.getColor("black"));
+ assertEquals(CellFormatPart.getColor("color2"),
CellFormatPart.getColor("white"));
+ assertEquals(CellFormatPart.getColor("Color3"),
CellFormatPart.getColor("red"));
+ assertEquals(CellFormatPart.getColor("color4"),
CellFormatPart.getColor("green"));
+ assertEquals(CellFormatPart.getColor("Color5"),
CellFormatPart.getColor("blue"));
+ assertEquals(CellFormatPart.getColor("color6"),
CellFormatPart.getColor("yellow"));
+ assertEquals(CellFormatPart.getColor("Color7"),
CellFormatPart.getColor("magenta"));
+ assertEquals(CellFormatPart.getColor("color8"),
CellFormatPart.getColor("cyan"));
+ assertEquals(CellFormatPart.getColor("Color9"), new Color(0x800000));
+ assertEquals(CellFormatPart.getColor("color10"), new Color(0x008000));
+ assertEquals(CellFormatPart.getColor("Color11"), new Color(0x000080));
+ assertEquals(CellFormatPart.getColor("color12"), new Color(0x808000));
+ assertEquals(CellFormatPart.getColor("Color13"), new Color(0x800080));
+ assertEquals(CellFormatPart.getColor("color14"), new Color(0x008080));
+ assertEquals(CellFormatPart.getColor("Color15"), new Color(0xC0C0C0));
+ assertEquals(CellFormatPart.getColor("color16"), new Color(0x808080));
+ assertEquals(CellFormatPart.getColor("Color17"), new Color(0x9999FF));
+ assertEquals(CellFormatPart.getColor("COLOR18"), new Color(0x993366));
+ assertEquals(CellFormatPart.getColor("Color19"), new Color(0xFFFFCC));
+ assertEquals(CellFormatPart.getColor("color20"), new Color(0xCCFFFF));
+ assertEquals(CellFormatPart.getColor("Color21"), new Color(0x660066));
+ assertEquals(CellFormatPart.getColor("COLOR22"), new Color(0xFF8080));
+ assertEquals(CellFormatPart.getColor("Color23"), new Color(0x0066CC));
+ assertEquals(CellFormatPart.getColor("color24"), new Color(0xCCCCFF));
+ assertEquals(CellFormatPart.getColor("Color25"), new Color(0x000080));
+ assertEquals(CellFormatPart.getColor("color26"), new Color(0xFF00FF));
+ assertEquals(CellFormatPart.getColor("Color27"), new Color(0xFFFF00));
+ assertEquals(CellFormatPart.getColor("COLOR28"), new Color(0x00FFFF));
+ assertEquals(CellFormatPart.getColor("Color29"), new Color(0x800080));
+ assertEquals(CellFormatPart.getColor("color30"), new Color(0x800000));
+ assertEquals(CellFormatPart.getColor("Color31"), new Color(0x008080));
+ assertEquals(CellFormatPart.getColor("Color32"), new Color(0x0000FF));
+ assertEquals(CellFormatPart.getColor("Color33"), new Color(0x00CCFF));
+ assertEquals(CellFormatPart.getColor("Color34"), new Color(0xCCFFFF));
+ assertEquals(CellFormatPart.getColor("Color35"), new Color(0xCCFFCC));
+ assertEquals(CellFormatPart.getColor("Color36"), new Color(0xFFFF99));
+ assertEquals(CellFormatPart.getColor("Color37"), new Color(0x99CCFF));
+ assertEquals(CellFormatPart.getColor("Color38"), new Color(0xFF99CC));
+ assertEquals(CellFormatPart.getColor("Color39"), new Color(0xCC99FF));
+ assertEquals(CellFormatPart.getColor("Color40"), new Color(0xFFCC99));
+ assertEquals(CellFormatPart.getColor("Color41"), new Color(0x3366FF));
+ assertEquals(CellFormatPart.getColor("Color42"), new Color(0x33CCCC));
+ assertEquals(CellFormatPart.getColor("Color43"), new Color(0x99CC00));
+ assertEquals(CellFormatPart.getColor("Color44"), new Color(0xFFCC00));
+ assertEquals(CellFormatPart.getColor("Color45"), new Color(0xFF9900));
+ assertEquals(CellFormatPart.getColor("Color46"), new Color(0xFF6600));
+ assertEquals(CellFormatPart.getColor("Color47"), new Color(0x666699));
+ assertEquals(CellFormatPart.getColor("Color48"), new Color(0x969696));
+ assertEquals(CellFormatPart.getColor("Color49"), new Color(0x003366));
+ assertEquals(CellFormatPart.getColor("Color50"), new Color(0x339966));
+ assertEquals(CellFormatPart.getColor("Color51"), new Color(0x003300));
+ assertEquals(CellFormatPart.getColor("Color52"), new Color(0x333300));
+ assertEquals(CellFormatPart.getColor("Color53"), new Color(0x993300));
+ assertEquals(CellFormatPart.getColor("Color54"), new Color(0x993366));
+ assertEquals(CellFormatPart.getColor("Color55"), new Color(0x333399));
+ assertEquals(CellFormatPart.getColor("Color56"), new Color(0x333333));
+ }
+
+ @Test
+ void testColorsInWorkbook() throws IOException {
+ // Create a workbook, row and cell to test with
+ try (Workbook wb = new HSSFWorkbook()) {
+ Sheet sheet = wb.createSheet();
+ Row row = sheet.createRow(0);
+ Cell cell = row.createCell(0);
+ CellFormatResult result;
+ CellFormat cf = CellFormat.getInstance(
+ "[GREEN]#,##0.0;[RED]\\(#,##0.0\\);[COLOR22]\"===\";[COLOR
8]\\\"@\\\"");
+
+ cell.setCellValue(100.0);
+ result = cf.apply(cell);
+ assertEquals("100.0", result.text);
+ assertEquals(result.textColor, CellFormatPart.getColor("color 4"));
+
+ cell.setCellValue(-50.0);
+ result = cf.apply(cell);
+ assertEquals("(50.0)", result.text);
+ assertEquals(result.textColor, CellFormatPart.getColor("red"));
+
+ cell.setCellValue("foo");
+ result = cf.apply(cell);
+ assertEquals("\"foo\"", result.text);
+ assertEquals(result.textColor, CellFormatPart.getColor("cyan"));
+
+ cell.setCellValue(0.0);
+ result = cf.apply(cell);
+ assertEquals("===", result.text);
+ assertEquals(result.textColor, CellFormatPart.getColor("color
22"));
+ }
+ }
@Test
void testElapsedSecondsRound() {
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
