|
libxlsxwriter
|
Conditional formatting is a feature of Excel that allows you to apply a format to a cell, or a range of cells, based on definable criteria. For example you might format cells that are >= 50 in red and cells that < 50 in green. Like this:
There are also types of conditional formats such as color scales and data bars that highlight the relative difference of cells in a range:
Libxlsxwriter supports almost all of Excel's conditional formatting types and options, which are explained in the sections below.
Conditional formatting is a feature in Excel that allows you to format cells based on criteria such as greater than or less than, top or bottom 10 items, or items that are above the average for the range.
The best place to start with conditional formats is in Excel. Create an example of the type for conditional format that you want and then translate that into an libxlsxwriter program using the worksheet_conditional_format_cell() or worksheet_conditional_format_range() functions and the lxw_conditional_format structure. We will walk through an example of this below.
Say you wanted to highlight in red all the cells in the range "B1:B9" that have a value less than 33. To do this in Excel you would select the range, click on "Conditional Formatting" and create a rule like this:
In the image above we see the "type" which is "Cell", the "Criteria" which is "less than", the "Value" which is 33 and the "Custom Format" which is red text. To replicate this in libxlsxwriter we will start by creating the format.
We do this in the same way we create any worksheet format to use in libxlsxwriter by creating a lxw_format object using workbook_add_format():
The formats used for conditional formats are the same as any other worksheet format, apart from the fact that Excel restricts some of the available properties. We will look at that below in parameter: format.
Next we create a lxw_conditional_format object. We will use this to set the properties of the conditional format.
We then set the "Type", "Criteria" and "Value" like in the Excel dialog above:
We then add the format we created earlier which, again, this is like the Excel dialog:
And finally we apply the conditional format to the desired range:
After we compile and run the program the output would look like this:
The complete version of this example is shown in conditional_format1.c. There is also another example that shows more advanced conditional formatting options, see conditional_format2.c.
The lxw_conditional_format struct is used to define a conditional format in libxlsxwriter. It has number of fields, most of which are optional. The main ones are explained here, as an introduction, and in more detail in the sections below. For reference here is our example from the previous section:
The type field in is a required parameter and it has no default value. This defines which type of Excel conditional format will be used.
The criteria field defines the criteria used to evaluate the conditional format. Most conditional formats will have a criteria, in which case it is a required field, but some types such as duplicate or blanks don't need to specify one since it is implicit in the type.
The value or value_string field sets the value to which the criteria is applied. Most conditional formats will have a value, or range of values, to which the criteria is applied, but some types or criteria don't need one.
Finally, the format field defines the lxw_format that will be applied if the conditional format matches. Most conditional formats will used the format field, since that is somewhat the point. However, it isn't actually required, in Excel or in libxlsxwriter, and conditional format types such as Color Scale or Data Bars don't need it since they rely on simpler color definitions.
The allowable type values withe their descriptive and enum values are shown in the following table along with their associated parameters.
| Type | Enum value | Parameters |
|---|---|---|
| cell | LXW_CONDITIONAL_TYPE_CELL | criteria |
| ^ | ^ | value |
| ^ | ^ | value_string |
| ^ | ^ | min_value |
| ^ | ^ | max_value |
| ^ | ^ | min_value_string |
| ^ | ^ | max_value_string |
| ^ | ^ | format |
| text | LXW_CONDITIONAL_TYPE_TEXT | criteria |
| ^ | ^ | value |
| ^ | ^ | value_string |
| ^ | ^ | format |
| time_period | LXW_CONDITIONAL_TYPE_TIME_PERIOD | criteria |
| ^ | ^ | format |
| average | LXW_CONDITIONAL_TYPE_AVERAGE | criteria |
| ^ | ^ | format |
| duplicate | LXW_CONDITIONAL_TYPE_DUPLICATE | format |
| unique | LXW_CONDITIONAL_TYPE_UNIQUE | format |
| top | LXW_CONDITIONAL_TYPE_TOP | criteria |
| ^ | ^ | value |
| ^ | ^ | value_string |
| ^ | ^ | format |
| bottom | LXW_CONDITIONAL_TYPE_BOTTOM | criteria |
| ^ | ^ | value |
| ^ | ^ | value_string |
| ^ | ^ | format |
| blanks | LXW_CONDITIONAL_TYPE_BLANKS | format |
| no_blanks | LXW_CONDITIONAL_TYPE_NO_BLANKS | format |
| errors | LXW_CONDITIONAL_TYPE_ERRORS | format |
| no_errors | LXW_CONDITIONAL_TYPE_NO_ERRORS | format |
| formula | LXW_CONDITIONAL_TYPE_FORMULA | value |
| ^ | ^ | value_string |
| ^ | ^ | format |
| 2_color_scale | LXW_CONDITIONAL_2_COLOR_SCALE | min_rule_type |
| ^ | ^ | max_rule_type |
| ^ | ^ | min_value |
| ^ | ^ | max_value |
| ^ | ^ | min_value_string |
| ^ | ^ | max_value_string |
| ^ | ^ | min_color |
| ^ | ^ | max_color |
| 3_color_scale | LXW_CONDITIONAL_3_COLOR_SCALE | min_rule_type |
| ^ | ^ | mid_rule_type |
| ^ | ^ | max_rule_type |
| ^ | ^ | min_value |
| ^ | ^ | mid_value |
| ^ | ^ | max_value |
| ^ | ^ | min_value_string |
| ^ | ^ | mid_value_string |
| ^ | ^ | max_value_string |
| ^ | ^ | min_color |
| ^ | ^ | mid_color |
| ^ | ^ | max_color |
| data_bar | LXW_CONDITIONAL_DATA_BAR | min_rule_type |
| ^ | ^ | max_rule_type |
| ^ | ^ | min_value |
| ^ | ^ | max_value |
| ^ | ^ | min_value_string |
| ^ | ^ | max_value_string |
| ^ | ^ | bar_only |
| ^ | ^ | bar_color |
| ^ | ^ | bar_solid* |
| ^ | ^ | bar_negative_color* |
| ^ | ^ | bar_border_color* |
| ^ | ^ | bar_negative_border_color* |
| ^ | ^ | bar_negative_color_same* |
| ^ | ^ | bar_negative_border_color_same* |
| ^ | ^ | bar_no_border* |
| ^ | ^ | bar_direction* |
| ^ | ^ | bar_axis_position* |
| ^ | ^ | bar_axis_color* |
| ^ | ^ | data_bar_2010* |
| icon_set | LXW_CONDITIONAL_TYPE_ICON_SETS | icon_style |
| ^ | ^ | reverse_icons |
| ^ | ^ | icons_only |
* are only available in Excel 2010 and later. Files that use these properties can still be opened in Excel 2007 but the data bars will be displayed without them.Each conditional format type is explained in more detail in the sections below.
This is the most common conditional formatting type. It is used when a format is applied to a cell, or range of cells, based on a simple criterion.
For example using a single value and the "greater than" criteria:
Or, using 2 values and the between criteria:
If the criteria is "equal to" and the value_string is a text string (but not a cell range) then Excel requires that the text string is double quoted:
The criteria that apply to Cell conditional formats are:
The time_period type is used to specify Excel's "Dates Occurring" style conditional format:
The period is set in the criteria and can have one of the following values:
The text type is used to specify Excel's "Specific Text" style conditional format. It is used to do simple string matching using the criteria and value parameters:
The value_string parameter should be a string with one or more characters.
The criteria for Text conditional formats can have one of the following values:
The average type is used to specify Excel's "Average" style conditional format:
The type of average for the conditional format range is specified by the criteria:
The duplicate type is used to highlight duplicate cells in a range. It doesn't take a criteria:
The unique type is used to highlight unique cells in a range. It doesn't take a criteria:
The top type is used to specify the top n values by number or percentage in a range:
The criteria can be used to indicate that a percentage condition is required:
The bottom type is used to specify the bottom n values by number or percentage in a range.
It takes the same parameters as top, see above.
The blanks type is used to highlight blank cells in a range:
The no_blanks type is used to highlight non blank cells in a range. It doesn't take a criteria:
The errors type is used to highlight error cells in a range. It doesn't take a criteria:
The no_errors type is used to highlight non error cells in a range. It doesn't take a criteria:
The formula type is used to specify a conditional format based on a user defined formula:
Formulas must be written with the US style separator/range operator which is a comma (not semi-colon) and should follow the same rules as worksheet_write_formula(). See Non US Excel functions and syntax for a full explanation:
"$A$1=5" and "A1=5" will give different results when applied to a range. It is best to verify the formula in Excel before transferring it to libxlsxwriter.The 2_color_scale type is used to specify Excel's "2 Color Scale" style conditional format:
This conditional type can be modified with additional parameters:
See the following sections below for more information:
The 3_color_scale type is used to specify Excel's "3 Color Scale" style conditional format:
This conditional type can be modified with additional parameters:
See the following sections below for more information:
The data_bar type is used to specify Excel's "Data Bar" style conditional format:
This conditional type can be modified with the following parameters, which are explained in the sections below. These properties were available in the original xlsx file specification used in Excel 2007:
Additional data bar properties were added in Excel 2010 such as solid (non-gradient) bars and control over how negative values are displayed. These properties can be set using the following parameters:
For example:
Files that use these Excel 2010 properties can still be opened in Excel 2007 but the data bars will be displayed without them.
The icon_set type is used to specify a conditional format with a set of icons such as traffic lights or arrows:
The icon set style is specified by the icon_style parameter. Valid options are:
The order of Icon Sets icons can be reversed by setting reverse_icons to LXW_TRUE.
The icons can be displayed without the cell value by settings the icons_only parameter to LXW_TRUE:
The fields/options in the the lxw_conditional_format are used to define a worksheet conditional format. The type field was explained in the previous section. The sections below explain the other options.
The criteria parameter is used to set the criteria by which the cell data will be evaluated. It is defined in lxw_conditional_criteria and has no default value. Some conditional format types such as duplicate or blanks don't need to specify as criteria since it is implicit in the type.
The values for different conditional format types are shown in lxw_conditional_criteria and in the type sections to which they apply, above.
The value parameter is generally used along with criteria to set the rule by which the cell data will be evaluated:
The value_string parameter is used for non-numeric values. In conditional formats this will generally be a cell reference like the following:
In general any value_string property that refers to a cell reference should use an absolute cell reference, like $B$1, especially if the conditional formatting is applied to a range of values. Without an absolute cell reference the conditional format will not be applied correctly by Excel, apart from the first cell in the formatted range. Double check in Excel if you don't get the result that you expect.
If the type is Cell, the criteria is "equal to" and the value_string is a text string (but not a cell range) then Excel requires that the text string is double quoted:
The format parameter is used to specify the format that will be applied to the cell when the conditional formatting criterion is met. The format is created using the workbook_add_format() function in the same way as cell formats:
Most conditional formats will have a lxw_format format, since that is somewhat the point. However, it isn't actually required, in Excel or in libxlsxwriter. Color Scale and Data Bar conditional format types use defined colors instead of a format.
In Excel, a conditional format is superimposed over the existing cell format and not all cell format properties can be modified in a conditional format. Properties that cannot be modified in a conditional format in Excel are: font name, font size, superscript and subscript, diagonal borders, all alignment properties and all protection properties.
Excel specifies some default formats when using the conditional formatting dialog such as red, yellow and green. These can be replicated using the following libxlsxwriter formats:
See also lxw_format and Working with Formats.
The min_value parameter is used to set the lower limiting value for Cell, Color Scale and Data Bar conditional formats.
For cell formats it is usually used when the criteria is either LXW_CONDITIONAL_CRITERIA_BETWEEN or LXW_CONDITIONAL_CRITERIA_NOT_BETWEEN:
The min_value_string parameter is used for non-numeric values. In conditional formats this will generally be a cell reference like $B$1:
The mid_value parameter is used to set the middle limit for Color Scale and Data Bar conditional formats.
See parameter: min_value and min_value_string above.
The max_value parameter is used to set the lower limiting value for Cell, Color Scale and Data Bar conditional formats.
See parameter: min_value and min_value_string above.
The min_rule_type and max_rule_type properties are used when the conditional formatting type is 2_color_scale, 3_color_scale or data_bar. The mid_rule_type is used with 3_color_scale. The properties are used as follows:
The available min/mid/max rule types are defined in lxw_conditional_format_rule_types:
Used for 3_color_scale. Same as min_rule_type, see above.
Same as min_rule_type, see above.
The min_color and max_color properties are available when the conditional formatting type is 2_color_scale or 3_color_scale. The mid_color is available for 3_color_scale. The properties are used as follows:
The color can be a Html style RRGGBB hex number or a limited number of named colors, see Working with Colors.
Used for 3_color_scale. Same as min_color, see above.
Same as min_color, see above.
The bar_color parameter sets the fill color for data bars:
The color can be a Html style RRGGBB hex number or a limited number of named colors, see Working with Colors.
The bar_only property displays a bar data but not the data in the cells:
See the image above.
The bar_solid property turns on a solid (non-gradient) fill for data bars:
See the image above.
Note, this property is only visible in Excel 2010 and later.
The bar_negative_color property sets the color fill for the negative portion of a data bar:
The color can be a Html style RRGGBB hex number or a limited number of named colors, see Working with Colors.
Note, this property is only visible in Excel 2010 and later.
The bar_border_color property sets the color for the border line of a data bar:
The color can be a Html style RRGGBB hex number or a limited number of named colors, see Working with Colors.
Note, this property is only visible in Excel 2010 and later.
The bar_negative_border_color property sets the color for the border of the negative portion of a data bar:
The color can be a Html style RRGGBB hex number or a limited number of named colors, see Working with Colors.
Note, this property is only visible in Excel 2010 and later.
The bar_negative_color_same property sets the fill color for the negative portion of a data bar to be the same as the fill color for the positive portion of the data bar:
Note, this property is only visible in Excel 2010 and later.
The bar_negative_border_color_same property sets the border color for the negative portion of a data bar to be the same as the border color for the positive portion of the data bar:
See the image above.
Note, this property is only visible in Excel 2010 and later.
The bar_no_border property turns off the border for data bars:
Note, this property is only visible in Excel 2010 and later, however the default in Excel 2007 is to not have a border.
The bar_direction property sets the direction for data bars. This property can be either one of the values from lxw_conditional_format_bar_direction shown below. If the property isn't set then Excel will adjust the position automatically based on the context. The available options are:
Here is an example:
Note, this property is only visible in Excel 2010 and later.
The bar_axis_position property sets the position of the axis that is shown in data bars when there are negative values to display. The property can be either midpoint or none.
If the property isn't set then Excel will position the axis based on the range of positive and negative values. The setting are defined in lxw_conditional_bar_axis_position:
Note, this property is only visible in Excel 2010 and later.
The bar_axis_color property sets the color for the axis that is shown in data bars when there are negative values to display:
Note, this property is only visible in Excel 2010 and later.
The data_bar_2010 property sets Excel 2010 style data bars even when Excel 2010 specific properties aren't used. This can be used for ensure conformity across all the data bar formatting in a worksheet:
The stop_if_true parameter can be used to set the "stop if true" feature of a conditional formatting rule when more than one rule is applied to a cell or a range of cells. When this parameter is set then subsequent rules are not evaluated if the current rule is true:
The multi_range option is used to extend a conditional format over non-contiguous ranges.
It is possible to apply the conditional format to different cell ranges in a worksheet using multiple calls to worksheet_conditional_format(). However, as a minor optimization it is also possible in Excel to apply the same conditional format to different non-contiguous cell ranges.
This is replicated in worksheet_conditional_format() using the multi_range option. The range must contain the primary range for the conditional format and any others separated by spaces.
For example to apply one conditional format to two ranges, B3:K6 and B9:K12:
1.8.10