Python Excel AutomationPractical guides for developers
How-to
Automating Reporting WorkflowsDeep dive

Test win32com and xlwings Code Without Excel

Unit-test Python code that drives Excel through win32com or xlwings on machines without Excel — Linux CI runners, Macs, containers: keep the Excel calls in a thin layer, pass the application in, replace it with small fakes that record what was done, check that workbooks are closed and Excel quit even after errors, stub the win32com import for older code, and keep real-Excel tests for Windows only.
COM & pywin32xlwingsLogging, testing & packaging

Code that automates Excel itself — refreshing connections, running macros, printing, reading values Excel has calculated — is the hardest Python Excel code to test. It needs Windows, an installed and licensed Excel, and a desktop session; CI runners, colleagues' Macs and containers have none of these. So it often goes untested, and its bugs show up as a hung EXCEL.EXE on a server, a workbook saved over by mistake, or a macro run against the wrong file. Most of the logic in such code, though, is not Excel's: which files to open, which sheet and range to read, what to do with the values, when to save and how to clean up when something fails. That logic can be tested anywhere, if the Excel calls are kept at the edge and replaced in tests with small fakes. This page restructures a win32com refresh job and an xlwings report so they can be tested on Linux, writes the fakes, tests the clean-up path, and keeps real-Excel tests for the machines that have it. It belongs to Testing and Packaging Excel Automation Scripts.

Prerequisites

Bash
pip install pytest pandas
# pywin32 and xlwings are only needed where Excel is installed

The tests on this page were run on Linux, where neither Excel nor pywin32 exists — exactly the situation of a CI runner. xlwings installs on Linux but cannot start Excel there, so the same approach applies to it.

Step 1 — Keep the Excel calls in one thin function

The job opens a workbook, refreshes its data connections, waits for the refresh, reads a total from a named cell, saves and closes — and must quit Excel whatever happens. Write it so that the Excel application is passed in rather than created inside:

Python
import logging

log = logging.getLogger(__name__)

def refresh_and_read(app, path, total_name="GrandTotal"):
    """Refresh all connections in the workbook at path and return the value of a named cell."""
    wb = app.Workbooks.Open(str(path), UpdateLinks=0, ReadOnly=False)
    try:
        wb.RefreshAll()
        app.CalculateUntilAsyncQueriesDone()
        total = wb.Names(total_name).RefersToRange.Value
        if total is None:
            raise ValueError(f"{total_name} is empty after refresh")
        wb.Save()
        return float(total)
    finally:
        wb.Close(SaveChanges=False)

def run(paths, make_app):
    app = make_app()
    app.DisplayAlerts = False
    try:
        return {str(p): refresh_and_read(app, p) for p in paths}
    finally:
        app.Quit()

if __name__ == "__main__":                                   # the only place that knows about win32com
    import win32com.client
    print(run(["C:/reports/sales.xlsx"], lambda: win32com.client.DispatchEx("Excel.Application")))

run receives a make_app function instead of calling win32com.client.DispatchEx itself; only the __main__ block, which tests never execute, imports win32com. The try … finally blocks are the important part: whatever goes wrong — a missing name, a failed refresh — every opened workbook is closed without saving over it, and Excel is told to quit. Those are exactly the behaviours worth testing, because getting them wrong leaves invisible Excel processes running on a server until someone kills them. The COM calls themselves are covered in Refresh Excel Data Connections and Recalculate with win32com, and what happens when clean-up is skipped in Close Excel Cleanly and Avoid Orphan COM Processes.

Where the Excel calls live The entry point is the only place that creates the real Excel application. The job logic receives an application object and is tested with fakes. Real-Excel integration tests run only on Windows machines with Excel installed. Entry point (__main__) creates Excel via win32com Job logic (run, refresh_and_read) receives app, tested with fakes Fakes record calls, simulate failures Integration tests Windows with Excel only

Step 2 — Fakes that record what happened

A fake is a small class with the same attribute names as the COM objects the code uses, which records calls instead of performing them. It needs only the parts the code touches:

Python
class FakeRange:
    def __init__(self, value):
        self.Value = value

class FakeName:
    def __init__(self, value):
        self.RefersToRange = FakeRange(value)

class FakeWorkbook:
    def __init__(self, app, path, names, fail_refresh=False):
        self.app, self.path, self._names, self.fail_refresh = app, path, names, fail_refresh
        self.saved = self.closed = False
        self.close_args = None

    def RefreshAll(self):
        self.app.calls.append(("RefreshAll", self.path))
        if self.fail_refresh:
            raise RuntimeError("(-2147352567, 'Exception occurred.', 'The connection failed')")

    def Names(self, name):
        if name not in self._names:
            raise KeyError(name)
        return FakeName(self._names[name])

    def Save(self):
        self.saved = True

    def Close(self, SaveChanges):
        self.closed, self.close_args = True, {"SaveChanges": SaveChanges}

class FakeWorkbooks:
    def __init__(self, app):
        self.app = app

    def Open(self, path, **kwargs):
        wb = FakeWorkbook(self.app, path, **self.app.books[path])
        self.app.opened.append(wb)
        return wb

class FakeExcel:
    def __init__(self, books):
        self.books, self.opened, self.calls = books, [], []
        self.Workbooks = FakeWorkbooks(self)
        self.DisplayAlerts = True
        self.quit_called = False

    def CalculateUntilAsyncQueriesDone(self):
        self.calls.append(("CalculateUntilAsyncQueriesDone",))

    def Quit(self):
        self.quit_called = True

Plain classes are clearer than unittest.mock.MagicMock here. A MagicMock accepts any attribute and call, so a typo such as wb.RefreshALL() passes silently; a fake raises AttributeError, just as the real object would fail. The fake's books are configured per test: which named values exist and whether refreshing should fail with a COM-style error.

Step 3 — Test the happy path and the clean-up path

Python
import pytest
from jobs.refresh import run
from tests.fakes import FakeExcel

def test_refreshes_saves_closes_and_quits():
    excel = FakeExcel({"a.xlsx": {"names": {"GrandTotal": 1250.5}}, "b.xlsx": {"names": {"GrandTotal": 80}}})
    result = run(["a.xlsx", "b.xlsx"], make_app=lambda: excel)
    assert result == {"a.xlsx": 1250.5, "b.xlsx": 80.0}
    assert [c[0] for c in excel.calls] == ["RefreshAll", "CalculateUntilAsyncQueriesDone"] * 2
    assert all(wb.saved and wb.closed for wb in excel.opened)
    assert excel.DisplayAlerts is False and excel.quit_called

def test_failed_refresh_closes_without_saving_and_quits():
    excel = FakeExcel({"a.xlsx": {"names": {"GrandTotal": 1}, "fail_refresh": True}})
    with pytest.raises(RuntimeError, match="connection failed"):
        run(["a.xlsx"], make_app=lambda: excel)
    wb = excel.opened[0]
    assert wb.closed and wb.close_args == {"SaveChanges": False} and not wb.saved
    assert excel.quit_called

def test_empty_total_is_an_error_not_a_zero():
    excel = FakeExcel({"a.xlsx": {"names": {"GrandTotal": None}}})
    with pytest.raises(ValueError, match="GrandTotal is empty"):
        run(["a.xlsx"], make_app=lambda: excel)
    assert excel.opened[0].closed and excel.quit_called
Python
import subprocess, sys

def pytest_run(*args):
    out = subprocess.run([sys.executable, "-m", "pytest", "-q", "-p", "no:cacheprovider", *args], capture_output=True, text=True).stdout
    print("\n".join(l for l in out.splitlines() if "passed" in l or "failed" in l or "skipped" in l or l.startswith("FAILED")))

pytest_run("tests/test_refresh.py")

All three tests pass in well under a second, on a machine without Excel. The second test is the one that pays for the effort: a refresh failure — simulated with the kind of message a COM error carries — still closes the workbook without saving and still quits Excel. The third makes sure an empty named cell is reported as an error rather than quietly becoming a total of zero. These are the behaviours that, untested, produce hung processes and wrong numbers in production.

A recording fake or a MagicMock A small fake class defines only the attributes the code uses, so typos fail like they would against Excel, and it records calls for assertions. A MagicMock accepts any attribute or call, so misspelled method names pass silently. Recording fake only real attribute names typos raise AttributeError simulates COM errors records calls and saves MagicMock accepts any attribute wb.RefreshALL() passes quick to write assertions on call lists What the failed-refresh test checks The test passes a fake Excel to run. The job opens the workbook and calls RefreshAll, which the fake makes fail. The job then closes the workbook without saving and quits Excel before the error reaches the test, which asserts all three. Test run() FakeExcel 1. make_app=lambda: fake 2. Workbooks.Open(a.xlsx) 3. RefreshAll 4. COM-style error 5. Close(SaveChanges=False), Quit 6. error re-raised

Step 4 — xlwings code and older code that imports win32com

xlwings code is tested the same way. Pass the book in, and fake the parts of the xlwings API the code uses — here sheet.range(...).options(...).value:

Python
import pandas as pd

def write_summary(book, df):
    sheet = book.sheets["Summary"]
    sheet.range("A1").options(index=False).value = df.groupby("Region", as_index=False)["Amount"].sum()
    sheet.range("E1").value = "Total"
    sheet.range("F1").value = float(df["Amount"].sum())
Python
import pandas as pd
from jobs.xw_report import write_summary

class FakeXwRange:
    def __init__(self, sheet, address):
        self.sheet, self.address, self.opts = sheet, address, {}
    def options(self, **opts):
        self.opts = opts
        return self
    @property
    def value(self):
        return self.sheet.cells.get(self.address)
    @value.setter
    def value(self, v):
        self.sheet.cells[self.address] = (v, self.opts)

class FakeXwSheet:
    def __init__(self):
        self.cells = {}
    def range(self, address):
        return FakeXwRange(self, address)

class FakeBook:
    def __init__(self):
        self.sheets = {"Summary": FakeXwSheet()}

def test_summary_written_without_index():
    book = FakeBook()
    df = pd.DataFrame({"Region": ["N", "S", "N"], "Amount": [10.0, 5.0, 2.5]})
    write_summary(book, df)
    table, opts = book.sheets["Summary"].cells["A1"]
    assert opts == {"index": False}
    assert table.to_dict("list") == {"Region": ["N", "S"], "Amount": [12.5, 5.0]}
    assert book.sheets["Summary"].cells["F1"][0] == 17.5

For older code that does import win32com.client at the top of the module, inject a stand-in module before importing it, in a fixture or conftest.py: sys.modules["win32com"] = types.ModuleType("win32com") plus a win32com.client module whose Dispatch returns your fake. It works, but it is a sign the code would benefit from Step 1's restructuring. Tests that really need Excel — checking that a macro still exists, or that a refresh against a test database succeeds — go in a separate file guarded with pytest.importorskip("win32com.client") or a skipif(sys.platform != "win32") marker, and run on a Windows machine with Excel:

Python
import sys
import pytest

pytestmark = pytest.mark.skipif(sys.platform != "win32", reason="needs Windows with Excel installed")

def test_real_refresh(tmp_path):
    win32 = pytest.importorskip("win32com.client")
    from jobs.refresh import run
    result = run(["C:/ci/fixtures/sales.xlsx"], lambda: win32.DispatchEx("Excel.Application"))
    assert result["C:/ci/fixtures/sales.xlsx"] > 0
Python
pytest_run("tests")

Four tests pass and the real-Excel test is skipped on Linux (run with -rs to see the skip reason) — so the suite runs everywhere, and the Windows machine that has Excel runs the one extra test. Running Excel jobs on a server in the first place has its own pitfalls, covered in Run Excel COM Automation from Task Scheduler Without a Logged-In User.

Common pitfalls

  • Creating Excel inside the logic. Pass the application in so tests can substitute it.
  • MagicMock everywhere. It hides typos; small fakes fail like the real thing.
  • Only testing success. Test that failures still close workbooks and quit Excel.
  • Imports that need Windows. Keep win32com imports at the entry point.
  • Real-Excel tests that fail on CI. Skip them by platform and run them where Excel exists.

Conclusion

To test Excel-driving code without Excel, keep the COM or xlwings calls in thin functions that receive the application or book as an argument, and import win32com only at the entry point. Replace the real objects in tests with small recording fakes that define only what the code uses, simulate failures, and assert that workbooks are closed without saving and Excel is quit even when something goes wrong. Keep the few tests that need real Excel in a separate file skipped on other platforms.

Frequently asked questions

How do I unit test win32com code on Linux? Pass the Excel application into your functions instead of creating it with Dispatch, and in tests pass a fake object with the same methods and properties that records what was called.

Should I use MagicMock for COM objects? Small hand-written fakes are safer; MagicMock accepts any attribute, so misspelled COM method names pass silently.

How do I test that Excel is closed after an error? Make the fake raise an error during a step such as RefreshAll, then assert that the workbook's Close was called with SaveChanges False and that Quit was called.

How do I run real Excel tests only on Windows? Put them in a separate module marked with skipif for non-Windows platforms, and use pytest.importorskip for win32com.client.

More on com & pywin32

More on com & pywin32 →