'Not able to set custom color in XSSFCell Apache POI

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.

javaexcelapache-poixssf


Solution 1:[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.

Solution 2:[2]

As a workaround you can use conditional formatting to set custom colors after you've set all other format options (alignment, borders...) with cell style.

Here is a working (Kotlin) example that defines a custom color to distinguish between even and odd rows:

private fun setEvenOddColorFormatting(sheet: XSSFSheet) {
            val sheetConditionalFormatting = sheet.sheetConditionalFormatting

            val rule = sheetConditionalFormatting.createConditionalFormattingRule("MOD(ROW(), 2) = 0")
            val formatForRule = rule.createPatternFormatting()
            formatForRule.setFillBackgroundColor(XSSFColor(byteArrayOf(221.toByte(), 235.toByte(), 247.toByte())))
            formatForRule.fillPattern = PatternFormatting.SOLID_FOREGROUND

            val region = arrayOf(CellRangeAddress(0, sheet.lastRowNum,0,10))
            sheetConditionalFormatting.addConditionalFormatting(region, rule)
        }

A drawback is, that you have to write the rule as excel function. But you should be able to use an always true function and only set the region.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Ondi

Related Questions