You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Import or convert an open Calc workbook so cell data stays the same while spreadsheet formulas become =PY() Python (venv-backed), targeting ~90% automated conversion on typical business sheets.
Product goal: A user opens a spreadsheet in LibreOffice Calc (.ods, .xlsx, or an existing document) and runs WriterAgent → Convert Sheet to Python…. The result is a workbook where:
Data is unchanged — constants, text, dates, and evaluated values in non-formula cells stay as-is (or are copied verbatim to a new sheet).
Logic becomes Python — each converted formula cell becomes =PY("…"; …) (or =PYTHON(…) alias), executing in the user’s venv with explicit data wiring so Calc’s recalc DAG stays correct.
Coverage target:~90% of formula cells on a curated benchmark corpus of typical business spreadsheets (see § Coverage model). The remaining ~10% are left as original Calc formulas or marked with a visible TODO comment in the generated Python / a companion audit sheet.
This plan is in-workbook conversion (formulas stay in Calc as =PY()). It does not replace the two-phase chat workflow (compute in venv → write back with tools); it automates that rewrite for existing sheets.
Optional dependency (evaluate in Phase 3 spike): vendored or dev-only formulas for ODS AST — only if a minimal in-tree parser is too costly. Default path: small hand-rolled parser for P1 grammar + list_calc_functions metadata.
5. Conversion pipeline
End-to-end steps
Step
Action
Output
1. Resolve scope
Active sheet / selection → used range cursor
RangeAddress
2. Snapshot
getDataArray + getFormulaArray on scope
2D cell matrix
3. Classify cells
Per cell: empty, constant, formula, py_formula, error
Tagged grid
4. Build graph
Precedents per formula (XFormulaQuery + regex)
DAG
5. Order
Topological sort for conversion (constants first)
Ordered formula list
6. Translate
Per formula → Python body assigning result
Code string
7. Wire
Map precedents → data / data[0]… / scalar second arg
=PY("…"; A1:B2; …)
8. Emit
Write to target sheet; copy constants unchanged
New grid
9. Verify
Full recalc; compare to snapshot oracle
Pass/fail per cell
10. Report
Aggregate stats + TODO list
JSON + dialog
Import vs in-place
Mode
When
Open in Calc
User already has .ods / .xlsx open — primary path
File picker
Same as above; LO opens file, then user runs convert
Chat attachment
Future — agent reads read_cell_range then proposes conversions
There is no separate file importer in v1: LibreOffice is the loader. Conversion operates on the open document model.
6. Cell handling rules
Cell type
Original
After conversion
Empty
—
Empty
Constant (number/text/bool)
Value
Same value (copy)
Constant date
Serial or formatted
Same (document # formats copied)
Calc formula
=SUM(A1:A10)
=PY("result = np.sum(data)"; A1:A10)
Homogeneous column
=A2*2 filled down
Single matrix =PY with vectorized data (Phase 4)
Existing =PY() / =PYTHON()
As-is
Normalize via parse_python_formula / rebuild_python_formula_with_data
Array formula
{=…}
TODO — keep Calc formula
Reference to other sheet
=Sheet2.A1
=PY(…; Sheet2.A1) if UNO range resolves; else TODO
Pivot / CF / chart
N/A
Unchanged (not formula cells)
Error cell (#DIV/0!)
Error
Convert formula anyway; verify may fail until data fixed
result and data conventions
Generated Python must assign result (WriterAgent sandbox contract). Examples:
Complex Calc functions that need shared semantics (SUMIF, XLOOKUP, FILTER, SUBTOTAL, date helpers, etc.) emit xl.* calls — not pasted def blocks. The venv sandbox auto-imports plugin.scripting.calc_functions as xl (same mechanism as np / pd).
Workbooks converted before this change may still contain inline pasted helpers; re-import to shrink formulas.
Microsoft xl() vs WriterAgent xl.* (different APIs, same name) {#microsoft-xl-vs-writeragent-xl-different-apis-same-name}
Name collision warning: Microsoft Python in Excel and WriterAgent both use the name xl, but they are not the same API. Microsoft’s xl("A1:B10") is a data bridge; WriterAgent’s xl.sumif(...) is a Calc formula emulator. There is no Microsoft-shipped xl.sumif, xl.xlookup, or similar library.
Microsoft xl()
WriterAgent xl.*
Shape
One callable: xl("A1:C10", headers=True)
Module alias with 259 helpers: xl.sumif(...), xl.xlookup(...), …
Purpose
Data ingress (ranges, tables, names, images, Power Query) → usually a pandas DataFrame
Replicate Calc/Excel formula semantics inside Python during spreadsheet conversion
Where it lives
Inside the Python code string in =PY(...)
Data arrives via explicit =PY(...; range) args as data; xl auto-imported from AUTO_IMPORTS
excel module — type conversion plumbing only (convert_to_scalar, convert_to_dataframe, client_timezone, client_locale); not formula parity (initialization settings).
Pre-loaded analysis stack — numpy, pandas, matplotlib, statsmodels, seaborn for aggregation, plotting, and stats after data is in Python.
User-defined init helpers — e.g. QuickStats, kpi_summary, format_currency in the editable initialization pane; community patterns, not 259 built-in formula emulators.
Excel formulas remain Excel formulas — Microsoft’s product model keeps =SUMIF / =XLOOKUP in normal cells and uses Python for analysis on table data pulled via xl(). They are not converting formula cells to Python the way this import pipeline does.
For a SUMIF-like operation in Python in Excel, the intended path is pandas (e.g. df.loc[df["Region"] == "East", "Sales"].sum()) or referencing a cell that still holds =SUMIF(...). WriterAgent’s xl.* library exists because spreadsheet import must emit readable Python that preserves Calc semantics without hand-rolling 200+ functions per workbook — a gap Microsoft does not fill.
flowchart TB
subgraph msExcel [Microsoft Python in Excel]
MS_PY["=PY code string"]
MS_xl["xl range ref inside string"]
MS_pd["pandas / numpy ops"]
MS_PY --> MS_xl --> MS_pd
end
subgraph waCalc [WriterAgent spreadsheet import]
LO_form[Calc formula cell]
Trans[translate.py]
WA_PY["=PY code; data ranges"]
WA_xl["xl.sumif etc in code"]
LO_form --> Trans --> WA_PY
WA_PY --> WA_xl
end
Loading
Coverage: WriterAgent ships 259 helpers in HELPER_NAMES; the translator maps ~157 Calc builtins today (see §8.12); gaps become TODO cells, inline np/math, or native Calc. Microsoft has no equivalent library.
Packaging: The xl.* stack is WriterAgent-only for now (LibrePy exclusion); LibrePy keeps explicit data args but not formula-parity helpers until spreadsheet conversion ships in core.
See also:Enabling NumPy — Microsoft Python in Excel vs WriterAgent for =PY architecture, dependency tracking, and runtime comparison (canonical doc for data ingress — this section covers the xl naming / formula-parity angle only).
Source of truth for done vs not done:§8.12 Master function inventory. Subsections §8.1–§8.11 below retain conceptual Python shapes from the original plan; tier labels (P1/P2/P3) are historical—check §8.12 Status column for current implementation.
Coverage tiers (historical plan): P1 ≈70% of formula cells; P1+P2 ≈90%; P3 / N/A = LLM, manual, or leave-as-Calc. Tier D (financial suite, OFFSET/INDIRECT, database functions) is in progress—see §8.12.
Convention: data is the primary injected range; data[n] is multi-range varargs. Scalar ranges collapse to scalar per calc_addin_data. Use float(...) when Calc expects a scalar double.
8.1 Arithmetic & aggregates (P1)
Calc function
Python / =PY body (conceptual)
Notes
SUM
result = float(np.sum(data))
Empty → 0 in Calc
SUMIF
result = float(np.sum(np.where(cond, data, 0)))
Needs criteria range wired
SUMIFS
pandas mask or np.sum on boolean
Multi-range
PRODUCT
result = float(np.prod(data))
QUOTIENT
result = float(data[0] // data[1])
MOD
result = float(data[0] % data[1])
POWER
result = float(data[0] ** data[1])
SQRT
result = float(np.sqrt(data))
ABS
result = float(np.abs(data))
SIGN
result = float(np.sign(data))
INT
result = float(np.floor(data))
TRUNC
result = float(np.trunc(data))
ROUND
result = float(np.round(data, n))
ROUNDUP
result = float(np.ceil(data * 10**n) / 10**n)
ROUNDDOWN
result = float(np.floor(data * 10**n) / 10**n)
CEILING
result = float(np.ceil(data))
Mode args P2
FLOOR
result = float(np.floor(data))
EVEN / ODD
np.ceil / np.floor parity helpers
AVERAGE
result = float(np.mean(data))
AVERAGEIF
masked mean
P2
AVERAGEIFS
multi-criteria mean
P2
COUNT
result = float(np.sum(~np.isnan(numeric)))
COUNTA
result = float(sum(x is not None and x != "" for x in flat))
COUNTBLANK
result = float(sum(x is None or x == "" for x in flat))
Semicolon rule: LibreOffice argument separator is ; (CALC_FORMULA_SYNTAX). Parser must accept ; inside formulas while generated =PY() strings also use ;.
Inventory summary:235 / 374 functions in this master list are Shipped in translate.py (232 emitters including ROW/COLUMN/IFS/SWITCH handlers).
LibreOffice Calc exposes ~508 built-ins (Calc Guide 25.2 Ch.9, Functions by Category); this curated list (~374) is the conversion goal set for business/statistical workbooks—not every locale alias (*_ADD, *_EXCEL2003) or extension-only symbol.
Status
Meaning
Shipped
Deterministic codegen in translate.py
Not started
Goal function; UNSUPPORTED_FUNCTION today
Planned (P3)
Financial, database, matrix, or LLM-assist tier (Tier D deferred)
N/A
Leave as Calc (dynamic refs, meta cells, hyperlinks)
Use list_calc_functions (chat tool) against a live Calc session for the authoritative runtime catalog and descriptions.
Run with make test. New tests follow module naming in AGENTS.md.
Golden example (P1):
Before
After
=SUM(A1:A10)
=PY("result = float(np.sum(data))"; A1:A10)
=IF(A1>0;B1;C1)
=PY("result = data[1] if data[0]>0 else data[2]"; A1; B1; C1)
42
42
12. Non-goals & known gaps
Feature
v1 handling
Pivot tables
Not converted
Charts / images
Preserved as objects
Conditional formatting
Preserved
Data validation rules
Preserved
Named ranges
Wire by resolving name → range (Phase 4); else TODO
Dynamic arrays / auto-spill
Matrix =PY manual range
INDIRECT, OFFSET heavy models
TODO
Circular references
Skip + report
=PROMPT()
User opt-out
Macros / Basic
Out of scope
Long tail (~10%): array formulas, financial functions, INDIRECT, add-ins, localized-only syntax errors, and sheets with > python_max_data_cells in one data arg (split or TODO).