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

    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

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

import xlwings as xw

import pandas as pd

from datetime import datetime

# Connect to running Excel

wb = xw.books.active

dashboard = 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 sheet

data_sheet['A1'].value = new_data

# Calculate profit

data_sheet['D1'].value = 'Profit'

data_sheet['D2'].value = '=B2-C2'

data_sheet['D2'].expand('down').value = data_sheet['D2'].formula

# Update KPIs on dashboard

dashboard['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 charts

for chart in dashboard.charts:

    chart.api.Refresh()

print("Dashboard updated!")

Example 2: Batch Processing Multiple Files

import xlwings as xw

from 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}")

# Usage

process_sales_files('/path/to/sales/', 'consolidated_sales.xlsx')

Example 3: Excel Add-in with UDFs

# myudfs.py - Place in xlwings project

import xlwings as xw

import 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.func

def 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

pip install xlwings

# For add-in functionality

xlwings addin install

Resources

• xlwings Documentation

• GitHub Repository

• UDF Tutorial

• Excel VBA Reference