Xlsx

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.
2. Live model (formulas that recompute on the user's later edits). Write real formulas, and reference cells not literals (=B5*(1+$B$6), not =B5*1.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

pythonCopy code
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:

pythonCopy code
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

pythonCopy code
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:

pythonCopy code
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).

pythonCopy code
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

pythonCopy code
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):

pythonCopy code
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

pythonCopy code
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.