Excel (.xlsx) workbooks

Runtime

Use exec with complete Python source (language: python). Prefer creating,

saving, reopening, and validating the workbook in one call; later calls can

revise the same relative filename. Follow the turn's User workspace

instructions for locating inputs, output boundaries, and presenting the

finished file.

Use openpyxl for cells, formulas, styles, charts, merged cells, multi-sheet

workbooks, number formats, and streaming large sheets. It is declared by every

supported DeepTutor installation. Do not assume pandas is installed: it exists

in the Docker runner but is not a direct dependency of every pip/source install.

THE critical gotcha: openpyxl writes formulas but never computes them

ws["B10"] = "=SUM(B2:B9)" stores the formula string. openpyxl has no formula

engine — the cached value stays empty (or stale, on an edited file). So:

• A workbook you create/edit with openpyxl opens fine in Excel/LibreOffice (they

recompute on open), but its cached values are wrong until then.

• Anything reading cached values first — data_only=True, another

pandas/openpyxl pass, or a downstream tool — sees blanks/stale data.

Pick by what the deliverable needs:

1. Static numbers (most common). If the user just needs correct values and

the sheet need not stay live, compute in Python and write the number, not

a formula string: ws["B10"] = sum(c.value for c in ws["B2:B9"][0]). Correct

immediately, no recalc needed.

1. Live model (formulas that recompute on the user's later edits). Write real

formulas, and reference cells not literals (=B5(1+$B$6), not =B51.05).

openpyxl can't set the cached value too. If shutil.which("soffice") succeeds,

recalculate through exec using subprocess.run and a

relative _recalc/ directory, replace out.xlsx with the recalculated copy,

then remove _recalc/. Never use /tmp or search for a desktop installation.

A later exec call can see the same bare filename. If LibreOffice is

absent, warn that formulas populate when the user opens the file in Excel.

Reading

from openpyxl import load_workbook

wb = load_workbook("in.xlsx", read_only=True, data_only=False)
for sheet_name in wb.sheetnames:
    ws = wb[sheet_name]
    for row in ws.iter_rows(values_only=True):
        print(row)

To read computed results of formulas (not the formula text), use openpyxl

with data_only=True — returns the value Excel last cached:

from openpyxl import load_workbook

wb = load_workbook("in.xlsx", data_only=True)
val = wb["Sheet1"]["B10"].value  # None if Excel never opened/saved the file

Gotcha: never save() a workbook loaded with data_only=True — that discards

every formula permanently (verified: the cell becomes None). Load twice if you

need both formulas and values.

Large file: load_workbook(path, read_only=True) streams rows cheaply.

Creating

from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Alignment

wb = Workbook()
ws = wb.active
ws.title = "Summary"
ws.append(["Region", "Sales"])  # header row
for r in [("West", 120), ("East", 95)]:
    ws.append(r)
ws["B4"] = "=SUM(B2:B3)"  # see formula gotcha above

ws["A1"].font = Font(bold=True)
ws["A1"].fill = PatternFill("solid", fgColor="DDDDDD")
ws["A1"].alignment = Alignment(horizontal="center")
ws["B2"].number_format = "#,##0"  # thousands separator
ws.column_dimensions["A"].width = 18
ws.freeze_panes = "A2"  # freeze header
wb.create_sheet("Detail")  # second sheet
wb.save("out.xlsx")

# Validate immediately; later exec calls can also reopen this relative path.
check = load_workbook("out.xlsx", data_only=False)
assert check.sheetnames, "generated workbook has no worksheets"
import zipfile

with zipfile.ZipFile("out.xlsx") as package:
    assert package.testzip() is None, "generated XLSX has a corrupt ZIP member"

For large exports, use openpyxl's write-only mode and append rows without

holding every cell object in memory:

from openpyxl import Workbook

wb = Workbook(write_only=True)
ws = wb.create_sheet("Data")
ws.append(["id", "value"])
for row in rows:
    ws.append(row)
wb.save("out.xlsx")

Editing (preserve existing formatting)

load_workbook keeps styles, formulas, merged cells, charts intact — edit only

what you touch. Do NOT round-trip through pandas to preserve formatting (pandas

rewrites the whole sheet, losing styles).

from openpyxl import load_workbook

wb = load_workbook("in.xlsx")  # keep formulas (data_only=False)
ws = wb["Sheet1"]
ws["C2"] = "Updated"
wb.save("out.xlsx")  # preserve the source; present the new file

Match the file's existing conventions (font, number formats, colors) rather than

imposing new ones — an established template wins over any default.

When inserting/deleting rows or columns (ws.insert_rows, ws.delete_cols),

openpyxl does not rewrite formulas that reference shifted cells. Re-point

affected formulas yourself, or avoid structural shifts in formula-heavy sheets.

Charts

from openpyxl.chart import BarChart, Reference

ch = BarChart()
ch.title = "Sales"
data = Reference(ws, min_col=2, min_row=1, max_row=3)  # include header for title
cats = Reference(ws, min_col=1, min_row=2, max_row=3)
ch.add_data(data, titles_from_data=True)
ch.set_categories(cats)
ws.add_chart(ch, "E2")

LineChart / PieChart / ScatterChart follow the same shape.

Verifying you produced clean output

In the same exec Python call, reload and scan for error strings after writing.

These mean broken formulas

that recalc surfaced (#REF! bad reference, #DIV/0! zero denominator,

#VALUE! type mismatch, #NAME? unknown function, #N/A):

from openpyxl import load_workbook

wb = load_workbook("out.xlsx", data_only=True)
errs = [
    f"{s}!{c.coordinate}={c.value}"
    for s in wb.sheetnames
    for row in wb[s].iter_rows()
    for c in row
    if isinstance(c.value, str) and c.value.startswith("#")
]
print(errs or "clean")

This only catches errors in cached values. If you wrote formulas and couldn't

recalc (no soffice), cached values are blank, so the check is meaningful only

after a recalc or after Excel opens the file. Writing computed numbers (option 1)

sidesteps this.

CSV / TSV

import csv

with open("in.csv", newline="", encoding="utf-8-sig") as source:
    rows = list(csv.reader(source))  # delimiter="\t" for TSV
with open("out.csv", "w", newline="", encoding="utf-8") as target:
    csv.writer(target).writerows(rows)

For messy input (junk rows, header not on row 1, ragged columns), inspect a

bounded sample and explicitly normalize only the requested rows/columns.

Raw OOXML (rarely needed)

openpyxl covers essentially all xlsx features; reach for raw XML only for the

narrow cases it can't express (e.g. preserving an exotic part it drops on

re-save). An .xlsx is a ZIP: xl/workbook.xml, xl/worksheets/sheet1.xml,

xl/sharedStrings.xml, plus [Content_Types].xml and _rels/. Unzip with

stdlib zipfile, edit the part, re-zip — keep [Content_Types].xml and every

.rels consistent, keep IDs unique, and don't pretty-print into value-bearing

text nodes. Correctness check = it opens in Excel with no repair prompt.