openpyxl write_only 流式写百万行 Excel 实战

问题场景

用 openpyxl 生成报表时，默认 Workbook 会把每个单元格包装成 Cell 对象并保留在工作表中。行数到十万、百万级后，内存占用会明显上升，甚至还没保存就被系统终止。典型写法是循环 ws.cell(row=i, column=j, value=...)，既慢又容易堆积对象。

如果业务只要求顺序生成一个 Excel 文件，不需要回读、随机改单元格，就适合用只写模式：Workbook(write_only=True)。它通过 append() 顺序写行，单元格对象不会长期留在内存里。

实现思路

1. 创建只写工作簿，并在写数据前设置列宽。
2. 表头用 WriteOnlyCell 设置字体、填充和对齐，再 append。
3. 数据行先在内存中攒一个小批次，达到阈值后逐行 append，然后清空批次。这里说的 flush 是清空自定义缓冲，不是调用 ws.flush()；openpyxl 的只写工作表没有公开的 flush 方法，append 会交给内部 writer 写出，最后 wb.save() 收尾。
4. 只对必要单元格加样式，避免每行创建大量样式对象。

最小可运行示例

from openpyxl import Workbook
from openpyxl.cell import WriteOnlyCell
from openpyxl.styles import Font, PatternFill, Alignment

def make_header(ws, headers):
    cells = []
    for text in headers:
        c = WriteOnlyCell(ws, value=text)
        c.font = Font(bold=True, color='FFFFFF')
        c.fill = PatternFill('solid', fgColor='4472C4')
        c.alignment = Alignment(horizontal='center', vertical='center')
        cells.append(c)
    ws.append(cells)

def write_big_excel(path='big.xlsx', total=1_000_000, batch_size=5000):
    wb = Workbook(write_only=True)
    ws = wb.create_sheet('data')

    # 列宽需在写行前设置；字母为列号占位
    widths = {'A': 18, 'B': 10, 'C': 12, 'D': 28}
    for col, width in widths.items():
        ws.column_dimensions[col].width = width

    make_header(ws, ['订单号', '数量', '金额', '备注'])

    buf = []
    for i in range(1, total + 1):
        buf.append([
            f'NO{i:08d}',
            i % 100,
            round((i % 1000) * 0.01, 2),
            '占位备注',
        ])
        if len(buf) >= batch_size:
            for row in buf:
                ws.append(row)
            buf.clear()  # 清空自定义批次，释放引用

    for row in buf:
        ws.append(row)

    wb.save(path)

if __name__ == '__main__':
    write_big_excel('big.xlsx', total=1_000_000)

运行方法：先安装依赖 pip install openpyxl，保存为 write_big_excel.py，执行 python write_big_excel.py。脚本会在当前目录生成 big.xlsx。把 total 调小可先验证表头和列宽，再逐步放大。

列宽与样式注意点

列宽通过 ws.column_dimensions['A'].width 设置，单位近似字符宽度，不是像素。只写模式下不要写 ws['A1'] = ...，也不要在 append 后再改某个单元格样式。需要样式的单元格应先构造 WriteOnlyCell，设置 font、fill、alignment 等，再放入 append 的行列表。数据行样式越少，临时对象越少；如非必要，不要给百万行逐格设置边框和底色。

边界与限制

• Excel 单表最多 1,048,576 行，表头占一行，数据行应控制在 1,048,575 行以内。
• 只写模式适合顺序生成，不适合回读、随机访问、按坐标改值；需要二次编辑时，应生成后再用普通模式或流式读取工具处理。
• 公式可以以 = 开头写入，但 openpyxl 不负责计算结果，打开文件后由 Excel 计算。
• 内存下降不等于零内存；共享字符串、样式对象、Python 行列表仍会占用内存。批次不宜过大，按环境调整。
• 保存阶段仍需磁盘空间和时间；超大文件要预留临时目录空间，并关注磁盘写入错误。
• 合并单元格、数据验证、图表等能力在不同 openpyxl 版本中支持程度不同，使用前先用小文件验证。