Pandas Conditional Formatting BITAND

user7VoI 0
Viewed 27

i have the script to check aws tags. For all resource the required tags was checked. Now i try to color bad tag value. I use column State to determinate column to color with BITAND operation.

        i = 0
        for i in range(len(self.config["tags"])):
            worksheet.conditional_format(0,  start_tag_column+i, max_row,  start_tag_column+i,
            { 'type':'formula','criteria': f"=BITAND($I1,{2**i})={2**i}",  'format': format_bad})

When I open xls the condition format was presented but it doesn't work

see ko.xlsx

When I edit (open/save) conditional format rule - all works fine,

see ok.xlsx

Any ideas?

Thank you!

p.s. i use macos m2, Python 3.10.6, pandas==1.4.4, XlsxWriter==3.0.3, Microsoft Excel For Mac 16.65

xlsxwriter pandas python
1 Answers

The BITAND() function is a "Future Function" added after the original Excel xlsx specification (see Formulas added in Excel 2010 and later in the XlsxWriter docs).

So you will need to prefix it with _xlfn. as follows:

worksheet.conditional_format(0,  start_tag_column+i, max_row,  start_tag_column+i,
    {'type': 'formula',
     'criteria': f"=_xlfn.BITAND($I1,{2**i})={2**i}",
     'format': format_bad})
jmcnamara0
Related