CODEWITHSUNDEEP
javaexcelapache-poi
reiley
reiley

Reputation: 3761

Apache poi ConditionalFormatting not working properly

I'm using apache poi to create excels in my java application.

My use case is when the value in A1 is Change it.

Style in cells A10 to A14 will be changed.

For this I'm following feature of ConditionalFormatting provided by poi.

But the style is only getting applied to cell A10 & not till A14.

What am I missing?

Code:

private void addValidations(Sheet sheet) {
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("=A1=\"Change it\"");
    FontFormatting fontFmt = rule1.createFontFormatting();
    fontFmt.setFontStyle(true, false);
    fontFmt.setFontColorIndex(IndexedColors.YELLOW.index);
    BorderFormatting bordFmt = rule1.createBorderFormatting();
    bordFmt.setBorderBottom(BorderStyle.THIN);
    bordFmt.setBorderTop(BorderStyle.THICK);
    bordFmt.setBorderLeft(BorderStyle.DASHED);
    bordFmt.setBorderRight(BorderStyle.DOTTED);
    ConditionalFormattingRule [] cfRules =
            {
                    rule1
            };
    CellRangeAddress[] regions = {
            CellRangeAddress.valueOf("A10:A14")
    };
    sheetCF.addConditionalFormatting(regions, cfRules);
}

Upvotes: 0

Views: 1069

Answers (2)

Axel Richter
Axel Richter

Reputation: 61852

The code sheetCF.createConditionalFormattingRule("=A1=\"Change it\""); cannot work at all. In HSSF it throws org.apache.poi.ss.formula.FormulaParseException: The specified formula '=A1="Change it"' starts with an equals sign which is not allowed.. In XSSF it creates a corrupt *.xlsx file. The leading equals sign is not stored in Excel formula cells. t only is shown in Excel's GUI. So it would must be sheetCF.createConditionalFormattingRule("A1=\"Change it\"");.

And the formula =A1="Change it" is relative in column letter as well as in row number. So applied to cell A10 it means =A1="Change it". But applied to cell A11 it means =A2="Change it". And applied to cell A12 it means =A3="Change it" and so on. So if the need is changing all font colors in A10:A14 if content of A1 changes, then the reference in the formula would must be fixed using $. So it would must be sheetCF.createConditionalFormattingRule("A$1=\"Change it\"");.

Complete Example:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

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

import java.io.FileOutputStream;

public class ConditionalFormatting {

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

  Sheet sheet = workbook.createSheet();

  for (int r = 9; r < 14; r++) {
   sheet.createRow(r).createCell(0).setCellValue("Text in Cell A" + (r+1));
  }

  SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

  ConditionalFormattingRule rule = sheetCF.createConditionalFormattingRule("A$1=\"Change it\"");
  FontFormatting fontFormatting = rule.createFontFormatting();
  fontFormatting.setFontStyle(false, true);
  fontFormatting.setFontColorIndex(IndexedColors.YELLOW.index);

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

  CellRangeAddress[] regions = new CellRangeAddress[]{CellRangeAddress.valueOf("A10:A14")};

  sheetCF.addConditionalFormatting(regions, cfRules);

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

 }
}

This changes font color in A10:A14 to yellow if cell content in A1 is Change it.

Upvotes: 1

Wils Mils
Wils Mils

Reputation: 633

Try this if this works.

CellRangeAddress[] regions = new CellRangeAddress[]{ CellRangeAddress.valueOf("A10:A14") };

Upvotes: 0

Related Questions