3

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.

NitSher01
  • 117
  • 1
  • 8
  • it's the morning here but what is the difference between 1 and 3 ? I think you are missing a fill pattern, see https://stackoverflow.com/a/31671032/180100 –  Jul 16 '17 at 09:00
  • Sure,I'll update the question with minimal code.Thank you – NitSher01 Jul 16 '17 at 09:02
  • fill pattern is not missing in the code and if fill pattern was missing the cell would be full white even after setting the color.But in my case,full cell is black. – NitSher01 Jul 16 '17 at 09:08
  • See https://stackoverflow.com/questions/43959440/apache-poi-fills-xssf-cell-with-black-instead-of-desired-custom-color-when-apply. Problem is using `RegionUtil` for setting borders. But `org.apache.poi.ss.util.*` is `SS` and not `XSSF` and `SS` cannot use `XSSFColor` only color indexes are possible. – Axel Richter Jul 16 '17 at 09:18
  • @AxelRichter : thanks,its working now.But,is there any way that I can use RegionUtil in my code because I have to use it for giving the outer border of a table? – NitSher01 Jul 16 '17 at 09:30
  • @AxelRichter you should make that an answer –  Jul 16 '17 at 09:32
  • Apache 4.0.0 answer here: https://stackoverflow.com/a/52926591/5611377 – ssimm Oct 22 '18 at 10:05

2 Answers2

10

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.

Axel Richter
  • 56,077
  • 6
  • 60
  • 87
0

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.

Ondi
  • 1
  • 1