Hope someone can shed some light on this as this has beaten me so far today.

Context:
I have a pandas dataframe styled via the pandas styler which I am exporting via a file dialgue so a file name can be entered with the correct extension via the windows file dialogue GUI using tkinter.

During the export process I am using xlsxwriter to format the column width and to format the column with the correct data type which is a GBP currency field £1,234.00

The column width and worksheet zoom setting applies perfectly fine. However the data type is not applying.

Expected Result:
£1,234.00

Actual Result
1234.00

Code below.

        with filedialog.asksaveasfile(mode='w', defaultextension=".xlsx", filetypes=[("Excel files", ".xlsx .xls")]) as file:
            writer = pd.ExcelWriter(file.name, engine="xlsxwriter")
            styler.to_excel(writer, columns=['Consultant','Dept','T/S ID','Client','Candidate','P/E Date','Charge','Pay','Split GP','Split %','Margin%','Comm %','Comm Value','Perm Salary','Placement'], index=False, sheet_name= "561 Comms")
            workbook = writer.book
            worksheet = writer.sheets['561 Comms']

            number_fmt = workbook.add_format({'num_format' : '0'})
            money_fmt = workbook.add_format({'num_format' : '£#,##0.00'})
            rate_fmt = workbook.add_format({'num_format' : '#0.00%'})

            worksheet.set_zoom(80)
            worksheet.set_column('G:I', 20, money_fmt)
            worksheet.set_column('J:L', 12, rate_fmt)
            writer.close()
        except AttributeError as ae:
            print(ae)

The excel file exports and saves perfectly fine – it is just the number_fmt, money_fmt and rate_fmt lines which do not seem to be working properly.

Any information or advice would be massively appreciated!
Thanks

Formatting an Excel Column using Python xlsxwriter

Expected Result:
£1,234.00

Actual Result
1234.00