Automating Excel With xlwings: The Basics

xlwings drives a running copy of Excel from Python. Instead of editing the file bytes on disk, it talks to the Excel application over COM (Windows) or AppleScript (macOS), so charts recalculate, formulas evaluate, and VBA macros run exactly as they would if you clicked the buttons yourself. That makes it the right tool for the last mile of reporting — populating a formatted template, refreshing a pivot, and handing back a workbook a colleague can open without surprises.

The trade-off is that xlwings needs a real Excel install on the same machine. Where the rest of Getting Started with Python Excel Automation leans on file-based libraries that touch .xlsx bytes directly, xlwings is the one tool here that automates Excel itself. This guide walks through the core objects — App, Book, Sheet, Range — and a complete, safe automation pattern you can adapt.

What xlwings needs to run

xlwings is a bridge to the desktop Excel application, so it requires:

• Microsoft Excel installed locally — Excel 2010+ on Windows, or Excel 2016+ on macOS. On Windows it uses pywin32; on macOS it uses AppleScript.
• An interactive desktop session. Excel launches as a GUI process even when hidden, so xlwings does not work on headless Linux, plain Docker containers, or CI runners like GitHub Actions.

If you need to read or write .xlsx files on a server or in CI with no Excel present, use the file-based libraries instead: see Reading Excel Files with Pandas for ingestion and Using openpyxl for Excel File Manipulation for direct file editing. A common split is to do data work headless and reserve xlwings for final formatting and macro-driven delivery on a workstation.

Install xlwings

pip install xlwings

That single package pulls in the platform dependencies it needs (pywin32 on Windows). You do not need to install Excel through pip — xlwings connects to the copy already on your machine.

Open a workbook

The quickest way to get a live workbook is xw.Book. With no argument it creates a new blank workbook in a visible Excel window; pass a path to open an existing file:

import xlwings as xw

# Create a new blank workbook (opens a visible Excel window)
book = xw.Book()

# Or open an existing file
book = xw.Book(r"C:\reports\sales.xlsx")

xw.Book is convenient for interactive work because it reuses an Excel instance if one is already open. For scheduled jobs you usually want full control over the Excel process — that means starting your own xw.App, which the production pattern below uses.

Target a sheet

A Book holds a collection of sheets. Select one by name (or index) to get a Sheet object:

sheet = book.sheets["Raw_Data"]   # by name
sheet = book.sheets[0]            # by position (first sheet)

# Add a new sheet and make it the active one
new_sheet = book.sheets.add("Summary")

Read and write cell values

A Range is the workhorse object. Index a sheet with a cell address (or call sheet.range(...)), then use the .value property to read or assign:

sheet = book.sheets["Raw_Data"]

# Write a single value
sheet["A1"].value = "Order ID"

# Read it back — .value coerces Excel types to Python types
header = sheet["A1"].value          # -> "Order ID"

# Write a 2D block in one COM call (rows of columns)
sheet["A1"].value = [
    ["Order ID", "SKU", "Quantity"],
    [1001, "A-100", 3],
    [1002, "B-200", 1],
]

Assigning a list of lists to the top-left cell writes the whole block in a single round trip to Excel. That bulk transfer is far faster than looping cell by cell, so prefer it whenever you have tabular data.

Write a list down a column

A plain 1D Python list is written horizontally by default — it fills a row, not a column. To lay a list out vertically, use .options(transpose=True):

ids = [1001, 1002, 1003, 1004]

# Horizontal (default): fills A1, B1, C1, D1
sheet["A1"].value = ids

# Vertical: fills A1, A2, A3, A4
sheet["A1"].options(transpose=True).value = ids

.options() is also how you control the shape of what you read back — for example sheet["A1:A4"].options(ndim=1).value returns a flat list rather than a list of one-item lists.

Run a VBA macro

xlwings can invoke a VBA Sub by name and run it synchronously. Get a callable with app.macro() (application-scoped) or book.macro() (workbook-scoped), then call it. Both forms work in current xlwings — app.macro() was simply added later, in 0.24.0, to allow application-level resolution:

# Application-scoped: qualify with the workbook name
run = book.app.macro(f"'{book.name}'!RefreshPivotTables")
run()

# Workbook-scoped form — equivalent and still supported
book.macro("RefreshPivotTables")()

Macros only exist in macro-enabled files (.xlsm, .xlsb, or .xlam); a plain .xlsx cannot store VBA. Arguments are passed positionally, e.g. run("North", 2024). For a fuller treatment — parameter passing, personal-macro workbooks, and the errors you will hit — see the xlwings run macro from Python example.

A complete, safe automation pattern

For anything unattended, manage the Excel process yourself with xw.App and guarantee cleanup with try/finally. If an exception escapes before app.quit(), you leave an orphaned EXCEL.EXE holding a file lock. The pattern below opens a template, writes data in bulk, runs a macro, saves a dated copy, and always shuts Excel down:

import xlwings as xw
from pathlib import Path
from datetime import datetime


def generate_report(template_path: str, output_path: str, rows: list) -> None:
    """Populate an Excel template, refresh it via VBA, and save a copy."""
    # add_book=False: don't create an empty workbook alongside the template
    app = xw.App(visible=False, add_book=False)
    book = None
    try:
        book = app.books.open(str(Path(template_path).resolve()))
        sheet = book.sheets["Report"]

        # Stamp the run time
        sheet["B2"].value = f"Generated: {datetime.now():%Y-%m-%d %H:%M}"

        # Bulk-write the data block starting at A5
        sheet["A5"].value = rows

        # Tidy the layout
        sheet["A5"].expand().columns.autofit()

        # Refresh pivots / charts via a VBA macro in the template
        book.macro("RefreshPivotTables")()

        book.save(str(Path(output_path).resolve()))
    finally:
        if book is not None:
            book.close()
        app.quit()


if __name__ == "__main__":
    data = [
        ["2024-01-05", "North", "Widget A", 12500],
        ["2024-01-12", "South", "Widget B", 18300],
        ["2024-01-18", "East", "Widget C", 9400],
    ]
    output = f"sales_report_{datetime.now():%Y%m%d}.xlsx"
    generate_report("monthly_template.xlsm", output, data)

A few decisions in that code are worth calling out:

• visible=False runs Excel hidden, which is what you want for a scheduled job. Flip it to True while developing so you can watch the workbook fill in and catch VBA dialogs.
• add_book=False stops xlwings from spawning a throwaway blank workbook every time you start an App.
• Absolute paths via pathlib. A scheduler (cron, Task Scheduler) runs with a different working directory than your shell, so relative paths break. Path(...).resolve() removes the ambiguity.
• Cleanup in finally. book.close() then app.quit() runs on both success and failure, so no zombie Excel process survives.

Common pitfalls

• Orphaned Excel processes. Almost always a missing try/finally. If one lingers, it keeps the output file locked and the next run fails. Make app.quit() unconditional.
• Workbook not found / file-not-found. Usually a relative path under a scheduler. Resolve to an absolute path, as above.
• A 1D list landed in a row, not a column. Add .options(transpose=True) before .value.
• com_error: CoInitialize has not been called. Happens when you drive xlwings from a background thread or async framework. Call pythoncom.CoInitialize() on that thread first, or run the automation on a dedicated synchronous thread.
• Macro silently does nothing. Confirm the file is .xlsm/.xlsb/.xlam and that the macro name (and workbook qualifier) match exactly.

When xlwings is the right tool — and when it is not

xlwings drives a real copy of Excel, which makes it uniquely capable and uniquely constrained. The decision is nearly always made by where the code will run rather than by what it does:

The practical consequence is that xlwings and openpyxl are rarely alternatives for the same job. A scheduled overnight report has to be pure Python; an interactive tool that a colleague runs from a button in a workbook has to be xlwings. Where a scheduled job genuinely needs recalculation — a model workbook whose numbers only exist once Excel has evaluated it — the usual answer is a headless LibreOffice conversion rather than an Excel installation on the server.

Reading and writing ranges efficiently

Every property access in xlwings crosses a bridge into Excel, and that crossing is the expensive part. Reading a range once and writing it back once is orders of magnitude faster than looping over cells:

import xlwings as xw

book = xw.Book("orders.xlsx")
sheet = book.sheets["Orders"]

# Slow: one round trip per cell
# for row in range(2, 1002):
#     sheet.range(f"E{row}").value = sheet.range(f"C{row}").value * sheet.range(f"D{row}").value

# Fast: two round trips in total
quantities = sheet.range("C2:C1001").value          # a list of floats
prices = sheet.range("D2:D1001").value
sheet.range("E2:E1001").value = [[q * p] for q, p in zip(quantities, prices)]

book.save()

The nested list in the assignment is required: a column of values is a list of one-element rows, and passing a flat list writes across a row instead of down a column. Reading a two-dimensional range gives you a list of lists in the same shape, which makes the round trip symmetrical.

sheet.range("C2").expand("down").value is the idiomatic way to read a column whose length you do not know, and expand("table") grabs a whole contiguous block — both far better than computing the last row yourself.

DataFrames across the bridge

xlwings converts DataFrames directly, which makes it a comfortable bridge between a live workbook and pandas:

import pandas as pd
import xlwings as xw

book = xw.Book("orders.xlsx")
sheet = book.sheets["Orders"]

df = sheet.range("A1").options(pd.DataFrame, expand="table", index=False).value
df["Revenue"] = df["Quantity"] * df["Unit_Price"]

summary = df.groupby("Region", as_index=False)["Revenue"].sum()
target = book.sheets.add("Summary", after=sheet) if "Summary" not in [s.name for s in book.sheets] else book.sheets["Summary"]
target.clear()
target.range("A1").options(index=False).value = summary
book.save()

options(pd.DataFrame, expand="table", index=False) is the whole conversion: it reads the contiguous block starting at A1, treats the first row as headers, and returns a frame. Writing back is the mirror image. Calling clear() before writing matters — a shorter result than last time otherwise leaves stale rows underneath, which is the xlwings equivalent of the stale-total problem.

Housekeeping that keeps automation reliable

Three settings turn a script that works into one that runs unattended on a desktop:

import xlwings as xw

app = xw.App(visible=False, add_book=False)      # no flashing window
app.display_alerts = False                        # no "save changes?" dialogs
app.screen_updating = False                       # much faster for bulk edits

try:
    book = app.books.open("orders.xlsx")
    book.sheets["Orders"].range("A1").value = "Updated"
    book.save()
    book.close()
finally:
    app.quit()                                    # never leave a stray EXCEL.EXE

The finally block is the one that matters most. An abandoned Excel process holds a lock on the file, consumes memory, and eventually stops the next run from opening anything — and because it is invisible with visible=False, nobody notices until a machine has a dozen of them. Wrapping the whole session in try/finally, or using xw.App() as a context manager, is not optional in automation.

Common xlwings failures

Every row in that table is really the same lesson: xlwings is automating an application, not editing a file. Anything that would block a person — a dialog, a lock, a missing display — blocks the script too, and the fixes are the ones you would apply to an interactive session rather than to a parser.

Keep the Python and the workbook loosely coupled

The most maintainable xlwings projects treat the workbook as an interface rather than as part of the program. Read the inputs from named ranges instead of from fixed cells, write outputs to a dedicated results area, and keep every piece of business logic in Python where it can be tested. A workbook whose layout can change without breaking the script is one that colleagues can actually use — and a script that never hardcodes B7 is one that survives the day someone inserts a row.

Testing code that drives Excel

Automation that needs a running application is awkward to test, which is the strongest practical argument for keeping business logic out of it. Split the work so that the calculation is a pure function taking and returning DataFrames, and the xlwings layer does nothing but read a range, call that function, and write the result back. The calculation can then be tested in milliseconds with no Excel present, and the thin bridge layer needs only a single smoke test on a machine that has it.

Frequently asked questions

Can I run xlwings on a headless Linux server or in CI? No. xlwings drives the desktop Excel application over COM (Windows) or AppleScript (macOS), and Excel launches as a GUI process even when hidden. On headless Linux, Docker, or CI runners like GitHub Actions, use pandas and openpyxl instead.

Why did my Python list land in a row instead of a column? A plain 1D list is written horizontally by default. Add .options(transpose=True) before .value — for example sheet["A1"].options(transpose=True).value = ids — to lay it out vertically down a column.

How do I avoid leaving an orphaned EXCEL.EXE process behind? Manage the process yourself with xw.App and put book.close() then app.quit() in a finally block, so cleanup runs on both success and failure. An exception that escapes before app.quit() leaves a zombie process holding a file lock.

Why does my macro call do nothing? A plain .xlsx cannot store VBA, so confirm the file is .xlsm, .xlsb, or .xlam and that the macro name and workbook qualifier match exactly. Get the callable with book.macro("Name")() or app.macro().

What's the difference between xw.Book and xw.App?xw.Book is convenient for interactive work and reuses an already-open Excel instance. For scheduled jobs you want full control over the process, so start your own xw.App(visible=False, add_book=False) and manage its lifecycle.

Key takeaways

• xlwings drives live Excel, not files on disk. It talks to a running Excel over COM (Windows) or AppleScript (macOS), so formulas recalculate, charts redraw, and VBA runs — at the cost of needing a real Excel install and an interactive desktop session.
• It will not run headless. Skip xlwings on Linux servers, plain Docker, and CI runners; reach for pandas and openpyxl there instead.
• Learn four objects: App, Book, Sheet, Range. Read and write cells through Range.value, and prefer assigning a list of lists so a whole block transfers in one round trip.
• Mind orientation. A 1D list fills a row by default — add .options(transpose=True) to write down a column.
• Own the process for unattended jobs. Start your own xw.App(visible=False, add_book=False), use absolute paths, and put book.close() then app.quit() in a finally block so no zombie Excel is left holding a lock.
• Split the work. Do extraction and transformation headless, then reserve xlwings for the final formatting and macro-driven delivery step on a workstation.

Related

• Up to the overview: Getting Started with Python Excel Automation
• Go deeper: xlwings run macro from Python example
• Stage the data first: Writing DataFrames to Excel with Pandas
• Read source data headless: Reading Excel Files with Pandas
• Edit files without Excel present: Using openpyxl for Excel File Manipulation
• Handle workbooks with many tabs: Working with Multiple Excel Sheets in Python