Work with Named Ranges and Tables in xlwings

Scripts that drive a live workbook through cell addresses are fragile. sheet["B7"] is the tax rate today; next month someone inserts a row above it and the script reads a label instead. Excel's own answer is to give things names: a named range for a single input or result — TaxRate, ForecastTotal — and a table for a block of records — Sales — which grows as rows are added and keeps its name wherever it moves. xlwings exposes both, so a script can read TaxRate and the Sales table without knowing where they are. This page reads and writes named ranges, manages names at workbook and sheet scope, reads a table into pandas, writes a DataFrame back with update() so the table resizes to fit, switches on a totals row, and creates a new table from Python. It belongs to Automating Excel with xlwings Basics.

Prerequisites

pip install xlwings pandas openpyxl

xlwings needs desktop Excel on Windows or macOS; the xlwings code below is shown as it runs against Excel. The example workbook — an Inputs sheet with a named tax rate and a Data sheet with a Sales table — is created with openpyxl in the first step, which runs anywhere, so you can reproduce the setup exactly.

Step 1 — A workbook with names and a table

from openpyxl import Workbook
from openpyxl.workbook.defined_name import DefinedName
from openpyxl.worksheet.table import Table, TableStyleInfo

wb = Workbook()
inputs = wb.active
inputs.title = "Inputs"
inputs["A2"], inputs["B2"] = "Tax rate", 0.2
inputs["A3"], inputs["B3"] = "Net total", None
wb.defined_names["TaxRate"] = DefinedName("TaxRate", attr_text="Inputs!$B$2")
wb.defined_names["NetTotal"] = DefinedName("NetTotal", attr_text="Inputs!$B$3")

data = wb.create_sheet("Data")
data.append(["Region", "Product", "Net"])
for row in [("North", "Bolts", 1250.0), ("South", "Nuts", 380.5), ("East", "Washers", 92.0)]:
    data.append(row)
sales = Table(displayName="Sales", ref="A1:C4")
sales.tableStyleInfo = TableStyleInfo(name="TableStyleMedium2", showRowStripes=True)
data.add_table(sales)
wb.save("names_and_tables.xlsx")
print(list(wb.defined_names), "|", list(data.tables))

Two workbook-level names point at single cells on Inputs, and the Data sheet holds a three-row table named Sales. These are exactly the objects people create in Excel with Formulas › Define Name and Insert › Table; creating them in Python is covered in Create a Named Range in Excel with openpyxl.

Step 2 — Read and write named ranges

With the workbook open in Excel, a name can be used anywhere xlwings accepts an address, or looked up through book.names:

import xlwings as xw

book = xw.Book("names_and_tables.xlsx")
rate = book.sheets["Inputs"]["TaxRate"].value                 # 0.2 — the name works as an address
name = book.names["TaxRate"]
name.refers_to                                               # '=Inputs!$B$2'
name.refers_to_range.value                                   # 0.2 — the same cell through the Name object
[n.name for n in book.names]                                 # ['NetTotal', 'TaxRate']

book.names["NetTotal"].refers_to_range.value = 1722.5         # write a result to the named cell
book.names.add("Region", "=Inputs!$B$5")                      # a new workbook-level name
book.sheets["Data"].names.add("FirstRegion", "=Data!$A$2")    # a name scoped to one sheet
book.names["Region"].delete()

sheet["TaxRate"] resolves the name to its range, so reading and writing look the same as with an address. The Name object adds what the address cannot: refers_to gives the definition as Excel stores it, with a leading =, and refers_to_range the Range it points at. book.names.add() takes the definition in the same =Sheet!$A$1 form. Names created through sheet.names are sheet-scoped: they exist only on that sheet, so two sheets can each have a FirstRegion without clashing — useful in workbooks with one sheet per region or month. Writing results to named output cells, rather than to addresses, means a template designer can move the cells without breaking the script.

Step 3 — Read a table into pandas

sheet.tables holds the tables on a sheet, and each table offers its parts as ranges:

import pandas as pd

table = book.sheets["Data"].tables["Sales"]
table.range.address                       # '$A$1:$C$4' — header and data
table.data_body_range.address             # '$A$2:$C$4' — data only
table.header_row_range.value              # ['Region', 'Product', 'Net']
df = table.range.options(pd.DataFrame, index=False).value
df["Gross"] = df["Net"] * (1 + book.names["TaxRate"].refers_to_range.value)

table.range covers the header and the data rows, so reading it with the DataFrame converter and index=False gives a DataFrame with the table's column names and every row — however many there are today. Nothing in the script mentions A1 or C4, so rows added in Excel, or the table moving to another position, need no code change. Without index=False, the first column would become the DataFrame index, as explained in Control How xlwings Converts DataFrames and Ranges.

Step 4 — Write back with update(), add totals, create tables

Table.update() writes a DataFrame into the table and resizes the table to fit it:

df = df.sort_values("Net", ascending=False)
table.update(df, index=False)              # table now has four columns and the same rows
table.show_totals = True                   # Excel adds a totals row; set each column's function in Excel or via table.api
book.names["NetTotal"].refers_to_range.value = float(df["Net"].sum())

summary = df.groupby("Region", as_index=False)["Gross"].sum()
out = book.sheets.add("Summary")
out["A1"].options(index=False).value = summary
new = out.tables.add(source=out["A1"].expand(), name="RegionSummary", table_style_name="TableStyleLight9")
new.name                                   # 'RegionSummary'
book.save()

After update(), the Sales table covers the new Gross column as well, rows are in the new order, and anything that refers to Sales[Net] keeps working because the table, not an address, is the reference. show_totals = True adds the totals row below the data. A new table is created with tables.add() from a range — here the summary written at A1, expanded to its full size — with a name and a style; from then on, other scripts and formulas can refer to it as RegionSummary. Tables in files that are not open in Excel are handled with openpyxl instead, as in Read an Excel Table by Name into pandas.

Naming conventions that help

Names are only as useful as they are predictable. A few conventions make workbooks easier to script and to read. Give inputs and outputs names that say what they hold — TaxRate, FxEurUsd, ForecastTotal — rather than positions such as Cell_B7. Keep names free of spaces (Excel does not allow them) and avoid names that look like cell addresses, such as FX1, which Excel rejects because it is a column-and-row reference. Name tables after the records they hold, in the plural — Sales, Customers, Rates — so formulas such as =SUM(Sales[Net]) read like sentences. And keep a short list of the names and tables a script relies on at the top of the script, which is exactly what the check below uses.

Check the names a script depends on

A script that works by name has a new failure mode: someone deletes or renames TaxRate, and the script fails half-way through with an unhelpful error. Check the names and tables the script needs before it changes anything. The check can run against the file with openpyxl — no Excel needed — so it also works in tests and on servers:

from openpyxl import load_workbook

REQUIRED_NAMES = {"TaxRate", "NetTotal"}
REQUIRED_TABLES = {"Data": {"Sales"}}

def missing_objects(path):
    wb = load_workbook(path)
    problems = [f"name {n} is missing" for n in sorted(REQUIRED_NAMES - set(wb.defined_names))]
    for sheet, tables in REQUIRED_TABLES.items():
        if sheet not in wb.sheetnames:
            problems.append(f"sheet {sheet} is missing")
            continue
        problems += [f"table {t} is missing on {sheet}" for t in sorted(tables - set(wb[sheet].tables))]
    return problems

print("before:", missing_objects("names_and_tables.xlsx") or "all present")
wb = load_workbook("names_and_tables.xlsx")
del wb.defined_names["TaxRate"]
wb.save("broken.xlsx")
print("after deleting TaxRate:", missing_objects("broken.xlsx"))

The original workbook has everything the script needs; the copy with TaxRate deleted is caught with a message that names the problem. In an xlwings script, run the same comparison against [n.name for n in book.names] and the sheet's tables before the first write, and stop with that message rather than half-updating the workbook. Templates that people edit benefit most: the check turns "the macro broke" into "someone deleted the TaxRate name", which they can fix themselves.

Common pitfalls

• Hard-coded addresses. Use names and tables so layout changes do not break scripts.
• Forgetting the = in names.add(). Definitions are written like formulas: =Sheet!$B$2.
• Writing a DataFrame over a table's range. Use table.update() so the table resizes.
• Losing the first column. Read tables with options(pd.DataFrame, index=False).
• Clashing names across sheets. Use sheet-scoped names for per-sheet values.

Conclusion

In xlwings, use names wherever you would use addresses: sheet["TaxRate"] or book.names["TaxRate"].refers_to_range for single inputs and outputs, book.names.add() and sheet.names.add() to create them, and sheet.tables["Sales"] for blocks of records. Read a table with table.range.options(pd.DataFrame, index=False), write it back with table.update(df, index=False) so it resizes, and create new tables with tables.add(). Scripts written this way keep working when people rearrange the workbook.

Frequently asked questions

How do I read a named range with xlwings? Use the name as an address, sheet"TaxRate".value, or look it up through book.names and read the value of its refers_to_range.

How do I create a named range with xlwings? Call book.names.add("Name", "=Sheet!$B$2") for a workbook-level name, or sheet.names.add for a name scoped to one sheet.

How do I read an Excel table into pandas with xlwings? Get the table with sheet.tables"Name" and read table.range.options(pd.DataFrame, index=False).value.

How do I write a DataFrame back into an Excel table with xlwings? Call table.update(df, index=False); it writes the data and resizes the table to the DataFrame.

Related

• Up one level: Automating Excel with xlwings Basics — live Excel from Python.
• Read a Named Range into pandas — named ranges from closed files.
• Read and Write a Live Excel Workbook with xlwings — the basics.
• Create an Excel Table with Python — tables with openpyxl.