excel - 如何使用 pandas.to_excel() 创建 Excel **Table**？

Question

需要从数据框中以编程方式实现这一点:


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

Answer 1

这是使用 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()

输出:

更新 :我在 XlsxWriter 文档中添加了一个类似的示例: Example: Pandas Excel output with a worksheet table