CODEWITHSUNDEEP
javaexcelapache-poi
Anirudh Lou
Anirudh Lou

Reputation: 841

How to properly use style format in apache-poi?

I am trying to format my excel generated file from my java code. This is what my code snippet looks like:

...
Row row = sheet.createRow(rowNum++);
CellStyle textCellStyle = workbook.createCellStyle();
if (rowNum % 2 == 0) {
    textCellStyle.setFillBackgroundColor(IndexedColors.GREEN.getIndex());
    textCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    textCellStyle.setFont(textFonts1);
} else {
    textCellStyle.setFont(textFonts2);
}

cell = row.createCell(0);
cell.setCellValue(student != null ? student.getIdNumber() : "");
cell.setCellStyle(textCellStyle);
...

My expectation was all even rows will be filled with green background but the produced output was different, instead it produced black background. I already tried different color or even changing my fill pattern but i always get black.

I am using poi-3.17 and poi-ooxml-3.17, excel 2007 and java 1.6

enter image description here

Upvotes: 2

Views: 3631

Answers (2)

Martin
Martin

Reputation: 188

I'm an Apache POI newbie and don't know much about the details yet. But—in some circumstances—what follows may be useful to you.

This code...

import java.io.FileOutputStream;
import java.io.OutputStream;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class CellBackground {

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

    XSSFWorkbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet();
    int rowNum = 0;
    for (FillPatternType fpt : FillPatternType.values()) {
      Row row = sheet.createRow(rowNum++);
      Cell cell = row.createCell(0);
      XSSFCellStyle textCellStyle = wb.createCellStyle();
      textCellStyle.setFillBackgroundColor(IndexedColors.GREEN.getIndex());
      textCellStyle.setFillPattern(fpt);
      cell.setCellValue(fpt.name());
      cell.setCellStyle(textCellStyle);
      sheet.createRow(rowNum++).setHeightInPoints(7);
    }
    sheet.autoSizeColumn(0);

    OutputStream fileOut = new FileOutputStream("CellBackground.xlsx");
    wb.write(fileOut);
    wb.close();
  }
}

...will produce this output in LibreOffice Calc (Apache POI 4.0.1):

enter image description here

Upvotes: 2

david
david

Reputation: 862

You have set a fill pattern for the cell as FillPatternType.SOLID_FOREGROUND. But you did not set a foreground color. This means that the standard color (black) is used as the foreground color.

Try to exchange...

textCellStyle.setFillBackgroundColor(IndexedColors.GREEN.getIndex());

... with ...

textCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());

... and it should render as you expect. The text in the cells should also be visible.

It seems counterintuitive to set a foreground color and fill pattern when you want to set a background though.

Upvotes: 2

Related Questions