Python Excel AutomationPractical guides for developers
Start learning
Guide
Getting Started With Python Excel AutomationDeep dive

Read and Write a Live Excel Workbook with xlwings

Drive the open application rather than the file: connect to a running workbook, read and write ranges, force a recalculation, read what the formulas produced, and clean up so no invisible Excel is left behind.

openpyxl and pandas read and write the .xlsx file. xlwings does something different: it drives the Excel application through its automation interface, so the workbook on screen responds to your Python code. That is the difference between "produce a file" and "operate the spreadsheet" — and it is why xlwings can do two things the file-level libraries cannot: recalculate formulas and run macros.

The cost is that Excel must actually be installed and running, which rules out the headless server where most reports are generated. This guide covers the live-workbook workflow — connect, read, write, recalculate, clean up — as part of Automating Excel with xlwings Basics.

Talking to the file versus talking to the application openpyxl and pandas read and write the .xlsx file on disk, so they never see a formula's result unless Excel cached one. xlwings sends commands to a running Excel application, which owns the calculation engine, the macros and any add-ins, and which writes the file itself when told to save. Python your script openpyxl / pandas reads and writes the file xlwings commands the application report.xlsx bytes on disk Excel calc engine · macros Only the lower path can evaluate a formula — which is exactly why it needs Excel installed, and exactly why it is the wrong choice for an unattended job on a server.

Prerequisites

Bash
pip install xlwings pandas

You also need Microsoft Excel installed — on Windows or macOS. xlwings drives the real application, so there is no Linux path and no headless mode. On macOS you will be asked to grant automation permission the first time; on Windows nothing extra is needed.

Step 1: Connect to a workbook

There are three ways in, and choosing the right one avoids most of the confusion:

Python
import xlwings as xw

# 1. Attach to a workbook that is already open by name
book = xw.Book("budget.xlsx")

# 2. Open a file in a NEW, invisible Excel instance — the automation case
app = xw.App(visible=False, add_book=False)
book = app.books.open("budget.xlsx")

# 3. The workbook this script was called from (xlwings add-in / RunPython)
# book = xw.Book.caller()

xw.Book("budget.xlsx") attaches to an open workbook of that name if there is one and opens it otherwise, which is convenient interactively and unpredictable in a script — if a colleague has a different budget.xlsx open, that is the one you get. For anything automated, create your own App and open the file explicitly.

add_book=False stops Excel creating an empty Book1 alongside yours, which is the small annoyance everyone hits first.

Step 2: Read and write ranges

A range's .value is the whole interface, and it converts in both directions:

Python
sheet = book.sheets["Budget"]

print(sheet["B2"].value)                  # a single cell → a scalar
print(sheet["A1:C5"].value)               # a block → a list of row lists
print(sheet["A1"].expand().value)         # the whole contiguous block from A1

sheet["B2"].value = 1250.0                # write one cell
sheet["A10"].value = [["North", 120], ["South", 180]]   # write a block

sheet["D1"].value = "=SUM(B2:B9)"         # write a formula
sheet["B2:B9"].number_format = '#,##0.00'
sheet.autofit("c")                        # widths from the live application

expand() is the one to know: it grows the range to the edges of the contiguous block, which is how you read "whatever the user typed" without hard-coding an end row. And autofit is genuinely Excel's autofit — the thing no file-level library can do, because the widths depend on font metrics only the application knows.

Why block reads and writes are so much faster than cell loops Every range access crosses the boundary between Python and the Excel process, and the crossing costs far more than the data. A thousand single-cell writes make a thousand crossings; assigning a list of lists to one range makes one, carrying the same values. a loop over cells Python Excel 1,000 values → 1,000 crossings tens of seconds, most of it overhead one block assignment Python Excel one block assignment 1,000 values → 1 crossing a fraction of a second, same result

DataFrames go both ways with an options call:

Python
import pandas as pd

df = sheet["A1"].expand().options(pd.DataFrame, index=False).value
df["variance"] = df["actual"] - df["budget"]
sheet["G1"].options(index=False).value = df

Step 3: Recalculate, then read the result

This is the capability that justifies xlwings. openpyxl reads formula text, or a cached value that may not exist; xlwings asks the running application, so the number is the number Excel just computed:

Python
book.app.calculate()                       # recalculate everything

total = sheet["D1"].value                  # the computed number, not "=SUM(B2:B9)"
print(f"total: {total:,.2f}")

sheet["B3"].value = 999.0
book.app.calculate()
print("after edit:", sheet["D1"].value)

If calculation has been set to manual — common in large models — nothing recalculates until you ask. Set it explicitly rather than assuming:

Python
book.app.calculation = "manual"            # batch a lot of writes...
for r, value in enumerate(new_values, start=2):
    sheet[f"B{r}"].value = value
book.app.calculation = "automatic"         # ...then recalculate once
book.app.calculate()

That pattern is also the main performance lever, because with automatic calculation on, every write triggers a recalculation of everything that depends on it.

Step 4: Save and quit without leaving Excel behind

An App that is never quit leaves an invisible EXCEL.EXE running, holding a lock on the file. Do the cleanup in finally, or let the context manager do it:

Python
import xlwings as xw


def update_budget(path, updates):
    with xw.App(visible=False, add_book=False) as app:
        app.display_alerts = False            # no "overwrite?" dialog
        app.screen_updating = False           # faster, and nothing flickers
        book = app.books.open(path)
        try:
            sheet = book.sheets["Budget"]
            for cell, value in updates.items():
                sheet[cell].value = value
            app.calculate()
            result = sheet["D1"].value
            book.save()
        finally:
            book.close()
    return result

display_alerts = False matters more than it looks: an automation run that trips a modal dialog — "a file already exists", "this workbook contains links" — blocks forever with no visible window to click. Turning alerts off makes Excel take the default action instead of waiting for a human who cannot see the prompt.

What happens when the Excel instance is not closed If an exception skips the cleanup, the hidden Excel process keeps running and keeps its lock on the workbook. Each subsequent run starts another one, memory climbs, and the next job that tries to write the file fails with a permission error. Wrapping the work in a context manager closes the application on every path. no cleanup on the error path run 1 run 2 run 3 three hidden EXCEL.EXE processes each holding a lock on the workbook, each keeping its memory the next write fails: permission denied with xw.App(...) as app: run 1 ✓ run 2 ✗ run 3 ✓ no processes left, even after the failure the exception still propagates — it is the cleanup that is guaranteed the file is free for the next run

Step 5: Know when to stop using it

xlwings is the right tool for a narrow set of jobs: recalculating a model whose formulas you cannot reimplement, running a macro, using an add-in's functions, exporting with Excel's own renderer, or updating a workbook a colleague has open in front of them.

It is the wrong tool for generating a report on a schedule. Microsoft does not support Office automation from a service or unattended session, and in practice it fails in the ways you would expect: a modal dialog with nobody to dismiss it, a licence prompt after an update, an orphaned process holding the output file. A scheduled job should build the workbook with openpyxl or xlsxwriter and, if a PDF is needed, use LibreOffice headless — the routes compared in Exporting Excel Reports to PDF.

Common pitfalls and gotchas

SymptomCauseFix
Invisible Excel processes pile upThe App was never quitUse with xw.App(...), or try/finally
Script hangs with no windowA modal dialog is waitingapp.display_alerts = False
Wrong workbook was modifiedxw.Book(name) attached to someone else'sCreate an App and open by full path
An empty Book1 appearsDefault new workbook on App()add_book=False
Formula result is staleCalculation set to manualapp.calculate() after the writes
Very slow with many writesRecalculating on every cellBatch writes, calculation manual, calculate once
Fails on the serverOffice automation is unsupported unattendedUse openpyxl for scheduled jobs
Permission denied on saveAnother instance still has the fileClose orphaned processes; check Task Manager

Performance and scale notes

Every property access crosses a process boundary, so the cost is per call, not per cell. Writing a thousand cells one at a time is slow; writing them as one block assigned to sheet["A1"].value is fast. The same is true for reading — expand().value once beats a loop over cells by an order of magnitude.

Beyond that, the two settings that matter are screen_updating = False and manual calculation while a batch runs. With both applied, a bulk update that took minutes typically drops to seconds. If it is still slow after that, the workbook's own formulas are the bottleneck, and no amount of Python tuning will help.

Conclusion

xlwings is the tool for when the spreadsheet has to be operated rather than produced: connect to the application, read and write ranges as blocks, force a calculation and read the numbers Excel actually computed. Open your own App rather than attaching by name, turn off alerts so no invisible dialog can block the run, batch the writes with calculation set to manual, and quit through a context manager so nothing is left holding the file. When the job has none of those needs, openpyxl is simpler and runs where Excel does not.

Frequently asked questions

When is xlwings the right tool rather than openpyxl? When you need Excel itself — to recalculate formulas, run a macro, use an add-in, or drive a workbook someone has open. For generating or reading files unattended, openpyxl and pandas are faster and need no Excel installation.

Does this work on a server? Not reliably, and Microsoft does not support Office automation in a service context. xlwings needs a real Excel installation and an interactive session; a scheduled job on a server should use openpyxl instead.

How do I read the value a formula produced? Read the range after Excel has calculated. Unlike openpyxl, xlwings asks the live application, so range.value returns the computed number rather than the formula text or a stale cached value.

Why is an invisible Excel process left running? The App was never quit, usually because an exception skipped the cleanup. Wrap the work in try/finally, or use xw.App as a context manager, and check Task Manager while developing.

Up to the parent guide:

Related guides: