Xlsxwriter & Sparklines With Data From Another Sheet, Render Issue

Question

I've created the below python code to reproduce a problem I'm having trying to create a workbook with two sheets, one sheet should have a sparkline that pulls from the other sheet, and sorting should work.

If I use regular ol' excel row and column notation, then sorting the sheet with the sparklines re-orders the rows but not the sparklines themselves. So to get around this, I found some solutions here and elsewhere that recommend using a Named Range, this works fine BUT after opening the generated workbook, I have to go into Name Manager and make a minor change to the formula, change it back to what it was, then close the Name Manager window before the sparklines will draw correctly.

Any idea how I can accomplish this and have the sparklines render right away on first open of the workbook?

import pathlib
import xlsxwriter

def main():
    workbook = xlsxwriter.Workbook(pathlib.Path().absolute() / "./sparkline-test.xlsx")

    worksheet1 = workbook.add_worksheet(name="Employee Data")
    worksheet1.write_string(0, 0, "Employee")
    worksheet1.write_string(0, 1, "1")
    worksheet1.write_string(0, 2, "2")
    worksheet1.write_string(0, 3, "3")
    worksheet1.write_string(0, 4, "4")
    worksheet1.write_string(0, 5, "5")

    worksheet1.write_string(1, 0, "Doe, John")
    worksheet1.write_number(1, 1, 50)
    worksheet1.write_number(1, 2, 20)
    worksheet1.write_number(1, 3, 30)
    worksheet1.write_number(1, 4, 20)
    worksheet1.write_number(1, 5, 40)
    worksheet1.write_string(2, 0, "Doe, Jane")
    worksheet1.write_number(2, 1, 24)
    worksheet1.write_number(2, 2, 17)
    worksheet1.write_number(2, 3, 38)
    worksheet1.write_number(2, 4, 42)
    worksheet1.write_number(2, 5, 19)

    worksheet2 = workbook.add_worksheet(name="Sparkline Results")
    worksheet2.write_string(0, 0, "Employee")
    worksheet2.write_string(0, 1, "Sparkline Chart")

    worksheet2.write_string(1, 0, "Doe, Jane")
    worksheet2.write_string(2, 0, "Doe, John")

    worksheet1.add_table(0, 0, 2, 5, {
        'name': 'EmployeeData',
        'first_column': True,
        'style': 'Table Style Medium 16',
        'columns': [
            {'header': 'Employee'},
            {'header': '1'},
            {'header': '2'},
            {'header': '3'},
            {'header': '4'},
            {'header': '5'},
       ]
    })
    worksheet2.add_table(0, 0, 2, 1, {
        'name': 'FinalResults',
        'first_column': True,
        'style': 'Table Style Medium 16',
        'columns': [
            {'header': 'Employee'},
            {'header': 'Sparkline Chart'},
       ]
    })
    
    workbook.define_name('SparklineData',
                         '=XLOOKUP(FinalResults[#this row], FinalResults[Employee], EmployeeData[[1]:[5]])')

    worksheet2.add_sparkline(1, 1, {'range': "SparklineData"})
    worksheet2.add_sparkline(2, 1, {'range': "SparklineData"})

    workbook.close()


if __name__ == '__main__':
    main()

Any help or pointers would be appreciated!

Answer 1

The XLOOKUP() formula needs to be prefixed with _xlfn. since it is a "Future Formula" (note, XlsxWriter adds this automatically in a standard formula but not in defined names or some other places like conditional formats). The [#this row] variable should also be sentence case.

Like this:

    workbook.define_name('SparklineData',
                         '=_xlfn.XLOOKUP(FinalResults[#This Row], FinalResults[Employee], EmployeeData[[1]:[5]])')

With those two changes you should get the output you expect: