表格

表格#

工作表的表格是对单元格组的引用。这使得某些操作(如样式化表中的单元格)更加容易。

创建表格#

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],
]

# 添加列标题。NB. 这些必须是字符串
ws.append(["Fruit", "2011", "2012", "2013", "2014"])
for row in data:
    ws.append(row)

tab = Table(displayName="Table1", ref="A1:E5")

# 添加带有条纹行和条纹列的默认样式
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("../build/table.xlsx")

---------------------------------------------------------------------------
FileNotFoundError                         Traceback (most recent call last)
Cell In[1], line 31
     26 '''
     27 Table must be added using ws.add_table() method to avoid duplicate names.
     28 Using this method ensures table name is unque through out defined names and all other table name. 
     29 '''
     30 ws.add_table(tab)
---> 31 wb.save("../build/table.xlsx")

File /opt/hostedtoolcache/Python/3.12.7/x64/lib/python3.12/site-packages/openpyxl/workbook/workbook.py:386, in Workbook.save(self, filename)
    384 if self.write_only and not self.worksheets:
    385     self.create_sheet()
--> 386 save_workbook(self, filename)

File /opt/hostedtoolcache/Python/3.12.7/x64/lib/python3.12/site-packages/openpyxl/writer/excel.py:291, in save_workbook(workbook, filename)
    279 def save_workbook(workbook, filename):
    280     """Save the given workbook on the filesystem under the name filename.
    281 
    282     :param workbook: the workbook to save
   (...)
    289 
    290     """
--> 291     archive = ZipFile(filename, 'w', ZIP_DEFLATED, allowZip64=True)
    292     workbook.properties.modified = datetime.datetime.now(tz=datetime.timezone.utc).replace(tzinfo=None)
    293     writer = ExcelWriter(workbook, archive)

File /opt/hostedtoolcache/Python/3.12.7/x64/lib/python3.12/zipfile/__init__.py:1331, in ZipFile.__init__(self, file, mode, compression, allowZip64, compresslevel, strict_timestamps, metadata_encoding)
   1329 while True:
   1330     try:
-> 1331         self.fp = io.open(file, filemode)
   1332     except OSError:
   1333         if filemode in modeDict:

FileNotFoundError: [Errno 2] No such file or directory: '../build/table.xlsx'

表名在工作簿中必须是唯一的。默认情况下，创建表时，第一行有一个标题，所有列的过滤器和表标题和列标题必须始终包含字符串。

警告

在仅写模式下，您必须手动向表中添加列标题，并且值必须始终与相应单元格的值相同(参见下面的示例)，否则 Excel 可能会认为文件无效并删除表。

样式是使用 TableStyleInfo 对象管理的。这允许您条纹行或列，并应用不同的配色方案。

ws.tables 是类似字典的对象，包含特定工作表中的所有表：

ws.tables

{'Table1': <openpyxl.worksheet.table.Table object>
 Parameters:
 id=1, name='Table1', displayName='Table1', comment=None, ref='A1:E5', tableType=None, headerRowCount=1, insertRow=None, insertRowShift=None, totalsRowCount=None, totalsRowShown=None, published=None, headerRowDxfId=None, dataDxfId=None, totalsRowDxfId=None, headerRowBorderDxfId=None, tableBorderDxfId=None, totalsRowBorderDxfId=None, headerRowCellStyle=None, dataCellStyle=None, totalsRowCellStyle=None, connectionId=None, autoFilter=None, sortState=None, tableColumns=[], tableStyleInfo=<openpyxl.worksheet.table.TableStyleInfo object>
 Parameters:
 name='TableStyleMedium9', showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=True}

按名称或范围获取表#

ws.tables["Table1"] # 或者 ws.tables["A1:E5"]

<openpyxl.worksheet.table.Table object>
Parameters:
id=1, name='Table1', displayName='Table1', comment=None, ref='A1:E5', tableType=None, headerRowCount=1, insertRow=None, insertRowShift=None, totalsRowCount=None, totalsRowShown=None, published=None, headerRowDxfId=None, dataDxfId=None, totalsRowDxfId=None, headerRowBorderDxfId=None, tableBorderDxfId=None, totalsRowBorderDxfId=None, headerRowCellStyle=None, dataCellStyle=None, totalsRowCellStyle=None, connectionId=None, autoFilter=None, sortState=None, tableColumns=[], tableStyleInfo=<openpyxl.worksheet.table.TableStyleInfo object>
Parameters:
name='TableStyleMedium9', showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=True

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

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

<openpyxl.worksheet.table.Table object>
Parameters:
id=1, name='Table1', displayName='Table1', comment=None, ref='A1:E5', tableType=None, headerRowCount=1, insertRow=None, insertRowShift=None, totalsRowCount=None, totalsRowShown=None, published=None, headerRowDxfId=None, dataDxfId=None, totalsRowDxfId=None, headerRowBorderDxfId=None, tableBorderDxfId=None, totalsRowBorderDxfId=None, headerRowCellStyle=None, dataCellStyle=None, totalsRowCellStyle=None, connectionId=None, autoFilter=None, sortState=None, tableColumns=[], tableStyleInfo=<openpyxl.worksheet.table.TableStyleInfo object>
Parameters:
name='TableStyleMedium9', showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=True

获取工作表中所有表的表名和范围#

返回表名及其范围的列表。

ws.tables.items()

[('Table1', 'A1:E5')]

删除表格#

del ws.tables["Table1"]

工作表中的表格数#

len(ws.tables)

手动添加列标题#

在只写模式下，你可以只添加没有标题的表格：

table.headerRowCount = False

或者手动初始化列标题：

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

过滤器#

过滤器将自动添加到包含标题行的表中。不可能在没有过滤器的情况下创建带有标题行的表格。

表格作为打印区域#

Excel 可以生成打印区域设置为表名的文档。然而，Openpyxl 不能解析这样的动态定义，并且在尝试这样做时会引发警告。

如果需要处理这个问题，可以提取表格的范围，并将打印区域定义为适当的单元格范围。

from openpyxl import load_workbook
wb = load_workbook("../build/QueryTable.xlsx")
ws = wb.active
table_range = ws.tables["InvoiceData"]
ws.print_area = table_range.ref  # Ref 是表当前覆盖的单元格范围

表格

目录

表格#