how to set background color of a cell using apache

问题:

I am trying to set background color using setFillBackgroundColor method , but it seems necessary to use setFillPattern with it. But using setFillPattern method I am not able to find the plain FillPatternType.

cellStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
cellStyle.setFillPattern(HSSFCellStyle.SPARSE_DOTS);

I am not able to find the plain fillPatternType, If I use NO_FILL then the background color is not applying. Without using setFillPattern, I am not able to see the effect of setFillBackgroundColor.

Could you please let me know how to set plain background color without any dots,brick,diamond or DIAG.

Thanks.

回答1:

Cell interior uses pattern fills. The fill background color is the color behind the pattern.The fill foreground color is the color of the pattern.

To fill the cell using a plain color, you need using fill foreground color and solid pattern.

See Fills and colors.

...
cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
...

Complete example having cell fill and cell content:

import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class CreateExcelCellFillColor {

 public static void main(String[] args) throws Exception {
  Workbook workbook = new XSSFWorkbook();
  //Workbook workbook = new HSSFWorkbook();

  CellStyle cellStyle = workbook.createCellStyle();
  cellStyle.setAlignment(HorizontalAlignment.CENTER);
  cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

  cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
  cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

  Sheet sheet = workbook.createSheet();
  Row row = sheet.createRow(0);
  Cell cell = row.createCell(0);
  cell.setCellValue("cell value");
  cell.setCellStyle(cellStyle);

  row.setHeightInPoints(50);
  sheet.setColumnWidth(0, 50 * 256);

  FileOutputStream out = null;
  if (workbook instanceof HSSFWorkbook) {
   out = new FileOutputStream("CreateExcelCellFillColor.xls");
  } else if (workbook instanceof XSSFWorkbook) {
   out = new FileOutputStream("CreateExcelCellFillColor.xlsx");
  }
  workbook.write(out);
  out.close();
  workbook.close();
 }
}

Result: