How to override cell style in excel file using java and Apache POI library?

Question

I am doing excel validations for input .xlsx file using java and Apache POI library.

Here below I am posting two functions from my java class. When I try to set the cell style it's not getting reflected in the excel file. I searched over internet about it, but everywhere they have given code to give style while creating cell/row itself.

public static CellStyle getNewCellStyle(){
    CellStyle style = myWorkBook.createCellStyle();
    style.setFillBackgroundColor(IndexedColors.GREEN.getIndex());
    style.setFillPattern(CellStyle.ALIGN_FILL); 
    return style;
}


public static void chCaseNumberColumnValidation(Cell cell){
    String cellData = getCellDataValue(cell);
    if(cellData.length() == 10){
        if(cellData.equals("BLANK") || cellData.trim().length() == 0){
            System.out.println("BLANK CELL:   " + cell.getRowIndex() + "," + cell.getColumnIndex());
        }

        if(cellData.charAt(0) != '5'){
            System.out.println("DON't START WITH 5:    " + cell.getRowIndex() + "," + cell.getColumnIndex());
            cell.setCellStyle(getNewCellStyle());
        }
    }
    else{
        System.out.println("****INVALID SIZE   " + cell.getRowIndex() + "," + cell.getColumnIndex());

    }

}

Is there any way by which I can give background color to the already existing cells. (altering cell style)

Cell Styles are workbook-scoped not cell-scoped, what happens if you only create that style once and re-use it on all the cells? — Gagravarr
@Gagravarr Creating cell style is different. Lets us ignore it. My question is I want to override cell style from existing into new one. How can I do it? If i try to read rows and update cell style for some specific row then it's not updating. If I create cell and apply cell style and add cell to the sheet then it works. But giving style to existing cell is not working. Because it has some data already present in it. — Madhusudan
There are only so many cell styles allowed by Excel in a workbook. If you create one for every cell, you'll quickly run out, and styling won't work! That's why you need to create the cell styles once and re-use — Gagravarr

johankr johankr · Accepted Answer · 2016-01-29T16:02:02

Pasting example of setting color from the Apache POI Developer Guide

Fills and colors

Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("new sheet");

// Create a row and put some cells in it. Rows are 0 based.
Row row = sheet.createRow((short) 1);

// Aqua background
CellStyle style = wb.createCellStyle();
style.setFillBackgroundColor(IndexedColors.AQUA.getIndex());
style.setFillPattern(CellStyle.BIG_SPOTS);
Cell cell = row.createCell((short) 1);
cell.setCellValue("X");
cell.setCellStyle(style);

// Orange "foreground", foreground being the fill foreground not the font color.
style = wb.createCellStyle();
style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = row.createCell((short) 2);
cell.setCellValue("X");
cell.setCellStyle(style);

// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();

How to override cell style in excel file using java and Apache POI library?

1 Answers