CODEWITHSUNDEEP
javaexcelapache-poi
Ragnarsson
Ragnarsson

Reputation: 1825

How to set different background colors for different cells using apache poi 4.1.0

I want to set background color of different cells with a different color. But it always sets one color for the whole column of the sheet. Below is my code snippet:

    for (int i = 1; i < rowTotal; i++) { 
        info("Read row " + i);

        XSSFRow dataRow = getRowData(i);

        setRowNumber(i);

        try {
            // do something

            setCellData("Passed", getRowNumber(), getColumnNumber());

            backgroundStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
            backgroundStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);                

            currentCell = dataRow.getCell(getColumnNumber());
            currentCell.setCellStyle(backgroundStyle);                
        } catch (Exception e) {
            setCellData("Failed", getRowNumber(), getColumnNumber());

            backgroundStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
            backgroundStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

            currentCell = dataRow.getCell(getColumnNumber());
            currentCell.setCellStyle(backgroundStyle);                
        }
    }

It works, but all cells are red. I expect the "Passed" cells should be green, but Passed cells are also red.

What am I missing?

Thanks.

Upvotes: 1

Views: 308

Answers (1)

deHaar
deHaar

Reputation: 18558

Using a single instance of CellStyle for different styles is not intended in apache-poi.
Each CellStyle defines a single style that can be applied to cells while you need one for every different style you want to have in your workbook, because styles are defined at workbook level. They should be reused, which also applies to Fonts used in CellStyles, they should be uniquely defined as well. There is a maximum for styles a workbook can handle or store, but I don't know its exact value.

In your use example, it would be recommended to create the styles before the loop (at least, there may be much better locations for style creation in your real code) and then apply the one that's needed for a cell: 

// create one style for a green background
CellStyle greenBackgroundStyle = wb.createCellStyle();
greenBackgroundStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
backgroundStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

// and another one for a red background
CellStyle redBackgroundStyle = wb.createCellStyle();
redBackgroundStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
redBackgroundStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

// then start looping
for (int i = 1; i < rowTotal; i++) { 
    info("Read row " + i);

    XSSFRow dataRow = getRowData(i);
    setRowNumber(i);

    try {
        // do something
        setCellData("Passed", getRowNumber(), getColumnNumber());            
        currentCell = dataRow.getCell(getColumnNumber());
        // set the style with green background
        currentCell.setCellStyle(greenBackgroundStyle);                
    } catch (Exception e) {
        setCellData("Failed", getRowNumber(), getColumnNumber());
        currentCell = dataRow.getCell(getColumnNumber());
        // set the style with red background
        currentCell.setCellStyle(redBackgroundStyle);                
    }
}

Upvotes: 1

Related Questions