- Python XlsxWriter Tutorial
- Python XlsxWriter - Home
- Python XlsxWriter - Overview
- Python XlsxWriter - Environment Setup
- Python XlsxWriter - Hello World
- Python XlsxWriter - Important classes
- Python XlsxWriter - Cell Notation & Ranges
- Python XlsxWriter - Defined Names
- Python XlsxWriter - Formula & Function
- Python XlsxWriter - Date and Time
- Python XlsxWriter - Tables
- Python XlsxWriter - Applying Filter
- Python XlsxWriter - Fonts & Colors
- Python XlsxWriter - Number Formats
- Python XlsxWriter - Border
- Python XlsxWriter - Hyperlinks
- Python XlsxWriter - Conditional Formatting
- Python XlsxWriter - Adding Charts
- Python XlsxWriter - Chart Formatting
- Python XlsxWriter - Chart Legends
- Python XlsxWriter - Bar Chart
- Python XlsxWriter - Line Chart
- Python XlsxWriter - Pie Chart
- Python XlsxWriter - Sparklines
- Python XlsxWriter - Data Validation
- Python XlsxWriter - Outlines & Grouping
- Python XlsxWriter - Freeze & Split Panes
- Python XlsxWriter - Hide/Protect Worksheet
- Python XlsxWriter - Textbox
- Python XlsxWriter - Insert Image
- Python XlsxWriter - Page Setup
- Python XlsxWriter - Header & Footer
- Python XlsxWriter - Cell Comments
- Python XlsxWriter - Working with Pandas
- Python XlsxWriter - VBA Macro
- Python XlsxWriter Useful Resources
- Python XlsxWriter - Quick Guide
- Python XlsxWriter - Useful Resources
- Python XlsxWriter - Discussion
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 applied 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 applied 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.