sheetops

Files (6)

• SKILL.md
• bulk.md · references/
• cli-profile.md · references/
• development.md · references/
• finance-python-legacy.md · references/
• workbook-operations.md · references/

SKILL.md (first lines)

# SheetOps

## Data-analysis workflow

Use the documented Python APIs below directly on the supplied files. This task
uses an installed spreadsheet library; a successful import in the analysis
script checks availability. The validated read supplies the data evidence.

1. Start the analysis script with the documented imports and the actual reader.
   Use given paths, sheets, header rows, encodings and delimiters. Inspect once
   only when a required source coordinate is unknown.
2. Validate each entire required population, then compute the requested controls
   on the loaded rows. Reuse the receipts and rows for all report fields.
3. Construct and save the requested JSON in that same execution, including its
   requested blocked shape when a control rejects. Print compact findings.

Finish when the requested report is saved and every applicable control has
passed or its blocker is reported. A successful import plus these receipts
completes availability/data checks. Extra package browsing or manual parser
reimplementation adds no control. Use a heredoc or a writable working directory.

For an unexpected API/import error, resolve the specific gap with the callable's
signature/docstring or that command's `--help` in one lookup, then use the actual
reader/validator. Inspect implementation for a reproducible contradiction or
package bug. Invalid input is a reportable blocker, not an API-discovery task.
All tools remain available for a justified investigation; preserve rejection.
Python imports `zalos_sheetops`; CLI: `sheetops`.

## Complete tables: read → profile → report

```python
import json
from pathlib import Path
from zalos_sheetops.io.read_table import read_table
from zalos_sheetops.io.read_csv_table import read_csv_table
from zalos_sheetops.io.read_csv_profile import read_csv_profile
from zalos_sheetops.validation.table_profile import profile_table
from zalos_sheetops.errors import SheetOpsError

try:
    # Choose one reader; None reads the entire population.
    table, receipt = read_csv_profile(Path("source.csv"), key_columns=["reference"],
        amount_columns=["amount"], encoding="auto", delimiter=",")
    # XLSX alternative: read_table(..., max_rows=None), then profile_table(...).
    # Adapt fields/shape to the requested report.
    result = {"row_count": receipt["rowCount"], "totals": receipt["totals"]}
except SheetOpsError as error:
    # Adapt this blocker to the user's requested JSON shape in this same run.
    result = {"status": "blocked", "errorCode": error.error_code, "details": error.details}
Path("result.json").write_text(json.dumps(result, ensure_ascii=False), encoding="utf-8")
print(json.dumps(result, ensure_ascii=False))
```

Readers return `headers` (stripped, lowercased, whitespace collapsed), `rows`, `rowCount`, `returnedRowCount`,
`truncated`. XLSX rows are string lists; CSV rows are dictionaries (CSV also adds
`sourceRows`, `encoding`, `delimiter`). Preserve IDs, amounts, whitespace and
multiline values. Use returned header names (e.g. `reference`, not `Reference`)
in key/amount lists. Resolve a missing column from the loaded `table["headers"]`.
Legacy encodings need an explicit codec; specify CSV delimiters.

`profile_table` checks the complete population, counts, rectangular rows,
nonblank text keys and finite bounded decimal-string amounts. It returns
`validated`, `rowCount`, exact `totals[column]`, and `columns[column]` containing
`first`/`last` (source order), `min`/`max` (lexical), `blankCount`.
Use the requested statistic and loaded rows for other fields. Previews
(`maxRows` CLI / `max_rows` Python) cannot validate totals.

**Report a rejection from its receipt.** `table_width_mismatch` detects populated
unheaded cells, including zero; `details["sheet"]` and `details["row"]` locate the
first offending source row. `invalid_table_values` gives `invalidRecordCount`,
`issueCount`, up to 20 `issues` with record/source-row locations, `issuesTruncated`.
Each issue includes `valueType`; strings add the original `valuePreview` (up to
80 characters) and `valueTruncated`. These are bounded examples; invalid-record
counts still cover the entire population. Use this evidence and the loaded rows
to explain the rejection; investigate another source read only for a concrete gap
or conflicting evidence.
Profile every whole source and collect all invalid-record counts before matching
or filtering. Investigate conflicting evidence or additional required controls;
a documented rejection alone needs no source-code audit. Never discard bad rows.
CLI: `ok: true, data` or `ok: false, errorCode, message, details` with nonzero exit.

For CSV source validation, `read_csv_profile(path, *, key_columns, amount_columns,
encoding="auto", delimiter=",", header_row=1)` returns `(complete_table, receipt)`.
It reuses the same complete-population/value checks, rejects row limits, and adds
`receipt.source`: actual path, SHA-256, headers, encoding, delimiter and row count.
Rejections preserve their codes/counts and include `details.sourceExcerpts` read
independently from original CSV text (up to 3 physical lines / 240 characters per
retained source row, with truncation flags). A changed source blocks the receipt.
Use this source evidence in the read/validate/report program; resolve additional
reads only for a concrete gap or conflicting evidence.

## Dates and bounded cell evidence

For dates, add `value_mode="typed"` to `read_table`: workbook epoch (1900/1904)
and date styles are applied; IDs/amounts retain source strings. Date cells become
datetime strings. With `from datetime import datetime`, a requested date-only
field is `datetime.fromisoformat(value).date().isoformat()`; otherwise keep time.
Raw mode returns serials; typed mode already resolves the epoch.
Typed XML table/cell receipts include `valueMode: "typed"` and `dateSystem:
"1900" | "1904"`. These identify the applied conversion; combine them with the
returned values/formats for date evidence. Investigate only a concrete conflict,
rather than opening workbook XML to redo a successful conversion.


For known coordinates, use this Python API in the same kernel as validation and
reporting. It returns the data dictionary directly (no CLI `ok`/`data` envelope):

```python
import hashlib
import json
from datetime import datetime, timedelta
from decimal import Decimal
from pathlib import Path
from openpyxl.styles.numbers import is_date_format
from openpyxl.utils.cell import get_column_letter, range_boundaries
from zalos_sheetops.io.read_cells import read_cells

# Bind actual sheet/range, column order, header labels and requested record count.
source, sheet, bounds = Path("source.xlsx"), "Data", "A1:C7"

From Zalos-io/zalos-sheetops, MIT.