Java apache poi: excel cell color

Question

I'm trying to change the background of a cell with apache poi.

I know there are a lot of answers about this, but I'm using the lastest version, (3.16) and they are all deprecated.

For example all the answers suggest that I use

CellStyle#setFillPattern(CellStyle.SOLID_FOREGROUND);

but it's deprecated entirely.

So, following the apache docs, I replaced all the deprecated functions with the new ones and came up with this MCVE:

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Mcve{
    public static void main(String[] args) {

    //Make workbook and first sheet
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("sheet1");

    //Make a style
    XSSFCellStyle style = workbook.createCellStyle();
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setFillBackgroundColor(IndexedColors.RED.getIndex());

    //Fill first line
    Row row = sheet.createRow(0);

    int i = 0;
    while (i < 5) {

        Cell cell = row.createCell(i);
        cell.setCellValue("TestCell " + i++);
                    cell.setCellStyle(style);
    }

    //Write to file
    File f = new File("Yourfilepathhere/document.xlsx"); //<-- FILL HERE

    try (FileOutputStream out = new FileOutputStream(f)) {
        workbook.write(out);
        workbook.close();
    } catch (Exception e) {
        e.printStackTrace();

    }
}
}

I suggest you paste it in an empty Maven project in your IDE of choice and please add these dependencies to your pom.xml:

https://pastebin.com/CXdViuW5

Now, on the lastest version of Excel, this either prints all-black cells or normal white-background cells, depending on the color. I have tried several colors and styles, doesn't seem to be working. The text is always there, but the background just won't apply.

What am I doing wrong here guys?

Solution

Looks like bug, But you can try setting forground color instead of Background color.

 XSSFCellStyle style = workbook.createCellStyle();
 style.setFillForegroundColor(IndexedColors.RED.getIndex());
 style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
 row.getCell(0).setCellStyle(style);

This will set your background color.

Comments

[Lory A]

Added it to the mcve now. I forgot to copy it in there, but in my main code I do have it, and it doesn't work. Any ideas ?

[Lory A]

You're right.. it does ! I'm sorry, I wouldn't have expected .setFillForegroundColor to set the background... talking about self explanatory method names.. I hope it's just a bug !