Create Excel Tables from Dictionary of Dataframes

Question

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()

Does this answer your question? [How to create Excel \*\*Table\*\* with pandas.to\_excel()?](https://stackoverflow.com/questions/58326392/how-to-create-excel-table-with-pandas-to-excel) — RichieV, Sep 04 '20 at 14:58
Besides separating on different sheets as in [this question](https://stackoverflow.com/q/14225676/6692898), the one flagged as duplicated shows how to create table objects in excel with `openpyxl` — RichieV, Sep 04 '20 at 15:01
@RichieV Thanks for the posting the link. Now I know that it's possible to accomplish with openpyxl. Unfortunately I am a bit confused on how to code this for a dictionary of multiple dataframes. — amongo, Sep 04 '20 at 16:47
Follow the suggestions on those answers, if you get stuck in a very specific line you can search for it or add a new question. Bozho has [a good blog post](https://techblog.bozho.net/tips-for-identifying-and-debugging-problems/) for debugging on your own before posting a question. — RichieV, Sep 04 '20 at 16:52

Mike67 · Accepted Answer · 2020-09-04T17:56:28.240

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

Thank you for submitting. But I am looking to add these dataframes as Excel Table Objects. In your approach you have described adding them as regular data. — amongo, Sep 04 '20 at 16:43
Exactly what I was looking for. Solution works as intended. Thank you! — amongo, Sep 04 '20 at 18:11

Create Excel Tables from Dictionary of Dataframes

1 Answers1