Conditional Formatting applied to an Excel sheet using Java, is not applied to the cell even after refreshing them

Question

I have been trying to do conditional formatting for a specific range of Excel cells using Java, Apache poi. For cells having TRUE or FALSE values the background should be set to a particular color based on the given rules. Same code worked for numbers when valuebasedformatting was applied while writing the file. Until I refresh each cell by selecting them and double clicking , the changes are not applied. Is there any way to refresh the entire sheet using Java?

Have tried using evaluateAllFormulaCells() function as XSSFFormulaEvaluator.evaluateAllFormulaCells(workbook);

But that also failed to make any changes. The conditional formatting method is as follows :

SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

              ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(CFRuleRecord.ComparisonOperator.EQUAL, "FALSE");
              PatternFormatting fill1 = rule1.createPatternFormatting();
              fill1.setFillBackgroundColor(IndexedColors.RED.index);
              fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);


              ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(CFRuleRecord.ComparisonOperator.EQUAL, "TRUE");
              PatternFormatting fill2 = rule2.createPatternFormatting();
              fill2.setFillBackgroundColor(IndexedColors.GREEN.index);
              fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

           CellRangeAddress[] regions = {
                   CellRangeAddress.valueOf("AP1:BH47")
           };

           sheetCF.addConditionalFormatting(regions, rule1, rule2);

Axel Richter Axel Richter · Accepted Answer · 2019-03-20T07:29:35

I suspect your "TRUE" and "FALSE" cell values in Excel are not really the Boolean cell values TRUE and FALSE but string cell values. But your rules only check for Boolean cell values.

The first part of the following code (conditional formatting rules in A1:A4) shows the problem. The second part (conditional formatting rules in C1:C4) shows how a formula conditional formatting rule could be used to check both, Boolean TRUE / FALSE as well as the strings "TRUE" / "FALSE".

import java.io.FileOutputStream;

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

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

public class ConditionalFormattingBooleanValues {

 public static void main(String[] args) throws Exception {
  //Workbook workbook = new HSSFWorkbook();
  Workbook workbook = new XSSFWorkbook();

  Sheet sheet = workbook.createSheet();

  sheet.createRow(0).createCell(0).setCellValue(true); //boolean value TRUE in A1
  sheet.getRow(0).createCell(2).setCellValue(true); //boolean value TRUE in C1
  sheet.createRow(1).createCell(0).setCellValue(false); //boolean value FALSE in A2
  sheet.getRow(1).createCell(2).setCellValue(false); //boolean value FALSE in C2
  sheet.createRow(2).createCell(0).setCellValue("TRUE"); //text value "TRUE" in A3
  sheet.getRow(2).createCell(2).setCellValue("TRUE"); //text value "TRUE" in C3
  sheet.createRow(3).createCell(0).setCellValue("FALSE"); //text value "FALSE" in A4
  sheet.getRow(3).createCell(2).setCellValue("FALSE"); //text value "FALSE" in C4

  SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

  ConditionalFormattingRule rule1;
  ConditionalFormattingRule rule2;
  PatternFormatting fill;
  ConditionalFormattingRule[] cfRules;
  CellRangeAddress[] regions;

  // check only boolean values in rules in A1:A4
  rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.EQUAL, "FALSE");
  fill = rule1.createPatternFormatting();
  fill.setFillBackgroundColor(IndexedColors.RED.index);
  fill.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
  rule2 = sheetCF.createConditionalFormattingRule(ComparisonOperator.EQUAL, "TRUE");
  fill = rule2.createPatternFormatting();
  fill.setFillBackgroundColor(IndexedColors.GREEN.index);
  fill.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
  cfRules = new ConditionalFormattingRule[]{rule1, rule2};
  regions = new CellRangeAddress[]{CellRangeAddress.valueOf("A1:A4")};
  sheetCF.addConditionalFormatting(regions, cfRules);

  // check boolean and text values in rules in C1:C4
  String formula1 = 
   (workbook instanceof HSSFWorkbook)?"OR(INDIRECT(\"C\"&ROW())=FALSE,INDIRECT(\"C\"&ROW())=\"FALSE\")":"OR(C1=FALSE,C1=\"FALSE\")";
  rule1 = sheetCF.createConditionalFormattingRule(formula1);
  fill = rule1.createPatternFormatting();
  fill.setFillBackgroundColor(IndexedColors.RED.index);
  fill.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
  String formula2 = 
   (workbook instanceof HSSFWorkbook)?"OR(INDIRECT(\"C\"&ROW())=TRUE,INDIRECT(\"C\"&ROW())=\"TRUE\")":"OR(C1=TRUE,C1=\"TRUE\")";
  rule2 = sheetCF.createConditionalFormattingRule(formula2);
  fill = rule2.createPatternFormatting();
  fill.setFillBackgroundColor(IndexedColors.GREEN.index);
  fill.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
  cfRules = new ConditionalFormattingRule[]{rule1, rule2};
  regions = new CellRangeAddress[]{CellRangeAddress.valueOf("C1:C4")};
  sheetCF.addConditionalFormatting(regions, cfRules);

  String fileout = (workbook instanceof HSSFWorkbook)?"ConditionalFormattingBooleanValues.xls":"ConditionalFormattingBooleanValues.xlsx";
  FileOutputStream out = new FileOutputStream(fileout);
  workbook.write(out);
  out.close();
  workbook.close();

 }
}

Conditional Formatting applied to an Excel sheet using Java, is not applied to the cell even after refreshing them

1 Answers