Write a List of Dictionaries to Excel Without pandas

JSON from an API, rows from a database driver, results built up in a loop — a lot of data in Python arrives as a list of dictionaries. pd.DataFrame(records).to_excel(...) is the usual way to save it, but it pulls in pandas for what is really a loop, turns None into NaN, and converts integers in columns with gaps into floats. openpyxl can write the same records directly: one row per dictionary, one column per key, with Python types mapped to Excel types. This page, part of Writing DataFrames to Excel with pandas, builds a small DictWriter for .xlsx files and covers the cases that break the naive version.

Prerequisites

pip install openpyxl

The records below are what an orders API might return: not every record has every key, one value is a list, one is a nested dictionary, the timestamps carry a timezone and the amounts are Decimal.

from datetime import datetime, timezone
from decimal import Decimal

records = [
    {"id": 501, "customer": "Arden Ltd", "total": Decimal("129.90"), "paid": True,
     "created": datetime(2026, 9, 28, 9, 15, tzinfo=timezone.utc), "tags": ["priority", "b2b"]},
    {"id": 502, "customer": "Birch & Co", "total": Decimal("48.00"), "paid": False,
     "created": datetime(2026, 9, 28, 11, 2, tzinfo=timezone.utc)},
    {"id": 503, "customer": "Cobalt GmbH", "total": Decimal("310.25"), "paid": True,
     "created": datetime(2026, 9, 29, 8, 40, tzinfo=timezone.utc), "coupon": "AUTUMN10",
     "shipping": {"method": "express", "cost": Decimal("12.50")}},
]
print(len(records), "records")

Step 1 — The naive version and why it fails

The obvious approach takes the header from the first record and appends each record's values:

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
header = list(records[0])
ws.append(header)
try:
    for rec in records:
        ws.append([rec.get(k) for k in header])
except Exception as exc:
    print(type(exc).__name__, exc)

It fails twice over. openpyxl refuses the list in tags with ValueError: Cannot convert ['priority', 'b2b'] to Excel — a cell holds one value, not a sequence. And even if it had not, the header came from the first record only, so coupon and shipping, which appear only in the third, would have been dropped without a word. Timezone-aware datetimes are the third trap: Excel has no timezone support, and openpyxl raises a TypeError for them as well (see Fix "Excel does not support timezones in datetimes").

Step 2 — Build the header from every record

Collect keys from all records, keeping the order in which they first appear, so the columns come out in the order a reader expects rather than alphabetically:

def collect_header(records):
    header = {}
    for rec in records:
        for key in rec:
            header.setdefault(key, None)
    return list(header)

header = collect_header(records)
print(header)

A dictionary is used as an ordered set: setdefault adds a key only the first time it is seen, and dictionaries keep insertion order. If the order matters more than that — say the API returns keys in a different order each time — pass an explicit list of columns and treat any extra key as an error or a warning, so a new field in the API response is noticed rather than appended at the far right.

Step 3 — Convert values to types Excel understands

Excel cells hold numbers, text, booleans, dates and times. Map everything else before writing:

import json
from datetime import date

def to_cell(value):
    if isinstance(value, datetime) and value.tzinfo is not None:
        return value.astimezone(timezone.utc).replace(tzinfo=None)
    if isinstance(value, (list, tuple, set)):
        return ", ".join(map(str, value))
    if isinstance(value, dict):
        return json.dumps(value, default=str)
    return value

print(to_cell(records[0]["tags"]), "|", to_cell(records[2]["shipping"]), "|", to_cell(records[0]["created"]))

• Timezones are converted to UTC and then dropped. Say so in the header — created (UTC) — because the cell alone no longer carries it.
• Lists become comma-separated text, which reads well and filters in Excel. Use a different separator if the items can contain commas.
• Nested dictionaries become JSON text. That keeps the data, but a nested object that matters to the reader deserves its own columns: flatten it into shipping.method and shipping.cost before writing.
• Decimal, bool, int, float, date and naive datetime go through unchanged: openpyxl writes Decimal as a number and True as an Excel boolean.

Step 4 — Put it together as a DictWriter

With the header and the converter, writing is a loop. Flatten nested dictionaries one level first so shipping becomes two proper columns:

from openpyxl.utils import get_column_letter
from openpyxl.worksheet.table import Table, TableStyleInfo

def flatten(rec, sep="."):
    out = {}
    for k, v in rec.items():
        if isinstance(v, dict):
            for k2, v2 in v.items():
                out[f"{k}{sep}{k2}"] = v2
        else:
            out[k] = v
    return out

def write_dicts(path, records, sheet="Data"):
    flat = [flatten(r) for r in records]
    header = collect_header(flat)
    wb = Workbook()
    ws = wb.active
    ws.title = sheet
    ws.append(header)
    for rec in flat:
        ws.append([to_cell(rec.get(k)) for k in header])
    for i, key in enumerate(header, start=1):
        col = get_column_letter(i)
        width = max(len(str(key)), *(len(str(to_cell(r.get(key, "")))) for r in flat))
        ws.column_dimensions[col].width = min(width + 2, 40)
        if key == "created":
            for cell in ws[col][1:]:
                cell.number_format = "yyyy-mm-dd hh:mm"
    ref = f"A1:{get_column_letter(len(header))}{len(flat) + 1}"
    table = Table(displayName="Orders", ref=ref)
    table.tableStyleInfo = TableStyleInfo(name="TableStyleMedium2", showRowStripes=True)
    ws.add_table(table)
    ws.freeze_panes = "A2"
    wb.save(path)
    return header

cols = write_dicts("orders.xlsx", records)
print(cols)

Missing keys come out as None from rec.get(k), which openpyxl writes as an empty cell — so coupon is blank for the first two orders instead of the string "None" or a NaN. Column widths are estimated from the longest value, capped at 40 characters, and the created column gets a date-time format so Excel does not show a bare serial number. The data is wrapped in an Excel table, which gives it filter buttons and banded rows. Reading the file back shows the types survived — with one caveat: Excel stores every number as a binary float, so Decimal("129.90") comes back as the float 129.9. The two-decimal display is a job for a number format, not for the stored value:

from openpyxl import load_workbook

ws = load_workbook("orders.xlsx")["Data"]
for row in ws.iter_rows(min_row=1, max_row=4, values_only=True):
    print(row)

Step 5 — Large lists: write-only mode

For hundreds of thousands of records, a normal workbook keeps every cell object in memory until save. Write-only mode streams rows to disk as they are appended. It cannot go back to earlier rows, so the header must be known before the first row — which means either a fixed column list or one pass over the records to collect keys:

import time

big = [{"id": i, "sku": f"SKU-{i % 500:04d}", "qty": i % 7, "price": Decimal("9.99")} for i in range(200_000)]
t = time.perf_counter()
wb = Workbook(write_only=True)
ws = wb.create_sheet("Data")
header = collect_header(big[:1000])
ws.append(header)
for rec in big:
    ws.append([rec.get(k) for k in header])
wb.save("big.xlsx")
print(f"{len(big):,} rows in {time.perf_counter() - t:.1f}s")

Collecting the header from a sample is safe only when every record has the same keys, as rows from a database query do. For API data with optional fields, do a full first pass — it is cheap compared with writing. Write Large DataFrames to Excel with Write-Only Mode covers styling rows in this mode.

Common pitfalls

• Header from the first record only. Optional keys disappear. Collect from all records, or fix the column list.
• Writing str(value) for everything. Numbers stored as text trigger Excel's green-triangle warnings and break sums. Only convert what Excel cannot store.
• Timezone-aware datetimes. Convert to one zone and strip tzinfo, and name the zone in the header.
• Illegal characters. Control characters in text from APIs raise IllegalCharacterError; see Fix IllegalCharacterError When Writing Excel with openpyxl.
• Text that starts with =. openpyxl writes a string beginning with = as a formula. For data from outside, that is a security issue, covered in Prevent Formula Injection in Excel Exports.

Conclusion

Collect the header from every record in first-seen order, convert the few types Excel cannot store — timezone-aware datetimes, lists, nested dictionaries — and append one row per record with rec.get(key) so missing keys become blank cells. Flatten one level of nesting into dotted column names, set widths and a date format, and wrap the range in a table. For very long lists, fix the header first and write in write-only mode.

Frequently asked questions

How do I write a list of dictionaries to Excel without pandas? Create an openpyxl Workbook, append a header row built from the dictionaries' keys, then append one row per dictionary with rec.get(k) for k in header. Missing keys become empty cells.

Why does openpyxl say "Cannot convert ... to Excel"? A cell can hold only a single number, string, boolean or date. Convert lists and dictionaries to text — a joined string or JSON — or split them into separate columns before writing.

How do I keep the column order of my dictionaries? Build the header by walking the records and adding each key the first time it appears. Python dictionaries keep insertion order, so the columns follow the order of the keys in the data.

Is openpyxl slower than pandas for this? For writing, pandas uses openpyxl or xlsxwriter underneath, so the speed is similar; pandas mostly adds conversion overhead. For very large lists use openpyxl's write-only mode, or xlsxwriter's constant_memory mode.

Related

• Up one level: Writing DataFrames to Excel with pandas — the pandas route and its options.
• Read Excel Rows into Dictionaries with openpyxl — the reverse direction.
• How openpyxl Stores Python Values in Cells — the type mapping in detail.
• Fetch API Data into Excel with Python requests — where records like these come from.