ExcelWriter align center error when opening output file

Question

This code is working well:

excel_path = os.path.join(path,'Report For Yr '+str(date.today().year)+' Mth ' +mth+'.xlsx')

writer = pd.ExcelWriter(excel_path, engine='xlsxwriter')

df2.to_excel(writer, sheet_name='report')

workbook  = writer.book

worksheet = writer.sheets['report']

format1 = workbook.add_format({'num_format': '#,###'})

format2 = workbook.add_format({'num_format': '0.00%'})

format3 = workbook.add_format({'num_format': '#,##0.00'})

worksheet.set_column('B:I', 10, format1)

worksheet.set_column('J:J', 10, format2)

worksheet.set_column('L:L', 10, format2)

worksheet.set_column('K:K', 10, format3)

writer.save() 

I'm trying to align center for certain columns. The code ran without any error but there is an error when opening the excel file:

Excel cannot open the file because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.

I tried with the working code and change this:

format1 = workbook.add_format({'num_format': '#,###'},{'align': 'center'})

got the error message

I tried with the working code and change this:

worksheet.set_column('B:I', 10, format1,{'align': 'center'})

also got the error message

Anyone can help?

Answer 1

I tried with the working code and change this:

That isn't the correct syntax. You should pass a single dict of values. Like this:

format1 = workbook.add_format({'num_format': '#,###', 'align': 'center'})