如何使用pandas.to_excel（）创建Excel ** Table **？ [英] How to create Excel **Table** with pandas.to_excel()?

问题描述

需要通过数据框以编程方式实现此目标：

Need the achieve this programmatically from a dataframe:

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

推荐答案

to_excel 。解决方法是打开生成的xlsx文件，并在其中添加 openpyxl 的表：

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:{chr(len(df.columns)+64)}{len(df)+1}')
wb[sheetname].add_table(tab)
wb.save(filename)

请注意所有表头必须为字符串。如果您有未命名的索引（这是规则），则第一个单元格（A1）将为空，这会导致文件损坏。为避免这种情况，请为您的索引命名（如上所示）或使用以下命令导出不带索引的数据框：

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)

这篇关于如何使用pandas.to_excel（）创建Excel ** Table **？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！