POI CellStyle does not appear to be applied

Question

Please note: I see a very similar question asked here but that answer was not very conclusive (I can't discern what the actual fix is/was). If someone can explain to me how that question/answer addresses my present issue at hand, I will happily delete this question myself! Just please don't DV/CV as a "dupe", and instead please help me make sense of that provided solution!

---

Java 8 and POI 4.1.x here. I am trying to write some Java/POI code that will produce a styled/formatted Excel file as output. I have created this GitHub project that perfectly reproduces the issue I'm seeing. If you really want, you can take a look at it and run it (its a Swing app) via ./gradlew clean build shadowJar && java -jar build/libs/hello-windows.jar, but the TLDR; of it is:

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

Font headerFont = workbook.createFont();
headerFont.setBold(true);

CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFont(headerFont);
cellStyle.setFillBackgroundColor(IndexedColors.YELLOW.getIndex());
cellStyle.setAlignment(HorizontalAlignment.CENTER);

int rowNum = 0;

Row headerRow = sheet.createRow(rowNum);
headerRow.setRowStyle(cellStyle);

Cell partNumberHeaderCell = headerRow.createCell(0);
partNumberHeaderCell.setCellValue("Part #");
partNumberHeaderCell.setCellStyle(cellStyle);

Cell partDescriptionHeaderCell = headerRow.createCell(1);
partDescriptionHeaderCell.setCellStyle(cellStyle);
partDescriptionHeaderCell.setCellValue("Description");

Cell partPriceHeaderCell = headerRow.createCell(2);
partPriceHeaderCell.setCellStyle(cellStyle);
partPriceHeaderCell.setCellValue("Price");

Cell manufacturerHeaderCell = headerRow.createCell(3);
manufacturerHeaderCell.setCellStyle(cellStyle);
manufacturerHeaderCell.setCellValue("Make");

rowNum++;

Row nextRow = sheet.createRow(rowNum);

nextRow.createCell(0).setCellValue(uuid);
nextRow.createCell(1).setCellValue("Some Part");
nextRow.createCell(2).setCellValue(2.99);
nextRow.createCell(3).setCellValue("ACME");

FileOutputStream fos = null;
try {
    fos = new FileOutputStream("acme.xlsx");

    workbook.write(fos);

    workbook.close();
} catch (IOException ex) {
    log.error(ExceptionUtils.getStackTrace(ex));
}

When this code runs it produces an Excel file that contains all my data (the header row and a "data" row) correctly, however all the formatting and cell styling seems to be ignored:

In the screenshot above, you can see that the header is not styled at all, however I believe I am styling it correctly:

CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFont(headerFont);
cellStyle.setFillBackgroundColor(IndexedColors.YELLOW.getIndex());
cellStyle.setAlignment(HorizontalAlignment.CENTER);

If my code is correct then I should see a header that:

1. Has a yellow background; and
2. Is horizontally-centered/aligned; and
3. Is bolded

Can anyone spot where I'm going awry?

Answer 1

Not clear why the bold font not gets applied for you, for me it gets.

But the problem with the cell interior is that Excel cell interiors have pattern fills. There the fill background color is the color behind the pattern and the fill foreground color is the color of the pattern. Solid filled cell interiors must have solid pattern having the needed fill foreground color set.

See also Busy Developers' Guide to HSSF and XSSF Features.

...
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFont(headerFont);
//cellStyle.setFillBackgroundColor(IndexedColors.YELLOW.getIndex());
cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
...

Let's have a complete example which will store your data in an Excel sheet:

import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

class CreateExcelCellStyle {

 public static void main(String[] args) throws Exception {

  try (Workbook workbook = new XSSFWorkbook(); 
       FileOutputStream fileout = new FileOutputStream("./Excel.xlsx") ) {

   Font headerFont = workbook.createFont();
   headerFont.setBold(true);

   CellStyle headerStyle = workbook.createCellStyle();
   headerStyle.setFont(headerFont);
   headerStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
   headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
   headerStyle.setAlignment(HorizontalAlignment.CENTER);

   Object[][] data = new Object[][] {
    new Object[] {"Part #", "Description", "Price", "Make"},
    new Object[] {"cb82c02", "Some Part", 2.99, "ACME"}
   };

   Sheet sheet = workbook.createSheet(); 

   for (int r = 0; r < data.length; r++) {
    Row row = sheet.createRow(r);
    for (int c = 0; c < data[0].length; c++) {
     Cell cell = row.createCell(c);
     if (r==0) cell.setCellStyle(headerStyle);
     Object content = data[r][c];
     if (content instanceof String) {
      cell.setCellValue((String)content);
     } else if (content instanceof Double) {
      cell.setCellValue((Double)content);
     }
    }
   }

   for (int c = 0; c < data[0].length; c++) {
    sheet.autoSizeColumn(c);
   }

   workbook.write(fileout);
  }

 }
}

Result: