Not able to set custom color in XSSFCell Apache POI

user_6300149 0
Viewed 2411

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.

xssf apache-poi excel java
2 Answers

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.

user_190154990
Related