Bug 56774

Summary: Inability to set custom colors in Custom Formatting Pattern Formatting
Product: POI Reporter: Bhaskar Edara <bhamue>
Component: XSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: normal CC: martin.fernau
Priority: P2    
Version: 3.12-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: All   
Bug Depends on: 58138    
Bug Blocks:    

Description Bhaskar Edara 2014-07-27 20:44:14 UTC
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));
Comment 1 Martin F. 2015-05-22 14:45:26 UTC
This still applies for the latest POI 3.12
Comment 2 Nick Burch 2015-07-14 21:27:03 UTC
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?
Comment 3 Bhaskar Edara 2015-07-23 14:42:36 UTC
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();
	}

}