Search code examples
javaexcelapachecolorsxssf

ConditionalFormatting Custom Colors in Apache POI XSSF

I am trying to set a custom color to fill a row using ConditionalFormatting in an XSSF sheet.

I've tried 

SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("MOD(ROW(),2)=0");
PatternFormatting fill1 = rule1.createPatternFormatting();
XSSFColor customColor = new XSSFColor(new byte[] {alpha, red, green, blue});


fill1.setFillForegroundColor(new XSSFColor(new java.awt.Color(128, 0, 128))); //1
fill1.setFillForegroundColor(customColor); //2

And neither of those two work. It doesn't accept an XSSFColor.

I saw this Bug Report and tried both "hacks" listed there as working, and again, neither work. Also the doc was no help.

I understand that doing:

setFillForegroundColor(new XSSFColor(new java.awt.Color(red,green,blue)));

May work for a single cell.

Most solutions I have come across are a few years old and seem not to be working. I just need a away to be able to enter a custom RGB or Hex color as a ConditionalFormatting in an XSSF sheet.

Any help?

Also I am running POI 3.9 to avoid parsing errors like: this and this.

Solution

  • I've just created a for loop to create a new rule for each color (short). It phased out around 62 with a few different shades from there to 80. Not exactly custom but at least I can attach the colors to a number.