setting cell style not working

Question

i'm working with apache poi and XLSX file. i use xssf classes to dynamically create a spreadsheet. i'd like to set cell's style in a for loop, but it doesn't seem to work...here's my code :

for(int i=1;i<=gc.getActualMaximum(GregorianCalendar.DAY_OF_MONTH);i++,gc.add(GregorianCalendar.DATE, 1),righe++){
        Row r = foglio.createRow(righe);

        if(getDayOfWeek(gc)== 6 || getDayOfWeek(gc) == 7){
            XSSFCellStyle cs1 = wb.createCellStyle();
            cs1.setFillBackgroundColor(IndexedColors.YELLOW.getIndex());
            cs1.setFillPattern(CellStyle.SOLID_FOREGROUND);
            XSSFFont f = wb.createFont();
            f.setBold(true);
            f.setColor(IndexedColors.RED.getIndex());
            cs1.setFont(f);
            Cell c1 = r.createCell(0);
                 c1.setCellValue(cost.getGiorni().get(getDayOfWeek(gc)-1).getNomeGiorno());
                 c1.setCellStyle(cs1);
            Cell c2 = r.createCell(1);
                 c2.setCellValue(i);
                 c2.setCellStyle(cs1);
        }               
        r.createCell(0).setCellValue(cost.getGiorni().get(getDayOfWeek(gc)-1).getNomeGiorno());
        r.createCell(1).setCellValue(i);

...this i just a portion of the code... i can't understand why is not working. Seems like the cellstyle is ignored or overwrited....

any clue ?

score 7 · Answer 1 · answered Jul 09 '13 at 10:56

7

CellStyles are per-workbook, and there's a hard limit that Excel imposes on the numbers that a file is allowed to have, so you need to make sure you create the cell style once outside the loop.

Your code would then look something like:

XSSFCellStyle cs1 = wb.createCellStyle();
cs1.setFillBackgroundColor(IndexedColors.YELLOW.getIndex());
cs1.setFillPattern(CellStyle.SOLID_FOREGROUND);

XSSFFont f = wb.createFont();
f.setBold(true);
f.setColor(IndexedColors.RED.getIndex());
cs1.setFont(f);

for(int i=1;i<=gc.getActualMaximum(GregorianCalendar.DAY_OF_MONTH) i++,gc.add(GregorianCalendar.DATE, 1),righe++){
    Row r = foglio.createRow(righe);

    if(getDayOfWeek(gc)== 6 || getDayOfWeek(gc) == 7){
        Cell c1 = r.createCell(0);
        c1.setCellValue(cost.getGiorni().get(getDayOfWeek(gc)-1).getNomeGiorno());
        c1.setCellStyle(cs1);
        Cell c2 = r.createCell(1);
        c2.setCellValue(i);
        c2.setCellStyle(cs1);
    }
}

If you're having issues with the styling not looking quite as you expect, the best option is to style a cell as you want it in Excel, save the file, read that into POI, and review the cell style that Excel wrote. Sometimes, Excel can do some strange things that take some getting used to, so check what it does to work out what you need to be doing!

answered Jul 09 '13 at 10:56

Gagravarr

47,320
10
111
156

i've already tried you method but doesn't solve anything...i also tought about create a .xlsx model file, but i'm producing a 100% dynamic spreadsheet based on some criteria defined by the user at runtime... – Medioman92 Jul 09 '13 at 11:39
Moreover if i use the "IndexedColor" class to set a background, it always results black – Medioman92 Jul 09 '13 at 11:45
1

See my advice at the bottom of the answer - create it as you want in excel, read that back from POI, and work out what options need setting to make the style look like you want – Gagravarr Jul 09 '13 at 11:55
1

this library is just crazy...neither the example are working...i've tried to load the style from a handmade xlsx file but there are bunch of parameter and is crazy to figure out how are they configured... – Medioman92 Jul 09 '13 at 13:09
1

The file format itself is crazy, POI just does its best to help you with it...! – Gagravarr Jul 09 '13 at 14:09
In newer versions it seems to be `.setFillPattern(FillPatternType.SOLID_FOREGROUND)` – parsecer Dec 10 '19 at 10:27
POI certainly makes the crazy Excel format easier to handle, but sadly CellStyles aren't working for me either. I've looked at what it is in Excel, but I can't reproduce it through POI. – mcv Jul 08 '22 at 08:06

score 6 · Answer 2 · answered Jul 09 '13 at 13:36

You can use Following method, perhaps this will resolve your problem.

public static void setCellColorAndFontColor(XSSFCell cell, IndexedColors FGcolor, IndexedColors FontColor ){
    XSSFWorkbook wb = cell.getRow().getSheet().getWorkbook();
    CellStyle style = wb.createCellStyle();
    XSSFFont font = wb.createFont();
    font.setBold(true);
    font.setColor(FontColor.getIndex());
    style.setFont(font);
    style.setFillForegroundColor(FGcolor.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cell.setCellStyle(style);
}

When you are calling this method the way should be like

setCellColorAndFontColor(cell, IndexedColors.BLACK, IndexedColors.WHITE);

will create bold & white font text color with black cell background color in the sheet.

That'll create one style per cell though, which you shouldn't do as you'll quickly run out - styles are workbook scoped! — Gagravarr, Jul 09 '13 at 14:09

score 2 · Answer 3 · answered Nov 20 '19 at 15:03

I think the .setFillPattern(CellStyle.SOLID_FOREGROUND);

got changed to .setFillPattern(FillPatternType.SOLID_FOREGROUND);

this works for me:

//design settings for header row
    Font headerFont = wb.createFont();
    headerFont.setColor(IndexedColors.WHITE.getIndex());
    CellStyle headerCellStyle = wb.createCellStyle();
    headerCellStyle.setFont(headerFont);
    headerCellStyle.setAlignment(HorizontalAlignment.CENTER);
    headerCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    headerCellStyle.setFillForegroundColor(IndexedColors.BLACK.getIndex());
    headerCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

setting cell style not working

3 Answers3