How to change font color of particular cell apache poi 3.9

Question

I can change foreground color with the following code in apache POI. Now I want to change font color of a single cell.

CellStyle style = wb.createCellStyle();
style.setFillForegroundColor(IndexedColors.GREEN.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell = rowxl.createCell((short) 7);
cell.setCellValue(" <<<<ONTRACK>>>>");
cell.setCellStyle(style);


rowxl.createCell(0).setCellValue(TEAM);

I have tried this but it does not change the color of first two columns

code:

public class fclr {
     public static void main(String[] args)  throws Exception {
           
         InputStream inp = new FileInputStream("c:/workbook1.xls");
            Workbook wb = WorkbookFactory.create(inp);
            CreationHelper createHelper = wb.getCreationHelper();
            Sheet sheet = wb.getSheetAt(0);
            Row rowxl = sheet.createRow((short)0);
            
        
            Cell cell = rowxl.createCell(0);

            //apply some colors from the standard palette,
            // as in the previous examples.
            //we'll use red text on a lime background

            CellStyle style = wb.createCellStyle();
            
           
          rowxl.createCell(1).setCellValue("ABC");
        rowxl.createCell(2).setCellValue("aaa");
            Font font = wb.createFont();
            font.setColor(HSSFColor.BLACK.index);
            style.setFont(font);
           
            
            cell.setCellStyle(style);
            
            FileOutputStream fileOut = new FileOutputStream("c:/workbook1.xls");
            wb.write(fileOut);
            fileOut.close();
     }
}

Firth

did you look at the Font.setColor in the poi guide? poi.apache.org/spreadsheet/quick-guide.html – Subsidize

Why are you creating cell 0 twice? And do you realise you're not assigning the cell style to cell 1? – Radiotelegram

@Radiotelegram yes thats the question how to assign cellstyle to a particular cell i didn't found it in the tutorial – Firth

I responded a similar question here: https://mcmap.net/q/281860/-java-code-for-excel-row-in-bold-text-style-with-background-color – Abbottson

Answer 1

You're currently creating some of your cells twice, which is why it's all going wrong

Firstly, I'd suggest you move the cell style creation to nearer the top of your code. Remember - cell styles are scoped to a workbook, so don't create one per cell!

        CellStyle style = wb.createCellStyle();
        Font font = wb.createFont();
        font.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        style.setFont(font);
        // Set more colours on the style as needed
        // Set formatting rules on the style as needed

Now, depending on your preference, either do your cell creation like this:

        Cell cell;

        cell = rowxl.createCell(0);
        cell.setCellValue("ABC");
        cell.setCellStyle(style);

        cell = rowxl.createCell(1);
        cell.setCellValue("aaa");
        cell.setCellStyle(style);

Or like this:

    rowxl.createCell(1).setCellValue("ABC");
    rowxl.createCell(2).setCellValue("aaa");
    rowx1.getCell(1).setCellStyle(style);
    rowx1.getCell(2).setCellStyle(style);

Just don't do that weird hybrid you've got at the moment, as you end up creating cells twice and missing out styling!

Radiotelegram

HSSFColor.BLACK is deprecated instead of this use HSSFColor.HSSFColorPredefined.BLACK – Eliaeliades

Is there a way to get font color from HEX or may be RGB? – Whew

@Whew Sure! But you'll need to ask that as a new question... – Radiotelegram

Did it! Just converted my string to short color, thanks :) – Whew

Answer 2

For change color of existed cell is is needed to create new Font and new CellStyle with all the cell attribute. I've wrote method:

public static CellStyle createCellStyle(Sheet sheet, CellAddress cellAddr, short color) {
    Workbook workbook = sheet.getWorkbook();
    CellStyle cellStyle = sheet.getRow(cellAddr.getRow()).getCell(cellAddr.getColumn()).getCellStyle();
    Font font = workbook.getFontAt(cellStyle.getFontIndex());
    Font newFont = workbook.createFont();
    newFont.setFontHeight(font.getFontHeight());
    newFont.setBold(font.getBold());
    newFont.setItalic(font.getItalic());
    newFont.setUnderline(font.getUnderline());
    newFont.setStrikeout(font.getStrikeout());
    newFont.setColor(color);
    CellStyle newCellStyle = workbook.createCellStyle();
    newCellStyle.cloneStyleFrom(cellStyle);
    newCellStyle.setFont(newFont);
    return newCellStyle;
}

Reuse this style for all target cells (e.g from Map, filled before first usage):

enum CellType {
    USER_ENABLED,
    USER_DISABLED,
    ...
}

private final EnumMap<CellType, CellStyle> cellStyles = new EnumMap<>(CellType.class);

cellStyles.computeIfAbsent(USER_ENABLED, cellType -> ExcelPoiConverter.createCellStyle(sheet, cellAddr, IndexedColors.GREEN.getIndex()));
cellStyles.computeIfAbsent(USER_DISABLED, cellType -> ExcelPoiConverter.createCellStyle(sheet, cellAddr, IndexedColors.RED.getIndex()));
cell.setCellStyle(cellStyles.get(user.isEnabled() ? USER_ENABLED : USER_DISABLED));

Absa