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

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!!

CodePudding user response：

Get the amount of rows in the data frames (assuming you have 2 data frames df0 and df1)

rows = len(df0.index)
rows  = len(df1.index)

Then add them to the row where you're adding the last line

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

CodePudding user response：

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