Worksheet

Pin a cell to the Watch Window. Returns the pushed entry.

function addCellWatch(ws: Worksheet, watch: CellWatch): CellWatch

Append a conditional formatting block.

function addConditionalFormatting(ws: Worksheet, cf: ConditionalFormatting): ConditionalFormatting

Append a DataValidation entry.

function addDataValidation(ws: Worksheet, dv: DataValidation): DataValidation

Append an ignored-error region.

function addIgnoredError(ws: Worksheet, ie: IgnoredError): IgnoredError

Append a table. The id and displayName must be workbook-unique — the caller is responsible.

function addTable(ws: Worksheet, table: TableDefinition): TableDefinition

Append a row of values starting at the next empty row. Returns the row index (1-based). Mirrors openpyxl's `Worksheet.append`. `null` / `undefined` entries leave the cell empty.

function appendRow(ws: Worksheet, values: readonly (CellValue | undefined)[]): number

Bulk version of appendRow: append a 2D array of values one row at a time. Returns `{firstRow, lastRow}` — both 1-based, inclusive. An empty input returns `{firstRow, lastRow: firstRow - 1}` so callers can detect the no-op without throwing. Common usage: `appendRows(ws, csvParsedRows)` for fast import.

function appendRows(ws: Worksheet, rows: readonly readonly (CellValue | undefined)[][]): { firstRow: number; lastRow: number }

Iterate over every cell coordinate in a range, calling `visit` once per (row, col). Allocates the cell on first touch so callers can mutate it freely.

function applyToRange(ws: Worksheet, range: string, visit: (cell: Cell, row: number, col: number) => void): void

Approximate autofit for every column with at least one populated cell. Walks the worksheet once collecting per-column widest-length + applies autofitColumn per column. `opts.workbook` enables font-size-aware scaling; without it the helper falls back to plain string length.

function autofitColumns(ws: Worksheet, opts?: { max?: number; min?: number; padding?: number; workbook?: { styles: { cellXfs: readonly { fontId: number }[]; fonts: readonly { size?: number }[] } } }): void

Wipe every populated cell on the worksheet, leaving styles, dimensions, merges, comments, hyperlinks etc. intact. Returns the count of cells removed. Useful when a sheet should be re-filled from scratch but its formatting kept.

function clearAllCells(ws: Worksheet): number

Delete every populated cell inside a range. Returns the number of cells removed. Row maps that go empty are pruned. Column / row dimensions, merges, comments etc. are left untouched.

function clearRange(ws: Worksheet, range: string): number

Collapse a column outline group: hide + mark `collapsed: true` for every column in `[fromCol, toCol]`. Columns must already carry an `outlineLevel` from groupColumns for the collapse to render correctly.

function collapseColumnGroup(ws: Worksheet, fromCol: number, toCol: number): void

Collapse a row outline group: hide every row in `[fromRow, toRow]` and mark them `collapsed: true`. Mirrors Excel's `−` button on a grouped row strip. Rows must already carry an `outlineLevel` from groupRows for the collapse to render correctly.

function collapseRowGroup(ws: Worksheet, fromRow: number, toRow: number): void

Copy every populated cell from `source` to `target` (within the same worksheet, or across worksheets via `targetWs`). Cells are shallow-cloned: `value` and `styleId` carry over but `row`/`col` are rewritten. The target's existing cells in the destination extent are overwritten; cells outside are untouched. The source and target ranges define the top-left corner — their dimensions need not match. If the target range is smaller than the source, only the cells that fit within the target's extent are copied; if larger, only the source's extent is filled. Returns the number of cells copied.

function copyRange(ws: Worksheet, source: string, target: string, opts?: { targetWs?: Worksheet }): number

Total populated cell count.

function countCells(ws: Worksheet): number

Delete a single cell from the sheet. Empty rows are pruned.

function deleteCell(ws: Worksheet, row: number, col: number): void

Expand a column outline group: drop `hidden` and `collapsed` from every column in `[fromCol, toCol]`. Leaves `outlineLevel` intact.

function expandColumnGroup(ws: Worksheet, fromCol: number, toCol: number): void

Expand a row outline group: drop the `hidden` and `collapsed` flags on every row in `[fromRow, toRow]`. Leaves `outlineLevel` and other dimensions intact.

function expandRowGroup(ws: Worksheet, fromRow: number, toRow: number): void

Iterate every populated cell, yielding those for which `predicate` returns true. Iteration order is row-then-column ascending. Cells whose `.value === null` (empty placeholders carrying only style or comment metadata) are still visited.

function findCells(ws: Worksheet, predicate: (c: Cell) => boolean): IterableIterator<Cell>

Freeze both top `rows` rows AND left `cols` columns.

function freezePanes(ws: Worksheet, rows: number, cols: number): void

Read the current AutoFilter, if any.

function getAutoFilter(ws: Worksheet): AutoFilter | undefined

Resolve a 1-based or "A1" coordinate; returns the populated Cell or undefined.

function getCell(ws: Worksheet, row: number, col: number): Cell | undefined

Convenience getter accepting an "A1" coordinate.

function getCellByCoord(ws: Worksheet, coord: string): Cell | undefined

Enumerate the populated cells of a column in row order. Walks the row map and collects whichever rows carry the column. Returns `[]` when the worksheet has no cell in that column.

function getCellsInColumn(ws: Worksheet, col: number): Cell[]

Iterate the populated cells inside a rectangular range. Cells that don't exist in the sparse store are skipped (no auto-allocate). Use applyToRange when you need every coordinate visited regardless of population.

function getCellsInRange(ws: Worksheet, range: string): IterableIterator<Cell>

Enumerate the populated cells of a row in column order. Unlike getRowValues, this skips empty columns and yields the cell objects (not just their values). Returns `[]` when the row is absent or empty.

function getCellsInRow(ws: Worksheet, row: number): Cell[]

Look up the ColumnDimension covering `col`. The search walks every registered entry's `min..max` range; that's fine for the typical spreadsheet (a handful of column entries) and stays simple.

function getColumnDimension(ws: Worksheet, col: number): ColumnDimension | undefined

Bounding-box of the populated cells: `{ minRow, maxRow, minCol, maxCol }` covering every cell in `ws.rows`. Returns `undefined` when the sheet is empty. Walks the sparse store once.

function getDataExtent(ws: Worksheet): { maxCol: number; maxRow: number; minCol: number; minRow: number } | undefined

Inverse of setFreezePanes; returns the top-left ref or undefined when no freeze is active.

function getFreezePanes(ws: Worksheet): string | undefined

Effective max column index based on populated cells (0 when empty).

function getMaxCol(ws: Worksheet): number

Effective max row index based on populated cells (0 when empty).

function getMaxRow(ws: Worksheet): number

Read-only iterator over the worksheet's merged ranges.

function getMergedCells(ws: Worksheet): readonly CellRangeBoundaries[]

Look up the merged range covering (row, col), or `undefined` if the coordinate isn't inside any merge. Lets callers introspect a merge without iterating `getMergedCells` themselves.

function getMergedRangeAt(ws: Worksheet, row: number, col: number): CellRangeBoundaries | undefined

Count non-empty cells. Distinct from countCells which counts every materialised cell (including ones whose `value === null`): this skips cells with a `null` value, plus optionally formulas / rich-text per the opts. Useful for "how many real values does this sheet contain" stats vs the materialised footprint.

function getNonEmptyCellCount(ws: Worksheet, opts?: { includeFormulas?: boolean; includeRichText?: boolean }): number

Sorted list of every column index that holds at least one populated cell anywhere on the sheet. Distinct columns only; returns `[]` for an empty worksheet.

function getPopulatedColumnIndices(ws: Worksheet): number[]

Sorted list of every row index that holds at least one populated cell. Returns `[]` for an empty worksheet. Useful when a caller wants to iterate only the rows the user actually populated, without walking 1..maxRow in dense fashion.

function getPopulatedRowIndices(ws: Worksheet): number[]

Read a rectangular range as a dense 2-D array of values. Empty cells yield `null`. The shape is `[maxRow - minRow + 1] × [maxCol - minCol + 1]`. Inverse of setRangeValues.

function getRangeValues(ws: Worksheet, range: string): CellValue[][]

Look up a row's dimension entry.

function getRowDimension(ws: Worksheet, row: number): RowDimension | undefined

Look up a table by displayName.

function getTable(ws: Worksheet, displayName: string): TableDefinition | undefined

Mirror Excel's "Data → Group → Columns" by stamping every column in `[fromCol, toCol]` with an outline depth of `level` (default 1).

function groupColumns(ws: Worksheet, fromCol: number, toCol: number, level?: number): void

Mirror Excel's "Data → Group → Rows" by stamping every row in `[fromRow, toRow]` with an outline depth of `level` (default 1). Allocates a RowDimension for each row that doesn't already have one. Ungroup with ungroupRows.

function groupRows(ws: Worksheet, fromRow: number, toRow: number, level?: number): void

Convenience: hide a column.

function hideColumn(ws: Worksheet, col: number): ColumnDimension

Bulk-hide every column in `[fromCol, toCol]`.

function hideColumns(ws: Worksheet, fromCol: number, toCol: number): void

Convenience: hide a row.

function hideRow(ws: Worksheet, row: number): RowDimension

Bulk-hide every row in `[fromRow, toRow]`.

function hideRows(ws: Worksheet, fromRow: number, toRow: number): void

True iff the worksheet has zero non-empty cells. Equivalent to `getNonEmptyCellCount(ws) === 0` but short-circuits on the first non-null value found, so the cost is O(first non-empty cell) rather than O(populated cells).

function isWorksheetEmpty(ws: Worksheet): boolean

Yield every populated cell in the worksheet as a flat stream (row-major, columns ascending). Distinct from iterRows which yields one row per row in the bounding box — use this when the caller wants only the populated cells without row boundaries or rectangular padding.

function iterCells(ws: Worksheet, opts?: IterRowsOptions): IterableIterator<Cell>

Iterate the worksheet rows rectangularly. Yields one row per row in `[minRow, maxRow]` — including entirely empty rows — and each yielded row has length `maxCol - minCol + 1`. Missing cell positions are `undefined` (no placeholder Cell allocations). Defaults: `minRow=1`, `maxRow=getMaxRow(ws)`, `minCol=1`, `maxCol=getMaxCol(ws)`. The default extent is the populated bounding box, not the 1M × 16K sheet limit, so the rectangular default doesn't iterate the whole grid for a small sheet. To iterate populated rows only, filter: `[...iterRows(ws)].filter(row => row.some((c) => c !== undefined))`. To iterate populated cells without row boundaries, use iterCells.

function iterRows(ws: Worksheet, opts?: IterRowsOptions): IterableIterator<(Cell | undefined)[]>

Same rectangular iteration as iterRows, but yields each cell's `.value`. Missing cell positions become `null` — already the canonical empty marker in `CellValue`.

function iterValues(ws: Worksheet, opts?: IterRowsOptions): IterableIterator<CellValue[]>

Read-only snapshot of every legacy comment on the sheet.

function listComments(ws: Worksheet): readonly LegacyComment[]

Read-only snapshot of every data validation block on the sheet.

function listDataValidations(ws: Worksheet): readonly DataValidation[]

Read-only snapshot of every hyperlink on the sheet.

function listHyperlinks(ws: Worksheet): readonly Hyperlink[]

Read-only snapshot of every Excel table defined on the sheet.

function listTables(ws: Worksheet): readonly TableDefinition[]

Build a Worksheet shell.

function makeWorksheet(title: string): Worksheet

Merge a range. The top-left cell keeps its value; every other cell in the range is dropped from `ws.rows` so the on-wire `<sheetData>` won't carry phantom cells underneath the merge. Mirrors openpyxl's `MergedCellRange.format()`. Idempotent for an identical range, throws when the range overlaps an existing merge.

function mergeCells(ws: Worksheet, refOrRange: string | CellRangeBoundaries): CellRangeBoundaries

Move every populated cell from `source` to `target`. Equivalent to `copyRange` followed by clearing the source. When the ranges overlap on the same sheet, the copy walks in the direction that preserves data — high-to-low along any axis where the move shifts forward, low-to-high otherwise — so cells aren't overwritten before they've been read. Returns the number of cells moved.

function moveRange(ws: Worksheet, source: string, target: string, opts?: { targetWs?: Worksheet }): number

Drop every legacy comment on the worksheet. Returns the count removed.

function removeAllComments(ws: Worksheet): number

Drop every conditional-formatting block on the worksheet. Returns the count removed.

function removeAllConditionalFormatting(ws: Worksheet): number

Drop every data validation block on the worksheet. Returns the count removed.

function removeAllDataValidations(ws: Worksheet): number

Drop every hyperlink on the worksheet. Returns the count removed.

function removeAllHyperlinks(ws: Worksheet): number

Drop every merged range on the worksheet. Returns the count of merges removed. Cells that were inside the merges keep their values — only the merge metadata is gone.

function removeAllMergedRanges(ws: Worksheet): number

Drop every Excel table on the worksheet. Returns the count removed.

function removeAllTables(ws: Worksheet): number

Remove cell watches matching `predicate`. Returns the count removed.

function removeCellWatches(ws: Worksheet, predicate: (w: CellWatch) => boolean): number

Drop every validation whose sqref overlaps `ref` (string parse). Returns count removed.

function removeDataValidations(ws: Worksheet, predicate: (dv: DataValidation) => boolean): number

Remove the hyperlink registered against `ref`. Returns true if anything was removed.

function removeHyperlink(ws: Worksheet, ref: string): boolean

Remove ignored-error entries matching `predicate`. Returns the count removed.

function removeIgnoredErrors(ws: Worksheet, predicate: (ie: IgnoredError) => boolean): number

Drop a table by displayName. Returns true when something was removed.

function removeTable(ws: Worksheet, displayName: string): boolean

Set or replace the worksheet's AutoFilter. Pass `undefined` to clear.

function setAutoFilter(ws: Worksheet, filter: AutoFilter | undefined): void

Create or update a Cell at (row, col). Existing cells keep their styleId / hyperlinkId / commentId unless explicitly overridden.

function setCell(ws: Worksheet, row: number, col: number, value?: CellValue, styleId?: number): Cell

Resolve an "A1" coordinate to a numeric (col, row) pair on the sheet.

function setCellByCoord(ws: Worksheet, coord: string, value?: CellValue, styleId?: number): Cell

Set a single-column ColumnDimension entry covering `col`. Shadows any existing run that overlaps — runs are not split for now (callers that need range-spanning entries can write directly into `ws.columnDimensions`).

function setColumnDimension(ws: Worksheet, col: number, opts: Partial<Omit<ColumnDimension, "min" | "max">>): ColumnDimension

Convenience: set a column's width, leaving other fields untouched.

function setColumnWidth(ws: Worksheet, col: number, width: number): ColumnDimension

Set widths for many columns in one call. `widths` maps either: - an array `[12, 16, 20]` interpreted positionally starting at column `startCol` (default 1), or - a `Record<number, number>` keyed by 1-based column index. Each entry sets `customWidth: true`.

function setColumnWidths(ws: Worksheet, widths: readonly number[] | Record<number, number>, startCol?: number): void

Add or replace the comment at `ref`.

function setComment(ws: Worksheet, opts: { author: string; ref: string; text: string }): LegacyComment

Set the default column width (characters) for cells without an explicit ColumnDimension entry. Mirrors Excel's "Default Width" dialog. Pass `undefined` to clear.

function setDefaultColumnWidth(ws: Worksheet, width: number | undefined): void

Set the default row height (points) for rows without an explicit RowDimension entry. Mirrors Excel's "Default Row Height" dialog. Pass `undefined` to clear.

function setDefaultRowHeight(ws: Worksheet, height: number | undefined): void

Freeze rows / columns above + left of `topLeftRef` ("B2" → 1 row + 1 col). Pass `undefined` to clear any existing freeze. Targets the workbook's primary SheetView (`ws.views[0]`); creates one if absent.

function setFreezePanes(ws: Worksheet, topLeftRef: string | undefined): void

Replace any prior hyperlink on the same `ref` with the given options. Pass `{ target }` for an external URL, `{ location }` for an internal jump, or both. Returns the resulting Hyperlink record.

function setHyperlink(ws: Worksheet, ref: string, opts: { display?: string; location?: string; target?: string; tooltip?: string }): Hyperlink

Set values across a rectangular range from a 2-D array. `rows[0]` is laid down starting at the top-left of `range`; subsequent rows follow. `null` / `undefined` entries skip the cell. Useful for dropping a header + data block in one call.

function setRangeValues(ws: Worksheet, range: string, rows: readonly readonly (CellValue | undefined)[][]): void

Convenience: set a row's height, marking customHeight=true.

function setRowHeight(ws: Worksheet, row: number, height: number): RowDimension

Set heights for many rows in one call. `heights` accepts an array (positional from `startRow`, default 1) or a `Record<number, number>` keyed by 1-based row index. Each entry sets `customHeight: true`.

function setRowHeights(ws: Worksheet, heights: readonly number[] | Record<number, number>, startRow?: number): void

Set the sheet tab strip colour. Accepts either a hex string (`"FF0070C0"`) or a partial `Color` object (`{ theme: 4, tint: 0.4 }`).

function setSheetTabColor(ws: Worksheet, color: string | Partial<Color>): Color

Switch the sheet view between Excel's "Normal" / "Page Break Preview" / "Page Layout" modes.

function setSheetViewMode(ws: Worksheet, mode: "normal" | "pageBreakPreview" | "pageLayout"): void

Set the zoom scale (percent) on the primary SheetView. Excel accepts integer percentages in `[10, 400]`.

function setSheetZoom(ws: Worksheet, scale: number): void

Drop the outline grouping for every column in `[fromCol, toCol]`. Removes the `outlineLevel` field from each affected ColumnDimension.

function ungroupColumns(ws: Worksheet, fromCol: number, toCol: number): void

Drop the outline grouping for every row in `[fromRow, toRow]`. Removes the `outlineLevel` field from each affected RowDimension.

function ungroupRows(ws: Worksheet, fromRow: number, toRow: number): void

Convenience: unhide a column. Drops the `hidden` flag from the column's dimension entry (and removes the entry altogether when no other fields remain).

function unhideColumn(ws: Worksheet, col: number): void

Bulk-unhide every column in `[fromCol, toCol]`.

function unhideColumns(ws: Worksheet, fromCol: number, toCol: number): void

Convenience: unhide a row. Drops the `hidden` flag from the row's dimension entry (and removes the entry altogether when no other fields remain).

function unhideRow(ws: Worksheet, row: number): void

Bulk-unhide every row in `[fromRow, toRow]`.

function unhideRows(ws: Worksheet, fromRow: number, toRow: number): void

Drop a previously-merged range. No-op if the range isn't registered.

function unmergeCells(ws: Worksheet, refOrRange: string | CellRangeBoundaries): boolean

Drop the merge that contains (row, col), if any. Returns `true` when a merge was unregistered. Useful when callers know a cell coordinate but not the original merge bounds.

function unmergeCellsAt(ws: Worksheet, row: number, col: number): boolean

Write a 2D array of values to the sheet starting at the given A1 anchor cell. Distinct from appendRows (which always writes past `_appendRowCursor`) — this lets you place a block at an arbitrary location, e.g. mid-sheet table updates. `null` / `undefined` entries leave the corresponding cell **untouched** (existing cell + style are preserved). Pre-existing cells inside the written rectangle are overwritten in place, so their `styleId` survives the write. Returns the bounding-box of the written area as 1-based inclusive coordinates. An empty rows array returns `undefined` rather than an invalid zero-area range.

function writeRange(ws: Worksheet, startRef: string, values: readonly readonly (CellValue | undefined)[][]): { maxCol: number; maxRow: number; minCol: number; minRow: number } | undefined

Build a header / footer string from optional left / center / right fragments using Excel's `&L` / `&C` / `&R` markers. An empty fragment is omitted (no marker emitted) so a center-only header doesn't leave a stray `&L` prefix. Returns `''` when all three fragments are undefined.

function buildHeaderFooterText(parts: { center?: string; left?: string; right?: string }): string

Excel's reserved header / footer code tokens. Drop these into the left / center / right text inputs of buildHeaderFooterText (or directly into a setHeader / setFooter string) to render dynamic values at print time.

const HEADER_FOOTER_CODES: Readonly<{ date: "&D"; fileName: "&F"; filePath: "&Z&F"; pageCount: "&N"; pageNumber: "&P"; picture: "&G"; sheetName: "&A"; time: "&T" }>

Inverse of makeFreezePane. Returns the top-left ref of the bottomRight pane, or undefined.

function freezePaneRef(view: SheetView): string | undefined

Build a frozen Pane from a top-left coordinate. Per Excel semantics: - "B2" → freeze 1 row + 1 col → xSplit=1, ySplit=1, activePane='bottomRight' - "A2" → freeze 1 row only → ySplit=1, activePane='bottomLeft' - "B1" → freeze 1 col only → xSplit=1, activePane='topRight' - "A1" → no freeze; throws (caller should clear `ws.views[].pane`).

function makeFreezePane(topLeftRef: string): Pane

Build a SheetView with sensible defaults.

function makeSheetView(opts?: Partial<SheetView>): SheetView

Build a single-column ColumnDimension entry covering `col`.

function makeColumnDimension(col: number, opts?: Partial<Omit<ColumnDimension, "min" | "max">>): ColumnDimension