I have dictionary of dataframes.
dd = {
'table': pd.DataFrame({'Name':['Banana'], 'color':['Yellow'], 'type':'Fruit'}),
'another_table':pd.DataFrame({'city':['Atlanta'],'state':['Georgia'], 'Country':['United States']}),
'and_another_table':pd.DataFrame({'firstname':['John'], 'middlename':['Patrick'], 'lastnme':['Snow']}),
}
I would like to create an Excel file which contains Excel Table objects created from these dataframes. Each Table needs to be on a separate Tab/Sheet and Table names should match dataframe names.
Is this possible to do with Python?
So far I was only able to export data to Excel normally without converting to tables using xlsxwriter
writer = pd.ExcelWriter('Results.xlsx', engine='xlsxwriter')
for sheet, frame in dd.items():
frame.to_excel(writer, sheet_name = sheet)
writer.save()
For writing multiple sheets from Pandas, use the openpyxl library. In addition, to prevent overwriting, set the workbook sheets before each update.
Try this code:
import pandas as pd
import openpyxl
dd = {
'table': pd.DataFrame({'Name':['Banana'], 'color':['Yellow'], 'type':'Fruit'}),
'another_table':pd.DataFrame({'city':['Atlanta'],'state':['Georgia'], 'Country':['United States']}),
'and_another_table':pd.DataFrame({'firstname':['John'], 'middlename':['Patrick'], 'lastnme':['Snow']}),
}
filename = 'Results.xlsx' # must exist
wb = openpyxl.load_workbook(filename)
writer = pd.ExcelWriter(filename, engine='openpyxl')
for sheet, frame in dd.items():
writer.sheets = dict((ws.title, ws) for ws in wb.worksheets) # need this to prevent overwrite
frame.to_excel(writer, index=False, sheet_name = sheet)
writer.save()
# convert data to tables
wb = openpyxl.load_workbook(filename)
for ws in wb.worksheets:
mxrow = ws.max_row
mxcol = ws.max_column
tab = openpyxl.worksheet.table.Table(displayName=ws.title, ref="A1:" + ws.cell(mxrow,mxcol).coordinate)
ws.add_table(tab)
wb.save(filename)
Output