xlsxwriter conditional format works only after manually applying it

Question

let me describe my issue below:

I've got got two excel worksheets, one containing past, the other - current data. They both have the following structure:

Col_1	Col_2	KEY	Col_3	Etc.
abc	xyz	key_1	foo	---
def	zyx	key_2	bar	---

Now, the goal is to check if a value for given key changed between the past and current iteration and if yes, color the given cell's background (in current data worksheet). This check has to be done for all the columns.

As the KEY column is not the very first one, I've decided to use XLOOKUP function and apply the formatting within the for loop. The full loop looks like this (in this example the KEY column is column C):

dark_blue = writer.book.add_format({'bg_color': '#3A67B8'})
old_sheet = "\'" + "old_" + "sheet_name" + "\'"
for col in range(last_col):
    col_name = xl_col_to_name(col) 
    if col_name in unformatted_cols: # Not apply the formatting to certain columns
        continue
    else:
        apply_range = '{0}1:{0}1048576'.format(col_name)
        formula = "XLOOKUP(C1, {1}!C1:C1048576, {1}!{0}1:{0}1048576) <> XLOOKUP(C1, C1:C1048576, {0}1:{0}1048576)".format(col_name, old_sheet)
        active_sheet.conditional_format(apply_range, {'type': 'formula',
                                                      'criteria': formula,
                                                      'format': dark_blue})

Now, my problem is that when I open the output the this conditional formatting doesn't work. If however I'll go to Conditional Formatting -> Manage Rules -> Edit Rule and without any editing I'll press OK and later apply it starts working correctly.

Does anyone know how to make this rule work properly without this manual intervention?

My all other conditional formatting rules, though simpler, work exactly as intended.

# This is the formula that I see in Python for the first loop iteration
=XLOOKUP(C1, 'old_sheet_name'!C1:C1048576, 'old_sheet_name'!A1:A1048576) <> XLOOKUP(C1, C1:C1048576, A1:A1048576)

# This formula I see in Excel for the same first column
=XLOOKUP(C1, 'old_sheet_name'!C:C, 'old_sheet_name'!A:A) <> XLOOKUP(C1, C:C, A:A)

Answer 1

The reason that XLOOKUPdoesn't work in your formula is that it is classified by Excel as a "Future Function", i.e, a function added after the original file format. In order to use it you need to prefix it with _xlfn. This is explained in the XlsxWriter docs on Formulas added in Excel 2010 and later.

Here is a working example:

import xlsxwriter

workbook = xlsxwriter.Workbook('conditional_format.xlsx')
worksheet1 = workbook.add_worksheet('old_sheet_name')
worksheet2 = workbook.add_worksheet('new_sheet_name')

worksheet1.write(0, 0, 'Foo')

format1 = workbook.add_format({'bg_color': '#C6EFCE',
                               'font_color': '#006100'})

xlookup_formula = '=_xlfn.XLOOKUP(C1, old_sheet_name!C:C, old_sheet_name!A:A) <> _xlfn.XLOOKUP(C1, C:C, A:A)'

worksheet2.conditional_format('D1:D10',
                              {'type':     'formula',
                               'criteria': xlookup_formula,
                               'format':   format1})

workbook.close()

Output: