Control How xlwings Converts DataFrames and Ranges

xlwings moves values between a live Excel workbook and Python, and the shape and type of what comes back depends on the range and on converter options — the arguments to range.options(...). Most surprises in xlwings code are conversion surprises: a single cell comes back as a float rather than a list, so a loop over "the rows" iterates over nothing; a one-row range comes back flat; whole numbers come back as 12.0; dates come back as datetimes with midnight times; and a DataFrame read without care loses its first column into the index. This page goes through the options one at a time — dimensions, numbers, dates, empty cells, orientation and expansion — then the DataFrame and dictionary converters, and finishes with a custom converter that applies a team's own conventions every time. The converter logic is exercised directly in Python, so its behaviour is shown without needing Excel. It belongs to Automating Excel with xlwings Basics.

Prerequisites

pip install xlwings pandas

Reading and writing ranges needs desktop Excel on Windows or macOS. The examples that touch a workbook assume a sheet with a small table starting in A1 — Region, Date, Qty — and are shown as they would run against Excel. The converter classes themselves are plain Python, so the blocks that call them directly run anywhere xlwings is installed, and their outputs below come from those runs.

Step 1 — Shape: scalar, list or list of lists

By default xlwings returns the simplest shape that fits the range:

import xlwings as xw
import datetime as dt

sheet = xw.books.active.sheets["Data"]
sheet["A2"].value                          # 'North'                      a single cell -> scalar
sheet["A2:C2"].value                       # ['North', datetime(...), 12.0] one row -> flat list
sheet["A2:A4"].value                       # ['North', 'South', 'East']    one column -> flat list
sheet["A1:C4"].value                       # [[...], [...], ...]           rectangle -> list of lists
sheet["A2:A4"].options(ndim=2).value       # [['North'], ['South'], ['East']]
sheet["A2"].options(ndim=1).value          # ['North']

A single row and a single column both come back as flat lists, which means code cannot tell them apart and a one-row table looks the same as a one-column list. ndim=2 always returns a list of rows, even for a single cell, and ndim=1 always returns a flat list. Any code that processes "rows" of a range whose size depends on the data — a table that sometimes has one row — should read with ndim=2, or it will break on the day there is only one row.

Step 2 — Numbers, dates, empty cells and orientation

Excel stores every number as a float and every date as a date-time. The numbers and dates options convert them on the way in; empty sets what blank cells become; transpose swaps rows and columns; expand sizes the range from the data:

sheet["C2:C4"].options(numbers=int).value                     # [12, 7, 30] instead of [12.0, 7.0, 30.0]
sheet["B2:B4"].options(dates=dt.date).value                   # [date(2026, 9, 1), ...] without times
sheet["A1:C4"].options(empty="").value                        # blanks become '' instead of None
sheet["A1:C4"].options(transpose=True).value                  # columns as rows
sheet["A1"].options(expand="table").value                     # the whole contiguous table from A1
sheet["A1"].options(transpose=True).value = [1, 2, 3]         # writes down a column instead of across

numbers=int converts every number in the range, so use it only where all values are whole — a quantity column, not a price column, because 2.5 would become 2. dates=dt.date drops the time part, which avoids 2026-09-01 00:00:00 appearing in output. empty is useful when the result goes into a text-based format where None would print as the word "None". expand="table" grows the range down and right from the anchor cell until it meets empty rows and columns, so the code does not need to know the table's size — but a blank row inside the data stops the expansion there. On writing, a flat list goes across a row; transpose=True sends it down a column.

Step 3 — DataFrames: header and index

The pd.DataFrame converter turns a list of rows into a DataFrame and back. What it does with the first row and the first column is controlled by header and index, and the default for index catches most people out. Here is the converter applied to the values xlwings reads from the A1:C3 block:

import datetime as dt
import pandas as pd
from xlwings.conversion import PandasDataFrameConverter, DictConverter
from xlwings.conversion.framework import Options

block = [["Region", "Date", "Qty"], ["North", dt.datetime(2026, 9, 1), 12.0], ["South", dt.datetime(2026, 9, 2), None]]
for opts in [{}, {"index": False}, {"index": False, "header": 2}]:
    df = PandasDataFrameConverter.read_value(block, Options(opts))
    print(f"{str(opts):<28} index={df.index.tolist()}  columns={list(df.columns)}")

summary = pd.DataFrame({"Region": ["North", "South"], "Qty": [12, 7]})
print("write, default:    ", PandasDataFrameConverter.write_value(summary, Options({})))
print("write, index=False:", PandasDataFrameConverter.write_value(summary, Options({"index": False})))

With no options, the first row becomes the column names and the first column becomes the index — Region disappears from the columns into the index. That default suits sheets written by xlwings itself with an index, and almost nothing else. index=False keeps it as an ordinary column, which is nearly always what a table read from a sheet needs. header=2 treats two rows as a two-level header, producing tuples as column names. Writing is symmetrical: by default the index is written as an extra first column — with an empty header cell and the numbers 0 and 1 — and index=False leaves it out. In code these are sheet["A1"].options(pd.DataFrame, expand="table", index=False).value to read and sheet["E1"].options(index=False).value = summary to write.

Step 4 — Dictionaries and a custom converter

A two-column range of names and values — a settings block — converts straight to a dictionary with options(dict). For conventions you apply every time, write a converter class and register it under a name:

import xlwings as xw

print(DictConverter.read_value([["rate", 0.2], ["fx", 1.17], ["region", "North"]], Options({})))

class CleanTable(xw.conversion.Converter):
    """Header row to snake_case, whole numbers as int, blank rows dropped; writes without the index."""

    @staticmethod
    def read_value(value, options):
        header, *rows = value
        cols = [str(h).strip().lower().replace(" ", "_") for h in header]
        rows = [r for r in rows if any(v not in (None, "") for v in r)]
        df = pd.DataFrame(rows, columns=cols)
        for c in df.columns:
            if pd.api.types.is_float_dtype(df[c]) and df[c].dropna().apply(float.is_integer).all():
                df[c] = df[c].astype("Int64")
        return df

    @staticmethod
    def write_value(df, options):
        return [list(df.columns)] + df.astype(object).where(df.notna(), None).values.tolist()

CleanTable.register("clean")
raw = [["Order ID", "Qty", "Unit Price"], ["SO-1", 12.0, 2.5], [None, None, None], ["SO-2", 7.0, None]]
df = CleanTable.read_value(raw, Options({}))
print(df.dtypes.to_dict())
print(CleanTable.write_value(df, Options({})))

The settings block becomes {'rate': 0.2, 'fx': 1.17, 'region': 'North'}, ready to look values up by name. The custom converter tidies headers into order_id, qty and unit_price, drops the blank row, and turns the Qty column — all whole numbers — into a nullable integer column while leaving Unit Price as floats; writing produces a header row and plain Python values with blanks as None. Registered as "clean", it is used like a built-in: sheet["A1"].options("clean", expand="table").value. Putting conventions in a converter means every script in a project reads tables the same way, and, as here, its logic can be tested without Excel by calling read_value and write_value directly. Bulk reading and writing for speed is covered in Speed Up xlwings with Bulk Reads and Writes.

Options on write

Most options also work when writing. options(index=False) and options(header=False) control what part of a DataFrame is written; transpose=True writes rows as columns; and assigning a list of lists to a single anchor cell — sheet["A1"].value = rows — fills the block starting there, so the target range never needs to be sized by hand. Writing None clears a cell, which is the counterpart of empty on reading. Options can be combined, and chaining options() once per range keeps the intent visible in the code.

Common pitfalls

• Assuming a list of rows. One row or one cell comes back flat or scalar; use ndim=2.
• The first column vanishing. The DataFrame converter uses it as the index; pass index=False.
• An extra index column in the sheet. Write with options(index=False).
• numbers=int on decimals. It truncates every number in the range.
• Blank rows inside a table. expand="table" stops at the first one.

Conclusion

Control xlwings conversions with range.options(): ndim=2 for a predictable list of rows, numbers=int and dates=dt.date for cleaner types, empty for blanks, transpose for orientation and expand="table" to size the range from the data. Read tables with options(pd.DataFrame, index=False) and write them with options(index=False), use options(dict) for settings blocks, and put your own conventions in a registered converter whose logic you can test without Excel.

Frequently asked questions

Why does xlwings return a single value instead of a list? A single cell returns a scalar and a single row or column a flat list; use options(ndim=2) to always get a list of rows.

How do I read a range into a pandas DataFrame with xlwings without losing the first column? Use range.options(pd.DataFrame, index=False, expand="table").value; without index=False the first column becomes the index.

How do I stop xlwings writing the DataFrame index? Write with range.options(index=False).value = df.

How do I make xlwings return integers instead of floats? Use options(numbers=int), but only for ranges where every number is whole, because decimals are truncated.

Related

• Up one level: Automating Excel with xlwings Basics — live Excel from Python.
• Read and Write a Live Excel Workbook with xlwings — the basics.
• Work with Named Ranges and Tables in xlwings — addressing data by name.
• How pandas Data Types Are Written to Excel Cells — the file-based equivalent.