excel-automation

Excel 自动化技能

概述

本技能利用 xlwings 实现高级 Excel 自动化——这是一个能与实时 Excel 实例交互的库。与 openpyxl（仅文件操作）不同，xlwings 可以实时控制 Excel、执行 VBA、更新仪表盘以及自动化复杂工作流。

使用方法

1. 描述您需要的 Excel 自动化任务
2. 指定是需要实时 Excel 交互还是文件处理
3. 我将生成 xlwings 代码并执行

示例提示：

• "用新数据更新这个实时 Excel 仪表盘"
• "运行这个 VBA 宏并获取结果"
• "创建一个用于数据验证的 Excel 插件"
• "自动化生成带实时图表的月度报告"

领域知识

xlwings vs openpyxl

特性	xlwings	openpyxl
需要 Excel	是	否
实时交互	是	否
执行 VBA	是	否
速度（大文件）	快	慢
服务器部署	有限	容易

xlwings 基础

import xlwings as xw

# 连接到活动 Excel 工作簿
wb = xw.Book.caller()  # 从 Excel 插件调用
wb = xw.books.active   # 活动工作簿

# 打开特定文件
wb = xw.Book('path/to/file.xlsx')

# 创建新工作簿
wb = xw.Book()

# 获取工作表
sheet = wb.sheets['Sheet1']
sheet = wb.sheets[0]

操作区域

读取与写入

# 单个单元格
sheet['A1'].value = 'Hello'
value = sheet['A1'].value

# 区域
sheet['A1:C3'].value = [[1, 2, 3], [4, 5, 6], [7, 8, 9]]
data = sheet['A1:C3'].value  # 返回列表的列表

# 命名区域
sheet['MyRange'].value = 'Named data'

# 扩展区域（检测数据边界）
sheet['A1'].expand().value  # 所有连续数据
sheet['A1'].expand('table').value  # 表格格式

动态区域

# 当前区域（类似 Ctrl+Shift+End）
data = sheet['A1'].current_region.value

# 已用区域
used = sheet.used_range.value

# 最后有数据的行
last_row = sheet['A1'].end('down').row

# 调整区域大小
rng = sheet['A1'].resize(10, 5)  # 10 行，5 列

格式化

# 字体
sheet['A1'].font.bold = True
sheet['A1'].font.size = 14
sheet['A1'].font.color = (255, 0, 0)  # RGB 红色

# 填充
sheet['A1'].color = (255, 255, 0)  # 黄色背景

# 数字格式
sheet['B1'].number_format = '$#,##0.00'

# 列宽
sheet['A:A'].column_width = 20

# 行高
sheet['1:1'].row_height = 30

# 自动调整
sheet['A:D'].autofit()

Excel 功能

图表

# 添加图表
chart = sheet.charts.add(left=100, top=100, width=400, height=250)
chart.set_source_data(sheet['A1:B10'])
chart.chart_type = 'column_clustered'
chart.name = 'Sales Chart'

# 修改现有图表
chart = sheet.charts['Sales Chart']
chart.chart_type = 'line'

表格

# 创建 Excel 表格
rng = sheet['A1'].expand()
table = sheet.tables.add(source=rng, name='SalesTable')

# 刷新表格
table.refresh()

# 访问表格数据
table_data = table.data_body_range.value

图片

# 添加图片
sheet.pictures.add('logo.png', left=10, top=10, width=100, height=50)

# 从 matplotlib 更新图片
import matplotlib.pyplot as plt
fig, ax = plt.subplots()
ax.plot([1, 2, 3], [1, 4, 9])
sheet.pictures.add(fig, name='MyPlot', update=True)

VBA 集成

# 运行 VBA 宏
wb.macro('MacroName')()

# 带参数
wb.macro('MyMacro')('arg1', 'arg2')

# 获取返回值
result = wb.macro('CalculateTotal')(100, 200)

# 访问 VBA 模块
vb_code = wb.api.VBProject.VBComponents('Module1').CodeModule.Lines(1, 10)

用户自定义函数 (UDF)

# 定义 UDF（在 Python 文件中）
import xlwings as xw

@xw.func
def my_sum(x, y):
    """两个数相加"""
    return x + y

@xw.func
@xw.arg('data', ndim=2)
def my_array_func(data):
    """处理数组数据"""
    import numpy as np
    return np.sum(data)

# 这些将成为 Excel 函数：=my_sum(A1, B1)

应用程序控制

# Excel 应用程序设置
app = xw.apps.active
app.screen_updating = False  # 加速
app.calculation = 'manual'   # 手动计算
app.display_alerts = False   # 抑制对话框

# 执行操作...

# 恢复
app.screen_updating = True
app.calculation = 'automatic'
app.display_alerts = True

最佳实践

1. 禁用屏幕更新：用于批量操作
2. 使用数组：一次性读写整个区域，而非逐个单元格
3. 手动计算：数据加载时关闭自动计算
4. 关闭连接：完成后正确关闭工作簿
5. 错误处理：处理 Excel 未安装的情况

常见模式

性能优化

import xlwings as xw

def batch_update(data, workbook_path):
    app = xw.App(visible=False)
    try:
        app.screen_updating = False
        app.calculation = 'manual'
        
        wb = app.books.open(workbook_path)
        sheet = wb.sheets['Data']
        
        # 一次性写入所有数据
        sheet['A1'].value = data
        
        app.calculation = 'automatic'
        wb.save()
    finally:
        wb.close()
        app.quit()

仪表盘更新

def update_dashboard(data_dict):
    wb = xw.books.active
    
    # 更新数据表
    data_sheet = wb.sheets['Data']
    for name, values in data_dict.items():
        data_sheet[name].value = values
    
    # 刷新所有图表
    dashboard = wb.sheets['Dashboard']
    for chart in dashboard.charts:
        chart.refresh()
    
    # 更新时间戳
    from datetime import datetime
    dashboard['A1'].value = f'上次更新：{datetime.now()}'

报告生成器

def generate_monthly_report(month, data):
    template = xw.Book('template.xlsx')
    
    # 填充数据
    sheet = template.sheets['Report']
    sheet['B2'].value = month
    sheet['A5'].value = data
    
    # 运行计算
    template.app.calculate()
    
    # 导出为 PDF
    sheet.api.ExportAsFixedFormat(0, f'report_{month}.pdf')
    
    template.save(f'report_{month}.xlsx')

示例

示例 1：实时仪表盘更新

import xlwings as xw
import pandas as pd
from datetime import datetime

# 连接到正在运行的 Excel
wb = xw.books.active
dashboard = wb.sheets['Dashboard']
data_sheet = wb.sheets['Data']

# 获取新数据（模拟）
new_data = pd.DataFrame({
    'Date': pd.date_range('2024-01-01', periods=30),
    'Sales': [1000 + i*50 for i in range(30)],
    'Costs': [600 + i*30 for i in range(30)]
})

# 更新数据表
data_sheet['A1'].value = new_data

# 计算利润
data_sheet['D1'].value = 'Profit'
data_sheet['D2'].value = '=B2-C2'
data_sheet['D2'].expand('down').value = data_sheet['D2'].formula

# 更新仪表盘上的 KPI
dashboard['B2'].value = new_data['Sales'].sum()
dashboard['B3'].value = new_data['Costs'].sum()
dashboard['B4'].value = new_data['Sales'].sum() - new_data['Costs'].sum()
dashboard['A1'].value = f'更新于：{datetime.now().strftime("%Y-%m-%d %H:%M")}'

# 刷新图表
for chart in dashboard.charts:
    chart.api.Refresh()

print("仪表盘已更新！")

示例 2：批量处理多个文件

import xlwings as xw
from pathlib import Path

def process_sales_files(folder_path, output_path):
    """将多个 Excel 文件合并为一个汇总表。"""
    
    app = xw.App(visible=False)
    app.screen_updating = False
    
    try:
        # 创建汇总工作簿
        summary_wb = xw.Book()
        summary_sheet = summary_wb.sheets[0]
        summary_sheet.name = 'Consolidated'
        
        headers = ['文件', '总销售额', '总数量', '平均价格']
        summary_sheet['A1'].value = headers
        
        row = 2
        for file in Path(folder_path).glob('*.xlsx'):
            wb = app.books.open(str(file))
            data_sheet = wb.sheets['Sales']
            
            # 提取汇总
            total_sales = data_sheet['B:B'].api.SpecialCells(11).Value  # xlCellTypeConstants
            total_units = data_sheet['C:C'].api.SpecialCells(11).Value
            
            # 计算并写入
            summary_sheet[f'A{row}'].value = file.name
            summary_sheet[f'B{row}'].value = sum(total_sales) if isinstance(total_sales, (list, tuple)) else total_sales
            summary_sheet[f'C{row}'].value = sum(total_units) if isinstance(total_units, (list, tuple)) else total_units
            summary_sheet[f'D{row}'].value = f'=B{row}/C{row}'
            
            wb.close()
            row += 1
        
        # 格式化汇总表
        summary_sheet['A1:D1'].font.bold = True
        summary_sheet['B:D'].number_format = '$#,##0.00'
        summary_sheet['A:D'].autofit()
        
        summary_wb.save(output_path)
        
    finally:
        app.quit()
    
    print(f"已合并 {row-2} 个文件到 {output_path}")

# 使用
process_sales_files('/path/to/sales/', 'consolidated_sales.xlsx')

示例 3：带 UDF 的 Excel 插件

# myudfs.py - 放置在 xlwings 项目中

import xlwings as xw
import numpy as np

@xw.func
@xw.arg('data', pd.DataFrame, index=False, header=False)
@xw.ret(expand='table')
def GROWTH_RATE(data):
    """计算环比增长率"""
    values = data.iloc[:, 0].values
    growth = np.diff(values) / values[:-1] * 100
    return [['增长率 %']] + [[g] for g in growth]

@xw.func
@xw.arg('range1', np.array, ndim=2)
@xw.arg('range2', np.array, ndim=2)
def CORRELATION(range1, range2):
    """计算两个区域之间的相关系数"""
    return np.corrcoef(range1.flatten(), range2.flatten())[0, 1]

@xw.func
def SENTIMENT(text):
    """基础情感分析（占位）"""
    positive = ['good', 'great', 'excellent', 'amazing']
    negative = ['bad', 'poor', 'terrible', 'awful']
    
    text_lower = text.lower()
    pos_count = sum(word in text_lower for word in positive)
    neg_count = sum(word in text_lower for word in negative)
    
    if pos_count > neg_count:
        return 'Positive'
    elif neg_count > pos_count:
        return 'Negative'
    return 'Neutral'

局限性

• 需要安装 Excel
• macOS 上部分功能支持有限
• 不适合服务器端处理
• VBA 功能需要信任设置
• 性能因 Excel 版本而异

安装

pip install xlwings

# 安装插件功能
xlwings addin install

资源

• xlwings 文档
• GitHub 仓库
• UDF 教程
• Excel VBA 参考