Python XlsxWriter - Conditional Formatting

Excel uses conditional formatting to change the appearance of cells in a range based on user defined criteria. From the conditional formatting menu, it is possible to define criteria involving various types of values.

In the worksheet shown below, the column A has different numbers. Numbers less than 50 are shown in red font color and grey background color.

This is achieved by defining a conditional formatting rule below −

The conditional_format() method

In XlsxWriter, there as a conditional_format() method defined in the Worksheet class. To achieve the above shown result, the conditional_format() method is called as in the following code −

import xlsxwriter

wb = xlsxwriter.Workbook( hello.xlsx )
ws = wb.add_worksheet()

data=[56,95,63,34,81,47,74,5,99,12]
row=0

for num in data:
   ws.write(row,0,num)
   row+=1
f1 = wb.add_format({ bg_color :  #D9D9D9 ,  font_color :  red })
ws.conditional_format(
    A1:A10 ,{
       type : cell ,  criteria : < ,  value :50,  format :f1
   }
)
wb.close()

Parameters

The conditional_format() method s first argument is the cell range, and the second argument is a dictionary of conditional formatting options.

The options dictionary configures the conditional formatting rules with the following parameters −

The type option is a required parameter. Its value is either cell, date, text, formula, etc. Each parameter has sub-parameters such as criteria, value, format, etc.

Type is the most common conditional formatting type. It is used when a format is appped to a cell based on a simple criterion.

Criteria parameter sets the condition by which the cell data will be evaluated. All the logical operator in addition to between and not between operators are the possible values of criteria parameter.

Value parameter is the operand of the criteria that forms the rule.

Format parameter is the Format object (returned by the add_format() method). This defines the formatting features such as font, color, etc. to be appped to cells satisfying the criteria.

The date type is similar the cell type and uses the same criteria and values. However, the value parameter should be given as a datetime object.

The text type specifies Excel s "Specific Text" style conditional format. It is used to do simple string matching using the criteria and value parameters.

Example

When formula type is used, the conditional formatting depends on a user defined formula.

import xlsxwriter

wb = xlsxwriter.Workbook( hello.xlsx )
ws = wb.add_worksheet()

data = [
   [ Anil , 45, 55, 50], [ Ravi , 60, 70, 80],
   [ Kiran , 65, 75, 85], [ Karishma , 55, 65, 45]
]   
for row in range(len(data)):
   ws.write_row(row,0, data[row])
   
f1 = wb.add_format({ font_color :  blue ,  bold :True})

ws.conditional_format(
    A1:D4 ,
   {
       type : formula ,  criteria : =AVERAGE($B1:$D1)>60 ,  value :50,  format :f1
   })
wb.close()

Output

Open the resultant workbook using MS Excel. We can see the rows satisfying the above condition displayed in blue color according to the format object. The conditional format rule manager also shows the criteria that we have set in the above code.