how to use python xlsxwriter to conditionally format on cell value

Question

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:

This is what I'm trying to get:

Is there a way of getting this done using xlsxwriter?

Two things. First, I cannot run your code without adding wb.close() to the end. Second, I generate a different output from your code then what you have shown. Can you update your post to include more information about your system? (i.e. python version, xlsxwriter version, and os) — wnnmaw
– wnnmaw, Commented Nov 17, 2016 at 21:29

wnnmaw · Accepted Answer · 2016-11-17 21:40:21Z

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:

Collectives™ on Stack Overflow

how to use python xlsxwriter to conditionally format on cell value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related