51

I am using POI to create an Excel spreadsheet in Java. I have the following code used for creating a header row:

HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("Report");

// some more code

HSSFRow row = sheet.createRow(0);

HSSFCell cell = row.createCell(cellNumber);
HSSFCellStyle cellStyle = wb.createCellStyle();

cellStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setColor(HSSFColor.WHITE.index);

cellStyle.setFont(font);
cell.setCellStyle(cellStyle);

The issue I am having is that setting the fill background color on the cell always comes out black, no matter what color I pick. What am I doing wrong? If I don't use the "setFillPattern" line, no color shows up at all.

Ascalonian
  • 14,409
  • 18
  • 71
  • 103

3 Answers3

83

I got this to work. I had to set the foreground color to make the background color work (??).

So I changed:

cellStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);

to:

cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);

and it worked!

Ascalonian
  • 14,409
  • 18
  • 71
  • 103
  • 12
    Ahh, I get it. I need change the foreground color to change the background color - genius! – Pakman Oct 18 '12 at 21:10
  • 4
    I think the cell is painted with a pattern that consists of foreground and background pixels. If you use SOLID_FOREGROUND, just the foreground pixel are visible. This color is different from the color used to render text, which is set with the font. – Axel Feb 20 '14 at 12:48
  • Thx Axel !! Note : the Javadoc of the 2 implementations is very clear about this, https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFCellStyle.html#setFillBackgroundColor(short) https://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFCellStyle.html#setFillBackgroundColor(short) Saddly, the base class does not says a word about that https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/CellStyle.html#setFillBackgroundColor(short) – jgraglia Sep 15 '15 at 11:15
  • 2
    With Apache POI version > 4 you now have to use the following : `HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex()`. – Yann39 Feb 15 '19 at 09:41
13

If you are setting the foreground color, use 

cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

If you are setting the background color, use

style.setFillPattern(FillPatternType.THICK_BACKWARD_DIAG);

or

style.setFillPattern(FillPatternType.THIN_BACKWARD_DIAG);

The foreground and background colors seem to 'stack' (red + blue = purple) if you set the foreground fill pattern before the background fill pattern, but not the other way round. There are several other fill patterns you can choose from. Note that the color will not be applied if you do not change the default fill pattern.

CellStyle.SOLID_FOREGROUND is deprecated in version 3.15+. Use FillPatternType.SOLID_FOREGROUND instead.

dizzy
  • 131
  • 3
  • 6
  • A good explanation about `style.setFillForegroundColor( IndexedColors.GREY_25_PERCENT.getIndex() ); style.setFillPattern( FillPatternType.SOLID_FOREGROUND );` which helped me, Thanks +1 ):- – Yash Nov 09 '17 at 08:19
1

csHeader.setFillForegroundColor(HSSFColor.SKY_BLUE.index); csHeader.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

SSSS
  • 69
  • 1
  • 2
  • 9