Apache-POI: Cell Background Color doesn't work

Question

I'm trying to configure generic CellStyles for formating HSSFCells using Apache-POI 3.11.

Here is a runnable sample of the code. The bold and border formating is being correctly aplied. The problem is with the Background and Foreground colors.

Any clue of what am I doing wrong?

public class TestSO {

private final static short 
        MY_LIGHT_BLUE=100,
        MY_DARK_BLUE=101,
        MY_BLACK=102,
        MY_WHITE=103;

public static void main(String[]args) throws Exception{
    HSSFWorkbook workbook = new HSSFWorkbook();
    setPallete( workbook.getCustomPalette() );

    HSSFFont fontNormal = workbook.createFont();
    fontNormal.setFontHeightInPoints((short)11);
    fontNormal.setFontName("Calibri");
    HSSFFont fontBold = workbook.createFont();
    fontBold.setFontHeightInPoints((short)11);
    fontBold.setFontName("Calibri");
    fontBold.setBold(true);


    HSSFCellStyle titleStyle = workbook.createCellStyle();
    titleStyle.setFillBackgroundColor(MY_DARK_BLUE);
    titleStyle.setFillForegroundColor(MY_WHITE);
    titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    titleStyle.setFont(fontBold);
    setTopBotBorder(titleStyle);

    HSSFCellStyle fpStyle = workbook.createCellStyle();
    fpStyle.setFillBackgroundColor(MY_LIGHT_BLUE);
    fpStyle.setFillForegroundColor(MY_BLACK);
    fpStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    fpStyle.setFont(fontNormal);
    setTopBotBorder(fpStyle);

    HSSFSheet sheet = workbook.createSheet("Leyenda");

    HSSFCell cell;

    cell = sheet.createRow( 1 ).createCell( 1 );
    cell.setCellValue("TitleStyle");
    cell.setCellStyle(titleStyle);

    cell = sheet.createRow( 2 ).createCell( 1 );
    cell.setCellValue("FpStyle");
    cell.setCellStyle(fpStyle);

    sheet.autoSizeColumn(1);

    try (FileOutputStream fos = new FileOutputStream( new File("TestWB.xls") )) {
        workbook.write( fos );
    }
}  

private static void setPallete( HSSFPalette pallete ){
    pallete.setColorAtIndex(MY_LIGHT_BLUE, (byte)189,(byte)215,(byte)238);
    pallete.setColorAtIndex(MY_DARK_BLUE, (byte)32,(byte)55,(byte)100);
    pallete.setColorAtIndex(MY_BLACK, (byte)0,(byte)0,(byte)0);
    pallete.setColorAtIndex(MY_WHITE, (byte)255,(byte)255,(byte)255);
}

private static void setTopBotBorder( CellStyle style ){
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(MY_BLACK);
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(MY_BLACK);
}

}

Here is the Excel file output:

Program output

Thanks in advance.

score 7 · Accepted Answer · answered Mar 24 '15 at 16:56

I think there are a couple of things you need to change. Firstly you can't just assign new index values for your custom colors. The pallete is already full, and so you need to overwrite existing colors with your own custom ones. Therefore try changing the definition of your two blues to:

private final static short MY_LIGHT_BLUE = HSSFColor.CORNFLOWER_BLUE.index,
        MY_DARK_BLUE = HSSFColor.BLUE.index;

Secondly, I never set both the foreground and background colors at the same time as this seems to clash. To change the color in the cell, try just setting the foreground color, for example:

HSSFCellStyle titleStyle = workbook.createCellStyle();
titleStyle.setFillForegroundColor(MY_DARK_BLUE);
titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
titleStyle.setFont(fontBold);
setTopBotBorder(titleStyle);

If you then want to change the color of the actual text in the cell, you can change the color of the Font for example:

fontNormal.setColor(HSSFColor.RED.index);

I have tested this out and it seems to work.

That's it. I've forgot to post the resolution but my mistake was assume the 'foreground' as the text color when the correct aproach is to set the fot color. Btw, once corrected the color setting, there was no problem with my manually choosed color indices. Thank you for there reply. — Netto, Mar 24 '15 at 17:15
setFillForegroundColor(MY_DARK_BLUE); setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); You should always add these two when applying background colors to cell — Nimantha, Jan 07 '20 at 11:09

Apache-POI: Cell Background Color doesn't work

1 Answers1