Spreadsheets

Produce XLSX deliverables with openpyxl, and edit workbooks that already exist with LibreOffice Calc. Follow every section below before declaring a workbook done.

Never round-trip an existing workbook with openpyxl

openpyxl writes new workbooks. It never touches a file that already exists — not to change one number, not for a "quick value edit".

load_workbook(path) followed by .save(path) does not edit the file; it rebuilds it from the parts openpyxl models, and everything else is gone. In practice that means silently losing:

• formula results the engine computed — openpyxl drops the cached values, so every formula reads as empty until someone opens the file in Excel
• named ranges, pivot tables and their caches
• charts, images, and shapes
• conditional formatting and data validation
• macros, custom styles, and sheet protection

None of this raises an error. The file saves, looks plausible in an inspection script, and hands the user a gutted copy of their own workbook.

Read-only inspection is fine and expected: load_workbook(path, read_only=True) or data_only=True, with no .save() anywhere near it. The bundled analyze_xlsx.py does exactly this.

Installing the library

Install the pinned dependency with its own exec call — commands have a bounded wall clock, and one pip invocation per package stays inside it:

python3 -m pip install --user openpyxl==3.1.5

Installs work only when this chat's network policy allows package managers, and they persist for the rest of the conversation. If an install is refused by policy, do not retry: tell the user to enable the package-manager network policy for this chat, and offer the closest format you can produce without the library (CSV) — only with their knowledge, never as a silent substitution. If a dependency cannot be installed at all, say so plainly instead of quietly delivering a lesser format.

Creating a new workbook

A workbook built from scratch is openpyxl's job, with the formula and layout rules below. Nothing in this section changes because of the edit path.

Editing a workbook that already exists

Use the bundled Calc driver, which opens the file in a real spreadsheet engine and saves it back through that engine's own filter — the edit lands in place and everything the file already carried survives:

python3 .tidebreak/exec-scripts/calc_uno.py inspect input/model.xlsx
python3 .tidebreak/exec-scripts/calc_uno.py get-cell input/model.xlsx Summary B7
python3 .tidebreak/exec-scripts/calc_uno.py set-cell input/model.xlsx Summary B7 '=SUM(B2:B6)'
python3 .tidebreak/exec-scripts/calc_uno.py list-named-ranges input/model.xlsx
python3 .tidebreak/exec-scripts/calc_uno.py set-named-range input/model.xlsx Revenue '$Data.$B$2:$B$13'

set-cell treats a leading = as a formula and anything else as a number when it parses as one, text otherwise. Each write recalculates and saves, so run one set-cell per change rather than batching edits you cannot inspect. Run it with python3: the UNO bridge binds to the system interpreter.

This needs the LibreOffice that ships in the sandbox document image. Outside that sandbox the script exits saying so — that is the end of the edit path, not a cue to fall back to openpyxl. Tell the user the workbook cannot be edited in this environment and what they would get instead (for example a new workbook built from scratch, which is not the same file).

Formulas: the hard rule

Write live formulas, never precomputed values, in any cell that represents a calculation. A total cell gets =SUM(B2:B13), not the number you computed in Python. A spreadsheet with baked-in values silently goes stale the moment the user edits an input, which defeats the reason they asked for a spreadsheet.

openpyxl writes formulas but does not calculate them — the file carries the formula text and Excel computes it on open. Two consequences:

• You cannot read a formula's result back by reopening the file with openpyxl — there is no cached value there to read. Compute the expected value in Python for your own checking, or let the recalculation step below evaluate the workbook properly.
• Any calculated value the user must see in the conversation should also be stated in chat, since they may not open the workbook immediately.

Layout conventions

• Give columns real number formats: currency #,##0.00, percentages 0.0%, dates yyyy-mm-dd — via cell.number_format. Never leave money as bare floats.
• Size columns to their content with worksheet.column_dimensions["A"].width; unreadably narrow columns are a defect.
• Freeze the header row (worksheet.freeze_panes = "A2") on any sheet the user will scroll.
• Bold the header row and keep one table per sheet; split unrelated data into multiple named sheets (workbook.create_sheet("Assumptions")) instead of stacking tables.

Saving deliverables

Save the finished workbook in output/ — files there are published to the user as durable outputs. Writing the same filename again publishes a new version of the same output, so keep the filename stable when revising and change it only when the user asks for a distinct workbook. Outputs above the 16 MiB ceiling are refused: keep workbooks lean (no embedded raw data dumps that a summary sheet serves better), and if the data genuinely cannot fit, tell the user and deliver a trimmed workbook plus the raw data as CSV.

Validation before declaring done

Two passes, in this order, after every create or edit.

1. Recalculate. Formulas you wrote are unproven until an engine evaluates them:

python3 .tidebreak/exec-scripts/xlsx_recalc.py output/<file>.xlsx

It opens the workbook in Calc, forces a full recalculation, saves the computed results back, then reports per sheet any cells holding #REF!, #DIV/0!, #VALUE!, #NAME?, #N/A, #NUM! or #NULL!, plus numbers that were written as text and so drop out of the sums above them. Finding problems is a report, not a failure — fix the offending cells and rerun until the report is clean. Like the editor, this needs the sandbox LibreOffice; when it is unavailable, say the formulas could not be verified rather than declaring them correct.

2. Inspect visually. Re-read the saved file with the bundled analyzer:

python3 .tidebreak/exec-scripts/analyze_xlsx.py output/<file>.xlsx

It prints each sheet's dimensions and sample rows and writes sheet thumbnails into preview/ (at most 3 images are returned per exec call). Check that every expected sheet exists, headers and sample rows look right, and the thumbnails show a readable layout. Only declare the workbook done after both passes are clean.