Example: Conditional Formatting

Example of how to add conditional formatting to an XlsxWriter file. Conditional formatting allows you to apply a format to a cell or a range of cells based on certain criteria.

_images/conditional_format1.png
###############################################################################
#
# Example of how to add conditional formatting to an XlsxWriter file.
#
# Conditional formatting allows you to apply a format to a cell or a
# range of cells based on certain criteria.
#
# Copyright 2013-2017, John McNamara, jmcnamara@cpan.org
#
import xlsxwriter

workbook = xlsxwriter.Workbook('conditional_format.xlsx')
worksheet1 = workbook.add_worksheet()
worksheet2 = workbook.add_worksheet()
worksheet3 = workbook.add_worksheet()
worksheet4 = workbook.add_worksheet()
worksheet5 = workbook.add_worksheet()
worksheet6 = workbook.add_worksheet()
worksheet7 = workbook.add_worksheet()
worksheet8 = workbook.add_worksheet()
worksheet9 = workbook.add_worksheet()

# Add a format. Light red fill with dark red text.
format1 = workbook.add_format({'bg_color': '#FFC7CE',
                               'font_color': '#9C0006'})

# Add a format. Green fill with dark green text.
format2 = workbook.add_format({'bg_color': '#C6EFCE',
                               'font_color': '#006100'})

# Some sample data to run the conditional formatting against.
data = [
    [34, 72, 38, 30, 75, 48, 75, 66, 84, 86],
    [6, 24, 1, 84, 54, 62, 60, 3, 26, 59],
    [28, 79, 97, 13, 85, 93, 93, 22, 5, 14],
    [27, 71, 40, 17, 18, 79, 90, 93, 29, 47],
    [88, 25, 33, 23, 67, 1, 59, 79, 47, 36],
    [24, 100, 20, 88, 29, 33, 38, 54, 54, 88],
    [6, 57, 88, 28, 10, 26, 37, 7, 41, 48],
    [52, 78, 1, 96, 26, 45, 47, 33, 96, 36],
    [60, 54, 81, 66, 81, 90, 80, 93, 12, 55],
    [70, 5, 46, 14, 71, 19, 66, 36, 41, 21],
]


###############################################################################
#
# Example 1.
#
caption = ('Cells with values >= 50 are in light red. '
           'Values < 50 are in light green.')

# Write the data.
worksheet1.write('A1', caption)

for row, row_data in enumerate(data):
    worksheet1.write_row(row + 2, 1, row_data)

# Write a conditional format over a range.
worksheet1.conditional_format('B3:K12', {'type': 'cell',
                                         'criteria': '>=',
                                         'value': 50,
                                         'format': format1})

# Write another conditional format over the same range.
worksheet1.conditional_format('B3:K12', {'type': 'cell',
                                         'criteria': '<',
                                         'value': 50,
                                         'format': format2})


###############################################################################
#
# Example 2.
#
caption = ('Values between 30 and 70 are in light red. '
           'Values outside that range are in light green.')

worksheet2.write('A1', caption)

for row, row_data in enumerate(data):
    worksheet2.write_row(row + 2, 1, row_data)

worksheet2.conditional_format('B3:K12', {'type': 'cell',
                                         'criteria': 'between',
                                         'minimum': 30,
                                         'maximum': 70,
                                         'format': format1})

worksheet2.conditional_format('B3:K12', {'type': 'cell',
                                         'criteria': 'not between',
                                         'minimum': 30,
                                         'maximum': 70,
                                         'format': format2})


###############################################################################
#
# Example 3.
#
caption = ('Duplicate values are in light red. '
           'Unique values are in light green.')

worksheet3.write('A1', caption)

for row, row_data in enumerate(data):
    worksheet3.write_row(row + 2, 1, row_data)

worksheet3.conditional_format('B3:K12', {'type': 'duplicate',
                                         'format': format1})

worksheet3.conditional_format('B3:K12', {'type': 'unique',
                                         'format': format2})


###############################################################################
#
# Example 4.
#
caption = ('Above average values are in light red. '
           'Below average values are in light green.')

worksheet4.write('A1', caption)

for row, row_data in enumerate(data):
    worksheet4.write_row(row + 2, 1, row_data)

worksheet4.conditional_format('B3:K12', {'type': 'average',
                                         'criteria': 'above',
                                         'format': format1})

worksheet4.conditional_format('B3:K12', {'type': 'average',
                                         'criteria': 'below',
                                         'format': format2})


###############################################################################
#
# Example 5.
#
caption = ('Top 10 values are in light red. '
           'Bottom 10 values are in light green.')

worksheet5.write('A1', caption)

for row, row_data in enumerate(data):
    worksheet5.write_row(row + 2, 1, row_data)

worksheet5.conditional_format('B3:K12', {'type': 'top',
                                         'value': '10',
                                         'format': format1})

worksheet5.conditional_format('B3:K12', {'type': 'bottom',
                                         'value': '10',
                                         'format': format2})


###############################################################################
#
# Example 6.
#
caption = ('Cells with values >= 50 are in light red. '
           'Values < 50 are in light green. Non-contiguous ranges.')

# Write the data.
worksheet6.write('A1', caption)

for row, row_data in enumerate(data):
    worksheet6.write_row(row + 2, 1, row_data)

# Write a conditional format over a range.
worksheet6.conditional_format('B3:K6', {'type': 'cell',
                                        'criteria': '>=',
                                        'value': 50,
                                        'format': format1,
                                        'multi_range': 'B3:K6 B9:K12'})

# Write another conditional format over the same range.
worksheet6.conditional_format('B3:K6', {'type': 'cell',
                                        'criteria': '<',
                                        'value': 50,
                                        'format': format2,
                                        'multi_range': 'B3:K6 B9:K12'})


###############################################################################
#
# Example 7.
#
caption = 'Examples of color scales and data bars. Default colors.'

data = range(1, 13)

worksheet7.write('A1', caption)

worksheet7.write('B2', "2 Color Scale")
worksheet7.write('D2', "3 Color Scale")
worksheet7.write('F2', "Data Bars")

for row, row_data in enumerate(data):
    worksheet7.write(row + 2, 1, row_data)
    worksheet7.write(row + 2, 3, row_data)
    worksheet7.write(row + 2, 5, row_data)

worksheet7.conditional_format('B3:B14', {'type': '2_color_scale'})
worksheet7.conditional_format('D3:D14', {'type': '3_color_scale'})
worksheet7.conditional_format('F3:F14', {'type': 'data_bar'})


###############################################################################
#
# Example 8.
#
caption = 'Examples of color scales and data bars. Modified colors.'

data = range(1, 13)

worksheet8.write('A1', caption)

worksheet8.write('B2', "2 Color Scale")
worksheet8.write('D2', "3 Color Scale")
worksheet8.write('F2', "Data Bars")

for row, row_data in enumerate(data):
    worksheet8.write(row + 2, 1, row_data)
    worksheet8.write(row + 2, 3, row_data)
    worksheet8.write(row + 2, 5, row_data)

worksheet8.conditional_format('B3:B14', {'type': '2_color_scale',
                                         'min_color': "#FF0000",
                                         'max_color': "#00FF00"})

worksheet8.conditional_format('D3:D14', {'type': '3_color_scale',
                                         'min_color': "#C5D9F1",
                                         'mid_color': "#8DB4E3",
                                         'max_color': "#538ED5"})

worksheet8.conditional_format('F3:F14', {'type': 'data_bar',
                                         'bar_color': '#63C384'})

###############################################################################
#
# Example 9.
#
caption = 'Examples of conditional formats with icon sets.'

data = [
    [1, 2, 3],
    [1, 2, 3],
    [1, 2, 3],
    [1, 2, 3],
    [1, 2, 3, 4],
    [1, 2, 3, 4, 5],
    [1, 2, 3, 4, 5],
]

worksheet9.write('A1', caption)

for row, row_data in enumerate(data):
    worksheet9.write_row(row + 2, 1, row_data)

worksheet9.conditional_format('B3:D3', {'type': 'icon_set',
                                        'icon_style': '3_traffic_lights'})

worksheet9.conditional_format('B4:D4', {'type': 'icon_set',
                                        'icon_style': '3_traffic_lights',
                                        'reverse_icons': True})

worksheet9.conditional_format('B5:D5', {'type': 'icon_set',
                                        'icon_style': '3_traffic_lights',
                                        'icons_only': True})

worksheet9.conditional_format('B6:D6', {'type': 'icon_set',
                                        'icon_style': '3_arrows'})

worksheet9.conditional_format('B7:E8', {'type': 'icon_set',
                                        'icon_style': '4_arrows'})

worksheet9.conditional_format('B8:F8', {'type': 'icon_set',
                                        'icon_style': '5_arrows'})

worksheet9.conditional_format('B9:F9', {'type': 'icon_set',
                                        'icon_style': '5_ratings'})

workbook.close()