excel-automation

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Excel Automation Skill

Excel自动化技能

Overview

概述

This skill enables advanced Excel automation using xlwings - a library that can interact with live Excel instances. Unlike openpyxl (file-only), xlwings can control Excel in real-time, execute VBA, update dashboards, and automate complex workflows.

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

How to Use

使用方法

Describe the Excel automation task you need
Specify if you need live Excel interaction or file processing
I'll generate xlwings code and execute it

Example prompts:

"Update this live Excel dashboard with new data"
"Run this VBA macro and get the results"
"Create an Excel add-in for data validation"
"Automate monthly report generation with live charts"

描述你需要的Excel自动化任务
指定是否需要实时Excel交互或仅文件处理
我会生成xlwings代码并执行

示例提示：

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

Domain Knowledge

领域知识

xlwings vs openpyxl

xlwings 与 openpyxl 对比

Feature	xlwings	openpyxl
Requires Excel	Yes	No
Live interaction	Yes	No
VBA execution	Yes	No
Speed (large files)	Fast	Slow
Server deployment	Limited	Easy

特性	xlwings	openpyxl
需要安装Excel	是	否
实时交互	是	否
VBA执行	是	否
大文件处理速度	快	慢
服务器部署	受限	简单

xlwings Fundamentals

xlwings 基础

python

import xlwings as xw

python

import xlwings as xw

Connect to active Excel workbook

连接到活动Excel工作簿

wb = xw.Book.caller() # From Excel add-in wb = xw.books.active # Active workbook

wb = xw.Book.caller() # 从Excel加载项中连接 wb = xw.books.active # 活动工作簿

Open specific file

打开指定文件

wb = xw.Book('path/to/file.xlsx')

Create new workbook

创建新工作簿

wb = xw.Book()

Get sheet

获取工作表

sheet = wb.sheets['Sheet1'] sheet = wb.sheets[0]

undefined

sheet = wb.sheets['Sheet1'] sheet = wb.sheets[0]

undefined

Working with Ranges

单元格区域操作

Reading and Writing

读写操作

python

undefined

python

undefined

Single cell

单个单元格

sheet['A1'].value = 'Hello' value = sheet['A1'].value

Range

单元格区域

sheet['A1:C3'].value = [[1, 2, 3], [4, 5, 6], [7, 8, 9]] data = sheet['A1:C3'].value # Returns list of lists

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

Named range

命名区域

sheet['MyRange'].value = 'Named data'

Expand range (detect data boundaries)

扩展区域（检测数据边界）

sheet['A1'].expand().value # All connected data sheet['A1'].expand('table').value # Table format

undefined

sheet['A1'].expand().value # 所有连续数据 sheet['A1'].expand('table').value # 表格格式

undefined

Dynamic Ranges

动态区域

python

undefined

python

undefined

Current region (like Ctrl+Shift+End)

当前区域（类似Ctrl+Shift+End）

data = sheet['A1'].current_region.value

Used range

已使用区域

used = sheet.used_range.value

Last row with data

最后一行有数据的行

last_row = sheet['A1'].end('down').row

Resize range

调整区域大小

rng = sheet['A1'].resize(10, 5) # 10 rows, 5 columns

undefined

rng = sheet['A1'].resize(10, 5) # 10行，5列

undefined

Formatting

格式设置

python

undefined

python

undefined

Font

字体

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

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

Fill

填充

sheet['A1'].color = (255, 255, 0) # Yellow background

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

Number format

数字格式

sheet['B1'].number_format = '$#,##0.00'

Column width

列宽

sheet['A:A'].column_width = 20

Row height

行高

sheet['1:1'].row_height = 30

Autofit

自动适配

sheet['A:D'].autofit()

undefined

sheet['A:D'].autofit()

undefined

Excel Features

Excel功能

Charts

图表

python

undefined

python

undefined

Add chart

添加图表

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'

Modify existing chart

修改现有图表

chart = sheet.charts['Sales Chart'] chart.chart_type = 'line'

undefined

chart = sheet.charts['Sales Chart'] chart.chart_type = 'line'

undefined

Tables

表格

python

undefined

python

undefined

Create Excel Table

创建Excel表格

rng = sheet['A1'].expand() table = sheet.tables.add(source=rng, name='SalesTable')

Refresh table

刷新表格

table.refresh()

Access table data

访问表格数据

table_data = table.data_body_range.value

undefined

table_data = table.data_body_range.value

undefined

Pictures

图片

python

undefined

python

undefined

Add picture

添加图片

sheet.pictures.add('logo.png', left=10, top=10, width=100, height=50)

Update picture from matplotlib

从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)

undefined

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)

undefined

VBA Integration

VBA集成

python

undefined

python

undefined

Run VBA macro

运行VBA宏

wb.macro('MacroName')()

With arguments

带参数运行

wb.macro('MyMacro')('arg1', 'arg2')

Get return value

获取返回值

result = wb.macro('CalculateTotal')(100, 200)

Access VBA module

访问VBA模块

vb_code = wb.api.VBProject.VBComponents('Module1').CodeModule.Lines(1, 10)

undefined

vb_code = wb.api.VBProject.VBComponents('Module1').CodeModule.Lines(1, 10)

undefined

User Defined Functions (UDFs)

用户自定义函数（UDFs）

python

undefined

python

undefined

Define a UDF (in Python file)

定义UDF（在Python文件中）

import xlwings as xw

@xw.func def my_sum(x, y): """Add two numbers""" return x + y

@xw.func @xw.arg('data', ndim=2) def my_array_func(data): """Process array data""" import numpy as np return np.sum(data)

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)

These become Excel functions: =my_sum(A1, B1)

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

undefined

undefined

Application Control

应用程序控制

python

undefined

python

undefined

Excel application settings

Excel应用程序设置

app = xw.apps.active app.screen_updating = False # Speed up app.calculation = 'manual' # Manual calc app.display_alerts = False # Suppress dialogs

app = xw.apps.active app.screen_updating = False # 提升速度 app.calculation = 'manual' # 手动计算 app.display_alerts = False # 抑制弹窗

Perform operations...

执行操作...

Restore

恢复设置

app.screen_updating = True app.calculation = 'automatic' app.display_alerts = True

undefined

app.screen_updating = True app.calculation = 'automatic' app.display_alerts = True

undefined

Best Practices

最佳实践

Disable Screen Updating: For batch operations
Use Arrays: Read/write entire ranges, not cell-by-cell
Manual Calculation: Turn off auto-calc during data loading
Close Connections: Properly close workbooks when done
Error Handling: Handle Excel not being installed

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

Common Patterns

常见模式

Performance Optimization

性能优化

python

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']
        
        # Write all data at once
        sheet['A1'].value = data
        
        app.calculation = 'automatic'
        wb.save()
    finally:
        wb.close()
        app.quit()

python

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()

Dashboard Update

仪表盘更新

python

def update_dashboard(data_dict):
    wb = xw.books.active
    
    # Update data sheet
    data_sheet = wb.sheets['Data']
    for name, values in data_dict.items():
        data_sheet[name].value = values
    
    # Refresh all charts
    dashboard = wb.sheets['Dashboard']
    for chart in dashboard.charts:
        chart.refresh()
    
    # Update timestamp
    from datetime import datetime
    dashboard['A1'].value = f'Last Updated: {datetime.now()}'

python

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()}'

Report Generator

报表生成器

python

def generate_monthly_report(month, data):
    template = xw.Book('template.xlsx')
    
    # Fill data
    sheet = template.sheets['Report']
    sheet['B2'].value = month
    sheet['A5'].value = data
    
    # Run calculations
    template.app.calculate()
    
    # Export to PDF
    sheet.api.ExportAsFixedFormat(0, f'report_{month}.pdf')
    
    template.save(f'report_{month}.xlsx')

python

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')

Examples

示例

Example 1: Live Dashboard Update

示例1：实时仪表盘更新

python

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

python

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

Connect to running Excel

连接到运行中的Excel

wb = xw.books.active dashboard = wb.sheets['Dashboard'] data_sheet = wb.sheets['Data']

Fetch new data (simulated)

获取新数据（模拟）

new_data = pd.DataFrame({ 'Date': pd.date_range('2024-01-01', periods=30), 'Sales': [1000 + i50 for i in range(30)], 'Costs': [600 + i30 for i in range(30)] })

Update data sheet

更新数据工作表

data_sheet['A1'].value = new_data

Calculate profit

计算利润

data_sheet['D1'].value = 'Profit' data_sheet['D2'].value = '=B2-C2' data_sheet['D2'].expand('down').value = data_sheet['D2'].formula

Update KPIs on dashboard

更新仪表盘上的关键指标

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'Updated: {datetime.now().strftime("%Y-%m-%d %H:%M")}'

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")}'

Refresh charts

刷新图表

for chart in dashboard.charts: chart.api.Refresh()

print("Dashboard updated!")

undefined

for chart in dashboard.charts: chart.api.Refresh()

print("仪表盘更新完成！")

undefined

Example 2: Batch Processing Multiple Files

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

python

import xlwings as xw
from pathlib import Path

def process_sales_files(folder_path, output_path):
    """Consolidate multiple Excel files into one summary."""
    
    app = xw.App(visible=False)
    app.screen_updating = False
    
    try:
        # Create summary workbook
        summary_wb = xw.Book()
        summary_sheet = summary_wb.sheets[0]
        summary_sheet.name = 'Consolidated'
        
        headers = ['File', 'Total Sales', 'Total Units', 'Avg Price']
        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']
            
            # Extract summary
            total_sales = data_sheet['B:B'].api.SpecialCells(11).Value  # xlCellTypeConstants
            total_units = data_sheet['C:C'].api.SpecialCells(11).Value
            
            # Calculate and write
            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
        
        # Format summary
        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"Consolidated {row-2} files to {output_path}")

python

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}")

Usage

使用示例

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

undefined

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

undefined

Example 3: Excel Add-in with UDFs

示例3：带UDF的Excel加载项

python

undefined

python

undefined

myudfs.py - Place in xlwings project

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): """Calculate period-over-period growth rate""" values = data.iloc[:, 0].values growth = np.diff(values) / values[:-1] * 100 return [['Growth %']] + [[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): """Calculate correlation between two ranges""" return np.corrcoef(range1.flatten(), range2.flatten())[0, 1]

@xw.func def SENTIMENT(text): """Basic sentiment analysis (placeholder)""" 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'

undefined

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'

undefined

Limitations

局限性

Requires Excel to be installed
Limited support on macOS for some features
Not suitable for server-side processing
VBA features require trust settings
Performance varies with Excel version

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

Installation

安装

bash

pip install xlwings

bash

pip install xlwings

For add-in functionality

如需加载项功能

xlwings addin install

undefined

xlwings addin install

undefined