Method Details

• getAppliesTo

  IRange getAppliesTo()

  Gets the IRange object that specifies the cell range to which the formatting rule is applied.

  Use setAppliesTo(IRange) to change the range associated with this conditional formatting rule.

  
   worksheet.getRange("A1:B5").setValue(new Object[][] {{1, 2}, {0, 1}, {0, 0}, {0, 3}, {4, 5}});
   IFormatCondition condition = (IFormatCondition) worksheet.getRange("B1:B5")
       .getFormatConditions().add(FormatConditionType.Expression, FormatConditionOperator.None, "=A1", null);
   condition.getInterior().setColor(Color.GetYellow());
   IRange appliesTo = condition.getAppliesTo();
   

  Returns:

  The IRange object that specifies the cell range to which the formatting rule is applied.

• setAppliesTo

  void setAppliesTo(IRange value)

  Sets the cell range to which the formatting rule is applied.

  Use this method to change the IRange associated with the current conditional formatting rule.

  
   worksheet.getRange("A1:B5").setValue(new Object[][] {{1, 2}, {0, 1}, {0, 0}, {0, 3}, {4, 5}});
   IFormatCondition condition = (IFormatCondition) worksheet.getRange("B1:B5")
       .getFormatConditions().add(FormatConditionType.Expression, FormatConditionOperator.None, "=A1", null);
   condition.getInterior().setColor(Color.GetYellow());
   condition.setAppliesTo(worksheet.getRange("A1:A5"));
   

  Parameters:

  value - The IRange object to which this formatting rule is applied. Must not be null.

• getBorders

  IBorders getBorders()

  Returns an instance of IBorders which represents the borders of this conditional format.

  Use the returned border collection to access or modify the border settings stored on the conditional formatting rule.

  
   worksheet.getRange("A1:B5").setValue(new Object[][] {{1, 2}, {0, 1}, {0, 0}, {0, 3}, {4, 5}});
   IFormatCondition condition = (IFormatCondition) worksheet.getRange("B1:B5")
       .getFormatConditions().add(FormatConditionType.Expression, FormatConditionOperator.None, "=A1", null);
   condition.getInterior().setColor(Color.GetYellow());
   IBorders borders = condition.getBorders();
   borders.setColor(Color.GetRed());
   

  Returns:

  An instance of IBorders that represents the borders of this conditional format.

• getDateOperator

  TimePeriods getDateOperator()

  Gets the TimePeriods date operator used in the format condition.

  For a time period conditional formatting rule, this value indicates the relative date period used by the rule.

  
   worksheet.getRange("A1").setFormula("=TODAY()-1");
   worksheet.getRange("B1").setFormula("=TODAY()");
   worksheet.getRange("C1").setFormula("=TODAY()+1");
   IFormatCondition condition = (IFormatCondition) worksheet.getRange("A1:C1")
       .getFormatConditions().add(FormatConditionType.TimePeriod, FormatConditionOperator.None, null, null);
   condition.getInterior().setColor(Color.GetYellow());
   condition.setDateOperator(TimePeriods.Today);
   TimePeriods dateOperator = condition.getDateOperator();
   

  Returns:

  The TimePeriods date operator used in the format condition.

• setDateOperator

  void setDateOperator(TimePeriods value)

  Sets the TimePeriods date operator used in the format condition.

  Use this method with a conditional formatting rule whose type is FormatConditionType.TimePeriod to specify which relative date period the rule evaluates, such as today, tomorrow, or the last 7 days.

  
   worksheet.getRange("A1").setFormula("=TODAY()-1");
   worksheet.getRange("B1").setFormula("=TODAY()");
   worksheet.getRange("C1").setFormula("=TODAY()+1");
   IFormatCondition condition = (IFormatCondition) worksheet.getRange("A1:C1")
       .getFormatConditions().add(FormatConditionType.TimePeriod, FormatConditionOperator.None, null, null);
   condition.getInterior().setColor(Color.GetYellow());
   condition.setDateOperator(TimePeriods.Today);
   

  Parameters:

  value - The TimePeriods value that specifies the date period to use in the format condition.

• getFont

  IFont getFont()

  Gets an instance of IFont which represents the font of this conditional format.

  Use the returned font object to configure font settings that are applied when the conditional formatting rule is triggered.

  
   worksheet.getRange("A1:B5").setValue(new Object[][] {{1, 2}, {0, 1}, {0, 0}, {0, 3}, {4, 5}});
   IFormatCondition condition = (IFormatCondition) worksheet.getRange("B1:B5")
       .getFormatConditions().add(FormatConditionType.Expression, FormatConditionOperator.None, "=A1", null);
   IFont font = condition.getFont();
   font.setBold(true);
   

  Returns:

  The IFont instance that represents the font of this conditional format.

• getFormula1

  Object getFormula1()

  Returns the first formula of this conditional format.

  The returned value represents the first comparison value or expression associated with the conditional formatting rule. Depending on the rule type, it can be a constant value, a string value, a cell reference, or a formula.

  
   worksheet.getRange("A1:B5").setValue(new Object[][] {{1, 2}, {0, 1}, {0, 0}, {0, 3}, {4, 5}});
   IFormatCondition condition = (IFormatCondition) worksheet.getRange("B1:B5")
       .getFormatConditions().add(FormatConditionType.Expression, FormatConditionOperator.None, "=A1>0", null);
   condition.getInterior().setColor(Color.GetYellow());
   Object formula1 = condition.getFormula1();
   

  Returns:

  The first formula, value, cell reference, or expression associated with this conditional format.

• setFormula1

  void setFormula1(Object value)

  Sets the first formula of this conditional format.

  The set value represents the first comparison value or expression associated with the conditional formatting rule. Depending on the rule type, it can be a constant value, a string, a cell reference, or a formula.

  
   worksheet.getRange("A1:B5").setValue(new Object[][] {{1, 2}, {0, 1}, {0, 0}, {0, 3}, {4, 5}});
   IFormatCondition condition = (IFormatCondition) worksheet.getRange("B1:B5")
       .getFormatConditions().add(FormatConditionType.Expression, FormatConditionOperator.None, "=A1>0", null);
   condition.getInterior().setColor(Color.GetYellow());
   condition.setFormula1("=A1>0");
   

  Parameters:

  value - The first formula, value, cell reference, or expression associated with this conditional format.

• getFormula2

  Object getFormula2()

  Returns the second formula of this conditional format.

  This property is used by conditional formatting rules that store two formulas, such as FormatConditionType.CellValue rules that use the FormatConditionOperator.Between or FormatConditionOperator.NotBetween operator.

  
   worksheet.getRange("A1:A5").setValue(new Object[][] {{1}, {3}, {5}, {7}, {9}});
   IFormatCondition condition = (IFormatCondition) worksheet.getRange("A1:A5")
       .getFormatConditions().add(FormatConditionType.CellValue, FormatConditionOperator.Between, "2", "8");
   condition.getInterior().setColor(Color.GetYellow());
   Object formula2 = condition.getFormula2();
   

  Returns:

  The second formula of this conditional format. Returns null if the rule does not use a second formula.

• setFormula2

  void setFormula2(Object value)

  Sets the second formula of this conditional format.

  This property is used by conditional formatting rules that store two formulas, such as FormatConditionType.CellValue rules that use the FormatConditionOperator.Between or FormatConditionOperator.NotBetween operator.

  
   worksheet.getRange("A1:A5").setValue(new Object[][] {{1}, {3}, {5}, {7}, {9}});
   IFormatCondition condition = (IFormatCondition) worksheet.getRange("A1:A5")
       .getFormatConditions().add(FormatConditionType.CellValue, FormatConditionOperator.Between, "2", "8");
   condition.getInterior().setColor(Color.GetYellow());
   condition.setFormula2(100);
   

  Parameters:

  value - The second formula of this conditional format. Sets null if the rule does not use a second formula.

• getInterior

  IInterior getInterior()

  Returns an instance of IInterior which represents the background color of this conditional format.

  Use the returned IInterior object to read or modify the fill settings applied when the conditional formatting rule is in effect.

  
   worksheet.getRange("A1:B5").setValue(new Object[][] {{1, 2}, {0, 1}, {0, 0}, {0, 3}, {4, 5}});
   IFormatCondition condition = (IFormatCondition) worksheet.getRange("B1:B5")
       .getFormatConditions().add(FormatConditionType.Expression, FormatConditionOperator.None, "=A1", null);
   IInterior interior = condition.getInterior();
   interior.setColor(Color.GetRed());
   

  Returns:

  An IInterior instance that represents the background color of this conditional format.

• getNumberFormat

  String getNumberFormat()

  Gets the number format applied to a cell if the conditional formatting rule evaluates to true.

  Returns the format string assigned through setNumberFormat(String) for this conditional formatting rule.

  
   worksheet.getRange("A1:A3").setValue(new Object[][] {{85}, {95}, {105}});
   IFormatCondition condition = (IFormatCondition) worksheet.getRange("A1:A3")
       .getFormatConditions().add(FormatConditionType.CellValue, FormatConditionOperator.Greater, "90", null);
   condition.getInterior().setColor(Color.GetYellow());
   condition.setNumberFormat("0.00");
   String numberFormat = condition.getNumberFormat();
   

  Returns:

  The number format applied to a cell when the conditional formatting rule evaluates to true.

• setNumberFormat

  void setNumberFormat(String value)

  Sets the number format applied to a cell if the conditional formatting rule evaluates to true.

  
   worksheet.getRange("A1:A3").setValue(new Object[][] {{85}, {95}, {105}});
   IFormatCondition condition = (IFormatCondition) worksheet.getRange("A1:A3")
       .getFormatConditions().add(FormatConditionType.CellValue, FormatConditionOperator.Greater, "90", null);
   condition.getInterior().setColor(Color.GetYellow());
   condition.setNumberFormat("0.00");
   

  Parameters:

  value - The number format applied to a cell when the conditional formatting rule evaluates to true.

• getOperator

  FormatConditionOperator getOperator()

  Returns the operator of this conditional format.

  The operator specifies how the conditional formatting rule compares the cell value with the rule's formula values. For example, it can indicate comparisons such as equal, greater than, or between.

  
   worksheet.getRange("A1:A3").setValue(new Object[][] {{1}, {3}, {5}});
   IFormatCondition condition = (IFormatCondition) worksheet.getRange("A1:A3")
       .getFormatConditions().add(FormatConditionType.CellValue, FormatConditionOperator.Between, "2", "4");
   condition.getInterior().setColor(Color.GetYellow());
   FormatConditionOperator operator = condition.getOperator();
   

  Returns:

  The operator of this conditional format.

• setOperator

  void setOperator(FormatConditionOperator value)

  Sets the operator of this conditional format.

  Use this method to change the comparison operator that the conditional formatting rule uses.

  
   IRange range = worksheet.getRange("A1:A5");
   range.setValue(new Object[][] {{10}, {30}, {50}, {70}, {90}});
   IFormatCondition condition = (IFormatCondition) range.getFormatConditions().add(
       FormatConditionType.CellValue, FormatConditionOperator.Between, 30, 70);
   condition.getInterior().setColor(Color.GetYellow());
   condition.setOperator(FormatConditionOperator.NotBetween);
   

  Parameters:

  value - The conditional formatting operator to apply.

• getPriority

  int getPriority()

  Returns the priority value of the conditional formatting rule. The priority determines the order of evaluation when multiple conditional formatting rules exist in a worksheet.

  A lower priority value indicates that the rule is evaluated earlier. Use setPriority(int), setFirstPriority(), or setLastPriority() to change the evaluation order.

  
   worksheet.getRange("A1:B5").setValue(new Object[][] {{1, 2}, {0, 1}, {0, 0}, {0, 3}, {4, 5}});
   IFormatCondition condition = (IFormatCondition) worksheet.getRange("B1:B5")
       .getFormatConditions().add(FormatConditionType.Expression, FormatConditionOperator.None, "=A1", null);
   condition.getInterior().setColor(Color.GetYellow());
   int priority = condition.getPriority();
   

  Returns:

  The priority value of the conditional formatting rule.

• setPriority

  void setPriority(int value)

  Sets the priority value of the conditional formatting rule. The priority determines the order of evaluation when multiple conditional formatting rules exist in a worksheet.

  
   worksheet.getRange("A1:B5").setValue(new Object[][] {{1, 2}, {0, 1}, {0, 0}, {0, 3}, {4, 5}});
   IFormatCondition condition = (IFormatCondition) worksheet.getRange("B1:B5")
       .getFormatConditions().add(FormatConditionType.Expression, FormatConditionOperator.None, "=A1", null);
   condition.getInterior().setColor(Color.GetYellow());
   condition.setPriority(1);
   

  Parameters:

  value - The priority value of the conditional formatting rule.

• getStopIfTrue

  boolean getStopIfTrue()

  Gets a Boolean value that determines if additional formatting rules on the cell should be evaluated if the current rule evaluates to true.

  If this method returns true, lower-priority formatting rules on the same cell are not evaluated after the current rule evaluates to true.

  
   worksheet.getRange("A1:B5").setValue(new Object[][] {{1, 2}, {0, 1}, {0, 0}, {0, 3}, {4, 5}});
   IFormatCondition condition = (IFormatCondition) worksheet.getRange("B1:B5")
       .getFormatConditions().add(FormatConditionType.Expression, FormatConditionOperator.None, "=A1>0", null);
   condition.getInterior().setColor(Color.GetYellow());
   condition.setStopIfTrue(true);
   boolean stopIfTrue = condition.getStopIfTrue();
   

  Returns:

  true if additional formatting rules on the cell should not be evaluated after the current rule evaluates to true; otherwise, false.

• setStopIfTrue

  void setStopIfTrue(boolean value)

  Sets a Boolean value that determines if additional formatting rules on the cell should be evaluated if the current rule evaluates to true.

  If this method Sets true, lower-priority formatting rules on the same cell are not evaluated after the current rule evaluates to true.

  
   worksheet.getRange("A1:B5").setValue(new Object[][] {{1, 2}, {0, 1}, {0, 0}, {0, 3}, {4, 5}});
   IFormatCondition condition = (IFormatCondition) worksheet.getRange("B1:B5")
       .getFormatConditions().add(FormatConditionType.Expression, FormatConditionOperator.None, "=A1>0", null);
   condition.getInterior().setColor(Color.GetYellow());
   condition.setStopIfTrue(true);
   

  Parameters:

  value - true if additional formatting rules on the cell should not be evaluated after the current rule evaluates to true; otherwise, false.

• getText

  String getText()

  Returns a string value that specifies the text used by the conditional formatting rule.

  Use this method to configure the text for a text-based conditional formatting rule. This value works together with getTextOperator() to define how the rule searches for matching text.

  
   worksheet.getRange("A1:A3").setValue(new Object[][] {{"Pending"}, {"Done"}, {"Pending Review"}});
   IFormatCondition condition = (IFormatCondition) worksheet.getRange("A1:A3")
       .getFormatConditions().add(FormatConditionType.TextString, FormatConditionOperator.None, null, null);
   condition.getInterior().setColor(Color.GetYellow());
   condition.setText("Pending");
   String text = condition.getText();
   

  Returns:

  The text used by the conditional formatting rule.

• setText

  void setText(String value)

  Sets a string value that specifies the text used by the conditional formatting rule.

  Use this method to configure the text for a text-based conditional formatting rule. This value works together with getTextOperator() to define how the rule searches for matching text.

  
   worksheet.getRange("A1:A3").setValue(new Object[][] {{"Pending"}, {"Done"}, {"Pending Review"}});
   IFormatCondition condition = (IFormatCondition) worksheet.getRange("A1:A3")
       .getFormatConditions().add(FormatConditionType.TextString, FormatConditionOperator.None, null, null);
   condition.getInterior().setColor(Color.GetYellow());
   condition.setText("Pending");
   

  Parameters:

  value - The text used by the conditional formatting rule.

• getTextOperator

  ContainsOperator getTextOperator()

  Returns one of the constants of the ContainsOperator enumeration, specifying the text search performed by the conditional formatting rule.

  Use this method with a FormatConditionType.TextString conditional formatting rule to determine whether the rule checks whether cell text contains, does not contain, begins with, or ends with the specified text.

  
   worksheet.getRange("A1:A4").setValue(new Object[][] {{"Apple"}, {"Pear"}, {"Apricot"}, {"Plum"}});
   IFormatCondition condition = (IFormatCondition) worksheet.getRange("A1:A4")
       .getFormatConditions().add(FormatConditionType.TextString);
   condition.getInterior().setColor(Color.GetYellow());
   condition.setText("Ap");
   condition.setTextOperator(ContainsOperator.BeginsWith);
   ContainsOperator textOperator = condition.getTextOperator();
   

  Returns:

  One of the ContainsOperator constants that specifies the text search performed by the conditional formatting rule.

• setTextOperator

  void setTextOperator(ContainsOperator value)

  Sets one of the constants of the ContainsOperator enumeration, specifying the text search performed by the conditional formatting rule.

  Use this method with a FormatConditionType.TextString conditional formatting rule to control whether the rule checks if cell text contains or does not contain the specified text, or begins or ends with it.

  
   worksheet.getRange("A1:A4").setValue(new Object[][] {{"Apple"}, {"Pear"}, {"Apricot"}, {"Plum"}});
   IFormatCondition condition = (IFormatCondition) worksheet.getRange("A1:A4")
       .getFormatConditions().add(FormatConditionType.TextString);
   condition.getInterior().setColor(Color.GetYellow());
   condition.setText("Ap");
   condition.setTextOperator(ContainsOperator.BeginsWith);
   

  Parameters:

  value - One of the ContainsOperator constants that specifies the text search performed by the conditional formatting rule. null is not supported.

• getType

  FormatConditionType getType()

  Returns the type of this conditional format.

  The returned value is one of the constants in FormatConditionType and indicates which kind of conditional formatting rule this object represents.

  
   worksheet.getRange("A1:B5").setValue(new Object[][] {{1, 2}, {0, 1}, {0, 0}, {0, 3}, {4, 5}});
   IFormatCondition condition = (IFormatCondition) worksheet.getRange("B1:B5")
       .getFormatConditions().add(FormatConditionType.Expression, FormatConditionOperator.None, "=A1", null);
   condition.getInterior().setColor(Color.GetYellow());
   FormatConditionType type = condition.getType();
   

  Returns:

  The type of this conditional format.

• delete

  void delete()

  Deletes this conditional format.

  Use this method to remove the current conditional formatting rule from the worksheet.

  
   worksheet.getRange("A1:B5").setValue(new Object[][] {{1, 2}, {0, 1}, {0, 0}, {0, 3}, {4, 5}});
   IFormatCondition condition = (IFormatCondition) worksheet.getRange("B1:B5")
       .getFormatConditions().add(FormatConditionType.Expression, FormatConditionOperator.None, "=A1", null);
   condition.getInterior().setColor(Color.GetYellow());
   condition.delete();
   

• setFirstPriority

  void setFirstPriority()

  Sets the priority value for this conditional formatting rule to 1 so that it will be evaluated before all other rules on the worksheet.

  Use this method to move the current conditional formatting rule to the highest evaluation priority in the worksheet.

  
   IRange range = worksheet.getRange("A1:A5");
   range.setValue(new Object[][] {{10}, {30}, {50}, {70}, {90}});
   IFormatCondition firstCondition = (IFormatCondition) range.getFormatConditions().add(FormatConditionType.CellValue, FormatConditionOperator.Greater, 60, null);
   firstCondition.getInterior().setColor(Color.GetYellow());
   IFormatCondition condition = (IFormatCondition) range.getFormatConditions().add(
       FormatConditionType.CellValue, FormatConditionOperator.Between, 30, 70);
   condition.getInterior().setColor(Color.GetLightBlue());
   condition.setFirstPriority();
   

• setLastPriority

  void setLastPriority()

  Sets the evaluation order for this conditional formatting rule so it is evaluated after all other rules on the worksheet.

  Use this method to move the current rule to the lowest evaluation priority among the conditional formatting rules on the worksheet.

  
   worksheet.getRange("A1:B5").setValue(new Object[][] {
       {10, 5}, {30, 15}, {20, 25}, {50, 35}, {40, 45}
   });
   IFormatCondition firstCondition = (IFormatCondition) worksheet.getRange("A1:A5")
       .getFormatConditions().add(FormatConditionType.CellValue, FormatConditionOperator.Greater, 25, null);
   firstCondition.getInterior().setColor(Color.GetYellow());
   IFormatCondition secondCondition = (IFormatCondition) worksheet.getRange("B1:B5")
       .getFormatConditions().add(FormatConditionType.CellValue, FormatConditionOperator.Less, 30, null);
   secondCondition.getInterior().setColor(Color.GetLightBlue());
   secondCondition.setLastPriority();
   

• fromJson

  void fromJson(String json)

  Generates the conditional format from the json string.

  Use this method to restore a conditional formatting rule from a JSON string, such as one previously generated by toJson().

  
   worksheet.getRange("A1:A5").setValue(new Object[][] {{1}, {2}, {3}, {4}, {5}});
   String json = "{\"ranges\":[{\"row\":0,\"col\":1,\"rowCount\":5,\"colCount\":1}],\"priority\":1,\"ruleType\":1,\"operator\":2,\"value1\":\"3\",\"value2\":\"\"}";
   IFormatCondition condition = (IFormatCondition) worksheet.getRange("B1:B5")
       .getFormatConditions().add(FormatConditionType.Expression, FormatConditionOperator.None, "=TRUE", null);
   condition.fromJson(json);
   condition.getInterior().setColor(Color.GetYellow());
   

  Parameters:

  json - The JSON string that defines the conditional format.

• toJson

  String toJson()

  Generates a JSON string from the conditional format.

  The returned string represents the current conditional formatting rule and can be used with fromJson(String) to recreate the rule.

  
   IRange range = worksheet.getRange("A1:A5");
   range.setValue(new Object[][] {{10}, {30}, {50}, {70}, {90}});
   IFormatCondition condition = (IFormatCondition) range.getFormatConditions().add(
       FormatConditionType.CellValue, FormatConditionOperator.Greater, 60, null);
   condition.getInterior().setColor(Color.GetYellow());
   String json = condition.toJson();
   

  Returns:

  The conditional format JSON string.