java xssf的背景色,设置背景自定义颜色不适用于Apache POI中的XSSF

I wrote code which should create an Excel file (xlsx or xls) and set a custom background color to cell. When creating the xls file the background color works fine, but in case of xlsx the background color is not set to the right color.

What is wrong in my code?

public class PoiWriteExcelFile {

static Workbook workbook;

static Sheet worksheet;

public static void main(String[] args) {

try {

String type = "xlsx"; //xls

FileOutputStream fileOut = new FileOutputStream("D:\\poi-test." + type);

switch (type) {

case "xls":

workbook = new HSSFWorkbook();

break;

case "xlsx":

workbook = new XSSFWorkbook();

break;

}

CellStyle cellStyle = workbook.createCellStyle();

switch (type) {

case "xls":

HSSFPalette palette = ((HSSFWorkbook) workbook).getCustomPalette();

palette.setColorAtIndex(HSSFColor.LAVENDER.index, (byte)128, (byte)0, (byte)128);

HSSFColor hssfcolor = palette.getColor(HSSFColor.LAVENDER.index);

cellStyle.setFillForegroundColor(hssfcolor.getIndex());

break;

case "xlsx":

XSSFColor color = new XSSFColor(new java.awt.Color(128, 0, 128));

cellStyle.setFillForegroundColor(color.getIndex());

break;

}

worksheet = workbook.createSheet("POI Worksheet");

Row row1 = worksheet.createRow((short) 0);

Cell cellA1 = row1.createCell((short) 0);

cellA1.setCellValue("Hello");

cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

cellA1.setCellStyle(cellStyle);

workbook.write(fileOut);

fileOut.flush();

fileOut.close();

} catch (FileNotFoundException e) {

e.printStackTrace();

} catch (IOException e) {

e.printStackTrace();

}

}

}

解决方案

You are trying to use an indexed color, but with your code for HSSF the indexed color is found, but not for the XSSF part. There Color.getIndex() will return zero, which is black.

There is a method isIndexed() on color which you need to check if the color is an indexed one and only then it makes sense to use getIndex() on the POI-Color-object.

You can make it work for XSSF by not using indexed colors, but the full color value by using the following:

((XSSFCellStyle)cellStyle).setFillForegroundColor(color);

This way you set the actual color and the resulting workbook will have the correct background.