aspose.cells

aspose.cells

Class FormatCondition

Represents conditional formatting condition.

Example:

$workbook = new cells\Workbook();
$sheet = $workbook->getWorksheets()->get(0);

//Adds an empty conditional formatting
$index = $sheet->getConditionalFormattings()->add();
$fcs = $sheet->getConditionalFormattings()->get($index);

//Sets the conditional format range
$ca = new cells\CellArea();
$ca->StartRow = 0;
$ca->EndRow = 0;
$ca->StartColumn = 0;
$ca->EndColumn = 0;
$fcs->addArea($ca);

$ca = new cells\CellArea();
$ca->StartRow = 1;
$ca->EndRow = 1;
$ca->StartColumn = 1;
$ca->EndColumn = 1;
$fcs->addArea($ca);

//Adds condition
$conditionIndex = $fcs->addCondition(cells\FormatConditionType::CELL_VALUE, cells\OperatorType::BETWEEN, "=A2", "100");
//Adds condition
$conditionIndex2 = $fcs->addCondition(cells\FormatConditionType::CELL_VALUE, cells\OperatorType::BETWEEN, "50", "100");

//Sets the background color
$fc = $fcs->get($conditionIndex);
$fc->getStyle()->setBackgroundColor(cells\Color::getRed());

$workbook->save(DEST_PATH."res.xlsx");

Property Getters/Setters Summary
functiongetAboveAverage()
Get the conditional formatting's "AboveAverage" instance. The default instance's rule highlights cells that are above the average for all values in the range. Valid only for type = AboveAverage.
functiongetColorScale()
Get the conditional formatting's "ColorScale" instance. The default instance is a "green-yellow-red" 3ColorScale . Valid only for type = ColorScale.
functiongetDataBar()
Get the conditional formatting's "DataBar" instance. The default instance's color is blue. Valid only for type is DataBar.
functiongetFormula1()
function
setFormula1(value)
           Gets and sets the value or expression associated with conditional formatting.
functiongetFormula2()
function
setFormula2(value)
           Gets and sets the value or expression associated with conditional formatting.
functiongetIconSet()
Get the conditional formatting's "IconSet" instance. The default instance's IconSetType is TrafficLights31. Valid only for type = IconSet.
functiongetOperator()
function
setOperator(value)
           Gets and sets the conditional format operator type. The value of the property is OperatorType integer constant.
functiongetPriority()
function
setPriority(value)
           The priority of this conditional formatting rule. This value is used to determine which format should be evaluated and rendered. Lower numeric values are higher priority than higher numeric values, where '1' is the highest priority.
functiongetStopIfTrue()
function
setStopIfTrue(value)
           True, no rules with lower priority may be applied over this rule, when this rule evaluates to true. Only applies for Excel 2007;
functiongetStyle()
function
setStyle(value)
           Gets or setts style of conditional formatted cell ranges.
functiongetText()
function
setText(value)
           The text value in a "text contains" conditional formatting rule. Valid only for type = containsText, notContainsText, beginsWith and endsWith. The default value is null.
functiongetTimePeriod()
function
setTimePeriod(value)
           The applicable time period in a "date occurring…" conditional formatting rule. Valid only for type = timePeriod. The default value is TimePeriodType.Today. The value of the property is TimePeriodType integer constant.
functiongetTop10()
Get the conditional formatting's "Top10" instance. The default instance's rule highlights cells whose values fall in the top 10 bracket. Valid only for type is Top10.
functiongetType()
function
setType(value)
           Gets and sets whether the conditional format Type. The value of the property is FormatConditionType integer constant.
 
Method Summary
functiongetFormula1(isR1C1, isLocal)
Gets the value or expression associated with this format condition.
functiongetFormula1(isR1C1, isLocal, row, column)
Gets the value or expression of the conditional formatting of the cell.
functiongetFormula1(row, column)
Gets the formula of the conditional formatting of the cell.
functiongetFormula2(isR1C1, isLocal)
Gets the value or expression associated with this format condition.
functiongetFormula2(isR1C1, isLocal, row, column)
Gets the value or expression of the conditional formatting of the cell.
functiongetFormula2(row, column)
Gets the formula of the conditional formatting of the cell.
functionsetFormula1(formula, isR1C1, isLocal)
Sets the value or expression associated with this format condition.
functionsetFormula2(formula, isR1C1, isLocal)
Sets the value or expression associated with this format condition.
functionsetFormulas(formula1, formula2, isR1C1, isLocal)
Sets the value or expression associated with this format condition.
 

    • Property Getters/Setters Detail

      • getFormula1/setFormula1 : String 

        function getFormula1() / function setFormula1(value)
        Gets and sets the value or expression associated with conditional formatting. Please add all areas before setting formula. For setting formula for this condition, if the input value starts with '=', then it will be taken as formula. Otherwise it will be taken as plain value(text, number, bool). For text value that starts with '=', user may input it as formula in format: "=\"=...\"".

      • getFormula2/setFormula2 : String 

        function getFormula2() / function setFormula2(value)
        Gets and sets the value or expression associated with conditional formatting. Please add all areas before setting formula. For setting formula for this condition, if the input value starts with '=', then it will be taken as formula. Otherwise it will be taken as plain value(text, number, bool). For text value that starts with '=', user may input it as formula in format: "=\"=...\"".

      • getOperator/setOperator : Number 

        function getOperator() / function setOperator(value)
        Gets and sets the conditional format operator type. The value of the property is OperatorType integer constant.OperatorType

      • getStopIfTrue/setStopIfTrue : boolean 

        function getStopIfTrue() / function setStopIfTrue(value)
        True, no rules with lower priority may be applied over this rule, when this rule evaluates to true. Only applies for Excel 2007;

      • getPriority/setPriority : Number 

        function getPriority() / function setPriority(value)
        The priority of this conditional formatting rule. This value is used to determine which format should be evaluated and rendered. Lower numeric values are higher priority than higher numeric values, where '1' is the highest priority.

      • getStyle/setStyle : Style 

        function getStyle() / function setStyle(value)
        Gets or setts style of conditional formatted cell ranges.

      • getType/setType : Number 

        function getType() / function setType(value)
        Gets and sets whether the conditional format Type. The value of the property is FormatConditionType integer constant.FormatConditionType

      • getIconSet : IconSet 

        function getIconSet()
        Get the conditional formatting's "IconSet" instance. The default instance's IconSetType is TrafficLights31. Valid only for type = IconSet.
        Returns:

      • getDataBar : DataBar 

        function getDataBar()
        Get the conditional formatting's "DataBar" instance. The default instance's color is blue. Valid only for type is DataBar.
        Returns:

      • getColorScale : ColorScale 

        function getColorScale()
        Get the conditional formatting's "ColorScale" instance. The default instance is a "green-yellow-red" 3ColorScale . Valid only for type = ColorScale.
        Returns:

      • getTop10 : Top10 

        function getTop10()
        Get the conditional formatting's "Top10" instance. The default instance's rule highlights cells whose values fall in the top 10 bracket. Valid only for type is Top10.
        Returns:

      • getAboveAverage : AboveAverage 

        function getAboveAverage()
        Get the conditional formatting's "AboveAverage" instance. The default instance's rule highlights cells that are above the average for all values in the range. Valid only for type = AboveAverage.
        Returns:

      • getText/setText : String 

        function getText() / function setText(value)
        The text value in a "text contains" conditional formatting rule. Valid only for type = containsText, notContainsText, beginsWith and endsWith. The default value is null.

      • getTimePeriod/setTimePeriod : Number 

        function getTimePeriod() / function setTimePeriod(value)
        The applicable time period in a "date occurring…" conditional formatting rule. Valid only for type = timePeriod. The default value is TimePeriodType.Today. The value of the property is TimePeriodType integer constant.

    • Method Detail

      • getFormula1

        function getFormula1(isR1C1, isLocal)
        Gets the value or expression associated with this format condition.
        Parameters:
        isR1C1: boolean - Whether the formula needs to be formatted as R1C1.
        isLocal: boolean - Whether the formula needs to be formatted by locale.
        Returns:
        The value or expression associated with this format condition.

      • getFormula2

        function getFormula2(isR1C1, isLocal)
        Gets the value or expression associated with this format condition.
        Parameters:
        isR1C1: boolean - Whether the formula needs to be formatted as R1C1.
        isLocal: boolean - Whether the formula needs to be formatted by locale.
        Returns:
        The value or expression associated with this format condition.

      • getFormula1

        function getFormula1(isR1C1, isLocal, row, column)
        Gets the value or expression of the conditional formatting of the cell. The given cell must be contained by this conditional formatting, otherwise null will be returned.
        Parameters:
        isR1C1: boolean - Whether the formula needs to be formatted as R1C1.
        isLocal: boolean - Whether the formula needs to be formatted by locale.
        row: Number - The row index.
        column: Number - The column index.
        Returns:
        The value or expression associated with the conditional formatting of the cell.

      • getFormula2

        function getFormula2(isR1C1, isLocal, row, column)
        Gets the value or expression of the conditional formatting of the cell. The given cell must be contained by this conditional formatting, otherwise null will be returned.
        Parameters:
        isR1C1: boolean - Whether the formula needs to be formatted as R1C1.
        isLocal: boolean - Whether the formula needs to be formatted by locale.
        row: Number - The row index.
        column: Number - The column index.
        Returns:
        The value or expression associated with the conditional formatting of the cell.

      • setFormulas

        function setFormulas(formula1, formula2, isR1C1, isLocal)
        Sets the value or expression associated with this format condition.
        Parameters:
        formula1: String - The value or expression associated with this format condition. If the input value starts with '=', then it will be taken as formula. Otherwise it will be taken as plain value(text, number, bool). For text value that starts with '=', user may input it as formula in format: "=\"=...\"".
        formula2: String - The value or expression associated with this format condition. The input format is same with formula1
        isR1C1: boolean - Whether the formula is R1C1 formula.
        isLocal: boolean - Whether the formula is locale formatted.

      • setFormula1

        function setFormula1(formula, isR1C1, isLocal)
        Sets the value or expression associated with this format condition.
        Parameters:
        formula: String - The value or expression associated with this format condition. If the input value starts with '=', then it will be taken as formula. Otherwise it will be taken as plain value(text, number, bool). For text value that starts with '=', user may input it as formula in format: "=\"=...\"".
        isR1C1: boolean - Whether the formula is R1C1 formula.
        isLocal: boolean - Whether the formula is locale formatted.

      • setFormula2

        function setFormula2(formula, isR1C1, isLocal)
        Sets the value or expression associated with this format condition.
        Parameters:
        formula: String - The value or expression associated with this format condition. If the input value starts with '=', then it will be taken as formula. Otherwise it will be taken as plain value(text, number, bool). For text value that starts with '=', user may input it as formula in format: "=\"=...\"".
        isR1C1: boolean - Whether the formula is R1C1 formula.
        isLocal: boolean - Whether the formula is locale formatted.

      • getFormula1

        function getFormula1(row, column)
        Gets the formula of the conditional formatting of the cell.
        Parameters:
        row: Number - The row index.
        column: Number - The column index.
        Returns:
        The formula.

      • getFormula2

        function getFormula2(row, column)
        Gets the formula of the conditional formatting of the cell.
        Parameters:
        row: Number - The row index.
        column: Number - The column index.
        Returns:
        The formula.