Automating Excel

Automating Excel (JXA-first, AppleScript discovery)

Relationship to the macOS automation skill

• Standalone for Excel, but aligned with automating-mac-apps patterns.
• Use automating-mac-apps for permissions, shell, and UI scripting guidance.
• PyXA Installation: To use PyXA examples in this skill, see the installation instructions in automating-mac-apps skill (PyXA Installation section).

Core Framing

• Excel AppleScript dictionary is AppleScript-first; use Script Editor for discovery.
• JXA is the production language for logic and data processing.
• Collections are specifiers; read via methods, set via assignments.
• Handle errors from Excel operations using try/catch blocks and Application error checking.

Workflow (default)

1) [ ] Discover dictionary terms in Script Editor (Excel). 2) [ ] Prototype a minimal AppleScript command. 3) [ ] Port to JXA and add defensive checks. 4) [ ] Use bulk read/write (2D arrays) for performance. 5) [ ] Use VBA run() when dictionary coverage is missing.

Validation Steps

• Test with empty documents to verify error handling
• Verify data integrity after batch operations
• Check Excel UI responsiveness after automation runs
• Log errors with specific Excel object paths for debugging

Examples

Basic workbook read:

const Excel = Application('Microsoft Excel');
const workbook = Excel.workbooks[0];
const worksheet = workbook.worksheets['Sheet1'];
const range = worksheet.ranges['A1:B10'];
const data = range.value();  // Returns 2D array

Bulk write with performance toggle:

Excel.screenUpdating = false;
Excel.calculation = 'manual';
try {
  const range = worksheet.ranges['C1:D100'];
  range.value = my2DArray;
} finally {
  Excel.calculate();
  Excel.screenUpdating = true;
}

VBA escape hatch:

Excel.run('MyMacro', {arg1: 'value', arg2: 123});  // Calls VBA subroutine

When Not to Use

• For general macOS automation without Excel involvement
• When AppleScript alone suffices (no JXA logic needed)
• For Excel tasks requiring complex UI interactions (use automating-mac-apps for that)
• When cross-platform compatibility is required

What to load

• JXA Excel basics: automating-excel/references/excel-basics.md
• Recipes (ranges, 2D arrays, formatting): automating-excel/references/excel-recipes.md
• Advanced patterns (performance toggles, VBA bridge): automating-excel/references/excel-advanced.md
• Pivot table guidance: automating-excel/references/excel-pivots.md
• Charting guidance: automating-excel/references/excel-charts.md
• Dictionary translation table: automating-excel/references/excel-dictionary.md
• PyXA (Python) alternative: automating-excel/references/excel-pyxa.md

Excel JXA Advanced Patterns

Performance Optimization

Global Settings (Always Restore!)

var Excel = Application("Microsoft Excel");

// Store original state
var prevScreen = Excel.screenUpdating();
var prevAlerts = Excel.displayAlerts();
var prevCalc = Excel.calculation();

try {
    // Disable for performance
    Excel.screenUpdating = false;   // Freeze UI
    Excel.displayAlerts = false;    // Suppress dialogs
    Excel.calculation = -4135;      // xlCalculationManual

    // === Heavy operations here ===

} finally {
    // ALWAYS restore state
    Excel.screenUpdating = prevScreen;
    Excel.displayAlerts = prevAlerts;
    Excel.calculation = prevCalc;
}

Calculation Constants

The Objective-C Bridge

File System Operations

ObjC.import('Foundation');

function fileExists(path) {
    return $.NSFileManager.defaultManager
        .fileExistsAtPath($(path).stringByStandardizingPath);
}

function listDirectory(dirPath) {
    var fm = $.NSFileManager.defaultManager;
    var contents = fm.contentsOfDirectoryAtPathError(dirPath, $());
    return ObjC.unwrap(contents);
}

function getDesktopPath() {
    var paths = $.NSSearchPathForDirectoriesInDomains(
        $.NSDesktopDirectory,
        $.NSUserDomainMask,
        true
    );
    return ObjC.unwrap(paths.objectAtIndex(0));
}

Reading Files with Proper Encoding

function readTextFile(path) {
    var errorRef = $();
    var nsPath = $(path).stringByStandardizingPath;

    var content = $.NSString.stringWithContentsOfFileEncodingError(
        nsPath,
        $.NSUTF8StringEncoding,
        errorRef
    );

    if (errorRef.code) {
        throw new Error("Read failed: " + ObjC.unwrap(errorRef.localizedDescription));
    }

    return ObjC.unwrap(content);
}

VBA Escape Hatch

When JXA lacks functionality, call existing VBA macros:

// Execute a VBA macro
Excel.run("FormatReport");

// Execute with parameters
Excel.run("UpdateCalculation", { arg1: 500 });

// Call macro in specific workbook
var wb = Excel.workbooks["Report.xlsm"];
wb.runVBMacro("Module1.ProcessData");

Use Cases:

• Pivot Table creation (unstable in JXA)
• Complex chart configurations
• Conditional formatting rules
• Advanced filtering

AppleScript to JXA Translation

Element vs Property Rule

Command Translation

2D Array Rules (Critical)

JXA enforces strict array structure for ranges:

// Single row: MUST wrap in outer array
range.value = [[a, b, c]];  // NOT [a, b, c]

// Single column: MUST wrap each value
range.value = [[a], [b], [c]];  // NOT [[a, b, c]]

// Block: Standard 2D array
range.value = [[r1c1, r1c2], [r2c1, r2c2]];

// Range dimensions MUST match array dimensions
// Mismatch causes Error -1700 (Type conversion failed)

Debugging

Common Error Codes

Inspecting Objects

// Get all properties of an object
var props = ws.properties();
console.log(JSON.stringify(props, null, 2));

// List available methods (triggers heavy Apple Events)
console.log(Object.keys(ws));

NSLog for Real-Time Debugging

function log(msg) {
    $.NSLog($.NSString.alloc.initWithUTF8String(msg));
}

// Usage
log("Processing row: " + i);

Batch Processing Pattern

function batchProcess(inputDir, outputDir) {
    ObjC.import('Foundation');

    var Excel = Application("Microsoft Excel");
    var fm = $.NSFileManager.defaultManager;

    // List Excel files
    var contents = fm.contentsOfDirectoryAtPathError(inputDir, $());
    var files = ObjC.unwrap(contents).filter(f => f.endsWith('.xlsx'));

    try {
        Excel.screenUpdating = false;
        Excel.displayAlerts = false;

        files.forEach(function(file) {
            try {
                var inputPath = inputDir + "/" + file;
                var outputPath = outputDir + "/" + file.replace('.xlsx', '_processed.xlsx');

                var wb = Excel.openWorkbook({ workbookFileName: inputPath });

                // Process workbook...
                var ws = wb.worksheets[0];
                var data = ws.usedRange.value();
                // Transform data in JavaScript
                var processed = data.map(row => row.map(cell => cell * 2));
                ws.usedRange.value = processed;

                wb.saveWorkbookAs({ filename: outputPath });
                wb.close({ saving: 'no' });

                console.log("Processed: " + file);
            } catch (e) {
                console.log("Error processing " + file + ": " + e.message);
            }
        });

    } finally {
        Excel.screenUpdating = true;
        Excel.displayAlerts = true;
    }
}

Production Template

/**
 * Excel JXA Automation Controller
 * Production-grade template
 */
'use strict';

ObjC.import('Foundation');

function run() {
    var Excel = Application("Microsoft Excel");
    Excel.includeStandardAdditions = true;

    // Store original state
    var state = {
        screen: Excel.screenUpdating(),
        alerts: Excel.displayAlerts(),
        calc: Excel.calculation()
    };

    try {
        // Performance mode
        Excel.screenUpdating = false;
        Excel.displayAlerts = false;
        Excel.calculation = -4135;

        // === AUTOMATION LOGIC ===

        console.log("Completed successfully");

    } catch (error) {
        console.log("Error: " + error.message);
        Excel.displayAlerts = true;

    } finally {
        // Restore state
        Excel.screenUpdating = state.screen;
        Excel.displayAlerts = state.alerts;
        Excel.calculation = state.calc;
    }
}

Excel JXA Basics

Runtime Architecture

JXA controls Excel through Apple Events (IPC). Unlike VBA which runs inside Excel's process, JXA scripts run externally. This means:

• Each property access/command is an IPC round-trip
• Objects are Object Specifiers (queries) not actual data
• Data is fetched only when explicitly accessed with () or .value()

Bootstrapping

'use strict';

var Excel = Application("Microsoft Excel");
Excel.includeStandardAdditions = true;

// Verify Excel is running
if (!Excel.running()) {
    Excel.activate();
    delay(1); // Allow launch time
}

Application Object

The Application('Microsoft Excel') object is the root of the automation hierarchy:

• Excel.frontmost - Boolean for active window status
• Excel.version - Excel version string
• Excel.activeWorkbook - Currently focused workbook
• Excel.workbooks - Collection of all open workbooks

Workbook Management

Creating Workbooks

// Create new workbook
var wb = Excel.Workbooks.add();

// Create from template
var templateWb = Excel.Workbooks.add({ template: "TemplateName" });

Opening Workbooks

// Use POSIX path with parameter object
var filePath = "/Users/Me/Documents/Data.xlsx";
var wb = Excel.openWorkbook({ workbookFileName: filePath });

Saving Workbooks

// Save to existing location
wb.save();

// Save As with format
wb.saveWorkbookAs({
    filename: "/Users/Me/Desktop/Report.xlsx",
    fileFormat: "xlsx"
});

Worksheet Operations

Accessing Worksheets

// By name (preferred - robust)
var sheet = wb.worksheets["Data"];

// By index (fragile - order can change)
var firstSheet = wb.worksheets[0];

// Iterate all sheets
for (var i = 0; i < wb.worksheets.length; i++) {
    var currentSheet = wb.worksheets[i];
    console.log(currentSheet.name());
}

Creating Worksheets

var newSheet = Excel.Sheet({ name: "Analysis" });
wb.worksheets.push(newSheet);

Worksheet Properties

ws.name = "Data";           // Rename
ws.activate();              // Bring to front
ws.delete();                // Remove (displayAlerts=false needed)

Range Addressing

Excel provides multiple ways to reference ranges:

// A1 notation
sheet.ranges["A1:C10"]

// Single cell
sheet.cells["A1"]

// Row access
sheet.rows[1]  // Second row (0-indexed)

// Column access
sheet.columns["C"]

// Dynamic used range
sheet.usedRange  // Auto-adjusts to data

Reading Cell Values

// Single cell - returns scalar
var value = ws.ranges["A1"].value();

// Range - returns 2D array (List of Lists)
var data = ws.ranges["A1:C3"].value();
// Result: [[a1, b1, c1], [a2, b2, c2], [a3, b3, c3]]

// Used range - dynamic
var allData = ws.usedRange.value();

Path Handling Notes

• Always use absolute POSIX paths: /Users/name/file.xlsx
• Use saveWorkbookAs({ filename: "..." }) for save operations
• The Path() constructor may be needed in some contexts
• Tilde expansion (~/) may not work - use full paths

Excel JXA charting

Preferred approach

• Use VBA macros for complex chart creation and formatting.
• Use JXA to populate data ranges and invoke VBA.

Minimal chart example (if supported)

// Assumes a chart can be created from a selected range
const range = ws.ranges["A1:B10"];
range.select();
Excel.run("CreateChartFromSelection"); // VBA macro

Notes

• JXA chart APIs may be incomplete or unstable across Excel versions.
• For consistent output, store a template workbook with prebuilt charts and update its data ranges.

Excel dictionary translation table

Common mappings

AppleScript                         JXA
----------------------------------- ------------------------------------------
workbook "Book1"                   Excel.workbooks.byName("Book1")
worksheet "Sheet1"                 wb.worksheets.byName("Sheet1")
value of range "A1"                ws.ranges["A1"].value()
set value of range "A1" to 1        ws.ranges["A1"].value = 1
used range                          ws.usedRange
active sheet                        Excel.activeSheet
screen updating                     Excel.screenUpdating
display alerts                      Excel.displayAlerts

Element vs property

• Elements -> collections (worksheets, workbooks, rows, columns).
• Properties -> camelCase attributes (screenUpdating, displayAlerts).

Excel JXA pivot tables (practical guidance)

Reality check

• Pivot table creation via JXA is brittle.
• Prefer VBA macros for creating pivots; use JXA to invoke them.

VBA workflow

1) Record or author a VBA macro in the workbook (e.g., BuildPivot). 2) Call it from JXA:

Excel.run("BuildPivot");

If you must attempt JXA

• Use explicit range addresses as strings (e.g., "Sheet1!A1:D100").
• Build a pivot cache via VBA or existing templates.

Excel Automation with PyXA (Python)

PyXA provides a Pythonic interface to control Microsoft Excel on macOS via Apple's Scripting Bridge.

Installation

pip install pyxa

Bootstrapping

import PyXA

# Launch or activate Excel
excel = PyXA.Application("Microsoft Excel")
excel.activate()

Workbook Management

Creating Workbooks

# Create new workbook
wb = excel.workbooks.new()

# Access first sheet
sheet = wb.sheets[0]

Opening Workbooks

# Open existing file
wb = excel.open("/Users/me/Documents/Data.xlsx")

Saving Workbooks

# Save to new location
wb.save_as("/Users/me/Desktop/Report.xlsx")

# Save existing
wb.save()

# Close without saving
wb.close(save=False)

Cell Operations

Reading and Writing Values

# Single cell
sheet.cells.item("A1").value = "Header"
value = sheet.cells.item("A1").value

# Using row/column indices (1-based)
sheet.cells.item(1, 1).value = "Row 1, Col 1"

Working with Ranges

# Range of cells
sheet.range("A1:C10").value = data_matrix

# Read range values (returns 2D list)
values = sheet.range("A1:C10").value

Formulas

# Set formula
sheet.cells.item("D1").formula = "=SUM(A1:C1)"
sheet.cells.item("E1").formula = "=B1*2"

Batch Data Operations

# Write 2D array to range (efficient)
data = [
    ["Name", "Score", "Grade"],
    ["Alice", 95, "A"],
    ["Bob", 87, "B"],
    ["Carol", 92, "A"]
]

# Calculate range size
rows = len(data)
cols = len(data[0])

# Write in one operation
sheet.range(f"A1:C{rows}").value = data

Formatting

Cell Formatting

# Bold headers
sheet.range("A1:C1").font_bold = True

# Background color
sheet.range("A1:C1").interior_color = PyXA.XAColor.blue

# Font properties
sheet.range("A1:C1").font_size = 14
sheet.range("A1:C1").font_name = "Arial"

Column Width

# Set specific width
sheet.columns.item("A").width = 20

# Auto-fit columns
sheet.columns.item("A:C").autofit()

Borders

# Add borders to range
sheet.range("A1:C10").borders.line_style = "continuous"
sheet.range("A1:C10").borders.weight = "thin"

Export to PDF

# Export sheet to PDF
sheet.export("/Users/me/Desktop/Report.pdf",
             export_format=PyXA.XL_ExportFormat.pdf)

Complete Report Generator

import PyXA

def generate_excel_report(output_path, headers, data):
    """Generate a formatted Excel report with PyXA."""
    excel = PyXA.Application("Microsoft Excel")
    excel.activate()

    # Create workbook
    wb = excel.workbooks.new()
    sheet = wb.sheets[0]
    sheet.name = "Report"

    # Prepare data matrix
    rows = [headers] + data
    row_count = len(rows)
    col_count = len(headers)

    # Write data in single operation
    sheet.range(f"A1:{chr(64+col_count)}{row_count}").value = rows

    # Format headers
    header_range = f"A1:{chr(64+col_count)}1"
    sheet.range(header_range).font_bold = True
    sheet.range(header_range).interior_color = PyXA.XAColor.blue
    sheet.range(header_range).font_color = PyXA.XAColor.white

    # Auto-fit columns
    for i in range(1, col_count + 1):
        sheet.columns.item(i).autofit()

    # Add borders to data area
    data_range = f"A1:{chr(64+col_count)}{row_count}"
    sheet.range(data_range).borders.line_style = "continuous"

    # Save
    wb.save_as(output_path)

    return {"success": True, "path": output_path}

# Usage
headers = ["ID", "Name", "Value", "Status"]
data = [
    [1, "Alpha", 100, "Active"],
    [2, "Beta", 200, "Pending"],
    [3, "Gamma", 150, "Active"]
]
generate_excel_report("/Users/me/Desktop/Report.xlsx", headers, data)

CSV Import Pattern

import PyXA
import csv

def csv_to_excel(csv_path, output_path):
    """Convert CSV file to formatted Excel workbook."""
    # Read CSV
    with open(csv_path, 'r') as f:
        reader = csv.reader(f)
        data = list(reader)

    # Create Excel workbook
    excel = PyXA.Application("Microsoft Excel")
    excel.activate()

    wb = excel.workbooks.new()
    sheet = wb.sheets[0]

    # Calculate dimensions
    rows = len(data)
    cols = len(data[0]) if data else 0

    # Write data
    if rows > 0 and cols > 0:
        sheet.range(f"A1:{chr(64+cols)}{rows}").value = data

    # Format header row
    sheet.range(f"A1:{chr(64+cols)}1").font_bold = True

    # Save
    wb.save_as(output_path)
    wb.close()

# Usage
csv_to_excel("/Users/me/data.csv", "/Users/me/data.xlsx")

PyXA vs JXA Comparison

Notes

• PyXA and JXA both use Scripting Bridge (similar performance)
• Excel ranges are 1-indexed in both APIs
• 2D arrays required for multi-cell operations
• Always match array dimensions to range dimensions
• Use wb.close(save=False) to discard changes

Excel JXA Recipes

The "List of Lists" Architecture (Critical)

Excel ranges are 2D matrices. JXA strictly enforces this structure - the most common source of "Type Mismatch" errors.

2D Array Rules

Writing Data (One-Shot Pattern)

// Build 2D array in JavaScript
var data = [
    ["Name", "Score", "Grade"],
    ["Ada", 98, "A"],
    ["Lin", 95, "A"],
    ["Max", 87, "B"]
];

// Calculate range size
var rowCount = data.length;
var colCount = data[0].length;

// Define exact range
var startCell = ws.cells["A1"];
var targetRange = startCell.resize({ rowSize: rowCount, columnSize: colCount });

// One-shot write (single Apple Event)
targetRange.value = data;

Reading Data

// Read entire used range
var used = ws.usedRange;
var values = used.value();  // Returns 2D array

// Process in JavaScript (fast - local memory)
var filtered = values.filter(row => row[1] > 90);

Performance: The One-Shot Rule

Each Apple Event has IPC latency. Cell-by-cell operations are 100-1000x slower than batch operations.

Bad (Slow) Pattern

// DON'T DO THIS - 1000 Apple Events
for (var i = 0; i < 1000; i++) {
    ws.cells["A" + (i+1)].value = i;  // Slow!
}

Good (Fast) Pattern

// DO THIS - 1 Apple Event
var data = [];
for (var i = 0; i < 1000; i++) {
    data.push([i]);  // Build in memory
}
ws.ranges["A1:A1000"].value = data;  // Single write

Formatting

Cell Backgrounds

var range = ws.ranges["A1:D1"];

// Using RGB array (modern)
range.interiorObject.color = [30, 50, 120];  // Dark blue

// Using colorIndex (legacy 56-color palette)
range.interiorObject.colorIndex = 3;  // Red

Font Styling

var header = ws.ranges["A1:D1"];
header.fontObject.name = "Arial";
header.fontObject.bold = true;
header.fontObject.size = 14;
header.fontObject.color = [255, 255, 255];  // White text

Borders

var range = ws.ranges["A1:D10"];
range.borders.lineStyle = "continuous";
range.borders.weight = "thin";

Auto-fit Columns

ws.columns["A:D"].autofit();

Complete Report Generator

function generateReport(outputPath, rawData) {
    var Excel = Application("Microsoft Excel");
    Excel.includeStandardAdditions = true;

    try {
        Excel.screenUpdating = false;
        Excel.displayAlerts = false;

        // Create workbook
        var wb = Excel.Workbooks.add();
        var ws = wb.worksheets[0];
        ws.name = "Report";

        // Prepare data with headers
        var headers = ["ID", "Name", "Value", "Status"];
        var dataMatrix = [headers].concat(rawData);

        // One-shot write
        var rowCount = dataMatrix.length;
        var colCount = dataMatrix[0].length;
        var range = ws.cells["A1"].resize({ rowSize: rowCount, columnSize: colCount });
        range.value = dataMatrix;

        // Format header row
        var headerRange = ws.ranges["A1:D1"];
        headerRange.interiorObject.color = [0, 51, 102];
        headerRange.fontObject.color = [255, 255, 255];
        headerRange.fontObject.bold = true;

        // Auto-fit and add borders
        ws.columns["A:D"].autofit();
        range.borders.lineStyle = "continuous";

        // Save
        wb.saveWorkbookAs({ filename: outputPath });

        console.log("Report generated: " + outputPath);

    } finally {
        Excel.screenUpdating = true;
        Excel.displayAlerts = true;
    }
}

// Usage
var testData = [
    [1, "Alpha", 100, "Active"],
    [2, "Beta", 200, "Pending"],
    [3, "Gamma", 150, "Active"]
];
generateReport("/Users/me/Desktop/Report.xlsx", testData);

CSV Import Pattern

function importCSV(csvPath, outputPath) {
    ObjC.import('Foundation');

    // Read CSV using ObjC (handles encoding properly)
    var nsPath = $(csvPath).stringByStandardizingPath;
    var content = $.NSString.stringWithContentsOfFileEncodingError(
        nsPath, $.NSUTF8StringEncoding, $()
    );
    var csvText = ObjC.unwrap(content);

    // Parse CSV to 2D array
    var rows = csvText.split('\n').filter(r => r.trim());
    var data = rows.map(row => row.split(','));

    // Write to Excel
    var Excel = Application("Microsoft Excel");
    var wb = Excel.Workbooks.add();
    var ws = wb.worksheets[0];

    var range = ws.cells["A1"].resize({
        rowSize: data.length,
        columnSize: data[0].length
    });
    range.value = data;

    wb.saveWorkbookAs({ filename: outputPath });
}

Conditional Formatting (Manual)

JXA doesn't directly support conditional formatting rules, but you can apply formatting based on values:

function applyConditionalColors(ws, dataRange) {
    var values = ws.ranges[dataRange].value();

    for (var r = 0; r < values.length; r++) {
        var value = values[r][0];  // Assuming first column has values
        var cellRef = "A" + (r + 1);

        if (value < 0) {
            ws.ranges[cellRef].fontObject.color = [255, 0, 0];  // Red
        } else if (value > 100) {
            ws.ranges[cellRef].fontObject.color = [0, 128, 0];  // Green
        }
    }
}