How to set cell background with Apache POI?

Question

I try to set the background color of a cell according to a value. Please, find below the corresponding code.

File inputFile = new File(path)
FileInputStream fis = new FileInputStream(inputFile)
XSSFWorkbook inputWorkbook = new XSSFWorkbook(fis) 

XSSFWorkbook outputWorkbook = new XSSFWorkbook()

XSSFSheet inputSheet = inputWorkbook.getSheetAt(1)
def inputSheetName = inputWorkbook.getSheetName(1)
XSSFSheet outputSheet = outputWorkbook.createSheet(inputSheetName)

// some code

CellStyle style = outputSheet.getRow(rowIndex).getCell(columnIndex).getCellStyle()
short color = (status.get(listIndex) == 'Passed') ? IndexedColors.GREEN.getIndex() : IndexedColors.RED.getIndex()
style.setFillBackgroundColor(color)
outputSheet.getRow(rowIndex).getCell(columnIndex).setCellStyle(style)

// some code

This code does not make an error, however, the cell background is not set ...

Someone can help me please? Thanks for help!

Update 1

CellStyle style = outputSheet.getRow(rowIndex).getCell(columnIndex).getCellStyle()
short color = (status.get(listIndex) == 'Passed') ? IndexedColors.GREEN.getIndex() : IndexedColors.RED.getIndex()
style.setFillPattern(FillPatternType.SOLID_FOREGROUND)
style.setFillForegroundColor(color)
outputSheet.getRow(rowIndex).getCell(columnIndex).setCellStyle(style)

Update 2

The goal of the following code is to copy and paste content of an excel file to another one. And I want to set the color of the cell according to the status value.

def function(dateTimeOfTestSuite, status, executionDateTime) {
    File inputFile = new File(path)
    FileInputStream fis = new FileInputStream(inputFile)
    XSSFWorkbook inputWorkbook = new XSSFWorkbook(fis)
    def inputSheetCount = inputWorkbook.getNumberOfSheets()

    def directoryName = 'Data Files\\Reports\\' + name
    File directory = new File(directoryName)
    if (!directory.exists())
        directory.mkdirs()

    def excelOutputFileName = directory.path + '\\' + dateTimeOfTestSuite + '.xlsx'
    excelOutputFileName = excelOutputFileName.replace(':', '-')
    File outputFile = new File(excelOutputFileName)
    FileOutputStream fos = new FileOutputStream(outputFile)

    XSSFWorkbook outputWorkbook = new XSSFWorkbook()

    for(int i = 0; i < inputSheetCount; i++) {
        XSSFSheet inputSheet = inputWorkbook.getSheetAt(i)
        def inputSheetName = inputWorkbook.getSheetName(i)
        XSSFSheet outputSheet = outputWorkbook.createSheet(inputSheetName)

        def numberOfRows = inputSheet.getPhysicalNumberOfRows()
        def numberOfColumns = inputSheet.getRow(0).getPhysicalNumberOfCells()

        def listIndex = 0
        def rowIndex = 0
        outputSheet.createRow(0)
        while (rowIndex < numberOfRows && inputSheet.getRow(rowIndex).getCell(0).getCellType() != Cell.CELL_TYPE_BLANK) {
            def columnIndex = 0
            while (columnIndex < numberOfColumns && inputSheet.getRow(0).getCell(columnIndex).getCellType() != Cell.CELL_TYPE_BLANK) {
                def value = (inputSheet.getRow(rowIndex).getCell(columnIndex).toString() == 'null') ? '' : inputSheet.getRow(rowIndex).getCell(columnIndex).toString()
                if (columnIndex == 0)
                    outputSheet.createRow(rowIndex).createCell(columnIndex).setCellValue(value)
                else
                    outputSheet.getRow(rowIndex).createCell(columnIndex).setCellValue(value)

                outputSheet.autoSizeColumn(columnIndex)
                columnIndex++
            }

            if(inputSheetName == "input") {
                if(rowIndex == 0) {
                    outputSheet.getRow(0).createCell(columnIndex).setCellValue('Status')
                    outputSheet.getRow(0).createCell(columnIndex + 1).setCellValue('ExecutedAt')
                }
                else if (rowIndex > 1) {
                    outputSheet.getRow(rowIndex).createCell(columnIndex).setCellValue(status.get(listIndex))

                    if(status.get(listIndex) != 'No run') {
                        outputSheet.getRow(rowIndex).createCell(columnIndex + 1).setCellValue(executionDateTime.get(listIndex))

                        CellStyle style = outputSheet.getRow(rowIndex).getCell(columnIndex).getCellStyle()
                        def color = (status.get(listIndex) == 'Passed') ? IndexedColors.GREEN.getIndex() : IndexedColors.RED.getIndex()
                        style.setFillPattern(FillPatternType.SOLID_FOREGROUND)
                        style.setFillForegroundColor(color)
                        outputSheet.getRow(rowIndex).getCell(columnIndex).setCellStyle(style)
                    }

                    listIndex++
                }

                outputSheet.autoSizeColumn(columnIndex)
                outputSheet.autoSizeColumn(columnIndex + 1)
            }

            rowIndex++
        }
    }

    outputWorkbook.write(fos)
    fos.close()
}

Solution

XSSFCellStyle style = outputWorkbook.createCellStyle()
def color = (status.get(listIndex) == 'Passed') ? IndexedColors.GREEN.getIndex() : IndexedColors.RED.getIndex()
style.setFillPattern(FillPatternType.SOLID_FOREGROUND)
style.setFillForegroundColor(color)
outputSheet.getRow(rowIndex).getCell(columnIndex).setCellStyle(style)

Have you seen this _StackOverflow_ question: [Java Apache Poi, how to set background color and borders at same time](https://stackoverflow.com/questions/38874115/java-apache-poi-how-to-set-background-color-and-borders-at-same-time) — Abra, Jul 17 '19 at 06:52
Yes but I don't understand why setFillForegroundColor is used instead of setFillBackgroundColor — Royce, Jul 17 '19 at 06:54

XtremeBaumer · Accepted Answer · 2019-07-17T07:03:37.593

3

Its actually not about the background. Its the foreground color. Also you need to specify a FillPattern like this:

style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setFillForegroundColor(color);

As for the reason, why it is setFillForegroundColor and not setFillBackgroundColor, I couldn't find any information what exactly the difference is, but following code shows how they work:

style.setFillPattern(FillPatternType.LEAST_DOTS);
style.setFillForegroundColor(IndexedColors.BRIGHT_GREEN.getIndex());
style.setFillBackgroundColor(IndexedColors.RED.getIndex());

And the result looks like this:

From this I would guess, that setFillBackgroundColor only works properly in conjuction with setFillForegroundColor to create patterns like this.

edited Jul 17 '19 at 07:03

answered Jul 17 '19 at 06:52

XtremeBaumer

6,275
3
19
65

Thanks for your answer. I updated my question with the new code. However, the problem is not solved. – Royce Jul 17 '19 at 06:59
Why? It compile when I execute it (I'm usin groovy, not java but it is the same ApachePOI library) – Royce Jul 17 '19 at 07:07
Any reason you try to reuse the cell styles already on the cells? I would suggest to create a new cellStyle and set that one to the cells you want to color – XtremeBaumer Jul 17 '19 at 07:14
@N.Lamblin or you could just post the code around it. Maybe something in there doesn't work properly – XtremeBaumer Jul 17 '19 at 07:20
All code works, the color is just a plus ahah. I used `XSSFSheet`, maybe is it the problem right? Maybe I have to use `XSSFColor`... – Royce Jul 17 '19 at 07:47
Can you post a screenshot of how it looks? – XtremeBaumer Jul 17 '19 at 07:50
Your code does look fine. The only thing I would add is a `outputWorkbook.close()` after `fos.close()`. This might not solve the issue though. Can you provide a screenshot of the issue you have now? – XtremeBaumer Jul 17 '19 at 08:03
I don't have any particular issue. It is just that the background color is not set and I don't understand why ... – Royce Jul 17 '19 at 08:06
Oh sorry. Is it a plus for the corresponding feature, but it was the problem here. Thanks for your answer. I found and posted a solution. As you mentionned the solution was to create a new `CellStyle` – Royce Jul 17 '19 at 08:15
1

Though that makes me wonder, as a similar approach to reuse the cells cellStyle, did work for me. But glad it works now – XtremeBaumer Jul 17 '19 at 08:38

How to set cell background with Apache POI?

1 Answers1