How to write a new line in pd.ExcelWriter using xlsxwritter engine- in python at the bottom?

Question

I have several pandas data frame and I'm using ExcelWriter to create an Excel sheet. I usually use below command

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

My all data frame go to one Excel sheet and I want to write a long sentence, after the last data frame. My program is automated and I don't know exactly the length of the data frame each time. So I can't use something like below. I mean I can't put something like 'B44:F46'

import xlsxwriter
workbook = xlsxwriter.Workbook('hello.xlsx')
worksheet = workbook.add_worksheet()

## adding 5-6 data frames

header2 = workbook.add_format({
    'bold':     True,
    'align':    'center',
    'border':   6,
    'valign':   'vcenter',
    'fg_color': '#D7E4BC',
    'font_name':'Calibri',
    'font_size': 12

})

worksheet.merge_range('B44:F46', "CompanyName:ABC \n Country:USA", header2)


workbook.close()

Is there a way to do thatin python?

Any suggestion would be appreciated. Thanks in advance!!

Answer 1

There are a few things you need to do to make this work:

• Track the end row in the output dataframes using df.shape or len(df)
• Use the (row, col) syntax in merge_range() instead of the A1 range syntax
• Add text_wrap to the format

Here is a working example:

import pandas as pd


# Create some Pandas dataframes from some data.
df1 = pd.DataFrame({'Data 1': [11, 12]})
df2 = pd.DataFrame({'Data 2': [21, 22, 23]})
df3 = pd.DataFrame({'Data 3': [31, 32, 33, 34]})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_example.xlsx', engine='xlsxwriter')

start_row = 0

# Output the data frames and keep track of the start/end row.
for df in (df1, df2, df3):
    df.to_excel(writer, sheet_name='Sheet1', startrow=start_row, index=False)
    start_row += df.shape[0] + 1

# Get the xlsxwriter workbook and worksheet objects.
workbook = writer.book
worksheet = writer.sheets['Sheet1']

# Add a format
header2 = workbook.add_format({
    'bold':     True,
    'align':    'center',
    'border':   6,
    'valign':   'vcenter',
    'fg_color': '#D7E4BC',
    'font_name': 'Calibri',
    'font_size': 12,
    'text_wrap': True
})

# Write a merge range.
start_col = 0
end_col = 3

worksheet.merge_range(start_row, start_col, start_row + 1, end_col,
                      "CompanyName: ABC\nCountry: USA", header2)

# Close the Pandas Excel writer and output the Excel file.
writer.save()

Output: