from openpyxl import Workbook
from openpyxl.worksheet.table import Table, TableStyleInfo
wb = Workbook()
ws = wb.active
data = [
    ['Apples', 10000, 5000, 8000, 6000],
    ['Pears',   2000, 3000, 4000, 5000],
    ['Bananas', 6000, 6000, 6500, 6000],
    ['Oranges',  500,  300,  200,  700],
]
# add column headings. NB. these must be strings
ws.append(["Fruit", "2011", "2012", "2013", "2014"])
for row in data:
    ws.append(row)
tab = Table(displayName="Table1", ref="A1:E5")
# Add a default style with striped rows and banded columns
style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False,
                       showLastColumn=False, showRowStripes=True, showColumnStripes=True)
tab.tableStyleInfo = style
'''
Table must be added using ws.add_table() method to avoid duplicate names.
Using this method ensures table name is unque through out defined names and all other table name. 
'''
ws.add_table(tab)
wb.save("table.xlsx")

Table names must be unique within a workbook. By default tables are created with a header from the first row and filters for all the columns and table headers and column headings must always contain strings.

警告

In write-only mode you must add column headings to tables manually and the values must always be the same as the values of the corresponding cells (ee below for an example of how to do this), otherwise Excel may consider the file invalid and remove the table.

Styles are managed using the the TableStyleInfo object. This allows you to stripe rows or columns and apply the different colour schemes.

操控表格 ¶

ws.tables is a dictionary-like object of all the tables in a particular worksheet:

>>> ws.tables
{"Table1",  <openpyxl.worksheet.table.Table object>}

按名称或范围获取表格 ¶

>>> ws.tables["Table1"]
or
>>> ws.tables["A1:D10"]

遍历工作表中的所有表格 ¶

>>> for table in ws.tables.values():
>>>    print(table)

获取工作表中所有表格的表格名称和范围 ¶

Returns a list of table name and their ranges.

>>> ws.tables.items()
>>> [("Table1", "A1:D10")]

删除表格 ¶

>>> del ws.tables["Table1"]

工作表中的表格数 ¶

>>> len(ws.tables)
>>> 1

手动添加列头 ¶

In write-only mode you can either only add tables without headings:

>>> table.headerRowCount = False

Or initialise the column headings manually:

>>> headings = ["Fruit", "2011", "2012", "2013", "2014"] # all values must be strings
>>> table._initialise_columns()
>>> for column, value in zip(table.tableColumns, headings):
    column.name = value