CODEWITHSUNDEEP
javakotlinapache-poi
Dipti
Dipti

Reputation: 106

Apache POI : Issue during setting foreground color in conditionalFormatting with SOLID_FILL pattern

I want to apply a formatting to achieve row striped format(odd row : grey and even row : no background color) in generated excel sheet using Apache POI.

Following is related code :

       val sheetCF = sheet.sheetConditionalFormatting       
       val rule1 = sheetCF.createConditionalFormattingRule(
        "MOD(ROW(), 2) = 0")

       val fill1 = rule1.createPatternFormatting()
       fill1.setFillForegroundColor( XSSFColor(Color(245,245,245),DefaultIndexedColorMap())) // Light Gray color
      fill1.fillPattern = PatternFormatting.SOLID_FOREGROUND // this is causing issue
      val regions1 = arrayOf(CellRangeAddress.valueOf("A1:D10"))   
      sheetCF.addConditionalFormatting(regions1, rule1)

Due to fillPattern, white color is applied as foreground. But if any other fillPattern is used , then it's working. Example: fill1.fillPattern = PatternFormatting.BIG_SPOTS

But I need to apply solid patter. Any help would be appreciate in it.

Upvotes: 0

Views: 778

Answers (2)

Axel Richter
Axel Richter

Reputation: 61860

In PatternFormatting the solid background fill really needs to be set using PatternFormatting.setFillBackgroundColor also for a solid pattern fill. This is different from CellStyle where the fill foreground color needs to be set for solid pattern fill. The fill foreground color is the color of the pattern and the fill background color is the color behind the pattern.

Let's have a complete example to show this and which is able serving both XSSFas well as HSSF.

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.xssf.usermodel.*;

import org.apache.poi.ss.util.CellRangeAddress;

import java.io.FileOutputStream;

public class CreateExcelConditionalFormatting {

 public static void main(String[] args) throws Exception {
  //Workbook workbook = new HSSFWorkbook(); String filePath = "./CreateExcelConditionalFormatting.xls";
  Workbook workbook = new XSSFWorkbook(); String filePath = "./CreateExcelConditionalFormatting.xlsx";

  Sheet sheet = workbook.createSheet();
  SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

  ConditionalFormattingRule rule = sheetCF.createConditionalFormattingRule("MOD(ROW(), 2) = 0");
  PatternFormatting fill = rule.createPatternFormatting();
  if (fill instanceof XSSFPatternFormatting) {
   fill.setFillBackgroundColor(new XSSFColor(new java.awt.Color(245,245,245),new DefaultIndexedColorMap()));
  } else if (fill instanceof HSSFPatternFormatting){
   HSSFPalette palette = ((HSSFWorkbook)workbook).getCustomPalette();
   HSSFColor hssfColor = palette.findColor((byte)245, (byte)245, (byte)245); 
   if (hssfColor == null) {
    palette.setColorAtIndex((short)63, (byte)245, (byte)245, (byte)245);
    hssfColor = palette.getColor((short)63);
   }
   fill.setFillBackgroundColor(hssfColor);
  }
  fill.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

  ConditionalFormattingRule[] cfRules = new ConditionalFormattingRule[]{rule};

  CellRangeAddress[] regions = new CellRangeAddress[]{CellRangeAddress.valueOf("A1:D10")};

  sheetCF.addConditionalFormatting(regions, cfRules);

  FileOutputStream out = new FileOutputStream(filePath);
  workbook.write(out);
  out.close();
  workbook.close();

 }
}

Upvotes: 1

XtremeBaumer
XtremeBaumer

Reputation: 6435

Contrary to how you usually color the cell, with XSSFConditionalFormattingRule you actually have to use setFillBackgroundColor() instead of setFillForegroundColor().

XSSFConditionalFormattingRule rule = sheet.getSheetConditionalFormatting().createConditionalFormattingRule("MOD(ROW(), 2) = 0");
XSSFPatternFormatting fill = rule.createPatternFormatting();
fill.setFillBackgroundColor(IndexedColors.BLUE.getIndex());
fill.setFillPattern(FillPatternType.SOLID_FOREGROUND.getCode());
CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:Z" + (sheet.getLastRowNum() + 1)) };

sheet.getSheetConditionalFormatting().addConditionalFormatting(regions, rule);

Upvotes: 2

Related Questions