python - 如何使用 OpenPyXL 遍历 Excel 表中的所有行？

Question

OpenPyXL for Excel Tables 的文档没有提到如何迭代表中的值（参见此处）。

什么是一种有效的方法？

标签： pythonopenpyxl

Answer 1

我想出了以下功能来做到这一点。

from typing import Any, Dict, Generator

from openpyxl import load_workbook
from openpyxl.worksheet.table import Table
from openpyxl.worksheet.worksheet import Worksheet
from openpyxl.utils import rows_from_range

TableRow = Dict[str, Any]

def iter_table_rows(ws:Worksheet, tb:Table) -> Generator[TableRow, None, None]:
    """Iterate over rows from a table with headers (row as dictionary)"""
    def get_row_values(row_cell_ref):
        return [ws[c].value for c in row_cell_ref]
    
    iter_rows = rows_from_range(tb.ref)
    headers = get_row_values(next(iter_rows))
    
    for row_cells in iter_rows:
        yield {h:v for h,v in zip(headers, get_row_values(row_cells))}


wb = load_workbook("my_file.xlsx")
ws = wb.active

tb = ws.tables["MyTable"]
for row in iter_table_rows(ws, tb):
    print(row)