利怡悦 发表于 2025-9-24 13:43:56

Python 操作 Excel 文件：扩展库指南

在当今数据驱动的工作环境中，Excel 文件操作已成为 Python 开发者必备的核心技能之一。本文将深入解析三个优秀的 Python Excel 库：openpyxl、pandas 和 Free Spire.XLS for Python，帮助您根据具体需求选择最佳工具，实现高效 Excel 自动化处理。
一、核心工具库对比与选择指南

需求场景
推荐库（&补充库）
核心优势
最佳适用场景
精细单元格操作
openpyxl
单元格级控制、公式图表支持
报表模板生成、格式定制
批量数据处理
pandas
简洁API、高效数据清洗分析
大数据分析、数据清洗转换
企业级解决方案
Free Spire.XLS for Python
全格式支持、 PDF 导出、跨平台
企业系统集成、格式转换需求
Excel交互
xlwings
支持VBA交互
自动化操作、数据分析和报表生成
高效生成xlsx
xlsxwriter
纯写入、极致格式化、轻量级
生成复杂的报表和图表
注：鉴于篇幅有限，本文重点介绍前三个库
二、环境安装与配置

安装三大核心库

pip install openpyxl pandaspip install free spire.xls注意：Free Spire.XLS for Python 仅用于学习和测试，企业应用需购买授权
三、openpyxl：专业 Excel 精细控制

核心优势与应用场景


[*]专业 .xlsx操作：原生支持最新 Excel 格式
[*]高级功能支持：完整控制公式、图表、条件格式
[*]最佳场景：财务报告、格式化模板、带公式的工作表
创建专业 Excel 报表

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill
from openpyxl.formatting.rule import ColorScaleRule

# 创建带格式的工作簿
wb = Workbook()
ws = wb.active
ws.title = "销售分析"

# 添加标题行（带样式）
header_font = Font(bold=True, color="FFFFFF")
header_fill = PatternFill(start_color="4F81BD", end_color="4F81BD", fill_type="solid")
ws.append(["产品", "季度", "销售额", "增长率"])
for cell in ws:
    cell.font = header_font
    cell.fill = header_fill

# 添加示例数据
sales_data = [
    ["手机", "Q1", 1500, 0.15],
    ["手机", "Q2", 2100, 0.40],
    ["笔记本", "Q1", 800, -0.05],
    ["笔记本", "Q2", 1200, 0.50]
]
for row in sales_data:
    ws.append(row)

# 添加条件格式
color_scale = ColorScaleRule(start_type='min', start_color='FF0000',
                            end_type='max', end_color='00FF00')
ws.conditional_formatting.add("D2:D5", color_scale)

# 保存专业报表
wb.save("sales_analysis.xlsx")图表嵌入技术要点


[*]引擎选择：必须使用 xlsxwriter 引擎（pip install xlsxwriter）
[*]数据引用：使用 Excel 公式语法（如 =数据透视表!$B$2:$F$2）动态引用数据范围
[*]图表类型：支持 25+ 种图表类型（柱状图/折线图/饼图等）
[*]样式定制：可调整颜色/字体/3D效果等 50+ 种样式参数
注：复杂图表建议结合 openpyxl 的 BarChart3D 等高级类实现
四、pandas：Excel 数据批处理专家

核心优势与应用场景


[*]数据处理效率：秒级处理百万行数据
[*]简洁API：read_excel() 和 to_excel()快速接口
[*]最佳场景：大数据清洗、分析、多表合并
多表数据处理与图表嵌入实战

import pandas as pd
import numpy as np

# 创建示例数据集
data = {
    '产品': ['手机', '笔记本', '平板', '耳机'] * 5,
    '月份': np.repeat(['1月', '2月', '3月', '4月', '5月'], 4),
    '销售额': np.random.randint(1000, 5000, 20),
    '成本': np.random.randint(500, 3000, 20)
}
df = pd.DataFrame(data)

# 添加计算列（类似Excel公式）
df['利润率'] = (df['销售额'] - df['成本']) / df['销售额']

# 创建数据透视表
pivot = pd.pivot_table(df,
                     values='销售额',
                     index='产品',
                     columns='月份',
                     aggfunc='sum',
                     margins=True,
                     margins_name='总计')

# 多表输出到Excel
with pd.ExcelWriter('sales_report.xlsx') as writer:
    df.to_excel(writer, sheet_name='原始数据', index=False)
    pivot.to_excel(writer, sheet_name='数据透视表')
   
with pd.ExcelWriter('sales_report.xlsx', engine='xlsxwriter') as writer:
    # 写入数据
    df.to_excel(writer, sheet_name='原始数据', index=False)
    pivot.to_excel(writer, sheet_name='数据透视表')
   
    # 获取工作簿和工作表对象
    workbook = writer.book
    worksheet = writer.sheets['数据透视表']
   
    # 创建柱状图 (需安装 xlsxwriter)
    chart = workbook.add_chart({'type': 'column'})
   
    # 动态获取数据范围 (B2:F6 为示例范围)
    chart.add_series({
      'name': '销售额',
      'categories': '=数据透视表!$B$2:$F$2',# 月份数据
      'values': '=数据透视表!$B$3:$F$6',   # 产品销售额
      'gap': 150# 柱间距
    })
   
    # 设置图表样式
    chart.set_title({'name': '产品月度销售额分布'})
    chart.set_x_axis({'name': '月份'})
    chart.set_y_axis({'name': '销售额 (万元)'})
   
    # 嵌入图表到指定位置
    worksheet.insert_chart('H2', chart)五、Free Spire.XLS for Python：免费的企业级 Excel 解决方案

核心优势与应用场景


[*]全格式支持：完美兼容 .xls、.xlsx、.xlsb 等格式
[*]无依赖运行：无需安装 Microsoft Excel
[*]免费版功能：PDF 导出、邮件合并、批量格式转换（免费版有功能限制）
[*]跨平台：支持 Windows/Linux/macOS/国产系统
企业级应用实战

import os
from spire.xls import Workbook, FileFormat, DateTime


def process_financial_report(template_path: str, output_excel_path: str,
                           generate_pdf: bool) -> None:
    """
    使用 Free Spire.XLS for Python 处理财务报告模板
    填充数据并导出为Excel和PDF格式
   
    参数:
      template_path: 财务报告模板的完整路径
      output_excel_path: 生成的Excel报告的保存路径
      generate_pdf: 是否额外生成PDF报告
      
    流程:
      1. 加载Excel模板
      2. 填充报告标题和时间戳
      3. 填充季度财务数据
      4. 保存Excel报告
      5. 可选生成PDF报告
    """
    workbook = Workbook()
    try:
      # 加载模板
      workbook.LoadFromFile(template_path)
      # 使用第一个工作表
      worksheet = workbook.Worksheets

      # 设置报告标题
      worksheet.Range["B2"].Text = "2023年度财务报告"

      # 设置当前日期
      worksheet.Range["C5"].DateTimeValue = DateTime.get_Now()

      # 准备数据
      quarterly_data = [["Q1", 1500000, 1200000, 300000],
                        ["Q2", 1650000, 1250000, 400000],
                        ["Q3", 1820000, 1350000, 470000],
                        ["Q4", 2100000, 1450000, 650000]]

      # 数据列对应的列标识
      data_columns = ["A", "B", "C", "D"]

      # 数据起始行（第7行开始）
      START_ROW = 7

      for row_index, quarter_row in enumerate(quarterly_data):
            # 计算当前数据行的行号
            excel_row = START_ROW + row_index

            for col_index, cell_value in enumerate(quarter_row):
                # 获取当前单元格地址
                cell_address = f"{data_columns}{excel_row}"

                # 根据数据类型设置单元格值
                if isinstance(cell_value, str):
                  worksheet.Range.Text = cell_value
                else:
                  worksheet.Range.NumberValue = cell_value

      # 保存Excel报告
      workbook.SaveToFile(output_excel_path, FileFormat.Version2016)

      # 可选生成PDF报告
      if generate_pdf:
            # 替换扩展名
            root, _ = os.path.splitext(output_excel_path)
            pdf_output_path = root + ".pdf"

            workbook.SaveToFile(pdf_output_path, FileFormat.PDF)
            print(f"PDF报告已生成: {pdf_output_path}")

    except Exception as e:
      print(f"报告生成失败: {str(e)}")
      # 实际项目中应记录详细错误日志
      # 可考虑重新抛出异常或返回错误状态码

    finally:
      # 确保释放工作簿资源
      workbook.Dispose()
      print("工作簿资源已释放")


# 使用示例
if __name__ == "__main__":
    # 路径配置（实际使用中建议从配置文件读取）
    TEMPLATE_PATH = "财务模版.xlsx"
    OUTPUT_PATH = "2023财务报告.xlsx"

    process_financial_report(template_path=TEMPLATE_PATH,
                           output_excel_path=OUTPUT_PATH,
                           generate_pdf=True)六、性能优化与最佳实践

大数据处理技巧

# CSV中转
pd.read_excel("large_dataset.xlsx").to_csv("temp.csv", index=False)

# 分块处理文件
chunk_size = 10000
csv_chunks = pd.read_csv("temp.csv", chunksize=chunk_size)

with pd.ExcelWriter("processed_data.xlsx", engine='openpyxl') as writer:
    for i, chunk in tqdm(enumerate(csv_chunks), desc="Processing"):
      processed = transform_data(chunk)
      # 安全写入策略
      if i >= 200:# 预留55个sheet给其他数据
            # 合并到主表
            start_row = 0 if i == 0 else writer.sheets['Main'].max_row
            processed.to_excel(writer, sheet_name='Main', startrow=start_row, index=False, header=(i==0))
      else:
            processed.to_excel(writer, sheet_name=f"Part_{i+1}", index=False)跨平台兼容方案


[*]Linux环境：使用 Free Spire.XLS for Python 替代需 Windows 依赖的库
[*]无Office环境：Free Spire.XLS for Python 无需安装 Excel 即可操作
[*]国产系统支持：Free Spire.XLS for Python 兼容中标麒麟、中科方德等系统
注：如需 .xls格式支持或 PDF转换，可考虑 Free Spire.XLS for Python等商业库的免费版，但需注意功能限制。开源方案可尝试 odfpy（ODS格式）或 LibreOffice转换工具。
七、总结：选择适合你的Excel工具

功能维度
openpyxl
pandas
Free Spire.XLS for Python
格式支持
★★★☆
★★★☆
★★★★★
单元格控制
★★★★★
★★☆
★★★★☆
大数据处理
★★☆
★★★★★
★★★★☆
图表/公式
★★★★☆
☆
★★★★★
格式转换
☆
☆
★★★★★
学习曲线
★★★☆
★★★★☆
★★★☆
综合评分：

[*]openpyxl(★★★★☆)
[*]pandas(★★★★☆)
[*]Free Spire.XLS for Python (★★★★☆)


[*]优点：强大的单元格级控制能力，支持高级格式化和图表
[*]缺点：大数据处理能力有限，不支持旧版.xls格式
[*]适合需要精细控制Excel格式的场景


[*]优点：卓越的数据处理性能，简洁的API接口
[*]缺点：对于 Excel 图表和格式的控制能力较弱
[*]适合数据分析和批处理任务


[*]优点：全面的格式支持，强大的转换能力，跨平台兼容性
[*]缺点：免费版功能受限，学习曲线中等
[*]适合企业级应用和格式转换需求
项目选型建议：

[*]数据分析团队 → pandas 快速处理数据
[*]财务部门 → openpyxl 创建精美报表
[*]IT系统集成 → Free Spire.XLS for Python 实现自动化工作流（注意免费版限制）
通过自动化 Excel 处理流程，典型数据清洗任务耗时从小时级降至分钟级。立即行动：选择适合您项目的库，开始自动化 Excel 处理流程。
通过本指南，您已掌握 Python 操作 Excel 的核心技术栈。实践是提升的关键 - 尝试将现有 Excel 工作流用 Python 自动化，体验效率的飞跃！
可参考资料

Openpyxl 教程文档 pandas 官方文档 Spire.XLS for Python中文教程 Python 官方文档
 

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

步雪卉 发表于 昨天 00:40

很好很强大我过来先占个楼 待编辑

釉她 发表于 昨天 00:40

谢谢分享，试用一下
页: [1]
查看完整版本: Python 操作 Excel 文件：扩展库指南