1---
2# ═══════════════════════════════════════════════════════════════════════════════
3# CLAUDE OFFICE SKILL - Enhanced Metadata v2.0
4# ═══════════════════════════════════════════════════════════════════════════════
5 
6# Basic Information
7name: excel-automation
8description: ">"
9version: "1.0"
10author: claude-office-skills
11license: MIT
12 
13# Categorization
14category: spreadsheet
15tags:
16  - excel
17  - automation
18  - macro
19  - workflow
20department: All
21 
22# AI Model Compatibility
23models:
24  recommended:
25    - claude-sonnet-4
26    - claude-opus-4
27  compatible:
28    - claude-3-5-sonnet
29    - gpt-4
30    - gpt-4o
31 
32# MCP Tools Integration
33mcp:
34  server: office-mcp
35  tools:
36    - read_xlsx
37    - create_xlsx
38    - apply_formula
39    - pivot_table
40 
41# Skill Capabilities
42capabilities:
43  - automation
44  - data_processing
45  - reporting
46 
47# Language Support
48languages:
49  - en
50  - zh
51---
52 
53# Excel Automation Skill
54 
55## Overview
56 
57This 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.
58 
59## How to Use
60 
611. Describe the Excel automation task you need
622. Specify if you need live Excel interaction or file processing
633. I'll generate xlwings code and execute it
64 
65**Example prompts:**
66- "Update this live Excel dashboard with new data"
67- "Run this VBA macro and get the results"
68- "Create an Excel add-in for data validation"
69- "Automate monthly report generation with live charts"
70 
71## Domain Knowledge
72 
73### xlwings vs openpyxl
74 
75| Feature | xlwings | openpyxl |
76|---------|---------|----------|
77| Requires Excel | Yes | No |
78| Live interaction | Yes | No |
79| VBA execution | Yes | No |
80| Speed (large files) | Fast | Slow |
81| Server deployment | Limited | Easy |
82 
83### xlwings Fundamentals
84 
85```python
86import xlwings as xw
87 
88# Connect to active Excel workbook
89wb = xw.Book.caller()  # From Excel add-in
90wb = xw.books.active   # Active workbook
91 
92# Open specific file
93wb = xw.Book('path/to/file.xlsx')
94 
95# Create new workbook
96wb = xw.Book()
97 
98# Get sheet
99sheet = wb.sheets['Sheet1']
100sheet = wb.sheets[0]
101```
102 
103### Working with Ranges
104 
105#### Reading and Writing
106```python
107# Single cell
108sheet['A1'].value = 'Hello'
109value = sheet['A1'].value
110 
111# Range
112sheet['A1:C3'].value = [[1, 2, 3], [4, 5, 6], [7, 8, 9]]
113data = sheet['A1:C3'].value  # Returns list of lists
114 
115# Named range
116sheet['MyRange'].value = 'Named data'
117 
118# Expand range (detect data boundaries)
119sheet['A1'].expand().value  # All connected data
120sheet['A1'].expand('table').value  # Table format
121```
122 
123#### Dynamic Ranges
124```python
125# Current region (like Ctrl+Shift+End)
126data = sheet['A1'].current_region.value
127 
128# Used range
129used = sheet.used_range.value
130 
131# Last row with data
132last_row = sheet['A1'].end('down').row
133 
134# Resize range
135rng = sheet['A1'].resize(10, 5)  # 10 rows, 5 columns
136```
137 
138### Formatting
139```python
140# Font
141sheet['A1'].font.bold = True
142sheet['A1'].font.size = 14
143sheet['A1'].font.color = (255, 0, 0)  # RGB red
144 
145# Fill
146sheet['A1'].color = (255, 255, 0)  # Yellow background
147 
148# Number format
149sheet['B1'].number_format = '$#,##0.00'
150 
151# Column width
152sheet['A:A'].column_width = 20
153 
154# Row height
155sheet['1:1'].row_height = 30
156 
157# Autofit
158sheet['A:D'].autofit()
159```
160 
161### Excel Features
162 
163#### Charts
164```python
165# Add chart
166chart = sheet.charts.add(left=100, top=100, width=400, height=250)
167chart.set_source_data(sheet['A1:B10'])
168chart.chart_type = 'column_clustered'
169chart.name = 'Sales Chart'
170 
171# Modify existing chart
172chart = sheet.charts['Sales Chart']
173chart.chart_type = 'line'
174```
175 
176#### Tables
177```python
178# Create Excel Table
179rng = sheet['A1'].expand()
180table = sheet.tables.add(source=rng, name='SalesTable')
181 
182# Refresh table
183table.refresh()
184 
185# Access table data
186table_data = table.data_body_range.value
187```
188 
189#### Pictures
190```python
191# Add picture
192sheet.pictures.add('logo.png', left=10, top=10, width=100, height=50)
193 
194# Update picture from matplotlib
195import matplotlib.pyplot as plt
196fig, ax = plt.subplots()
197ax.plot([1, 2, 3], [1, 4, 9])
198sheet.pictures.add(fig, name='MyPlot', update=True)
199```
200 
201### VBA Integration
202```python
203# Run VBA macro
204wb.macro('MacroName')()
205 
206# With arguments
207wb.macro('MyMacro')('arg1', 'arg2')
208 
209# Get return value
210result = wb.macro('CalculateTotal')(100, 200)
211 
212# Access VBA module
213vb_code = wb.api.VBProject.VBComponents('Module1').CodeModule.Lines(1, 10)
214```
215 
216### User Defined Functions (UDFs)
217```python
218# Define a UDF (in Python file)
219import xlwings as xw
220 
221@xw.func
222def my_sum(x, y):
223    """Add two numbers"""
224    return x + y
225 
226@xw.func
227@xw.arg('data', ndim=2)
228def my_array_func(data):
229    """Process array data"""
230    import numpy as np
231    return np.sum(data)
232 
233# These become Excel functions: =my_sum(A1, B1)
234```
235 
236### Application Control
237```python
238# Excel application settings
239app = xw.apps.active
240app.screen_updating = False  # Speed up
241app.calculation = 'manual'   # Manual calc
242app.display_alerts = False   # Suppress dialogs
243 
244# Perform operations...
245 
246# Restore
247app.screen_updating = True
248app.calculation = 'automatic'
249app.display_alerts = True
250```
251 
252## Best Practices
253 
2541. **Disable Screen Updating**: For batch operations
2552. **Use Arrays**: Read/write entire ranges, not cell-by-cell
2563. **Manual Calculation**: Turn off auto-calc during data loading
2574. **Close Connections**: Properly close workbooks when done
2585. **Error Handling**: Handle Excel not being installed
259 
260## Common Patterns
261 
262### Performance Optimization
263```python
264import xlwings as xw
265 
266def batch_update(data, workbook_path):
267    app = xw.App(visible=False)
268    try:
269        app.screen_updating = False
270        app.calculation = 'manual'
271        
272        wb = app.books.open(workbook_path)
273        sheet = wb.sheets['Data']
274        
275        # Write all data at once
276        sheet['A1'].value = data
277        
278        app.calculation = 'automatic'
279        wb.save()
280    finally:
281        wb.close()
282        app.quit()
283```
284 
285### Dashboard Update
286```python
287def update_dashboard(data_dict):
288    wb = xw.books.active
289    
290    # Update data sheet
291    data_sheet = wb.sheets['Data']
292    for name, values in data_dict.items():
293        data_sheet[name].value = values
294    
295    # Refresh all charts
296    dashboard = wb.sheets['Dashboard']
297    for chart in dashboard.charts:
298        chart.refresh()
299    
300    # Update timestamp
301    from datetime import datetime
302    dashboard['A1'].value = f'Last Updated: {datetime.now()}'
303```
304 
305### Report Generator
306```python
307def generate_monthly_report(month, data):
308    template = xw.Book('template.xlsx')
309    
310    # Fill data
311    sheet = template.sheets['Report']
312    sheet['B2'].value = month
313    sheet['A5'].value = data
314    
315    # Run calculations
316    template.app.calculate()
317    
318    # Export to PDF
319    sheet.api.ExportAsFixedFormat(0, f'report_{month}.pdf')
320    
321    template.save(f'report_{month}.xlsx')
322```
323 
324## Examples
325 
326### Example 1: Live Dashboard Update
327```python
328import xlwings as xw
329import pandas as pd
330from datetime import datetime
331 
332# Connect to running Excel
333wb = xw.books.active
334dashboard = wb.sheets['Dashboard']
335data_sheet = wb.sheets['Data']
336 
337# Fetch new data (simulated)
338new_data = pd.DataFrame({
339    'Date': pd.date_range('2024-01-01', periods=30),
340    'Sales': [1000 + i*50 for i in range(30)],
341    'Costs': [600 + i*30 for i in range(30)]
342})
343 
344# Update data sheet
345data_sheet['A1'].value = new_data
346 
347# Calculate profit
348data_sheet['D1'].value = 'Profit'
349data_sheet['D2'].value = '=B2-C2'
350data_sheet['D2'].expand('down').value = data_sheet['D2'].formula
351 
352# Update KPIs on dashboard
353dashboard['B2'].value = new_data['Sales'].sum()
354dashboard['B3'].value = new_data['Costs'].sum()
355dashboard['B4'].value = new_data['Sales'].sum() - new_data['Costs'].sum()
356dashboard['A1'].value = f'Updated: {datetime.now().strftime("%Y-%m-%d %H:%M")}'
357 
358# Refresh charts
359for chart in dashboard.charts:
360    chart.api.Refresh()
361 
362print("Dashboard updated!")
363```
364 
365### Example 2: Batch Processing Multiple Files
366```python
367import xlwings as xw
368from pathlib import Path
369 
370def process_sales_files(folder_path, output_path):
371    """Consolidate multiple Excel files into one summary."""
372    
373    app = xw.App(visible=False)
374    app.screen_updating = False
375    
376    try:
377        # Create summary workbook
378        summary_wb = xw.Book()
379        summary_sheet = summary_wb.sheets[0]
380        summary_sheet.name = 'Consolidated'
381        
382        headers = ['File', 'Total Sales', 'Total Units', 'Avg Price']
383        summary_sheet['A1'].value = headers
384        
385        row = 2
386        for file in Path(folder_path).glob('*.xlsx'):
387            wb = app.books.open(str(file))
388            data_sheet = wb.sheets['Sales']
389            
390            # Extract summary
391            total_sales = data_sheet['B:B'].api.SpecialCells(11).Value  # xlCellTypeConstants
392            total_units = data_sheet['C:C'].api.SpecialCells(11).Value
393            
394            # Calculate and write
395            summary_sheet[f'A{row}'].value = file.name
396            summary_sheet[f'B{row}'].value = sum(total_sales) if isinstance(total_sales, (list, tuple)) else total_sales
397            summary_sheet[f'C{row}'].value = sum(total_units) if isinstance(total_units, (list, tuple)) else total_units
398            summary_sheet[f'D{row}'].value = f'=B{row}/C{row}'
399            
400            wb.close()
401            row += 1
402        
403        # Format summary
404        summary_sheet['A1:D1'].font.bold = True
405        summary_sheet['B:D'].number_format = '$#,##0.00'
406        summary_sheet['A:D'].autofit()
407        
408        summary_wb.save(output_path)
409        
410    finally:
411        app.quit()
412    
413    print(f"Consolidated {row-2} files to {output_path}")
414 
415# Usage
416process_sales_files('/path/to/sales/', 'consolidated_sales.xlsx')
417```
418 
419### Example 3: Excel Add-in with UDFs
420```python
421# myudfs.py - Place in xlwings project
422 
423import xlwings as xw
424import numpy as np
425 
426@xw.func
427@xw.arg('data', pd.DataFrame, index=False, header=False)
428@xw.ret(expand='table')
429def GROWTH_RATE(data):
430    """Calculate period-over-period growth rate"""
431    values = data.iloc[:, 0].values
432    growth = np.diff(values) / values[:-1] * 100
433    return [['Growth %']] + [[g] for g in growth]
434 
435@xw.func
436@xw.arg('range1', np.array, ndim=2)
437@xw.arg('range2', np.array, ndim=2)
438def CORRELATION(range1, range2):
439    """Calculate correlation between two ranges"""
440    return np.corrcoef(range1.flatten(), range2.flatten())[0, 1]
441 
442@xw.func
443def SENTIMENT(text):
444    """Basic sentiment analysis (placeholder)"""
445    positive = ['good', 'great', 'excellent', 'amazing']
446    negative = ['bad', 'poor', 'terrible', 'awful']
447    
448    text_lower = text.lower()
449    pos_count = sum(word in text_lower for word in positive)
450    neg_count = sum(word in text_lower for word in negative)
451    
452    if pos_count > neg_count:
453        return 'Positive'
454    elif neg_count > pos_count:
455        return 'Negative'
456    return 'Neutral'
457```
458 
459## Limitations
460 
461- Requires Excel to be installed
462- Limited support on macOS for some features
463- Not suitable for server-side processing
464- VBA features require trust settings
465- Performance varies with Excel version
466 
467## Installation
468 
469```bash
470pip install xlwings
471 
472# For add-in functionality
473xlwings addin install
474```
475 
476## Resources
477 
478- [xlwings Documentation](https://docs.xlwings.org/)
479- [GitHub Repository](https://github.com/xlwings/xlwings)
480- [UDF Tutorial](https://docs.xlwings.org/en/stable/udfs.html)
481- [Excel VBA Reference](https://docs.microsoft.com/en-us/office/vba/api/overview/excel)
482