xlwings — controlling live Excel from Python

Needs: Excel installed on Windows or Mac, and pip install xlwings. It won't work on servers or GitHub Actions — use pandas + openpyxl there.

1. Connect to a workbook

import xlwings as xw

wb = xw.Book("sales.xlsx")      # opens the file in Excel (or connects if already open)
# wb = xw.books.active          # the workbook currently in front
sht = wb.sheets["Data"]

You'll see Excel open and changes appear live as the script runs.

2. Read and write cells

print(sht["A1"].value)                   # single value
sht["G1"].value = "Checked by Python"
values = sht["A2:E5"].value              # list of lists
sht["A10"].value = [["Product", "Qty"], ["Laptop", 12]]   # writes a 2×2 block

3. Whole tables ↔ pandas

import pandas as pd

df = sht["A1"].options(pd.DataFrame, index=False, expand="table").value
summary = df.groupby("Region", as_index=False)["Amount"].sum()

sht["H1"].options(index=False).value = summary

expand="table" grows from A1 to the full block of data, like Ctrl + A. This is the main reason to use xlwings: grab the user's current data, do pandas magic, put the result back.

4. Formatting and helpers

sht["H1:I1"].font.bold = True
sht["H1:I1"].color = (31, 78, 120)        # fill colour as RGB
sht["H1:I1"].font.color = (255, 255, 255)
sht.autofit()                             # real Excel AutoFit
last_row = sht["A1"].end("down").row      # like Ctrl + ↓
wb.save()

5. Run a VBA macro from Python

format_header = wb.macro("FormatHeader")   # a macro in this .xlsm
format_header()

Useful when a team already has macros — Python handles data, VBA handles what it already does well.

6. Run Excel in the background

with xw.App(visible=False) as app:
    wb = app.books.open("sales.xlsx")
    wb.sheets["Data"]["G1"].value = "Updated"
    wb.save()
    wb.close()

Excel starts hidden, does the job and quits — handy for scheduled jobs on your own PC (the PC must be logged in).

7. Run Python from a button in Excel

1. Install the add-in once: xlwings addin install
2. Create a project: xlwings quickstart salestool — this makes salestool.xlsm and salestool.py in the same folder.
3. In salestool.py:

import xlwings as xw
import pandas as pd

def main():
    wb = xw.Book.caller()                       # the workbook that clicked the button
    sht = wb.sheets["Data"]
    df = sht["A1"].options(pd.DataFrame, index=False, expand="table").value
    out = df.groupby("Region", as_index=False)["Amount"].sum()
    wb.sheets["Summary"]["A1"].options(index=False).value = out

4. In salestool.xlsm, the VBA module already contains a call like RunPython "import salestool; salestool.main()". Assign that macro to a button.

Now users click a button in Excel and Python does the work. (On Windows, xlwings can also turn Python functions into worksheet formulas with @xw.func.)

8. Licensing note

xlwings itself is open source and free. Some extra features are part of a paid "xlwings PRO" — everything in this lesson works with the free version.

Common mistakes

Using xlwings in a scheduled job on a server (no Excel there). Forgetting expand="table" and getting a single value. Leaving hidden Excel instances running when a script crashes — use the with xw.App() form.