浏览器与自动化
excel-automation
>
claude-office-skills · 已有静态检查记录
安装与使用
运行命令前先阅读来源与依赖说明。复制命令不会自动安装或执行。
npx skills add claude-office-skills/skills --skill excel-automation技能说明
═══════════════════════════════════════════════════════════════════════════════
CLAUDE OFFICE SKILL - Enhanced Metadata v2.0
═══════════════════════════════════════════════════════════════════════════════
Basic Information
name: excel-automation description: ">" version: "1.0" author: claude-office-skills license: MIT
Categorization
category: spreadsheet tags:
- excel
- automation
- macro
- workflow department: All
AI Model Compatibility
models: recommended: - claude-sonnet-4 - claude-opus-4 compatible: - claude-3-5-sonnet - gpt-4 - gpt-4o
MCP Tools Integration
mcp: server: office-mcp tools: - read_xlsx - create_xlsx - apply_formula - pivot_table
Skill Capabilities
capabilities:
- automation
- data_processing
- reporting
Language Support
languages:
- en
- zh
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
| 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 Fundamentals
import xlwings as xw
# Connect to active Excel workbook
wb = xw.Book.caller() # From Excel add-in
wb = xw.books.active # Active workbook
# 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]
Working with Ranges
Reading and Writing
# 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
# 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
Dynamic Ranges
# Current region (like 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
Formatting
# Font
sheet['A1'].font.bold = True
sheet['A1'].font.size = 14
sheet['A1'].font.color = (255, 0, 0) # RGB red
# Fill
sheet['A1'].color = (255, 255, 0) # Yellow background
# 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()
Excel Features
Charts
# 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'
Tables
# Create Excel Table
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
Pictures
# Add picture
sheet.pictures.add('logo.png', left=10, top=10, width=100, height=50)
# Update picture from 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 Integration
# Run VBA macro
wb.macro('MacroName')()
# With arguments
wb.macro('MyMacro')('arg1', 'arg2')
# Get return value
result = wb.macro('CalculateTotal')(100, 200)
# Access VBA module
vb_code = wb.api.VBProject.VBComponents('Module1').CodeModule.Lines(1, 10)
User Defined Functions (UDFs)
# Define a UDF (in Python file)
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)
# These become Excel functions: =my_sum(A1, B1)
Application Control
# Excel application settings
app = xw.apps.active
app.screen_updating = False # Speed up
app.calculation = 'manual' # Manual calc
app.display_alerts = False # Suppress dialogs
# Perform operations...
# Restore
app.screen_updating = True
app.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
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
def update_dashboard(data_dict):
阅读完整来源 ↗