How to create Excel **Table** with pandas.to_excel()?

Question

Need the achieve this programmatically from a dataframe:

https://learn.microsoft.com/en-us/power-bi/service-admin-troubleshoot-excel-workbook-data

Answer 1

Here is one way to do it using XlsxWriter:

import pandas as pd

# Create a Pandas dataframe from some data.
data = [10, 20, 30, 40, 50, 60, 70, 80]
df = pd.DataFrame({'Rank': data,
                   'Country': data,
                   'Population': data,
                   'Data1': data,
                   'Data2': data})

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

# Convert the dataframe to an XlsxWriter Excel object. Turn off the default
# header and index and skip one row to allow us to insert a user defined
# header.
df.to_excel(writer, sheet_name='Sheet1', startrow=1, header=False, index=False)

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

# Get the dimensions of the dataframe.
(max_row, max_col) = df.shape

# Create a list of column headers, to use in add_table().
column_settings = []
for header in df.columns:
    column_settings.append({'header': header})

# Add the table.
worksheet.add_table(0, 0, max_row, max_col - 1, {'columns': column_settings})

# Make the columns wider for clarity.
worksheet.set_column(0, max_col - 1, 12)

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

Output:

Update: I've added a similar example to the XlsxWriter docs: Example: Pandas Excel output with a worksheet table

Answer 2

You can't do it with to_excel. A workaround is to open the generated xlsx file and add the table there with openpyxl:

import pandas as pd

df = pd.DataFrame({'Col1': [1,2,3], 'Col2': list('abc')})

filename = 'so58326392.xlsx'
sheetname = 'mySheet'
with pd.ExcelWriter(filename) as writer:
    if not df.index.name:
        df.index.name = 'Index'
    df.to_excel(writer, sheet_name=sheetname)
    
import openpyxl
wb = openpyxl.load_workbook(filename = filename)
tab = openpyxl.worksheet.table.Table(displayName="df", ref=f'A1:{openpyxl.utils.get_column_letter(df.shape[1])}{len(df)+1}')
wb[sheetname].add_table(tab)
wb.save(filename)

Please note the all table headers must be strings. If you have an un-named index (which is the rule) the first cell (A1) will be empty which leads to file corruption. To avoid this give your index a name (as shown above) or export the dataframe without the index using:

df.to_excel(writer, sheet_name=sheetname, index=False)

Answer 3

Another workaround, if you don't want to save, re-open, and re-save, is to use xlsxwriter. It can write ListObject tables directly, but does not do so directly from a dataframe, so you need to break out the parts:

import pandas as pd
import xlsxwriter as xl

df = pd.DataFrame({'Col1': [1,2,3], 'Col2': list('abc')})

filename = 'output.xlsx'
sheetname = 'Table'
tablename = 'TEST'

(rows, cols) = df.shape
data = df.to_dict('split')['data']
headers = []
for col in df.columns:
    headers.append({'header':col})

wb = xl.Workbook(filename)
ws = wb.add_worksheet()

ws.add_table(0, 0, rows, cols-1,
    {'name': tablename
    ,'data': data
    ,'columns': headers})

wb.close()

The add_table() function expects 'data' as a list of lists, where each sublist represents a row of the dataframe, and 'columns' as a list of dicts for the header where each column is specified by a dictionary of the form {'header': 'ColumnName'}.

Answer 4

I created a package to write properly formatted excel tables from pandas: pandas-xlsx-tables

from pandas_xlsx_tables import df_to_xlsx_table
import pandas as pd

data = [10, 20, 30, 40, 50, 60, 70, 80]
df = pd.DataFrame({'Rank': data,
                'Country': data,
                'Population': data,
                'Strings': [f"n{n}" for n in data],
                'Datetimes': [pd.Timestamp.now() for _ in range(len(data))]})

df_to_xlsx_table(df, "my_table", index=False, header_orientation="diagonal")

You can also do the reverse with xlsx_table_to_df

Answer 5

Based on the answer of @jmcnamara, but as a convenient function and using "with" statement:

import pandas as pd

def to_excel(df:pd.DataFrame, excel_name: str, sheet_name: str, startrow=1, startcol=0):
    """ Exports pandas dataframe as a formated excel table """
    with pd.ExcelWriter(excel_name, engine='xlsxwriter') as writer:
        df.to_excel(writer, sheet_name=sheet_name, startrow=startrow, startcol=startcol, header=True, index=False)
        workbook = writer.book
        worksheet = writer.sheets[sheet_name]
        max_row, max_col = df.shape

        olumn_settings = [{'header': header} for header in df.columns]
        worksheet.add_table(startrow, startcol, max_row+startrow, max_col+startcol-1, {'columns': column_settings})
        # style columns
        worksheet.set_column(startcol, max_col + startcol, 21)