跳到主要内容

Excel 自动化

问题​

如何实现 Excel 操作的自动化？VBA 和 Python 各有什么优劣？

答案​

自动化方案对比​

方案适用场景优势劣势
VBAExcel 内部自动化原生支持、录制宏语法老旧、难调试
Python openpyxl读写 xlsx 文件生态丰富、易学不支持 xlsx 格式
Python xlsxwriter生成报表格式控制强只写不读
Python pandas数据处理 + 导出数据处理最强格式控制弱
Office ScriptsExcel Online云端自动化仅 Web 版

VBA 基础​

录制宏​

开发工具 → 录制宏 → 执行操作 → 停止录制 → 查看VBA代码

常用 VBA 示例​

' 遍历工作表，汇总数据
Sub SummarizeSheets()
    Dim ws As Worksheet
    Dim summary As Worksheet
    Set summary = Sheets("汇总")
    Dim row As Long: row = 2
    
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "汇总" Then
            summary.Cells(row, 1).Value = ws.Name
            ' 汇总每个表的A列求和
            summary.Cells(row, 2).Value = _
                Application.Sum(ws.Range("A:A"))
            row = row + 1
        End If
    Next ws
End Sub
' 批量处理文件夹中的 Excel 文件
Sub BatchProcess()
    Dim folder As String
    folder = "C:\Reports\"
    Dim filename As String
    filename = Dir(folder & "*.xlsx")
    
    Do While filename <> ""
        Dim wb As Workbook
        Set wb = Workbooks.Open(folder & filename)
        ' 执行处理逻辑...
        wb.Close SaveChanges:=True
        filename = Dir()
    Loop
End Sub

Python 自动化​

openpyxl（读写 xlsx）​

npm install openpyxl
信息

openpyxl 是 Python 包，使用 pip install openpyxl 安装。

from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.chart import BarChart, Reference

# 读取文件
wb = load_workbook("sales.xlsx")
ws = wb.active

# 遍历数据
for row in ws.iter_rows(min_row=2, values_only=True):
    product, quantity, price = row[0], row[1], row[2]
    print(f"{product}: {quantity} × {price}")

# 写入数据
ws["E1"] = "总计"
for i in range(2, ws.max_row + 1):
    ws[f"E{i}"] = f"=B{i}*C{i}"

# 设置样式
header_font = Font(bold=True, size=12, color="FFFFFF")
header_fill = PatternFill(start_color="4472C4", fill_type="solid")

for cell in ws[1]:
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = Alignment(horizontal="center")

# 添加图表
chart = BarChart()
chart.title = "销量对比"
data = Reference(ws, min_col=2, min_row=1, max_row=ws.max_row)
categories = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
ws.add_chart(chart, "G2")

wb.save("sales_report.xlsx")

xlsxwriter（生成报表）​

import xlsxwriter

wb = xlsxwriter.Workbook("report.xlsx")
ws = wb.add_worksheet("月度报表")

# 定义格式
title_fmt = wb.add_format({
    "bold": True, "font_size": 16,
    "align": "center", "valign": "vcenter"
})
header_fmt = wb.add_format({
    "bold": True, "bg_color": "#4472C4",
    "font_color": "white", "border": 1
})
number_fmt = wb.add_format({"num_format": "#,##0.00", "border": 1})
percent_fmt = wb.add_format({"num_format": "0.0%", "border": 1})

# 写入标题
ws.merge_range("A1:E1", "2024年月度销售报表", title_fmt)

# 写入表头
headers = ["月份", "销售额", "目标", "完成率", "环比"]
for col, header in enumerate(headers):
    ws.write(2, col, header, header_fmt)

# 写入数据
data = [
    ["1月", 150000, 120000, 1.25, None],
    ["2月", 180000, 150000, 1.20, 0.20],
    ["3月", 165000, 160000, 1.03, -0.083],
]
for row_idx, row_data in enumerate(data, start=3):
    ws.write(row_idx, 0, row_data[0])
    ws.write(row_idx, 1, row_data[1], number_fmt)
    ws.write(row_idx, 2, row_data[2], number_fmt)
    ws.write(row_idx, 3, row_data[3], percent_fmt)
    if row_data[4] is not None:
        ws.write(row_idx, 4, row_data[4], percent_fmt)

# 设置列宽
ws.set_column("A:A", 10)
ws.set_column("B:E", 15)

# 条件格式 - 完成率 < 100% 标红
ws.conditional_format("D4:D100", {
    "type": "cell", "criteria": "<", "value": 1,
    "format": wb.add_format({"bg_color": "#FFC7CE", "font_color": "#9C0006"})
})

wb.close()

pandas 导出​

import pandas as pd

df = pd.read_excel("raw_data.xlsx")

# 数据处理
summary = df.groupby("地区").agg(
    总销售额=("金额", "sum"),
    订单数=("订单ID", "count"),
    平均客单价=("金额", "mean")
).round(2)

# 导出到 Excel（多 Sheet）
with pd.ExcelWriter("output.xlsx", engine="xlsxwriter") as writer:
    summary.to_excel(writer, sheet_name="汇总")
    df.to_excel(writer, sheet_name="明细", index=False)
    
    # 获取 xlsxwriter 对象来设置格式
    workbook = writer.book
    worksheet = writer.sheets["汇总"]
    
    # 添加图表
    chart = workbook.add_chart({"type": "column"})
    chart.add_series({
        "categories": ["汇总", 1, 0, summary.shape[0], 0],
        "values": ["汇总", 1, 1, summary.shape[0], 1],
        "name": "总销售额"
    })
    worksheet.insert_chart("F2", chart)

选型建议​

常见面试问题​

Q1: Python 替代 VBA 有什么好处？​

答案：

维度VBAPython
生态仅 Office 环境数据分析全栈
调试简陋IDE 断点、日志
版本控制难以 Git 管理正常代码文件
数据处理逐行循环，慢pandas 向量化，快
学习成本低（录制宏起步）中等
无需 Excel不行可以（服务器端运行）

Q2: 如何用 Python 每天自动生成日报？​

答案：

# 1. 从数据库/API获取数据
# 2. pandas 处理
# 3. xlsxwriter 生成格式化报表
# 4. 使用 schedule 或 cron 定时执行
# 5. 用 smtplib 发送邮件附件

import schedule
import time

def generate_daily_report():
    # ... 生成报表逻辑
    # ... 发送邮件
    pass

schedule.every().day.at("09:00").do(generate_daily_report)

while True:
    schedule.run_pending()
    time.sleep(60)

Q3: 处理超大 Excel 文件（几百 MB）怎么办？​

答案：

  1. openpyxl read_only 模式：流式读取，不加载整个文件到内存
  2. pandas chunksize：分块读取处理
  3. 转换格式：先转 CSV 再用 pandas 读取，或导入数据库
  4. xlsxwriter constant_memory 模式：写入时逐行写入，不缓存
# 流式读取大文件
from openpyxl import load_workbook
wb = load_workbook("huge_file.xlsx", read_only=True)
ws = wb.active

for row in ws.iter_rows(values_only=True):
    # 逐行处理，内存占用极低
    process(row)

wb.close()

相关链接​