How to set an Excel Cell foreground color for individual cells in the new version for apache-poi?

0
votes

I am using the new version for poi – 3.11

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.11</version>
</dependency>

I found that the old code for setting a foreground color is not compiled anymore but my new code does not work either. The code below sets Red as foreground color for the whole worksheet, but I need various cell colors. Cell values are set correctly.

XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("Calendar");

for (int rowNum=0; rowNum<n; rowNum ++)
{
    Row row = sheet.createRow(rowNum);
    for (int colNum = 0; colNum < m; colNum++) 
    {
        Cell cell = row.createCell(colNum);
        cell.setCellValue(grid[rowNum][colNum]);
        CellStyle cellStyle = cell.getCellStyle();
        cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

        cellStyle.setFillForegroundColor(HSSFColor.WHITE.index);
        if (res[rowNum][colNum] == CellEnum.BUSY.getValue())
        {
            cell.setCellValue(res[rowNum][colNum] + "|" + grid[rowNum][colNum]);
            cellStyle.setFillForegroundColor(HSSFColor.RED.index);
        }
        if (res[rowNum][colNum] == CellEnum.Pass.getValue())
        {
            cell.setCellValue(res[rowNum][colNum] + "|" + grid[rowNum][colNum]);
            cellStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
        }
    }
}
1
javaexcelapache-poi
If you do CellStyle cellStyle = cell.getCellStyle(); with cell being a new created cell without special style, then you will get the default cell style. You must creating a cell style if you want it be additional to default cell style. See: poi.apache.org/spreadsheet/quick-guide.html#FillsAndFrills. – Axel Richter

1 Answers

0
votes

The comment of Axel Richter practically gave the solution. So, the refined code is as follows:

    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("Calendar");
    CellStyle styleWhite = workbook.createCellStyle();
    styleWhite.setFillForegroundColor(IndexedColors.WHITE.getIndex());
    styleWhite.setFillPattern(CellStyle.SOLID_FOREGROUND);

    CellStyle styleYellow = workbook.createCellStyle();
    styleYellow.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
    styleYellow.setFillPattern(CellStyle.SOLID_FOREGROUND);

    CellStyle styleRed = workbook.createCellStyle();
    styleRed.setFillForegroundColor(IndexedColors.RED.getIndex());
    styleRed.setFillPattern(CellStyle.SOLID_FOREGROUND);

    for (int rowNum = 0; rowNum < n; rowNum++) {
        Row row = sheet.createRow(rowNum);
        for (int colNum = 0; colNum < m; colNum++) {
            Cell cell = row.createCell(colNum);
            cell.setCellValue(grid[rowNum][colNum]);
            cell.setCellStyle(styleWhite);
            if (res[rowNum][colNum] == CellEnum.BUSY.getValue()) {
                cell.setCellStyle(styleRed);
            } else if (res[rowNum][colNum] == CellEnum.Pass.getValue()) {
                cell.setCellStyle(styleYellow);
            } else {
                cell.setCellStyle(styleWhite);
            }
        }
    }