Python使用xlsxwriter在一个Excel文件中写入多个Sheet

顾星河

发布时间：2026-10-31| 6人浏览过

原创

详细讲解在Python中使用xlsxwriter向同一个Excel文件（.xlsx）中写入多个Sheet的核心方法与工程实践，包括工作簿与工作表对象关系、基础写入、动态循环生成、跨表公式引用、与pandas整合以及命名避坑规范。

Python 数据处理 · Excel 自动化

在导出业务统计或报表数据时，往往需要将不同维度的数据分别组织到同一个 Excel 文件（.xlsx）中的不同工作表（Sheet）里。例如将“总览指标”、“各销售区域明细”和“产品分类数据”分别归类存放，便于阅读和核算。在 Python 中使用 xlsxwriter 库完成这一需求的核心逻辑非常纯粹：通过 xlsxwriter.Workbook() 创建一个工作簿实例，多次调用该实例的 add_worksheet('Sheet名称') 方法获取多个独立的工作表对象，分别对各工作表写入内容，最后执行 close() 完成文件落盘。

Python使用xlsxwriter在一个Excel文件中写入多个Sheet — 图 1：Python xlsxwriter 多工作表（Multi-Sheet）输出结构全景

一、核心机制与对象关系模型

深入了解 xlsxwriter 的工作机制，能够避免在处理多工作表时出现混淆。在 xlsxwriter 的设计中，整个 Excel 文件的操作围绕三个核心对象展开：

Workbook（工作簿）：代表整个磁盘文件。它是所有工作表、全局格式和图表的根容器。在一次写入任务中，只需要创建一个 Workbook 实例。
Worksheet（工作表）：代表 Excel 底部的具体 Tab 页（Sheet）。每次调用 workbook.add_worksheet() 都会返回一个新的 Worksheet 句柄，数据写入、列宽设定、网格线控制都在此句柄上进行。
Format（单元格格式）：由 workbook.add_format() 统一注册和管理。格式对象归属于 Workbook，因此同一个表头格式或货币格式可以在多个工作表之间无缝复用，不需要为每个工作表重复定义。

需要特别明确的是，xlsxwriter 采用的是基于内存流的高性能只写架构。这意味着向工作表中写入的数据会按行顺序流式处理并写入临时 XML 文件，写入之后无法回读单元格内容，也无法像读取库那样定位已有单元格的值。所有的工作表在调用 workbook.close() 时统一打包为 OpenXML ZIP 容器。

二、基础操作与标准实现步骤

实现多 Sheet 写入并不复杂。先通过 pip 安装依赖（若尚未安装）：

pip install XlsxWriter

下面是一个完整的端到端示例，在一个名为 sales_report.xlsx 的工作簿中分别写入“销售总览”和“订单明细”两个工作表：

import xlsxwriter

# 1. 创建工作簿实例
workbook = xlsxwriter.Workbook('sales_report.xlsx')

# 2. 注册公用单元格格式
header_format = workbook.add_format({
    'bold': True,
    'font_name': '微软雅黑',
    'font_size': 11,
    'bg_color': '#142537',
    'font_color': '#FFFFFF',
    'align': 'center',
    'valign': 'vcenter',
    'border': 1
})

currency_format = workbook.add_format({
    'num_format': '¥#,##0.00',
    'font_name': '微软雅黑',
    'align': 'right'
})

date_format = workbook.add_format({
    'num_format': 'yyyy-mm-dd',
    'font_name': '微软雅黑',
    'align': 'center'
})

# 3. 创建第 1 个工作表：销售总览
ws_summary = workbook.add_worksheet('销售总览')

# 设置列宽
ws_summary.set_column('A:A', 18)
ws_summary.set_column('B:B', 22)

# 写入总览表数据
ws_summary.write('A1', '指标名称', header_format)
ws_summary.write('B1', '数值', header_format)
ws_summary.write('A2', '统计周期')
ws_summary.write('B2', '2026年第一季度')
ws_summary.write('A3', '订单总量')
ws_summary.write('B3', 3)
ws_summary.write('A4', '销售总金额')
ws_summary.write('B4', 8550.00, currency_format)

# 4. 创建第 2 个工作表：订单明细
ws_details = workbook.add_worksheet('订单明细')

# 设置明细表列宽
ws_details.set_column('A:A', 14)
ws_details.set_column('B:B', 16)
ws_details.set_column('C:C', 20)
ws_details.set_column('D:D', 16)

# 写入明细表表头
details_headers = ['订单编号', '下单日期', '客户名称', '成交金额']
ws_details.write_row('A1', details_headers, header_format)

# 写入明细表行记录
orders_data = [
    ('ORD-2026-001', '2026-01-15', '华东创新科技有限公司', 2300.00),
    ('ORD-2026-002', '2026-01-20', '北方数据技术研究院', 4150.00),
    ('ORD-2026-003', '2026-02-08', '南方智能网络信息部', 2100.00)
]

for row_idx, order in enumerate(orders_data, start=1):
    ws_details.write(row_idx, 0, order[0])
    ws_details.write(row_idx, 1, order[1], date_format)
    ws_details.write(row_idx, 2, order[2])
    ws_details.write(row_idx, 3, order[3], currency_format)

# 冻结明细表首行表头
ws_details.freeze_panes(1, 0)

# 5. 显式关闭工作簿以保存文件
workbook.close()

在上述代码中，add_worksheet() 接受一个可选的字符串参数作为工作表名称。如果调用时不传参数（例如 workbook.add_worksheet()），xlsxwriter 会按默认规则依次命名为 Sheet1、Sheet2 等。

三、循环批量生成与动态数据驱动

在实际业务开发中，工作表的数量和名称通常不是硬编码的，而是根据数据库查询或字典数据动态生成的。例如按部门、按月份或按业务大区分组生成。

此时，可以将各组数据组织为一个以“目标工作表名称”为键、数据行列表为值的字典，使用循环逐一创建工作表并填充：

import xlsxwriter
import re

# 模拟按部门归类的业务数据
department_reports = {
    '研发一部': [
        ['工号', '姓名', '项目工时', '完成任务数'],
        ['RD01', '张立', 168, 14],
        ['RD02', '李晓', 160, 11]
    ],
    '市场运营部': [
        ['工号', '姓名', '投放渠道', '获取线索数'],
        ['MKT01', '王敏', '信息流广告', 342],
        ['MKT02', '陈浩', '品牌活动', 188]
    ],
    '财务部': [
        ['科目编码', '科目名称', '借方金额', '贷方金额'],
        ['1001', '库存现金', 50000.00, 42000.00],
        ['1002', '银行存款', 1200000.00, 980000.00]
    ]
}

def clean_sheet_name(name: str) -> str:
    """清洗工作表名称：过滤特殊符号并截断至31字符以内"""
    cleaned = re.sub(r'[:\\/?*\[\]]', '_', str(name)).strip()
    return cleaned[:31] if cleaned else 'Sheet'

# 使用 with 上下文管理器自动管理 close()
with xlsxwriter.Workbook('department_reports.xlsx') as workbook:
    header_style = workbook.add_format({
        'bold': True,
        'bg_color': '#1E3A54',
        'font_color': '#FFFFFF',
        'align': 'center'
    })
    
    seen_names = set()

    for dept_name, rows in department_reports.items():
        # 清洗并确保工作表名不重复
        safe_name = clean_sheet_name(dept_name)
        if safe_name.lower() in seen_names:
            safe_name = f"{safe_name[:28]}_{len(seen_names)}"
        seen_names.add(safe_name.lower())

        # 动态创建工作表
        worksheet = workbook.add_worksheet(safe_name)
        
        # 写入表头与数据
        for r_idx, row_data in enumerate(rows):
            fmt = header_style if r_idx == 0 else None
            worksheet.write_row(r_idx, 0, row_data, fmt)
            
        # 设置适度列宽
        worksheet.set_column(0, len(rows[0]) - 1, 16)

四、跨 Sheet 公式引用与联动计算

当一个 Excel 文件中存在多个工作表时，通常需要在“汇总页”引用其他“明细页”的数据区域进行求和、求平均值或条件统计。

在 xlsxwriter 中写入跨表公式的语法与标准 Excel 公式完全相同，规则如下：

跨表公式规则：如果被引用的工作表名称中包含中文、空格或特殊字符，在公式中必须使用单引号将工作表名称包裹起来，其后紧跟英文感叹号 !。例如 ='订单明细'!D2:D100。若省略单引号，Excel 打开文件时可能会解析错误或报 #REF!。

import xlsxwriter

with xlsxwriter.Workbook('cross_sheet_calc.xlsx') as wb:
    num_fmt = wb.add_format({'num_format': '¥#,##0.00'})

    # 创建明细表并写入数据
    ws_data = wb.add_worksheet('一月明细')
    ws_data.write('A1', '类别')
    ws_data.write('B1', '支出金额')
    ws_data.write('A2', '服务器租赁')
    ws_data.write('B2', 12500, num_fmt)
    ws_data.write('A3', '办公宽带')
    ws_data.write('B3', 3800, num_fmt)
    ws_data.write('A4', '云存储')
    ws_data.write('B4', 4200, num_fmt)

    # 创建汇总表并引用明细表的数据
    ws_summary = wb.add_worksheet('年度汇总')
    ws_summary.write('A1', '项目')
    ws_summary.write('B1', '汇总结果')
    
    ws_summary.write('A2', '一月总支出')
    # 写入跨表求和公式
    ws_summary.write_formula('B2', "=SUM('一月明细'!B2:B4)", num_fmt)

    ws_summary.write('A3', '一月单项平均')
    # 写入跨表平均值公式
    ws_summary.write_formula('B3', "=AVERAGE('一月明细'!B2:B4)", num_fmt)

注意：xlsxwriter 负责将公式文本写入 .xlsx 文件的 XML 节点中，它自身并不内置公式计算引擎。用户使用 Excel 或 WPS 打开生成的文档时，办公软件会自动执行计算并渲染出数值结果。

五、结合 Pandas 输出多 Sheet 文件

在数据分析工作流中，数据常以 pandas.DataFrame 的形态存在。若直接使用 df.to_excel('filename.xlsx')，每次调用都会覆写目标文件，无法直接保存多个 DataFrame。

利用 Pandas 的 ExcelWriter 并指定 engine='xlsxwriter'，可以优雅地将多个 DataFrame 依次输出至同一个工作簿的不同 Sheet，同时依然可以使用 xlsxwriter 的底层原生对象控制格式：

import pandas as pd

# 构建示例 DataFrame
df_users = pd.DataFrame({
    '用户ID': [101, 102, 103],
    '姓名': ['张华', '李明', '王芳'],
    '注册城市': ['北京', '上海', '深圳']
})

df_activity = pd.DataFrame({
    '记录ID': [1, 2, 3, 4],
    '用户ID': [101, 101, 102, 103],
    '访问模块': ['首页', '订单中心', '个人资料', '支付页'],
    '停留秒数': [45, 120, 30, 210]
})

# 借助 ExcelWriter 结合 xlsxwriter 引擎
with pd.ExcelWriter('user_analysis.xlsx', engine='xlsxwriter') as writer:
    # 依次输出到不同 Sheet
    df_users.to_excel(writer, sheet_name='用户主档', index=False)
    df_activity.to_excel(writer, sheet_name='访问明细', index=False)

    # 获取 xlsxwriter 底层工作簿和工作表对象以添加定制样式
    workbook = writer.book
    worksheet_users = writer.sheets['用户主档']
    worksheet_activity = writer.sheets['访问明细']

    # 针对特定 Sheet 设置列宽
    worksheet_users.set_column('A:C', 18)
    worksheet_activity.set_column('A:D', 16)

在此模式下，writer.sheets 字典中存放的正是 xlsxwriter.worksheet.Worksheet 对象，能够随时调用所有原生排版 API。

六、关键规则与避坑指南

在多 Sheet 场景下，由于涉及多个工作表命名、样式分发与流式输出，开发者常会遇到以下几类典型问题：

检查维度	规则说明	违背后果	正确防御实践
工作表命名长度	单个 Sheet 名称不得超过 31 个字符（包含英文字母、中文及符号）。	抛出异常，脚本中断中断执行。	写入前使用 `name[:31]` 做硬性截断。
特殊字符限制	禁止包含保留符号：`:`、`\`、`/`、`?`、`*`、`[`、`]`。	生成非法 XML 标签或抛出异常。	使用正则表达式 `re.sub` 将特殊字符统一替换为下划线。
命名唯一性	同一个工作簿内不允许存在同名工作表（且 Excel 不区分大小写）。	`DuplicateWorksheetName` 异常。	维护一个集合跟踪已使用名称，遇重名动态拼接数字后缀。
文件持久化	必须执行 `workbook.close()` 触发流打包与落盘。	文件损坏（0 字节）或无法打开。	优先使用 `with xlsxwriter.Workbook(...) as wb:` 结构。
文件修改属性	`xlsxwriter` 是纯写库，不支持“打开现有文件并追加一个新 Sheet”。	原文件内容全部被完全覆盖丢失。	如需在已有 Excel 文件后追加 Sheet，需选用 `openpyxl`。

七、总结与最佳实践建议

在 Python 中使用 xlsxwriter 写入多个 Sheet 是非常高效且模式固定的操作。归纳其关键要点如下：

单实例、多句柄：始终保持单个 Workbook 实例贯穿全局，仅通过多次调用 add_worksheet() 获取各自的 Worksheet 句柄。
格式与内容解耦：所有 add_format() 声明应在循环外集中定义，多个 Sheet 共享复用同一个格式对象，减少内存开销。
防御性命名校验：在动态数据源场景下，对 Sheet 字符串做非空、去保留字符、31字符截断与防重复校验，保障批量输出的健壮性。
严格确保落盘：始终通过 with 语句管理工作簿生命周期，确保即便写入过程遇到中间错误，文件句柄也能被安全关闭。