Pandas Dataframe With Dynamic Title

Suppose I have the following dataframe: df = pd.DataFrame({ 'A':list('abcdef'), 'B':[4,5,4,5,5,4], 'C':[7,8,9,4,2,3], 'D':[1,3,5,7,1,0],

Solution 1:

Here is one way to do it using xlsxwriter as the Pandas Excel engine. Something similar would also be possible using openpyxl:

import pandas as pd

df = pd.DataFrame({'A': list('abcdef'),
                   'B': [4, 5, 4, 5, 5, 4],
                   'C': [7, 8, 9, 4, 2, 3],
                   'D': [1, 3, 5, 7, 1, 0],
                   'E': [5, 3, 6, 9, 2, 4],
                   'F': list('aaabbb')})

writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')

# Shift the dataframe down one row in the Excel file.
df.to_excel(writer, sheet_name='Sheet1', startrow=1)

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

# Write the title to the worksheet.
title = 'The sales'
worksheet.write(0, 0, title)

writer.save()

Output:

Solution 2:

You can use xlwings, it is one of the best libraries to get your data into excel using python

import pandas as pd
import xlwings as xw

df = pd.DataFrame({
        'A':list('abcdef'),
         'B':[4,5,4,5,5,4],
         'C':[7,8,9,4,2,3],
         'D':[1,3,5,7,1,0],
         'E':[5,3,6,9,2,4],
         'F':list('aaabbb')
})


title = 'The sales'

wb = xw.Book("sales.xlsx")

sheet = wb.sheets['Sheet1']

sheet.range('A1').value = title

sheet.range('A2').options(pd.DataFrame, index=False).value = df

wb.save('sales.xlsx')
wb.close()

#If you dont need Index keep above #If you need Index put index=True