add_format not working as expected with xlsxwriter

Question

I'm trying to add a Title to an excel file, using pandas and xlsxwriter

The worksheet.merge_range merge_format isn't working as expected.

df = pd.DataFrame.from_records(pd.json_normalize(data))
writer = pd.ExcelWriter("teste12.xlsx", engine='xlsxwriter')
df.to_excel(writer, sheet_name=nome_tabela, index=False, startcol=0, startrow=5)
workbook = xlsxwriter.Workbook(writer)
merge_format = workbook.add_format()
merge_format.set_border(True)
merge_format.set_bold(True)
merge_format.set_align("center")
merge_format.set_align("vcenter")
merge_format.set_font_size(20)
worksheet = writer.sheets[nome_tabela]
worksheet.merge_range(0,0,3,6,"Test", merge_format)
writer.save()

I also tryed using the merge_format as the documentation example:

merge_format = workbook.add_format({
'bold': 1,
'border': 1,
'align': 'center',
'valign': 'vcenter',
'fg_color': 'yellow',
'font_size' : 20})

In both cases the bold, border and align work as intended. The font_size, valign and fg_color don't work.

What am I doing wrong ???

Answer 1

The issue is that you are overwriting the workbook created by Pandas with a new workbook created by xlsxwriter.Workbook().

The correct way to get the Pandas created workbook is shown in Working with Python Pandas and XlsxWriter and in the working example below:

import pandas as pd


nome_tabela = 'Sheet1'

df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})

writer = pd.ExcelWriter("teste12.xlsx", engine='xlsxwriter')
df.to_excel(writer, sheet_name=nome_tabela, index=False, startcol=0, startrow=5)

# Get the Pandas created workbook.
workbook  = writer.book
merge_format = workbook.add_format()

merge_format.set_border(True)
merge_format.set_bold(True)
merge_format.set_align("center")
merge_format.set_align("vcenter")
merge_format.set_font_size(20)

worksheet = writer.sheets[nome_tabela]

worksheet.merge_range(0, 0, 3, 6, "Test", merge_format)
writer.save()

Output: