python - 如何在python中执行excel的 'format as table'

我正在使用xlwt在和excel 中创建表。在和excelt 中，有一种功能格式作为表格，该表格使表格的每一列都有一个自动过滤器。有没有办法使用python做到这一点？

最佳答案

您也可以使用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 并应用header_cell_format。我们已经在worksheet.add_table(...)中完成了此操作，因此它看起来很多余，但这是使用Excel的 AutoFit 选项的一种方法-如果没有此选项，则所有 header 单元格都将具有默认宽度(如果您使用90degs旋转，则单元格高度将是默认值)，因此整个内容都是可见的，或者必须应用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()

关于python - 如何在python中执行excel的 'format as table'，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/22831520/