CODEWITHSUNDEEP
pythonexcelxlsxwriter
Stuber
Stuber

Reputation: 477

how to use python xlsxwriter to conditionally format on cell value

I would like to output conditional formatted Excel worksheets where for each column the condition is dependent on the value in the first row. Using the shown data frame cells A2, A4, B3, B4, C4, C5, D4, and E4 will be highlighted red.

import xlsxwriter

data = [
    [1, 1, 5, 'A', 1],
    [1, 4, 4, 'B', 0],
    [2, 1, 5, 2, 2],
    [1, 1, 5, 'A', 1],
]

wb = xlsxwriter.Workbook('testout.xlsx')
ws = wb.add_worksheet()

formatyellow = wb.add_format({'bg_color':'#F7FE2E'})
formatred = wb.add_format({'bg_color':'#FF0000'})
i=0
for row, row_data in enumerate(data):
    print (row)
    print (row_data)
    ws.write_row(row, 0, row_data)
    i += 1

ws.conditional_format(0,1,4,5, {'type':'text',
                      'criteria':'containing',
                      'value':'B',
                      'format':formatyellow})
ws.conditional_format(0,1,4,5, {'type':'cell',
                      'criteria':'==',
                      'value':'A$1',
                      'format':formatred})

This is what I'm getting: Getting this:

This is what I'm trying to get: Going for this:

Is there a way of getting this done using xlsxwriter?

Upvotes: 0

Views: 6163

Answers (1)

wnnmaw
wnnmaw

Reputation: 5514

I was able to produce the desired result after changing the indexing in ws.conditional_format. Remember that xslxwriter uses zero indexing, so row=0, col=0 corresponds to cell A0. Also, I had to add wb.close() to the end of the fuction

import xlsxwriter

data = [
    [1, 1, 5, 'A', 1],
    [1, 4, 4, 'B', 0],
    [2, 1, 5, 2, 2],
    [1, 1, 5, 'A', 1],
]

wb = xlsxwriter.Workbook('testout.xlsx')
ws = wb.add_worksheet()

formatyellow = wb.add_format({'bg_color':'#F7FE2E'})
formatred = wb.add_format({'bg_color':'#FF0000'})
i=0
for row, row_data in enumerate(data):
    print (row)
    print (row_data)
    ws.write_row(row, 0, row_data)
    i += 1

ws.conditional_format(1,0,3,4, {'type':'text',   #Start from row 1 ("B") and column 0 (1)
                      'criteria':'containing',
                      'value':'B',
                      'format':formatyellow})
ws.conditional_format(1,0,3,4, {'type':'cell',
                      'criteria':'==',
                      'value':'A$1',
                      'format':formatred})

wb.close()

Output:

produced output

Upvotes: 2

Related Questions