Scripting Guide — `com.tjclp.xl.scripting`

The scripting prelude is the fastest way to use XL from a script, a REPL, or any JVM project that just wants to get an .xlsx in and out without ceremony. One import gives you the core API, the patch DSL, compile-time literals, formula evaluation, sync IO, streaming IO, smart value detection, and the .unsafe boundary.

This guide is for Maven/scala-cli users of the published library. If you are driving XL through Claude Code, the same material ships as the xl-scripting skill (plugin/skills/xl-scripting/SKILL.md).

Snippet convention (same as the skill): every fenced block that starts with //> using is a complete, standalone script — these are compile-verified in CI by scripts/verify-skill-snippets.sh. Blocks without the directive header are fragments.

One import

import com.tjclp.xl.scripting.{*, given}

{*, given} is required — plain * misses the given instances (codecs, conversions, display).
The prelude and the pure library import are mutually exclusive: never combine com.tjclp.xl.scripting.{*, given} with com.tjclp.xl.{*, given} in one file — the overlapping forwarders become ambiguous. import com.tjclp.xl.{*, given} remains the 100% pure alternative (no .unsafe, no sync Excel in scope) for library/production code.
java.time types are not re-exported; import java.time.LocalDate yourself when needed.

The canonical script header (byte-identical across the skill, recipes, and this guide — a release bump is a mechanical substitution):

//> using scala 3.8.3
//> using dep com.tjclp::xl:0.19.1
import com.tjclp.xl.scripting.{*, given}

val sheet = Sheet("Demo").put(ref"A1", "Hello").put(ref"B1", 42)
Excel.write(Workbook(sheet), "/tmp/demo.xlsx")
println(s"wrote ${sheet.cells.size} cells")

Run with scala-cli run script.sc. .sc files take top-level statements — no @main, no object wrapper.

Read → modify → write

The sync Excel facade (read/write/modify) is the IO edge for scripts. Everything between read and write is pure values.

//> using scala 3.8.3
//> using dep com.tjclp::xl:0.19.1
import com.tjclp.xl.scripting.{*, given}

val wb = Excel.read("input.xlsx")
val updated = wb
  .upsert("Audit", _.put(ref"A1", "reviewed")) // total: creates the sheet if missing
  .update("Data", _.put(ref"B2", 99))          // XLResult: "Data" must exist
  .unsafe                                      // ONE unwrap, at the edge
Excel.write(updated, "output.xlsx")

Workbook.upsert(name, f) is total update-or-create; Workbook.update(name, f) returns XLResult[Workbook] and fails if the sheet is absent. Pick by intent.
Excel.modify("file.xlsx")(f) does read → transform → write in place with atomic file replacement (no ZIP corruption on a crashed write).
Excel.write also accepts an XLResult[Workbook] directly.

Formulas are written with whatever cache they carry — recalculate first. A freshly built fx"…" cell has no cached value, so a plain Excel.write produces a file whose formulas show up blank in every cached-value consumer (openpyxl data_only, pandas, previewers, Excel before its first recalc). If the workbook contains formulas, write it with Excel.writeRecalculated (since 0.13.0) instead — one call recalculates and writes, returning the RecalcResult so failures are visible instead of silent:
val result = Excel.writeRecalculated(updated, "output.xlsx") // recalc → write → report
if !result.isClean then result.errors.foreach(e => println(e.render))
The file is written even when some formulas fail — errors are data conditions; failed cells stay uncached and Excel recalculates them on open. Overloads take an explicit Clock (deterministic TODAY/NOW), a Clock + Rng (reproducible RAND/RANDBETWEEN), or an XLResult[Workbook] directly. For full control (e.g. fail-hard pipelines), drop to wb.recalculate() and write result.workbook yourself — see Formulas: build, recalculate, inspect.

Compile-time vs runtime refs (and the `fx` rule)

Literal refs and formulas are validated at compile time — a typo fails the build, not the workbook:

val a = ref"A1"              // ARef
val rng = ref"A1:B10"        // CellRange
val f = fx"=SUM(A1:B10)"     // CellValue.Formula (syntax/parens checked at compile time)
val m = money"$$1,234.56"    // Formatted(Number, Currency)

$ is the interpolation character inside every interpolated literal, so Excel's absolute anchors need $$: write fx"=SUM($$A$$1:B10)" to get =SUM($A$1:B10). Same for money"$$1,234.56".

With runtime interpolation, validation moves to runtime and the macros return Either:

val row = 5
val cellE = ref"A$row"       // Either[XLError, RefType]
val formE = fx"=B$row*C$row" // Either[XLError, CellValue]
val cell2 = fx"=B$row*2".unsafe // explicit boundary when fail-fast is fine

The same split applies to Sheet(name) — and it is easier to trip over because there is no $ at the call site to warn you. A string literal validates at compile time and returns Sheet; a name held in a val makes the very same call return XLResult[Sheet], so a chained .put(...) suddenly type-errors:

val lit = Sheet("Acquisitions").put(ref"A1", 1) // : Sheet
val nm: String = config.sheetName
val dyn = Sheet(nm)                             // : XLResult[Sheet] — the return type changed!

For names computed at runtime, use Sheet.named (since 0.18.0) — the documented dynamic-name factory. Validation is identical (Excel's rules: non-empty, ≤31 chars, no : \ / ? * [ ]); the difference is that XLResult is spelled in the signature, so the .map/.unsafe step reads as intended instead of surprising the chain:

//> using scala 3.8.3
//> using dep com.tjclp::xl:0.19.1
import com.tjclp.xl.scripting.{*, given}

val region = Seq("North", "East").mkString(" ")                     // runtime name
val sheet = Sheet.named(region).map(_.put(ref"A1", "ready")).unsafe // XLResult, spelled out
Excel.write(Workbook(sheet), "/tmp/named.xlsx")

On ≤0.18.0 (no named), make the union explicit at the call site with an ascription: val s: XLResult[Sheet] = Sheet(nm).

Prefer total navigation over interpolated refs in loops — no Either at all:

val base = ref"A2"
base.down(2)     // A4   (default step is 1: base.down() == A3)
base.right(1)    // B2
base.up(1)       // A1
base.left(1)     // out of bounds! see below
base.shift(1, 2) // B4   (colOffset, rowOffset)

Navigation is total but unchecked at the sheet edges: ref"A1".up() produces the non-existent "A0", which corrupts output if written. Keep loop bounds inside your data extent.

For runtime column handles (since 0.13.0) — column-oriented builders that fold over letters computed at runtime — use Column.parse instead of special-casing macro literals; a runtime RefType also exposes .col (the cell's column, or the range's starting column):

Vector("C" -> 14.0, "D" -> 22.0).foldLeft(sheet) { case (s, (letter, w)) =>
  val col = Column.parse(letter).getOrElse(sys.error(s"bad column: $letter"))
  s.setColumnProperties(col, ColumnProperties(width = Some(w)))
}
Column.parse("D1")                      // Right(D) — trailing row digits tolerated
RefType.parse("Sales!C2:E9").map(_.col) // Right(C) — starting column of the range

Range fill and the patch DSL

Patches are pure values forming a monoid — build the whole change set with ++, apply once with sheet.put(patch):

val patch = (ref"A1" := "Report") ++ ref"A1:C1".merge ++ ref"A1".styled(CellStyle.default.bold)
val sheet2 = sheet.put(patch)

range := value fills every cell in the range with the value — Excel Ctrl+Enter semantics:

ref"E2:E100" := 0          // 99 Puts, one per cell
ref"A1" := "one cell"      // a 1x1 fill is a single Put

Fill cost is proportional to range size by design — ref"A:A" := 0 really creates 1,048,576 cells. Size fill ranges to your data.

Formulas: build, recalculate, inspect

wb.recalculate() is a total whole-workbook recalculation: every formula on every sheet evaluates in dependency order, cross-sheet references resolve automatically, and failures never throw — they are collected per cell.

//> using scala 3.8.3
//> using dep com.tjclp::xl:0.19.1
import com.tjclp.xl.scripting.{*, given}

val title = CellStyle.default.bold.size(14.0).center
val label = CellStyle.default.bold.indent(1)
val currencyStyle = CellStyle.default.currency
val totalRow = CellStyle.default.currency.bold.borderTop(BorderStyle.Thin)

val model = Sheet("Model").put(
  (ref"B1" := "FY2026 Plan") ++ ref"B1:C1".merge ++ ref"B1".styled(title) ++
    (ref"B3" := "Revenue") ++ (ref"C3" := 1200000) ++
    (ref"B4" := "Costs") ++ (ref"C4" := fx"=C3*0.62") ++
    (ref"B5" := "Profit") ++ (ref"C5" := fx"=C3-C4") ++
    ref"B3:B5".styled(label) ++ ref"C3:C5".styled(currencyStyle) ++
    ref"C5".styled(totalRow) ++ ref"B3:C5".outlined(BorderStyle.Medium)
)

Workbook(model).recalculate().toEither match
  case Right(wb) =>
    Excel.write(wb, "/tmp/plan.xlsx")
    given Sheet = wb.sheets.headOption.getOrElse(sys.exit(1))
    println(excel"Profit: ${ref"C5"}") // displays through NumFmt: $456,000.00
  case Left(errors) =>
    errors.foreach(e => println(s"✗ ${e.render}"))
    sys.exit(1)

Since 0.12.1, conditional formatting is typed: sheet.conditionalFormat(range, CfRule.cellIs(...)) authors cellIs/expression/colorScale/dataBar/top10 rules with Dxf differential formats; structural edits shift rule ranges, and rule families xl does not model survive round-trips byte-faithfully.

Since 0.12.0 the prelude also exposes the drawing layer: sheet.addImage(bytes, format, at) embeds pictures (7 formats, natural-size PNG/JPEG sniffing) and com.tjclp.xl.charts.Chart authors bar/line/pie charts anchored to ranges — both round-trip through OOXML with unmodeled content preserved byte-faithfully.

Since 0.11.2, formulas may use LET (lexical bindings), INDIRECT (dynamic references — evaluated in a deferred last partition), and RAND/RANDBETWEEN. Randomness is an explicit capability: pass Rng.seeded(42L) to the rng-taking overloads (wb.recalculate(clock, rng), sheet.evaluateFormula(f, clock, rng)) for reproducible runs; the default is Rng.system. For Excel-style format inheritance on formula entry, use the opt-in sheet.putFormulaInheriting(ref, formula).

recalculate(clock: Clock = Clock.system) returns a RecalcResult:

Member	Meaning
`workbook`	The workbook with every successful formula cached (`Formula(expr, Some(value))`)
`evaluated`	`Map[SheetName, Map[ARef, CellValue]]` — computed values for inspection
`errors`	`Vector[CellEvalError]` — per-cell host failures (parse errors, missing sheets, cycle participants, cells blocked by a cycle). Since 0.14.0, Excel error values (`#DIV/0!`, `#N/A`, …) are results, not failures — they cache like any value and do not appear here
`excelErrors`	(0.14.0) `Vector[(SheetName, ARef, CellError)]` — cells whose cached result is an Excel error value, sorted; inspect when you want to surface `#DIV/0!`s without treating them as host failures
`isClean`	`true` when `errors.isEmpty` — a workbook full of cached `#DIV/0!`s is "clean" (the recalculation succeeded; the errors are data)
`toEither`	`Right(workbook)` when clean, `Left(errors)` otherwise — for fail-hard pipelines
`converged`	(0.20.0) `cycles.forall(_.converged)` — `false` iff some cyclic component exhausted `maxIter` without every member's \|Δ\| dropping below `maxChange`. The last-round values are kept (Excel semantics, `errors` stays empty), so gate on this after any large circular perturbation. Non-iterative runs report `true`
`iterationsUsed`	(0.20.0) rounds run by the WORST component: `0` when no iteration happened, `maxIter` when any component exhausted, otherwise the round it converged on
`cycles`	(0.20.0) `Vector[SccReport]` — one verdict per cyclic strongly-connected component actually iterated (`members`, `converged`, `rounds`, `maxDelta`, plus `render`), sorted by the component's minimum member. Empty on non-iterative and acyclic runs
`unconverged`	(0.20.0) `cycles.filterNot(_.converged)` — the offenders to name in a report
`certified`	(0.20.0) `errors.isEmpty && converged` — the single gate meaning "this workbook is at its global fixpoint"

Reference cycles are isolated: the participants and their downstream dependents are reported (e.g. Model!A7: Formula error in '=B7': Circular reference via CellEvalError.render) while the acyclic remainder still evaluates and caches.

Since 0.13.0, circular models are opt-in rather than always errors: pass an IterativeCalc to fixpoint declared cycles instead — wb.recalculate(IterativeCalc(maxIter = 100, maxChange = BigDecimal("0.001"))) runs Jacobi iteration (each member reads previous-iteration values until every |Δ| < maxChange or maxIter rounds; non-convergence keeps the last values with no error, per Excel). Plain recalculate() still isolates cycles. Honor a file's own settings with wb.metadata.calcPr.filter(_.iterativeCalculation).map(IterativeCalc.fromCalcPr), and author them on scratch builds with wb.withCalcPr(CalcPr(iterativeCalculation = true, maxIterations = Some(100), maxChange = Some(BigDecimal("0.001")))) (emits <calcPr iterate iterateCount iterateDelta/>).

Since 0.20.0 an iterative recalculation walks the SCC condensation of the workbook graph once in dependency-first order — a run of acyclic cells evaluates, then each cyclic component fixpoints against those freshly computed values, and so on. Consequences worth knowing: maxIter/maxChange are per component (one permanently-oscillating cycle no longer burns an unrelated cycle's budget, and cycles names the offender); one pass reaches the workbook's global fixpoint, so recalculate(IterativeCalc) is idempotent on a converged book and re-solving a cached circular book is safe (it was not before 0.20.0); and one iterative recalculation is one volatile generation — TODAY()/NOW() agree inside the fixpoints and in the acyclic cells between them. Dynamic (INDIRECT/OFFSET) cycles are still invisible to Tarjan and are not covered by converged.

Also since 0.20.0, cycle members warm-start from their loaded cached number (0 for every other shape, and as the fallback), matching Excel — a book already at its fixpoint re-solves to itself in one round instead of being driven back through the 0-seed transient. Pass IterativeCalc(maxIter, maxChange, seedFromCaches = false) for a cold start when a book's numeric caches are known to be poisoned. Two consequences to keep in mind: for a circular book, recalculate(wb) and recalculate(wb with caches stripped) are no longer guaranteed to agree on a nonlinear cycle with several fixpoints; and a member whose cache is a stale error or text value seeds 0 rather than itself, so such a cycle still heals (seeding it would wedge the cycle at its own poison, since arithmetic propagates both shapes unchanged).

Also since 0.13.0, defined names resolve in formulas: =IF(case=2,…), =entry_mult*ltm_ebitda, and =SUM(rev_range) evaluate against workbook- and sheet-scoped names (sheet-scoped shadows global), contribute dependency edges so recalculate() orders name-gated families correctly, and round-trip byte-faithfully; unresolvable names are clean per-cell errors.

When the very next step is a write, Excel.writeRecalculated(wb, path) (since 0.13.0) fuses the two — recalculate, write the cached workbook (even on partial failure), return the same RecalcResult. Use the explicit recalculate().toEither pattern above when a dirty result must abort before anything lands on disk.

For one-off questions, wb.evaluateFormula("=SUM(Data!A1:A9)", "Summary") returns XLResult[CellValue] with cross-sheet context wired automatically (108 functions supported — see the skill API reference for the full list).

Typed extraction

sheet.readTyped[BigDecimal](ref"C2")  // Either[CodecError, Option[BigDecimal]]
sheet.readTypedOr[Int](ref"B2", 0)    // total, with default
sheet.readTypedOpt[String](ref"A2")   // flat Option — mismatch and empty both None

Nine codec types: String, Int, Long, Double, BigDecimal, Boolean, LocalDate, LocalDateTime, RichText. Use readTyped when you must distinguish a type mismatch from an empty cell; readTypedOr/readTypedOpt when you just need a value.

Smart value detection

FormattedParsers.detect (available everywhere) turns a raw string into a value + number format; the prelude adds String.toFormatted sugar:

sheet.put(ref"C1", "$1,234.56".toFormatted) // Number(1234.56) + Currency format
"45.5%".toFormatted                          // Number(0.455) + Percent
"2026-01-15".toFormatted                     // DateTime + Date format
"plain text".toFormatted                     // Text (detection is total — never fails)

Styling quick hits

CellStyle.default.bold.italic.underline.size(12.0).fontFamily("Arial")
CellStyle.default.center.middle.wrap.indent(2)      // alignment (+ Align indent)
CellStyle.default.red.bgGray                        // font / background color
CellStyle.default.currency                          // named formats: .percent .decimal .dateFormat .dateTime
CellStyle.default.withNumFmt(NumFmt.Custom("0.0x")) // any Excel format code
CellStyle.default.bordered                          // thin border, all sides
CellStyle.default.borderTop(BorderStyle.Thin)       // per-side: borderBottom/borderLeft/borderRight, color overloads
ref"B3:F9".outlined(BorderStyle.Medium)             // outline the range edges only (banker box)

range.outlined is edge-correct (corners get both sides, interior cells untouched) and merges into existing borders at apply time, preserving each cell's font/fill/format.

Print and view setup

SheetView (gridlines, zoom) and PageSetup (orientation, fit, margins, header/footer, print area, repeat rows) live in com.tjclp.xl.sheets and are not part of the prelude export — import them explicitly:

//> using scala 3.8.3
//> using dep com.tjclp::xl:0.19.1
import com.tjclp.xl.scripting.{*, given}
import com.tjclp.xl.sheets.{HeaderFooter, PageMargins, PageSetup, SheetView}

val report = Sheet("Report")
  .put(ref"A1", "Quarterly Report")
  .withViewSettings(SheetView(showGridLines = false, zoomScale = Some(90)))
  .withPageSetup(
    PageSetup(
      orientation = Some("landscape"),
      fitToWidth = Some(1),
      // 0.11.1+: HeaderFooter also takes evenHeader/evenFooter/firstHeader/firstFooter
      // with differentOddEven/differentFirst; fitToWidth/Height emit the fitToPage flag
      headerFooter = Some(HeaderFooter(oddFooter = Some("&LACME Corp&RPage &P of &N"))),
      margins = Some(PageMargins(left = 0.5, right = 0.5)),
      printArea = Some(ref"A1:H40"),     // _xlnm.Print_Area defined name
      repeatRows = Some((1, 2))          // rows 1-2 repeat on every printed page
    )
  )

Excel.write(Workbook(report), "/tmp/report.xlsx")
println("wrote print-ready report")

Header/footer strings use Excel's codes: &P page number, &N total pages, &D date, &F file name, &A sheet name, with &L/&C/&R section markers.

The `.unsafe` boundary

Everything fallible returns XLResult[A] (= Either[XLError, A]). The prelude sanctions exactly one unwrap style — .unsafe, which throws a structured XLException wrapping the XLError:

val wb2 = wb.update("Sales", _.put(ref"A1", "x")).unsafe // fail-fast script style

Use it once, at the edge — compose with for-comprehensions in between, or lean on the total APIs (literal refs, upsert, range fill, readTypedOr, recalculate) so there is nothing to unwrap.

`Excel` vs `ExcelIO`

	`Excel` (sync facade)	`ExcelIO` (cats-effect)
Style	`Excel.read("in.xlsx")` returns `Workbook`, throws at the IO edge	`ExcelIO.instance[IO].read(path)` returns `IO[Workbook]`
For	Scripts, REPL, quick tools	Production services, streaming, resource safety
Streaming	—	`readStream`/`writeStream`: `fs2.Stream[F, RowData]`, O(1) memory

Both are in scope from the prelude. Switch to streaming above ~100k rows — Excel.read loads the whole workbook:

//> using scala 3.8.3
//> using dep com.tjclp::xl:0.19.1
import com.tjclp.xl.scripting.{*, given}
import cats.effect.IO
import cats.effect.unsafe.implicits.global
import java.nio.file.Paths

val excel = ExcelIO.instance[IO]
val total = excel
  .readStream(Paths.get("huge.xlsx")) // fs2.Stream[IO, RowData], O(1) memory
  .map(_.cells.get(2))                // column C (0-based)
  .collect { case Some(CellValue.Number(n)) => n }
  .compile
  .fold(BigDecimal(0))(_ + _)
  .unsafeRunSync()
println(s"column C total: $total")

Streaming writes: Stream.emits(rows).through(excel.writeStream(path, "Sheet1")) with RowData(rowIndex, Map(colIdx -> CellValue)) (1-based rows, 0-based columns).

Going further

examples/scripting_tour.sc — the canonical runnable tour of everything above; examples/README.md catalogs all example scripts.
plugin/skills/xl-scripting/reference/RECIPES.md — seven complete scripts: bulk transform, typed extraction + validation, model build, workbook merge, streaming filter, cell-level diff, CSV ingest.
QUICK-START.md — the pure-library path (com.tjclp.xl.{*, given}).

Navigation Menu

Uh oh!

Scripting Guide — `com.tjclp.xl.scripting`

One import

Read → modify → write

Compile-time vs runtime refs (and the `fx` rule)

Range fill and the patch DSL

Formulas: build, recalculate, inspect

Typed extraction

Smart value detection

Styling quick hits

Print and view setup

The `.unsafe` boundary

`Excel` vs `ExcelIO`

Going further

Uh oh!

FilesExpand file tree

scripting.md

Latest commit

History

scripting.md

File metadata and controls

Scripting Guide — com.tjclp.xl.scripting

One import

Read → modify → write

Compile-time vs runtime refs (and the fx rule)

Range fill and the patch DSL

Formulas: build, recalculate, inspect

Typed extraction

Smart value detection

Styling quick hits

Print and view setup

The .unsafe boundary

Excel vs ExcelIO

Going further

Scripting Guide — `com.tjclp.xl.scripting`

Compile-time vs runtime refs (and the `fx` rule)

The `.unsafe` boundary

`Excel` vs `ExcelIO`