<< All versions
Skill v1.0.1
currentAutomated scan100/100dvcrn/openclaw-skills-marketplace/excel-automation
2 files
──Details
PublishedJune 9, 2026 at 04:42 AM
Content Hashsha256:c0d589e7d3da3f28...
Git SHA67b1c2373b5a
Bump Typepatch
──Files
Files (1 file, 10.5 KB)
SKILL.md10.5 KBactive
SKILL.md · 436 lines · 10.5 KB
version: "1.0.1" name: excel-automation description: "Advanced Excel automation with Python using xlwings - interact with live Excel instances"
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
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]
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
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
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()
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'
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
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)
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)
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 npreturn 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
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 xwdef batch_update(data, workbook_path):app = xw.App(visible=False)try:app.screen_updating = Falseapp.calculation = 'manual'wb = app.books.open(workbook_path)sheet = wb.sheets['Data']# Write all data at oncesheet['A1'].value = dataapp.calculation = 'automatic'wb.save()finally:wb.close()app.quit()
Dashboard Update
python
def update_dashboard(data_dict):wb = xw.books.active# Update data sheetdata_sheet = wb.sheets['Data']for name, values in data_dict.items():data_sheet[name].value = values# Refresh all chartsdashboard = wb.sheets['Dashboard']for chart in dashboard.charts:chart.refresh()# Update timestampfrom datetime import datetimedashboard['A1'].value = f'Last Updated: {datetime.now()}'
Report Generator
python
def generate_monthly_report(month, data):template = xw.Book('template.xlsx')# Fill datasheet = template.sheets['Report']sheet['B2'].value = monthsheet['A5'].value = data# Run calculationstemplate.app.calculate()# Export to PDFsheet.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!")
Example 2: Batch Processing Multiple Files
python
import xlwings as xwfrom pathlib import Pathdef process_sales_files(folder_path, output_path):"""Consolidate multiple Excel files into one summary."""app = xw.App(visible=False)app.screen_updating = Falsetry:# Create summary workbooksummary_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 = headersrow = 2for file in Path(folder_path).glob('*.xlsx'):wb = app.books.open(str(file))data_sheet = wb.sheets['Sales']# Extract summarytotal_sales = data_sheet['B:B'].api.SpecialCells(11).Value # xlCellTypeConstantstotal_units = data_sheet['C:C'].api.SpecialCells(11).Value# Calculate and writesummary_sheet[f'A{row}'].value = file.namesummary_sheet[f'B{row}'].value = sum(total_sales) if isinstance(total_sales, (list, tuple)) else total_salessummary_sheet[f'C{row}'].value = sum(total_units) if isinstance(total_units, (list, tuple)) else total_unitssummary_sheet[f'D{row}'].value = f'=B{row}/C{row}'wb.close()row += 1# Format summarysummary_sheet['A1:D1'].font.bold = Truesummary_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 projectimport 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].valuesgrowth = np.diff(values) / values[:-1] * 100return [['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
bash
pip install xlwings# For add-in functionalityxlwings addin install