如何在openpyxl只写模式工作表中正确添加表格？

我需要加载一个12万行的大Excel文件，添加表格后重新保存。用常规openpyxl操作会触发MemoryError，所以采用只读模式打开原文件，再用只写模式创建新工作簿。但执行newSheet.add_table(tab)时出现警告In write-only mode you must add table columns manually，保存后的文件损坏，Excel提示有问题。

原代码如下：

import openpyxl
from openpyxl.worksheet.table import Table, TableStyleInfo
from openpyxl.utils import get_column_letter

# Load excel file
wb = openpyxl.load_workbook(excelFile, read_only=True)
ws = wb[wb.sheetnames[0]]

# Create table
maxRow = ws.max_row
maxCol = get_column_letter(ws.max_column)
tab = Table(displayName="Table1", ref=f"A1:{maxCol}{maxRow}")
style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False,
                       showLastColumn=False, showRowStripes=True, showColumnStripes=True)

tab.tableStyleInfo = style

# Create a new workbook and add a worksheet with optimized write
newBook = openpyxl.Workbook(write_only=True)
newSheet = newBook.create_sheet()
newSheet.title = 'test'

for row in ws.rows:
    newSheet.append((cell.value for cell in row))

# Initialize headers and add to table
tab._initialise_columns()
for column, value in zip(tab.tableColumns, headers):
    column.name = value

# Add table
newSheet.add_table(tab)  # Warning here
newBook.save(f"C:\\testSaveDirec\\test.xlsx")

问题原因 #

警告信息已经明确指出：只写模式下必须手动添加表格列。原代码中使用的tab._initialise_columns()是openpyxl的内部方法，在只写模式下无法正确初始化表格列的元数据，导致Excel无法识别表格结构，最终文件损坏。

修正方案 #

核心是放弃依赖内部方法，手动为表格创建每一列的完整定义，确保表格的引用范围与实际写入的行列完全匹配。

修正后的代码：

import openpyxl
from openpyxl.worksheet.table import Table, TableStyleInfo, TableColumn
from openpyxl.utils import get_column_letter

# 加载原文件（只读模式）
wb = openpyxl.load_workbook(excelFile, read_only=True)
ws = wb[wb.sheetnames[0]]

# 获取行列信息
max_row = ws.max_row
max_col = ws.max_column
max_col_letter = get_column_letter(max_col)

# 手动获取表头（假设原表第一行为表头）
headers = [cell.value for cell in next(ws.rows)]

# 创建表格并配置样式
tab = Table(displayName="Table1", ref=f"A1:{max_col_letter}{max_row}")
style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False,
                       showLastColumn=False, showRowStripes=True, showColumnStripes=True)
tab.tableStyleInfo = style

# 手动添加表格列（只写模式必须步骤）
for idx, header in enumerate(headers, 1):
    col_letter = get_column_letter(idx)
    # 为每一列创建完整定义，指定名称、ID和引用范围
    col = TableColumn(name=header, id=str(idx), ref=f"{col_letter}1:{col_letter}{max_row}")
    tab.tableColumns.append(col)

# 创建只写模式新工作簿
newBook = openpyxl.Workbook(write_only=True)
newSheet = newBook.create_sheet(title='test')

# 写入所有行（包括表头）
newSheet.append(headers)  # 先写入表头
for row in ws.iter_rows(min_row=2):  # 从第二行开始写入数据
    newSheet.append([cell.value for cell in row])

# 添加表格到工作表
newSheet.add_table(tab)

# 保存文件
newBook.save(r"C:\testSaveDirec\test.xlsx")

关键说明 #

• 手动创建TableColumn对象，每个列必须指定name（表头名称）、id（列序号）和ref（列的单元格范围），确保Excel能解析完整的表格结构
• 提前单独提取表头，避免在遍历行时重复处理，同时保证表格列名与实际表头一致
• 表格的ref范围必须和实际写入的单元格范围完全匹配，不能有行数或列数偏差

内容的提问来源于stack exchange，提问作者BraedonM