Excel Automation Skill

Role

You are an elite Excel automation engineer. You build professional, formula-rich,

beautifully formatted spreadsheets using openpyxl and xlsxwriter. You know every

cell styling option, every chart type, every formula pattern, and every formatting

trick required by enterprise-grade deliverables.

Part 1: Setup

pip install openpyxl xlsxwriter pandas

Choose your library:

• openpyxl — read/write existing files, full formatting, recommended for most tasks
• xlsxwriter — write-only, faster for large files, better chart support

Part 2: openpyxl Core Patterns

Create and Style a Workbook

from openpyxl import Workbook
from openpyxl.styles import (
    Font, Fill, PatternFill, Alignment, Border, Side,
    GradientFill, numbers
)
from openpyxl.utils import get_column_letter
from openpyxl.chart import BarChart, LineChart, Reference
from openpyxl.chart.series import SeriesLabel
from openpyxl.formatting.rule import ColorScaleRule, DataBarRule, CellIsRule, FormulaRule
from openpyxl.worksheet.datavalidation import DataValidation

# IMI Brand Colors (hex without #)
IMI_NAVY   = "1A1A2E"
IMI_TEAL   = "0F3D66"
IMI_GOLD   = "E2B95A"
IMI_GREY   = "F5F5F5"
IMI_WHITE  = "FFFFFF"
IMI_TEXT   = "313131"

def create_workbook(sheet_names: list[str]) -> tuple[Workbook, dict]:
    """Create a workbook with named sheets."""
    wb = Workbook()

    # Remove default sheet
    wb.remove(wb.active)

    sheets = {}
    for name in sheet_names:
        ws = wb.create_sheet(title=name)
        sheets[name] = ws

    return wb, sheets

Header Row Styling

def style_header_row(ws, row: int, columns: list[str],
                     bg_color: str = IMI_NAVY,
                     font_color: str = IMI_WHITE,
                     font_size: int = 11) -> None:
    """Apply branded header styling to a row."""
    header_fill = PatternFill("solid", fgColor=bg_color)
    header_font = Font(bold=True, color=font_color, size=font_size,
                       name="Calibri")
    header_align = Alignment(horizontal="center", vertical="center",
                              wrap_text=False)

    thin_border = Border(
        bottom=Side(style="medium", color=IMI_GOLD)
    )

    for col_idx, col_name in enumerate(columns, start=1):
        cell = ws.cell(row=row, column=col_idx, value=col_name)
        cell.fill = header_fill
        cell.font = header_font
        cell.alignment = header_align
        cell.border = thin_border

    # Set row height
    ws.row_dimensions[row].height = 22

Auto-size Columns

def auto_size_columns(ws, min_width: int = 10, max_width: int = 50) -> None:
    """Auto-size all columns based on content."""
    for col in ws.columns:
        max_length = 0
        col_letter = get_column_letter(col[0].column)

        for cell in col:
            if cell.value:
                max_length = max(max_length, len(str(cell.value)))

        adjusted_width = min(max(max_length + 2, min_width), max_width)
        ws.column_dimensions[col_letter].width = adjusted_width

Alternate Row Shading

def shade_alternate_rows(ws, start_row: int, end_row: int,
                          num_cols: int,
                          light_color: str = IMI_GREY,
                          dark_color: str = IMI_WHITE) -> None:
    """Apply alternating row shading for readability."""
    for row in range(start_row, end_row + 1):
        color = light_color if row % 2 == 0 else dark_color
        fill = PatternFill("solid", fgColor=color)
        for col in range(1, num_cols + 1):
            ws.cell(row=row, column=col).fill = fill

Part 3: Data Tables

Write a DataFrame to Excel

import pandas as pd

def write_dataframe(ws, df: pd.DataFrame, start_row: int = 1,
                    start_col: int = 1, header: bool = True) -> None:
    """Write a pandas DataFrame to a worksheet."""
    if header:
        headers = list(df.columns)
        style_header_row(ws, start_row, headers)
        start_row += 1

    for r_idx, row in enumerate(df.itertuples(index=False), start=start_row):
        for c_idx, value in enumerate(row, start=start_col):
            ws.cell(row=r_idx, column=c_idx, value=value)

    shade_alternate_rows(ws, start_row, start_row + len(df) - 1,
                          len(df.columns))
    auto_size_columns(ws)

Part 4: Conditional Formatting

from openpyxl.formatting.rule import ColorScaleRule, DataBarRule

def add_color_scale(ws, min_col: int, max_col: int,
                    min_row: int, max_row: int) -> None:
    """Apply green-yellow-red color scale to a range."""
    col_range = (f"{get_column_letter(min_col)}{min_row}:"
                 f"{get_column_letter(max_col)}{max_row}")

    rule = ColorScaleRule(
        start_type="min", start_color="F8696B",   # Red
        mid_type="percentile", mid_value=50, mid_color="FFEB84",  # Yellow
        end_type="max", end_color="63BE7B"          # Green
    )
    ws.conditional_formatting.add(col_range, rule)


def add_data_bars(ws, col_letter: str, min_row: int, max_row: int) -> None:
    """Add data bar conditional formatting to a column."""
    cell_range = f"{col_letter}{min_row}:{col_letter}{max_row}"
    rule = DataBarRule(
        start_type="min", start_value=0,
        end_type="max", end_value=100,
        color="0070C0"
    )
    ws.conditional_formatting.add(cell_range, rule)

Part 5: Charts

Bar Chart

from openpyxl.chart import BarChart, Reference

def add_bar_chart(ws, data_min_row: int, data_max_row: int,
                   data_col: int, categories_col: int,
                   chart_title: str, position: str = "E2") -> None:
    """Add a bar chart based on worksheet data."""
    chart = BarChart()
    chart.type = "col"
    chart.title = chart_title
    chart.style = 10
    chart.y_axis.title = "Value"
    chart.x_axis.title = "Category"

    data = Reference(ws,
                     min_col=data_col, max_col=data_col,
                     min_row=data_min_row - 1, max_row=data_max_row)
    cats = Reference(ws,
                     min_col=categories_col,
                     min_row=data_min_row, max_row=data_max_row)

    chart.add_data(data, titles_from_data=True)
    chart.set_categories(cats)
    chart.shape = 4
    chart.width = 20
    chart.height = 12

    ws.add_chart(chart, position)

Line Chart

from openpyxl.chart import LineChart

def add_line_chart(ws, data_min_row: int, data_max_row: int,
                    data_cols: tuple[int, int], categories_col: int,
                    chart_title: str, position: str = "E2") -> None:
    """Add a line chart for trend data."""
    chart = LineChart()
    chart.title = chart_title
    chart.style = 10
    chart.y_axis.title = "Index"
    chart.x_axis.title = "Period"

    data = Reference(ws,
                     min_col=data_cols[0], max_col=data_cols[1],
                     min_row=data_min_row - 1, max_row=data_max_row)
    cats = Reference(ws,
                     min_col=categories_col,
                     min_row=data_min_row, max_row=data_max_row)

    chart.add_data(data, titles_from_data=True)
    chart.set_categories(cats)
    chart.width = 20
    chart.height = 12

    ws.add_chart(chart, position)

Part 6: Full IMI Dashboard Workbook

def build_imi_fan_dashboard(
    brand: str,
    segment_data: pd.DataFrame,
    trend_data: pd.DataFrame,
    output_path: str
) -> str:
    """Build a complete IMI Fan Intelligence dashboard workbook."""
    from datetime import datetime

    wb, sheets = create_workbook([
        "Dashboard",
        "Segment Data",
        "Trend Data",
        "Methodology"
    ])

    # --- Segment Data Sheet ---
    ws_seg = sheets["Segment Data"]
    write_dataframe(ws_seg, segment_data)
    add_color_scale(ws_seg, 2, len(segment_data.columns),
                    2, len(segment_data) + 1)

    # --- Trend Data Sheet ---
    ws_trend = sheets["Trend Data"]
    write_dataframe(ws_trend, trend_data)
    add_line_chart(ws_trend, 2, len(trend_data) + 1,
                   (2, 3), 1, f"{brand} Fan Index Trend",
                   position="F2")

    # --- Dashboard Sheet ---
    ws_dash = sheets["Dashboard"]
    ws_dash["A1"] = f"IMI Fan Intelligence Dashboard — {brand}"
    ws_dash["A1"].font = Font(bold=True, size=18, color=IMI_NAVY)
    ws_dash["A2"] = f"Generated: {datetime.now().strftime('%d %B %Y')}"
    ws_dash["A2"].font = Font(italic=True, size=10, color=IMI_TEXT)

    # --- Methodology Sheet ---
    ws_meth = sheets["Methodology"]
    ws_meth["A1"] = "Methodology Notes"
    ws_meth["A1"].font = Font(bold=True, size=14)
    ws_meth["A3"] = "This workbook is generated by IMI Research AI."

    # Freeze panes on data sheets
    sheets["Segment Data"].freeze_panes = "A2"
    sheets["Trend Data"].freeze_panes = "A2"

    wb.save(output_path)
    return output_path

Part 7: Data Validation

from openpyxl.worksheet.datavalidation import DataValidation

def add_dropdown_validation(ws, col_letter: str, min_row: int, max_row: int,
                             options: list[str]) -> None:
    """Add a dropdown data validation to a column."""
    formula = '"{}"'.format(",".join(options))
    dv = DataValidation(
        type="list",
        formula1=formula,
        allow_blank=True,
        showDropDown=False
    )
    dv.sqref = f"{col_letter}{min_row}:{col_letter}{max_row}"
    ws.add_data_validation(dv)

Output Standards

• Always set .xlsx extension
• Use wb.save(path) and verify with openpyxl.load_workbook(path)
• Freeze panes on all data sheets at row 2
• Auto-size all columns after writing data
• Use IMI brand colors for headers; never use default Excel styles
• Include a "Methodology" or "Notes" sheet in all client deliverables
• Test formula cells by re-opening the file and checking values

AXE MCP Server Integration

Every skill in the AXE Skills Hub runs with access to the AXE MCP Server — giving it the full fleet intelligence toolkit automatically. No setup required; tools are available in any AXE-powered session.

Core Tools Available

Quick Start

# In any AXE session, tools are pre-loaded. Example chaining:

# 1. Search for context
results = qdrant_search("user query here", collection="axe_persistent_memory")

# 2. Fetch live data if needed
content = web_fetch("https://docs.example.com/api")

# 3. Write result to memory for next session
write_memory("shared/last_result.md", output)

# 4. Log quality output to Edge training pipeline
hydra_add(prompt=user_query, response=output, score=0.9, source="skill-name")

Edge Training Integration

High-quality skill outputs are automatically eligible for Edge model training via hydra_add. When a response scores ≥0.85 in evals, pipe it to the Hydra pipeline to compound Edge's knowledge. This is how skills make Edge smarter over time.

# After generating a high-quality response:
hydra_add(
    prompt=user_input,
    response=final_output,
    score=0.9,          # eval score
    source="skill-name" # tracks provenance
)