如何在Python中实现Excel的“格式化为表”功能

Question

我正在使用 xlwt 来创建 Excel 表格。在 Excel 中，有一个功能叫做 格式化为表格，这个功能可以让每一列都有自动筛选的功能。请问有没有办法用 Python 来实现这个功能？

Answer 1

如果你想把数据表的格式应用到一个用 XlsxWriter 输出到 Excel 的数据框（dataframe），可以参考这个文档：https://xlsxwriter.readthedocs.io/example_pandas_table.html，这是根据评论的建议。

下面是我最初的一个不太优雅的解决方案 format_tbl：

import pandas as pd

def format_tbl(writer, sheet_name, df):
    outcols = df.columns
    if len(outcols) > 25:
        raise ValueError('table width out of range for current logic')
    tbl_hdr = [{'header':c} for c in outcols]
    bottom_num = len(df)+1
    right_letter = chr(65-1+len(outcols))
    tbl_corner = right_letter + str(bottom_num)

    worksheet = writer.sheets[sheet_name]
    worksheet.add_table('A1:' + tbl_corner,  {'columns':tbl_hdr})


df = pd.DataFrame({
    'city': ['New York', 'London', 'Prague'], 
    'population': [19.5, 7.4, 1.3], 
    'date_of_birth': ['1625', '43', 'early 8th century'],
    'status_of_magnetism': ['nice to visit', 'nice to visit', 'definetely MUST visit']
})

fn_out='blah.xlsx'
with pd.ExcelWriter(fn_out, mode='w', engine='xlsxwriter') as writer: 
    sheet_name='xxx'
    df.to_excel(writer, sheet_name=sheet_name, index=False)
    format_tbl(writer, sheet_name, df)

Answer 2

你也可以用Pandas来实现这个功能。下面是一个例子：

import pandas as pd

df = pd.DataFrame({
    'city': ['New York', 'London', 'Prague'], 
    'population': [19.5, 7.4, 1.3], 
    'date_of_birth': ['1625', '43', 'early 8th century'],
    'status_of_magnetism': ['nice to visit', 'nice to visit', 'definetely MUST visit']
})

# initialize ExcelWriter and set df as output
writer = pd.ExcelWriter(r'D:\temp\sample.xlsx', engine='xlsxwriter') 
df.to_excel(writer, sheet_name='Cities', index=False)

# worksheet is an instance of Excel sheet "Cities" - used for inserting the table
worksheet = writer.sheets['Cities']
# workbook is an instance of the whole book - used i.e. for cell format assignment 
workbook = writer.book

接下来，通过workbook.add_format来定义单元格的格式，比如旋转文字、设置垂直和水平对齐方式。

header_cell_format = workbook.add_format()
header_cell_format.set_rotation(90)
header_cell_format.set_align('center')
header_cell_format.set_align('vcenter')

然后……

# create list of dicts for header names 
#  (columns property accepts {'header': value} as header name)
col_names = [{'header': col_name} for col_name in df.columns]

# add table with coordinates: first row, first col, last row, last col; 
#  header names or formatting can be inserted into dict 
worksheet.add_table(0, 0, df.shape[0], df.shape[1]-1, {
    'columns': col_names,
    # 'style' = option Format as table value and is case sensitive 
    # (look at the exact name into Excel)
    'style': 'Table Style Medium 10'
})

另外，你可以使用worksheet.add_table('A1:D{}'.format(shape[0]), {...})，不过如果你的数据框（df）有更多列或者起始位置不一样，就需要计算像AA、AB这样的组合（而不是简单的"D"）。

最后，下面的循环会重新写入表头，并应用header_cell_format。虽然我们在worksheet.add_table(...)中已经做过这个步骤，看起来有点多余，但这是使用Excel的自动调整选项的方法。如果不这样做，所有的表头单元格都会有默认的宽度（或者如果你使用90度旋转的话，会有默认的高度），这样可能会导致内容看不全，或者需要使用set_shrink()，但那样内容就会变得难以阅读了 :）。

(在Office 365中测试过)

# skip the loop completly if AutoFit for header is not needed
for i, col in enumerate(col_names):
    # apply header_cell_format to cell on [row:0, column:i] and write text value from col_names in
    worksheet.write(0, i, col['header'], header_cell_format)

# save writer object and created Excel file with data from DataFrame     
writer.save()

Answer 3

好的，经过在网上搜索，我发现用 xlwt 是做不到的，但用 XlsxWriter 就可以，而且非常简单方便。