Not able to set custom color in XSSFCell Apache PO

2019-02-28 19:37发布

问题:

I am trying to set some custom(from hexcode or rgb value) color to a xssfcell.But the color of the cell is becoming black even though I am giving some other color.I have tried doing this by the following ways :

File xlSheet = new File("C:\\Users\\IBM_ADMIN\\Downloads\\Excel Test\\Something3.xlsx");
    System.out.println(xlSheet.createNewFile());
    FileOutputStream fileOutISPR = new FileOutputStream("C:\\Users\\IBM_ADMIN\\Downloads\\Excel Test\\Something3.xlsx");
    XSSFWorkbook isprWorkbook = new XSSFWorkbook();
    XSSFSheet sheet = isprWorkbook.createSheet("TEST");
    XSSFRow row = sheet.createRow(0);
    XSSFCellStyle cellStyle = isprWorkbook.createCellStyle();
    byte[] rgb = new byte[3];
    rgb[0] = (byte) 24; // red
    rgb[1] = (byte) 22; // green
    rgb[2] = (byte) 219; // blue
    XSSFColor myColor = new XSSFColor(rbg);
    cellStyle.setFillForegroundColor(myColor);
    cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    XSSFCell cell = row.createCell(0);
    cell.setCellValue("Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has");
    cell.setCellStyle(cellStyle);
    CellRangeAddress rangeAddress = new CellRangeAddress(0, 0, 0, 2);
    sheet.addMergedRegion(rangeAddress);
    int width = ((int)(90 * 0.73)) * 256;
    sheet.setColumnWidth(cell.getColumnIndex(), width);
    //sheet.autoSizeColumn(cell.getColumnIndex());
    RegionUtil.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM, rangeAddress, sheet, isprWorkbook);
    RegionUtil.setBottomBorderColor(IndexedColors.RED.getIndex(), rangeAddress, sheet, isprWorkbook);

    XSSFCell cell2 = row.createCell(11);
    cell2.setCellValue("222222222222222");
    isprWorkbook.write(fileOutISPR);

//END of the program

   XSSFCellStyle cellStyle = isprWorkbook.createCellStyle();
   byte[] rgb = new byte[3];
    rgb[0] = (byte) 24; // red
    rgb[1] = (byte) 22; // green
    rgb[2] = (byte) 219; // blue
    XSSFColor myColor = new XSSFColor(rgb);
    cellStyle.setFillForegroundColor(myColor);//1st method
    //cellStyle.setFillForegroundColor(new XSSFColor(new   java.awt.Color(128, 0, 128)));//2nd method  
  //XSSFColor myColor = new XSSFColor(Color.decode("0XFFFFFF"));
  cellStyle.setFillForegroundColor(myColor);//3rd Method

I tried many other ways mentioned in answers to related questions but none of those solved my problem. Please help me out.

回答1:

This is caused by an incompleteness of Package org.apache.poi.ss.util.

PropertyTemplate as well as CellUtil and RegionUtilare be based on ss.usermodel level only and not on xssf.usermodel level. But org.apache.poi.ss.usermodel.CellStyle does not know something about a setFillForegroundColor(Color color) until now. It only knows setFillForegroundColor(short bg). So ss.usermodel level simply cannot set a Color as fill foreground color until now. Only a short (a color index) is possible.

If it comes to the question why setting the color is necessary when only the border shall be set using org.apache.poi.ss.util then the answer is, it is necessary because both, color and border, are in the same CellStyle. Thats why when adding the border settings to the CellStyle, the color settings must be maintain and finally be set new.

So in conclusion, there is not a way out of this dilemma. If you need using org.apache.poi.ss.util then you cannot use setFillForegroundColor(XSSFColor color) the same time. The only hope is setFillForegroundColor(Color color) will be added to org.apache.poi.ss.usermodel.CellStyle in later versions of apache poi.