CODEWITHSUNDEEP
pythonif-statementconditional-formattingxlsxwriter
JC21
JC21

Reputation: 21

How do I create an "if and" statement to conditionally format a column of cells using xlsxwriter in Python

This isn't working (obviously), so i'm wondering if there are any suggestions. Thank you.

The goals: - If AA2 > 0 and AB2 < 0, then format cells in column A with formatc3. - If AA2 < 0 and AB2 > 0, then format cells in column A with formatc3. 

worksheet.conditional_format('A2:A2100', {'type':   'formula',
                                        'criteria': '=AND($AA2>0,$AB2<0)',
                                        'format':   formatc3})

worksheet.conditional_format('A2:A2100', {'type':   'formula',
                                        'criteria': '=AND($AA2<0,$AB2>0)',
                                        'format':   formatc3})

Upvotes: 1

Views: 813

Answers (1)

Dimitris Thomas
Dimitris Thomas

Reputation: 1393

You almost got it right, you miss one small thing in the excel formula, they should start from the first row and not the second. Also no need to write two different statements as your conditions could be nested in just one, it's more efficient and readable. In general try to reproduce your desired output manually in excel, then it's easy to convert it to xlsxwriter's syntax.

Here is a working example:

import pandas as pd
import numpy as np

data = {'Col1': [2, 3, -2, 5],
        'Col2': [-5, 6, 7, -8],
        'Col3': [np.nan, np.nan, np.nan, np.nan]}

df = pd.DataFrame(data)

writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1', index=False)
workbook = writer.book
worksheet = writer.sheets['Sheet1']

green = workbook.add_format({'bg_color': '#C6EFCE'})

worksheet.conditional_format('C1:C1048576', {'type': 'formula',
                            'criteria': '=OR(AND($A1>0,$B1<0),AND($A1<0,$B1>0))',
                            'format': green})

writer.save()

Output:

enter image description here

Upvotes: 2

Related Questions