Difference between revisions of "Documentation/DevGuide/Spreadsheets/Conditional Formats"

Revision as of 20:55, 3 July 2018

A cell can be formatted automatically with a conditional format, depending on its contents or the result of a formula. A conditional format consists of several condition entries that contain the condition and name of a cell style. The style of the first met condition, true or "not zero", is applied to the cell.

TableConditionalFormats

A cell or cell range object contains the properties ConditionalFormat and ConditionalFormatLocal. These properties return the interface com.sun.star.sheet.XSheetConditionalEntries of the conditional format container com.sun.star.sheet.TableConditionalFormat. The objects of both properties are equal, except for the representation of formulas. The ConditionalFormatLocal property uses function names in the current language.

After a conditional format is changed, it has to be reinserted into the property set of the cell or cell range.

A condition entry of a conditional format is represented by the service com.sun.star.sheet.TableConditionalEntry. It implements two interfaces:

• The interface com.sun.star.sheet.XSheetCondition gets and sets the operator, the first and second formula and the base address for relative references.
• The interface com.sun.star.sheet.XSheetConditionalEntry gets and sets the cell style name.

The service com.sun.star.sheet.TableConditionalFormat contains all format conditions and returns com.sun.star.sheet.TableConditionalEntry objects. The interface com.sun.star.sheet.XSheetConditionalEntries inserts new conditions and removes them.

• The method addNew() inserts a new condition. It expects a sequence of com.sun.star.beans.PropertyValue objects. The following properties are supported:
  • Operator: A com.sun.star.sheet.ConditionOperator constant describing the operation to perform.
  • Formula1 and Formula2: Strings that contain the values or formulas to evaluate. Formula2 is used only if the property Operator contains BETWEEN or NOT_BETWEEN.
  • SourcePosition: A com.sun.star.table.CellAddress struct that contains the base address for relative cell references in formulas.
  • StyleName: The name of the cell style to apply.
• The methods removeByIndex() removes the condition entry at the specified position.
• The method clear() removes all condition entries.

The following example applies a conditional format to a cell range. It uses the cell style "MyNewCellStyle" that is applied to each cell containing a value greater than 1. The xSheet is the com.sun.star.sheet.XSpreadsheet interface of a spreadsheet.

 // get the conditional format object of the cell range
 com.sun.star.table.XCellRange xCellRange = xSheet.getCellRangeByName("A1:B10");
 com.sun.star.beans.XPropertySet xPropSet = (com.sun.star.beans.XPropertySet)
     UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, xCellRange);
 com.sun.star.sheet.XSheetConditionalEntries xEntries =
     (com.sun.star.sheet.XSheetConditionalEntries) xPropSet.getPropertyValue("ConditionalFormat");
 
 // create a condition and apply it to the range
 com.sun.star.beans.PropertyValue[] aCondition = new com.sun.star.beans.PropertyValue[3];
 aCondition[0] = new com.sun.star.beans.PropertyValue();
 aCondition[0].Name = "Operator";
 aCondition[0].Value = com.sun.star.sheet.ConditionOperator.GREATER;
 aCondition[1] = new com.sun.star.beans.PropertyValue();
 aCondition[1].Name = "Formula1";
 aCondition[1].Value = "1";
 aCondition[2] = new com.sun.star.beans.PropertyValue();
 aCondition[2].Name = "StyleName";
 aCondition[2].Value = "MyNewCellStyle";
 xEntries.addNew(aCondition);
 xPropSet.setPropertyValue("ConditionalFormat", xEntries);