How to set background color for merged cells in Apache POI

Question

I use the following code to set background color for cells:

XSSFCellStyle cellStyle = (XSSFCellStyle) excelStyle.getCellStyle();
cellStyle.setFillForegroundColor(new XSSFColor(java.awt.Color.decode("#FFFF99")));
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

But it is not working for cells which are merged, it always becomes black no matter of what I try to set. It is working only if I set a predefined color like:

cellStyle.setFillForegroundColor(IndexedColors.CORAL.index);

The color is defined by user so I can't use IndexedColors. Also, I can't create a custom palette color (as explained here) because my workbook is of type SXSSFWorkbook, not HSSFWorkbook.

How to set background color for merged cells?

When I do not know how to use Apache POI, I usually do the formatting in Excel and then look at the result XLSX file what is stored there. You can often guess what you should do in POI then :) Just rename XLSX to ZIP and look inside. Compare the formatting of unmerged and merged cells. — Honza Zidek, Sep 18 '18 at 17:44
@HonzaZidek in style file I spoted some differences, but still can't say what's the problem https://pastebin.com/Nk1Xz9Dp — A5300, Sep 19 '18 at 09:02
Have you tried all described here: https://stackoverflow.com/q/20561710/2886891 ? — Honza Zidek, Sep 19 '18 at 09:12

score 0 · Answer 1 · edited Feb 14 '21 at 21:02

I have tried below steps and working for me.

Create a sheet
Create a row
Create style with custom colors using RGB values
Loop for creating cell > create cell and add value to cells 0 which will appear for subsequent merged cells, apply style to every cell,
Finally merge cells > lets say you want to merge cell 0 to cell 3 of row 0 use sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));

After lot of debugging I found issue which was causing the merged cell to be always black was below line.

CellUtil.setAlignment(sheetSummary.getRow(0).getCell(4), HorizontalAlignment.CENTER_SELECTION);

How to set background color for merged cells in Apache POI

1 Answers1