Under XSSF there is no way of setting custom colors when using ConditionalFormatting. I believe in the HSSF side we can alter the pallet to replace an indexed color with a custom collor and then use that but on XSSF side this is not possible. Would it be possible to add the following funtion to XSSFPatternFormatting class. I tried this hack and seems to be working fine. public void setFillBackgroundColor(java.awt.Color clr){ CTPatternFill ptrn = _fill.isSetPatternFill() ? _fill.getPatternFill() : _fill.addNewPatternFill(); CTColor bgColor = CTColor.Factory.newInstance(); bgColor.setRgb(new byte[]{(byte)clr.getRed(), (byte)clr.getGreen(), (byte)clr.getBlue()}); ptrn.setBgColor(bgColor); } With this method, I can set a custom color as ConditionalFormattingRule colorHigh = sheetCF.createConditionalFormattingRule(ComparisonOperator.GT, "H$48"); XSSFPatternFormatting highFill = (XSSFPatternFormatting)colorHigh.createPatternFormatting(); highFill.setFillBackgroundColor(new java.awt.Color(198, 239, 206));
This still applies for the latest POI 3.12
This has hopefully been solved with bug #58138, as XSSFPatternFormatting now additionally accepts XSSFColor objects for its colour setters Would you be able to check a nightly build / 3.13 beta 1 once released, and confirm that your use case is now covered?
I am able to verify this works using build poi-bin-3.13-beta1-20150720. Test code I have used import java.io.FileOutputStream; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.ConditionalFormattingRule; import org.apache.poi.ss.usermodel.PatternFormatting; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.SheetConditionalFormatting; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFColor; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class TestColor { public static void main(String[] args) throws Exception{ Workbook wb = new XSSFWorkbook(); Sheet s = wb.createSheet("TestColor"); s.setColumnWidth(0, 20*256); s.setColumnWidth(1, 20*256); for (int i = 0; i < 5; i++) { Row r = s.createRow(i); Cell c1 = r.createCell(0); c1.setCellValue(i); Cell c2 = r.createCell(1); c2.setCellValue(i+1); } SheetConditionalFormatting sheetCF = s.getSheetConditionalFormatting(); ConditionalFormattingRule cfRule = sheetCF.createConditionalFormattingRule("MOD(ROW(),2)=1"); PatternFormatting pfmt = cfRule.createPatternFormatting(); pfmt.setFillBackgroundColor(new XSSFColor(new java.awt.Color(98, 239, 216))); CellRangeAddress[] region = {CellRangeAddress.valueOf("A1:B5")}; sheetCF.addConditionalFormatting(region, cfRule); wb.write(new FileOutputStream("C:/poi-test/test.xlsx")); wb.close(); } }