Excel Automation

作者 claude-office-skills9c4c7d5cd281MIT499 个星标收录于 2026年10月8日更新于 2026年10月8日仓库8个月前更新

仅含说明Documents & Office

AI 生成的概览

讲解如何使用 xlwings 实现 Excel 自动化，涵盖实时工作簿控制、VBA 宏、自定义函数、图表与报表生成。

功能: 该技能提供通过 xlwings 库自动化 Excel 的说明与代码示例，包括连接正在运行的 Excel 工作簿、读写单元格区域、设置格式、创建图表和数据表、运行 VBA 宏以及定义用户自定义函数。它还介绍关闭屏幕更新、手动计算等性能优化做法，并给出仪表板更新、批量文件合并和月度报表导出 PDF 的示例流程。它产出的是生成的 Python 代码与指导，本身不执行任何操作。
适用场景: 适用于需要操作实时 Excel 实例、执行 VBA、刷新仪表板或生成 Excel 报表与加载项的任务。当仅处理文件的库无法满足、需要与 Excel 实时交互时使用。
运行要求: 需要 xlwings Python 包以及本地安装的 Microsoft Excel；VBA 功能需要相应的信任设置。该技能不附带脚本，仅为说明文档，但其中提到 office-mcp 服务器及 read_xlsx、create_xlsx 等工具。

Excel Automation Skill

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.

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"

Domain Knowledge

xlwings vs openpyxl

xlwings Fundamentals

python

import xlwings as xw
# Connect to active Excel workbookwb = xw.Book.caller()  # From Excel add-inwb = xw.books.active   # Active workbook
# Open specific filewb = xw.Book('path/to/file.xlsx')
# Create new workbookwb = xw.Book()
# Get sheetsheet = wb.sheets['Sheet1']sheet = wb.sheets[0]

import xlwings as xw
# Connect to active Excel workbookwb = xw.Book.caller()  # From Excel add-inwb = xw.books.active   # Active workbook
# Open specific filewb = xw.Book('path/to/file.xlsx')
# Create new workbookwb = xw.Book()
# Get sheetsheet = wb.sheets['Sheet1']sheet = wb.sheets[0]

Working with Ranges

Reading and Writing

python

# Single cellsheet['A1'].value = 'Hello'value = sheet['A1'].value
# Rangesheet['A1:C3'].value = [[1, 2, 3], [4, 5, 6], [7, 8, 9]]data = sheet['A1:C3'].value  # Returns list of lists
# Named rangesheet['MyRange'].value = 'Named data'
# Expand range (detect data boundaries)sheet['A1'].expand().value  # All connected datasheet['A1'].expand('table').value  # Table format

# Single cellsheet['A1'].value = 'Hello'value = sheet['A1'].value
# Rangesheet['A1:C3'].value = [[1, 2, 3], [4, 5, 6], [7, 8, 9]]data = sheet['A1:C3'].value  # Returns list of lists
# Named rangesheet['MyRange'].value = 'Named data'
# Expand range (detect data boundaries)sheet['A1'].expand().value  # All connected datasheet['A1'].expand('table').value  # Table format

Dynamic Ranges

python

# Current region (like Ctrl+Shift+End)data = sheet['A1'].current_region.value
# Used rangeused = sheet.used_range.value
# Last row with datalast_row = sheet['A1'].end('down').row
# Resize rangerng = sheet['A1'].resize(10, 5)  # 10 rows, 5 columns

# Current region (like Ctrl+Shift+End)data = sheet['A1'].current_region.value
# Used rangeused = sheet.used_range.value
# Last row with datalast_row = sheet['A1'].end('down').row
# Resize rangerng = sheet['A1'].resize(10, 5)  # 10 rows, 5 columns

Formatting

python

# Fontsheet['A1'].font.bold = Truesheet['A1'].font.size = 14sheet['A1'].font.color = (255, 0, 0)  # RGB red
# Fillsheet['A1'].color = (255, 255, 0)  # Yellow background
# Number formatsheet['B1'].number_format = '$#,##0.00'
# Column widthsheet['A:A'].column_width = 20
# Row heightsheet['1:1'].row_height = 30
# Autofitsheet['A:D'].autofit()

# Fontsheet['A1'].font.bold = Truesheet['A1'].font.size = 14sheet['A1'].font.color = (255, 0, 0)  # RGB red
# Fillsheet['A1'].color = (255, 255, 0)  # Yellow background
# Number formatsheet['B1'].number_format = '$#,##0.00'
# Column widthsheet['A:A'].column_width = 20
# Row heightsheet['1:1'].row_height = 30
# Autofitsheet['A:D'].autofit()

Excel Features

Charts

python

# Add chartchart = 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 chartchart = sheet.charts['Sales Chart']chart.chart_type = 'line'

# Add chartchart = 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 chartchart = sheet.charts['Sales Chart']chart.chart_type = 'line'

Tables

python

# Create Excel Tablerng = sheet['A1'].expand()table = sheet.tables.add(source=rng, name='SalesTable')
# Refresh tabletable.refresh()
# Access table datatable_data = table.data_body_range.value

# Create Excel Tablerng = sheet['A1'].expand()table = sheet.tables.add(source=rng, name='SalesTable')
# Refresh tabletable.refresh()
# Access table datatable_data = table.data_body_range.value

Pictures

python

# Add picturesheet.pictures.add('logo.png', left=10, top=10, width=100, height=50)
# Update picture from matplotlibimport matplotlib.pyplot as pltfig, ax = plt.subplots()ax.plot([1, 2, 3], [1, 4, 9])sheet.pictures.add(fig, name='MyPlot', update=True)

# Add picturesheet.pictures.add('logo.png', left=10, top=10, width=100, height=50)
# Update picture from matplotlibimport matplotlib.pyplot as pltfig, ax = plt.subplots()ax.plot([1, 2, 3], [1, 4, 9])sheet.pictures.add(fig, name='MyPlot', update=True)

VBA Integration

python

# Run VBA macrowb.macro('MacroName')()
# With argumentswb.macro('MyMacro')('arg1', 'arg2')
# Get return valueresult = wb.macro('CalculateTotal')(100, 200)
# Access VBA modulevb_code = wb.api.VBProject.VBComponents('Module1').CodeModule.Lines(1, 10)

# Run VBA macrowb.macro('MacroName')()
# With argumentswb.macro('MyMacro')('arg1', 'arg2')
# Get return valueresult = wb.macro('CalculateTotal')(100, 200)
# Access VBA modulevb_code = wb.api.VBProject.VBComponents('Module1').CodeModule.Lines(1, 10)

User Defined Functions (UDFs)

python

# Define a UDF (in Python file)import xlwings as xw
@xw.funcdef 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)
# These become Excel functions: =my_sum(A1, B1)

# Define a UDF (in Python file)import xlwings as xw
@xw.funcdef 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)
# These become Excel functions: =my_sum(A1, B1)

Application Control

python

# Excel application settingsapp = xw.apps.activeapp.screen_updating = False  # Speed upapp.calculation = 'manual'   # Manual calcapp.display_alerts = False   # Suppress dialogs
# Perform operations...
# Restoreapp.screen_updating = Trueapp.calculation = 'automatic'app.display_alerts = True

# Excel application settingsapp = xw.apps.activeapp.screen_updating = False  # Speed upapp.calculation = 'manual'   # Manual calcapp.display_alerts = False   # Suppress dialogs
# Perform operations...
# Restoreapp.screen_updating = Trueapp.calculation = 'automatic'app.display_alerts = True

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

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

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

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

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

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

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

Examples

Example 1: Live Dashboard Update

python

import xlwings as xwimport pandas as pdfrom datetime import datetime
# Connect to running Excelwb = xw.books.activedashboard = 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 + i*50 for i in range(30)],    'Costs': [600 + i*30 for i in range(30)]})
# Update data sheetdata_sheet['A1'].value = new_data
# Calculate profitdata_sheet['D1'].value = 'Profit'data_sheet['D2'].value = '=B2-C2'data_sheet['D2'].expand('down').value = data_sheet['D2'].formula
# Update KPIs on dashboarddashboard['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")}'
# Refresh chartsfor chart in dashboard.charts:    chart.api.Refresh()
print("Dashboard updated!")

import xlwings as xwimport pandas as pdfrom datetime import datetime
# Connect to running Excelwb = xw.books.activedashboard = 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 + i*50 for i in range(30)],    'Costs': [600 + i*30 for i in range(30)]})
# Update data sheetdata_sheet['A1'].value = new_data
# Calculate profitdata_sheet['D1'].value = 'Profit'data_sheet['D2'].value = '=B2-C2'data_sheet['D2'].expand('down').value = data_sheet['D2'].formula
# Update KPIs on dashboarddashboard['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")}'
# Refresh chartsfor chart in dashboard.charts:    chart.api.Refresh()
print("Dashboard updated!")

Example 2: Batch Processing Multiple Files

python

import xlwings as xwfrom 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}")
# Usageprocess_sales_files('/path/to/sales/', 'consolidated_sales.xlsx')

import xlwings as xwfrom 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}")
# Usageprocess_sales_files('/path/to/sales/', 'consolidated_sales.xlsx')

Example 3: Excel Add-in with UDFs

python

# myudfs.py - Place in xlwings project
import xlwings as xwimport 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.funcdef 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'

# myudfs.py - Place in xlwings project
import xlwings as xwimport 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.funcdef 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'

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

Installation

Resources

来源与署名

来源：claude-office-skills/skills位于excel-automation提交9c4c7d5

许可证： MIT

内容归原作者所有。SourceWeft 从公开仓库中收录这些内容。

举报或申请下架

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

Excel Automation

Excel Automation Skill

Overview

How to Use

Domain Knowledge

xlwings vs openpyxl

xlwings Fundamentals

Working with Ranges

Reading and Writing

Dynamic Ranges

Formatting

Excel Features

Charts

Tables

Pictures

VBA Integration

User Defined Functions (UDFs)

Application Control

Best Practices

Common Patterns

Performance Optimization

Dashboard Update

Report Generator

Examples

Example 1: Live Dashboard Update

Example 2: Batch Processing Multiple Files

Example 3: Excel Add-in with UDFs

Limitations

Installation

Resources

来源与署名

更多来自 claude-office-skills/skills 的技能

Whatsapp Automation

Xlsx Manipulation

Web Search

Weekly Report

Tiktok Marketing

Template Engine

更多Documents & Office技能

Pdf

View Pdf

Wiki Ado Convert

Esign Field Placement

Visa Doc Translate

Google Workspace Ops