Apache POI background color style seems not working

Question

Good evening! I've some trouble with apache poi v. 3.14, when I open the rendered xlsx with Libreoffice or Excel the style is not applied correctly.

I have this code:

class... {

private XSSFCellStyle doubleLockStyle;
/**
     * Create default cell style.
     *
     * @param myWorkBook
     * @return The cell style.
     */
    private XSSFCellStyle createCell(XSSFWorkbook myWorkBook, boolean locked) {
        XSSFCellStyle cs = myWorkBook.createCellStyle();
        cs.setAlignment(HorizontalAlignment.CENTER);
        if (locked) {
            cs.setFillBackgroundColor(LOCKED_COLOR); //rgb from awt colors
            cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            cs.setLocked(true);
        } else {
            cs.setLocked(false);
        }
        return cs;
    }

     /**
     * Prepare cell styles.
     *
     * @param myWorkBook
     */
    private void initStyles(XSSFWorkbook myWorkBook) {
     ...
        short df = myWorkBook.createDataFormat().getFormat("0.00");
        doubleLockStyle = createCell(myWorkBook, true);
        doubleLockStyle.setDataFormat(df);
     ...
    }
    private void writeSheet(XSSFWorkbook myWorkBook, CalendarValueType type, Date startDate, Date endDate) {
     ...
     cell.setCellStyle(doubleLockStyle);
     ... 
    }
}

Sheet is set as locked. The output doesn't show the background colors and doesn't lock the cells. I can't figure out what's the problem. I've also tried to see if it's a knowed bug, but it isn't. Anyone have had the same problem? The cells locks if i change the background fill pattern to oblique lines, but the background doesn't appar correctly between Excel and Libre office. Thank you!

Answer 1

The problem with the background color is that colors are a little unintuitive. A cell has three colors, one for the font, and two for the fill. The fill is what most people call the cell background. It is implemented as a fill pattern and has a Foreground Color and a Background Color. When you use the solid fill:

cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);

the only color used is the fill Foreground color which you can set using:

cs.setFillForegroundColor(LOCKED_COLOR);

You used the fill background color which is not visible when using the solid fill pattern.

To make locked cells work you have to enable sheet protection via:

Sheet.protectSheet(password)

You must set a password, if the password is null then protection is disabled. You can remove the password by using:

Sheet.setSheetPassword(null, null);