Method Details

• getCount

  int getCount()

  Gets the number of objects in the collection.

  This collection contains the conditional formats associated with a range.

  
   IRange range = worksheet.getRange("A1:A5");
   range.setValue(new Object[][] {{1}, {3}, {5}, {7}, {9}});
   IFormatCondition condition = (IFormatCondition)range.getFormatConditions().add(FormatConditionType.CellValue, FormatConditionOperator.Between, 1, 5);
   condition.getInterior().setColor(Color.GetYellow());
   IFormatCondition condition1 = (IFormatCondition)range.getFormatConditions().add(FormatConditionType.Expression, FormatConditionOperator.None, "=A1>6", null);
   condition1.getInterior().setColor(Color.GetLightBlue());
   int count = range.getFormatConditions().getCount();
   

  Returns:

  The number of conditional format objects in the collection.

• get

  Object get(int index)

  Returns the conditional format at the specified zero-based index.

  Use this method to access an existing conditional format in the collection for a range.

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

  Parameters:

  index - The zero-based index of the conditional format in the collection.

  Returns:

  The specified conditional format.

  Throws:

  IllegalArgumentException - if index is less than 0 or greater than or equal to getCount().

• add

  Object add(FormatConditionType type)

  Adds a new conditional format.

  Creates a conditional formatting rule of the specified type and adds it to this collection.

  
   IRange range = worksheet.getRange("A1:A5");
   range.setValue(new Object[][] {{1}, {3}, {5}, {7}, {9}});
   IAboveAverage condition = (IAboveAverage)range.getFormatConditions().add(FormatConditionType.AboveAverageCondition);
   condition.getInterior().setColor(Color.GetYellow());
   

  Parameters:

  type - Specifies the type for the new conditional format.

  Returns:

  The new conditional format.

• add

  Object add(FormatConditionType type, FormatConditionOperator formatConditionOperator, Object formula1, Object formula2)

  Adds a new conditional format.

  Creates a conditional formatting rule of the specified type and adds it to this collection.

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

  Parameters:

  type - Specifies the type for the new conditional format.

  formatConditionOperator - Specifies the operator of the conditional format. For FormatConditionType.CellValue, this value must not be FormatConditionOperator.None.

  formula1 - Specifies the first formula of the conditional format. For FormatConditionType.CellValue, this value must not be null.

  formula2 - Specifies the second formula of the conditional format. For FormatConditionOperator.Between and FormatConditionOperator.NotBetween, this value must not be null.

  Returns:

  The new conditional format.

  Throws:

  IllegalArgumentException - if required arguments are missing for the specified conditional format type.

  UnsupportedOperationException - if formula1 or formula2 uses an unsupported table formula or reference formula.

• addAboveAverage

  IAboveAverage addAboveAverage()

  Returns a new IAboveAverage object representing a conditional formatting rule for the specified range.

  Use the returned object to configure an average-based conditional formatting rule, such as whether cells are evaluated as above or below the average.

  
   IRange range = worksheet.getRange("A1:A5");
   range.setValue(new Object[][] {{1}, {2}, {3}, {4}, {10}});
   IAboveAverage aboveAverage = range.getFormatConditions().addAboveAverage();
   aboveAverage.getInterior().setColor(Color.GetYellow());
   aboveAverage.setAboveBelow(AboveBelow.AboveAverage);
   

  Returns:

  A new IAboveAverage object.

• addColorScale

  IColorScale addColorScale(ColorScaleType colorScaleType)

  Returns a new IColorScale object representing a conditional formatting rule that uses gradations in cell colors to indicate relative differences in the values of cells included in a selected range.

  Use colorScaleType to specify the type of color scale rule to add.

  
   IRange range = worksheet.getRange("A1:A5");
   range.setValue(new Object[][] {{1}, {2}, {3}, {4}, {5}});
   IColorScale colorScale = range.getFormatConditions().addColorScale(ColorScaleType.TwoColorScale);
   colorScale.getColorScaleCriteria().get(0).getFormatColor().setColor(Color.GetRed());
   colorScale.getColorScaleCriteria().get(1).getFormatColor().setColor(Color.GetGreen());
   

  Parameters:

  colorScaleType - The color scale type to add.

  Returns:

  A new IColorScale object.

• addDatabar

  IDataBar addDatabar()

  Returns a IDataBar object representing a data bar conditional formatting rule for the specified range.

  Use the returned rule to configure how data bars visualize relative cell values in the range, such as the bar fill type, color, direction, and whether cell values are displayed.

  
   IRange range = worksheet.getRange("A1:A5");
   range.setValue(new Object[][] {{1}, {2}, {3}, {4}, {5}});
   IDataBar dataBar = range.getFormatConditions().addDatabar();
   dataBar.setBarFillType(DataBarFillType.Solid);
   

  Returns:

  A new IDataBar object.

• addIconSetCondition

  IIconSetCondition addIconSetCondition()

  Returns a new IIconSetCondition object which represents an icon set conditional formatting rule for the specified range.

  Use the returned rule to choose an icon set and configure the threshold criteria that determine which icon is displayed for each cell value.

  
   IRange range = worksheet.getRange("A1:A5");
   range.setValue(new Object[][] {{1}, {2}, {3}, {4}, {5}});
   IIconSetCondition condition = range.getFormatConditions().addIconSetCondition();
   condition.setIconSet(workbook.getIconSets().get(IconSetType.Icon3Symbols));
   condition.getIconCriteria().get(1).setValue(50);
   

  Returns:

  A new IIconSetCondition object.

• addTop10

  ITop10 addTop10()

  Returns a ITop10 object representing a conditional formatting rule for the specified range.

  Use the returned rule to configure whether the conditional formatting evaluates the top or bottom values in the range, and whether the rank is based on a count or a percentage.

  
   IRange range = worksheet.getRange("A1:A5");
   range.setValue(new Object[][] {{1}, {2}, {3}, {4}, {5}});
   ITop10 top10 = range.getFormatConditions().addTop10();
   top10.getInterior().setColor(Color.GetYellow());
   top10.setTopBottom(TopBottom.Top);
   top10.setRank(3);
   

  Returns:

  A new ITop10 object.

• addUniqueValues

  IUniqueValues addUniqueValues()

  Returns a new IUniqueValues object representing a conditional formatting rule for the specified range.

  The returned rule is added to this collection and is configured to evaluate unique values in the represented range.

  
   IRange range = worksheet.getRange("A1:A5");
   range.setValue(new Object[][] {{100}, {100}, {200}, {300}, {300}});
   IUniqueValues condition = range.getFormatConditions().addUniqueValues();
   condition.getInterior().setColor(Color.GetYellow());
   

  Returns:

  The IUniqueValues object for the new conditional formatting rule.

• delete

  void delete()

  Deletes all conditional formats associated with the represented range.

  Use this method to remove every conditional formatting rule from the range's IFormatConditions collection.

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

• fromJson

  void fromJson(String json)

  Generates the conditional formats from the json string.

  This method clears the existing conditional formats in the current range first, and then applies new conditional formats from the specified JSON string.

  The conditional formats are created in the range on which this method is called. Range information contained in the JSON string is not preserved.

  
   IRange range = worksheet.getRange("E2:E7");
   range.setValue(new Object[][] {{67}, {62}, {72}, {66}, {76}, {68}});
   range.getFormatConditions().fromJson(
       "{\"rules\":[{\"ruleType\":1,\"operator\":6,\"stopIfTrue\":true,\"ranges\":[{\"row\":1,\"rowCount\":6,\"col\":4,\"colCount\":1}],\"value1\":\"66\",\"value2\":\"70\"}]}"
   );
   

  Parameters:

  json - The JSON string that defines the conditional formats. null is not supported.

• toJson

  String toJson()

  Generates a JSON string from the conditional formats.

  Use this method to serialize all conditional formatting rules associated with the range so that they can be stored or later restored with fromJson(String).

  
   worksheet.getRange("A1:A5").setValue(new Object[][] {
       {10}, {30}, {50}, {70}, {90}
   });
   IFormatConditions conditions = worksheet.getRange("A1:A5").getFormatConditions();
   ITop10 top10 = conditions.addTop10();
   top10.getInterior().setColor(Color.GetYellow());
   top10.setRank(3);
   String json = conditions.toJson();
   

  Returns:

  The conditional formats JSON string.