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