Libreoffice calc

LibreOffice Calc

Use the bundled calc modules for UNO-backed Calc spreadsheet work. All paths must be absolute. Bundled modules live under scripts/ in this skill directory, so set PYTHONPATH=<skill_base_dir>/scripts. If setup or runtime issues appear, check references/troubleshooting.md.

API Surface

# Non-session utilities
create_spreadsheet(path, source=None)              # source: path to .json or .xml file to import
export_spreadsheet(path, output_path, export_format)   # formats: "pdf", "xlsx", "csv"
snapshot_area(doc_path, output_path, sheet="Sheet1", row=0, col=0, width=None, height=None, dpi=150)

# Session (primary editing API)
CalcSession(path) -> context manager

CalcSession methods:
  read_cell(target: CalcTarget) -> dict[str, object]
  write_cell(target: CalcTarget, value, value_type="auto")
  read_range(target: CalcTarget) -> list[list[dict[str, object]]]
  write_range(target: CalcTarget, data)
  format_range(target: CalcTarget, formatting: CellFormatting)
  list_sheets() -> list[dict[str, object]]
  add_sheet(name, index=None)
  rename_sheet(target: CalcTarget, new_name)
  delete_sheet(target: CalcTarget)
  define_named_range(name, target: CalcTarget)
  get_named_range(target: CalcTarget) -> dict[str, object]
  delete_named_range(target: CalcTarget)
  set_validation(target: CalcTarget, rule: ValidationRule)
  clear_validation(target: CalcTarget)
  create_chart(target: CalcTarget, spec: ChartSpec)
  update_chart(target: CalcTarget, spec: ChartSpec)
  delete_chart(target: CalcTarget)
  recalculate()
  patch(patch_text, mode="atomic") -> PatchApplyResult
  export(output_path, export_format)
  reset()
  close(save=True)

# Standalone patch utility
patch(path, patch_text, mode="atomic") -> PatchApplyResult

Structured Targets: CalcTarget

from calc import CalcTarget

CalcTarget(
    kind="cell" | "range" | "sheet" | "named_range" | "chart",
    sheet=None,
    sheet_index=None,
    row=None,
    col=None,
    end_row=None,
    end_col=None,
    name=None,
    index=None,
)

Target kinds

Kind	Supported fields	Use
cell	sheet or sheet_index, row, col	Read or write one cell
range	sheet or sheet_index, row, col, end_row, end_col	Read, write, format, validate, or chart a rectangular range
sheet	sheet or sheet_index	Rename or delete one sheet
named_range	name	Inspect or delete one named range
chart	sheet or sheet_index, plus name or index	Update or delete one chart

Resolution rules

• Coordinates are zero-based and must be non-negative.
• sheet and sheet_index are mutually exclusive.
• name and index are mutually exclusive.
• Range targets must keep end_row >= row and end_col >= col.
• Chart targets must identify one sheet plus one chart selector.
• Calc does not auto-convert a one-cell range into a cell target; keep those shapes explicit.

Cell Read Results

read_cell() and read_range() return cell dictionaries with the same shape:

{
    "value": 100.0,
    "formula": None,
    "error": None,
    "type": "number",
    "raw": 100.0,
}

Empty cells return type="empty" with value=None:

{"value": None, "formula": None, "error": None, "type": "empty", "raw": None}

Formula cells use type="formula"; when Calc reports a formula error, error is populated and value becomes None.

Boolean values (True/False) are stored as numeric 1.0/0.0. Reading back a cell written with a Python bool returns type="number" with value=1.0 or value=0.0, not a text string.

Formatting Payload: CellFormatting

from calc import CellFormatting

CellFormatting(
    bold=None,
    italic=None,
    font_name=None,
    font_size=None,
    color=None,          # named color or integer
    number_format=None,  # "currency" | "percentage" | "date" | "time"
)

Notes:

• At least one formatting field must be set.
• color accepts a named color or 0xRRGGBB integer.
• format_range() works for both a cell target and a rectangular range target.

Validation Payload: ValidationRule

from calc import ValidationRule

ValidationRule(
    type="whole",
    condition="between",
    value1=1,
    value2=10,
    show_error=True,
    error_message="Enter a value from 1 to 10.",
    show_input=True,
    input_title="Allowed values",
    input_message="Only integers from 1 to 10 are valid.",
    ignore_blank=True,
    error_style=0,
)

Supported type values:

• any
• whole
• decimal
• date
• time
• text_length
• list

Supported condition values:

• between
• not_between
• equal
• not_equal
• greater_than
• less_than
• greater_or_equal
• less_or_equal

Chart Payload: ChartSpec

from calc import CalcTarget, ChartSpec

ChartSpec(
    chart_type="line",
    data_range=CalcTarget(
        kind="range",
        sheet="Data",
        row=0,
        col=0,
        end_row=5,
        end_col=1,
    ),
    anchor_row=7,
    anchor_col=0,
    width=10000,
    height=7000,
    title="Revenue Trend",
)

Notes:

• chart_type must be one of bar, line, pie, or scatter.
• width and height use Calc chart rectangle units (the same units the packaged API already accepts).
• Create charts by targeting a sheet; update or delete charts by targeting a chart.

Patch DSL

Use patch() or session.patch() to apply ordered spreadsheet operations.

[operation]
type = write_range
target.kind = range
target.sheet = Revenue Data
target.row = 0
target.col = 0
target.end_row = 2
target.end_col = 1
data <<JSON
[["Label", "Value"], ["Revenue", 100], ["Cost", 80]]
JSON

[operation]
type = format_range
target.kind = range
target.sheet = Revenue Data
target.row = 1
target.col = 1
target.end_row = 2
target.end_col = 1
format.number_format = currency
format.bold = true

[operation]
type = create_chart
target.kind = sheet
target.sheet = Revenue Data
chart.chart_type = line
chart.data_range.kind = range
chart.data_range.sheet = Revenue Data
chart.data_range.row = 0
chart.data_range.col = 0
chart.data_range.end_row = 2
chart.data_range.end_col = 1
chart.anchor_row = 5
chart.anchor_col = 0
chart.width = 9000
chart.height = 6000
chart.title = Revenue Trend

Supported operation types

• write_cell
• write_range
• format_range
• add_sheet
• rename_sheet
• delete_sheet
• define_named_range
• delete_named_range
• set_validation
• clear_validation
• create_chart
• update_chart
• delete_chart
• recalculate

Patch value rules

• Use target.* fields for the primary target.
• Use format.* fields for CellFormatting.
• Use rule.* fields for ValidationRule.
• Use chart.* fields for ChartSpec; chart source ranges use chart.data_range.*.
• data must be valid JSON.
• Heredoc blocks are supported with <<TAG ... TAG for multiline JSON or text.

Modes

• atomic stops on first failure, resets the session, and persists nothing.
• best_effort keeps successful earlier operations and records failures.

PatchApplyResult fields:

• mode
• overall_status = "ok" | "partial" | "failed"
• operations = list of PatchOperationResult
• document_persisted

For standalone patch(path, ...), document_persisted means the changes were saved to disk. For session.patch(...), it means the patch produced successful mutations in the current open session state.

Example: Build a Spreadsheet in Session

from pathlib import Path

from calc import (
    CalcSession,
    CalcTarget,
    CellFormatting,
    ChartSpec,
    ValidationRule,
)
from calc.core import create_spreadsheet

output = str(Path("test-output/revenue-report.ods").resolve())
create_spreadsheet(output)

with CalcSession(output) as session:
    session.rename_sheet(CalcTarget(kind="sheet", sheet="Sheet1"), "Revenue Data")
    session.add_sheet("Summary")
    session.write_range(
        CalcTarget(kind="range", sheet="Revenue Data", row=0, col=0, end_row=2, end_col=1),
        [["Label", "Value"], ["Revenue", 100], ["Cost", 80]],
    )
    session.format_range(
        CalcTarget(kind="range", sheet="Revenue Data", row=1, col=1, end_row=2, end_col=1),
        CellFormatting(number_format="currency", bold=True),
    )
    session.define_named_range(
        "RevenueValues",
        CalcTarget(kind="range", sheet="Revenue Data", row=1, col=1, end_row=2, end_col=1),
    )
    session.set_validation(
        CalcTarget(kind="range", sheet="Revenue Data", row=1, col=1, end_row=2, end_col=1),
        ValidationRule(type="whole", condition="greater_than", value1=0),
    )
    session.create_chart(
        CalcTarget(kind="sheet", sheet="Revenue Data"),
        ChartSpec(
            chart_type="line",
            data_range=CalcTarget(
                kind="range",
                sheet="Revenue Data",
                row=0,
                col=0,
                end_row=2,
                end_col=1,
            ),
            anchor_row=5,
            anchor_col=0,
            width=9000,
            height=6000,
            title="Revenue Trend",
        ),
    )
    session.recalculate()

Example: Patch an Existing Spreadsheet

from calc import patch

result = patch(
    "/abs/path/revenue-report.ods",
    """
    [operation]
    type = write_cell
    target.kind = cell
    target.sheet = Summary
    target.row = 1
    target.col = 1
    value = Ready
    value_type = text

    [operation]
    type = format_range
    target.kind = cell
    target.sheet = Summary
    target.row = 1
    target.col = 1
    format.bold = true

    [operation]
    type = recalculate
    """,
    mode="best_effort",
)

print(result.overall_status)

Snapshots

from pathlib import Path
from calc import snapshot_area

result = snapshot_area(doc_path, "/tmp/revenue.png", sheet="Revenue Data", row=0, col=0, dpi=150)
print(result.file_path, result.width, result.height)
Path(result.file_path).unlink(missing_ok=True)   # clean up used snapshots

Use snapshots to verify chart placement, formatting, and sheet layout before delivery.

• snapshot_area() output dimensions can differ slightly from the requested size; treat small pixel differences as normal.

Common Mistakes

• Passing a relative path; UNO-facing Calc APIs expect absolute file paths.
• Mixing up cell and range targets; Calc keeps them distinct even for one-cell selections.
• Using one-based coordinates; rows and columns are zero-based.
• Assuming create_chart() picks a random later target name; when title is set, targeting the chart by that same name is the safest follow-up pattern.
• The packaged API does not expose chart-listing or validation-inspection helpers; deeper verification may require direct UNO inspection in advanced workflows.
• Forgetting chart.data_range.* fields when patching chart operations.
• Expecting exact requested PNG dimensions from snapshot_area(); Calc export can differ by a small amount.
• Forgetting to clean up captured snapshots after inspection.
• Calling session methods after session.close().
• JSON / XML import requires LibreOffice 26.2+.