Worksheet

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

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

Parameters

Returns

CellWatch

Append a conditional formatting block.

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

Parameters

Returns

ConditionalFormatting

Append a DataValidation entry.

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

Parameters

Returns

DataValidation

Append an ignored-error region.

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

Parameters

Returns

IgnoredError

Append a table, rejecting one whose geometry or column names disagree with the cells under it: the column count has to match the width of ref, ref has to contain the header and totals rows, column names have to be unique and non-empty, and every header cell has to hold its column's name as text. The id and displayName must be workbook-unique, and stay the caller's responsibility: neither is visible from a single sheet.

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

Parameters

Returns

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 unless opts.styleIds names a style for that column.

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

Parameters

Returns

number

Bulk version of appendRow: append a 2D array of values one row at a time. Returns {firstRow, lastRow}, both 1-based and 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.

opts is column-indexed, not row-indexed: the same AppendRowOptions.styleIds apply to every row, which is the point when a column has one format down the whole table. Rows shorter than styleIds still get the trailing styled blanks described there, so trim per row when the input is ragged.

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

Parameters

Returns

{ 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: RangeRef, visit: (cell: Cell, row: number, col: number) => void): void

Parameters

Returns

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

Parameters

Returns

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

Parameters

Returns

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: RangeRef): number

Parameters

Returns

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

Parameters

Returns

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

Parameters

Returns

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 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.

The landing rectangle is replaced whole, the way pasting over a selection in Excel replaces it: a coordinate whose source cell is empty ends up empty rather than keeping what the destination held. Cells outside the landing rectangle are untouched.

Source and target may overlap on the same sheet. Every source cell is read before the first one is written, so a copy that shifts by less than its own height or width lands the values the caller asked for rather than the ones the copy had already written.

targetWs has to belong to the same workbook as ws. styleId is an index into that workbook's cellXfs and nothing here can retarget it, so cells copied into a second workbook's sheet arrive wearing whichever style happens to occupy the same slot over there.

Returns the number of populated cells copied. Coordinates blanked because their source cell was empty don't count.

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

Parameters

Returns

number

Total populated cell count.

function countCells(ws: Worksheet): number

Parameters

Returns

number

function countCellsByKind(ws: Worksheet): CellsByKindCounts

Parameters

Returns

CellsByKindCounts

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

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

Parameters

Returns

void

Get the Cell at (row, col), allocating an empty one when the coordinate is not populated yet. An existing cell is returned untouched, value and all, which makes this the way to reach a cell you are about to style or attach a formula to.

mergeCells drops the cells underneath a merge; reaching one of those coordinates allocates it again, and the written <sheetData> then carries a blank <c> under the merge. A merged block's value lives on its top-left cell, so address that coordinate when the block is what you mean.

function ensureCell(ws: Worksheet, row: number, col: number): Cell

Parameters

Returns

Cell

A1-addressed ensureCell. Throws OpenXmlSchemaError when coord is not a plain A1 reference.

function ensureCellByCoord(ws: Worksheet, coord: string): Cell

Parameters

Returns

Cell

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

Parameters

Returns

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

Parameters

Returns

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>

Parameters

Returns

IterableIterator<Cell>

Read the current AutoFilter, if any.

function getAutoFilter(ws: Worksheet): AutoFilter | undefined

Parameters

Returns

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

Parameters

Returns

Cell | undefined

Convenience getter accepting an "A1" coordinate.

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

Parameters

Returns

Cell | undefined

Bounding-box of the cells the sheet materialises: { minRow, maxRow, minCol, maxCol } covering every cell in ws.rows, whether or not it holds a value. Returns undefined when the sheet has no cells. Walks the sparse store once.

This is Excel's used range, and what it writes into <dimension>: a cell that exists only to carry formatting (<c r="A6" s="4"/>, no <v>) counts. getValueExtent answers "where does the data end" instead.

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

Parameters

Returns

{ maxCol: number; maxRow: number; minCol: number; minRow: number } | undefined

Same as getCellExtent but returns the canonical "A1:E10" range string for the bounding box, or undefined when the sheet is empty.

This is the plain two-corner form makeAutoFilter and makeTableDefinition take for their ref, which is otherwise reachable only by hand-rolling column letters: getRangeAddress returns the sheet-qualified 'Sheet1!A1:C5' instead. Both fields take a plain string, so narrow the undefined an empty sheet returns before passing it on.

function getCellExtentRef(ws: Worksheet): string | undefined

Parameters

Returns

string | 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[]

Parameters

Returns

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: RangeRef): IterableIterator<Cell>

Parameters

Returns

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[]

Parameters

Returns

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

Parameters

Returns

ColumnDimension | undefined

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

function getFreezePanes(ws: Worksheet): string | undefined

Parameters

Returns

string | undefined

Highest column index holding a cell (0 when the sheet has none). Counts formatting-only cells, same as getMaxRow; the last column of data is getValueExtent(ws)?.maxCol.

function getMaxCol(ws: Worksheet): number

Parameters

Returns

number

Highest row index holding a cell (0 when the sheet has none). Counts a cell that carries only formatting, matching Excel's used range rather than the last row of data, which is getValueExtent(ws)?.maxRow.

function getMaxRow(ws: Worksheet): number

Parameters

Returns

number

Every merged range on the worksheet, as a read-only view of the live array, not a copy. mergeCells and unmergeCells are visible through it, and removeAllMergedRanges replaces the array outright, which leaves an earlier return value stale. Copy it ([...getMergedCells(ws)]) before mutating the sheet while you read.

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

Parameters

Returns

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

Parameters

Returns

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

Parameters

Returns

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[]

Parameters

Returns

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[]

Parameters

Returns

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: RangeRef): CellValue[][]

Parameters

Returns

CellValue[][]

Look up a row's dimension entry.

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

Parameters

Returns

RowDimension | undefined

Look up a table by displayName.

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

Parameters

Returns

TableDefinition | undefined

Bounding-box of the cells holding a value, i.e. those whose value is neither null nor ''. Returns undefined when no cell on the sheet holds one.

A cell is outside the box when all it carries is formatting, a hyperlink, a comment, or the empty string a converter leaves behind, so this is narrower than getCellExtent on a sheet formatted or linked past its content. Merged ranges and table refs are not consulted either: the box covers cells, not declared geometry.

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

Parameters

Returns

{ maxCol: number; maxRow: number; minCol: number; minRow: number } | 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

Parameters

Returns

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

Parameters

Returns

void

Convenience: hide a column.

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

Parameters

Returns

ColumnDimension

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

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

Parameters

Returns

void

Convenience: hide a row.

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

Parameters

Returns

RowDimension

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

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

Parameters

Returns

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

Parameters

Returns

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.

Populated means materialised, not "holds a value": explicit styled cells (<c r="A6" s="4"/>, a style id and no <v>) reach the caller with value === null. Readers coming from a library that drops them will see a blank-but-formatted column arrive as present-and-empty cells rather than as a gap. Skip them with if (isEmptyCell(cell)) continue;. Passing getValueExtent's box as the bounds removes trailing blanks but retains blanks inside that box.

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

Parameters

Returns

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), so position i of every yielded row is column minCol + i for the whole iteration.

Defaults: minRow=1, maxRow=getMaxRow(ws), minCol=1, maxCol=getMaxCol(ws). That bounding box is the sheet's populated cells, not the 1M × 16K grid limit, so the rectangular default doesn't iterate the whole grid for a small sheet. A cell carrying only formatting is populated, so a sheet formatted past its content iterates to the end of the formatting. Pass getValueExtent's box as the options to stop at the last value instead; it is undefined for a sheet holding no value at all, which the caller narrows.

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)[]>

Parameters

Returns

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, so every yielded row has the full extent width: a null means "no value at that position" rather than a short row.

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

Parameters

Returns

IterableIterator<CellValue[]>

Every legacy comment on the sheet, as a read-only view of the live array, not a copy. See getMergedCells.

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

Parameters

Returns

readonly LegacyComment[]

Every data validation block on the sheet, as a read-only view of the live array, not a copy. See getMergedCells.

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

Parameters

Returns

readonly DataValidation[]

Every hyperlink on the sheet, as a read-only view of the live array, not a copy. See getMergedCells.

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

Parameters

Returns

readonly Hyperlink[]

Every Excel table defined on the sheet, as a read-only view of the live array, not a copy. See getMergedCells.

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

Parameters

Returns

readonly TableDefinition[]

Build a Worksheet shell.

function makeWorksheet(title: string): Worksheet

Parameters

Returns

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.

The registered range is a validated, normalised copy of refOrRange, so mutating a bounds object afterwards can't rewrite a merge that is already on the sheet.

function mergeCells(ws: Worksheet, refOrRange: RangeRef): CellRangeBoundaries

Parameters

Returns

CellRangeBoundaries

Move every populated cell from source to target, clearing the source band behind it. Extent clamping, whole-rectangle replacement at the landing site and the same-workbook requirement on targetWs all match copyRange.

Not a copy followed by a clear: where the ranges overlap on one sheet, a trailing clear of the source would take cells the move had just landed there. The source band is read, then cleared, then written, so moveRange(ws, 'A1:A3', 'A2:A4') over [1, 2, 3] leaves [_, 1, 2, 3] where copy-then-clear leaves [_, _, _, 3].

Returns the number of populated cells moved.

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

Parameters

Returns

number

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

function removeAllComments(ws: Worksheet): number

Parameters

Returns

number

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

function removeAllConditionalFormatting(ws: Worksheet): number

Parameters

Returns

number

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

function removeAllDataValidations(ws: Worksheet): number

Parameters

Returns

number

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

function removeAllHyperlinks(ws: Worksheet): number

Parameters

Returns

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

Parameters

Returns

number

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

function removeAllTables(ws: Worksheet): number

Parameters

Returns

number

Remove cell watches matching predicate. Returns the count removed.

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

Parameters

Returns

number

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

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

Parameters

Returns

number

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

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

Parameters

Returns

boolean

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

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

Parameters

Returns

number

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

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

Parameters

Returns

boolean

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

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

Parameters

Returns

void

Write a Cell at (row, col), creating it when the coordinate is empty. value always lands on the cell, so an existing value is replaced. Use ensureCell to reach a cell without writing to it. An existing cell keeps its styleId / hyperlinkId / commentId unless styleId is passed.

null is the explicit empty value: it clears the value and leaves the cell in the sheet with its fill, border and number format, the way Excel's Delete key does. deleteCell drops the cell entirely, formatting included, and clearRange does the same across a rectangle.

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

Parameters

Returns

Cell

A1-addressed setCell: resolves coord to a numeric (row, col) and writes value there. Throws OpenXmlSchemaError when coord is not a plain A1 reference.

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

Parameters

Returns

Cell

Set a single-column ColumnDimension entry covering col. opts replaces the column's fields rather than merging with them.

An existing run that spans col and other columns is split: col gets its own entry and the rest of the run keeps its fields. Callers that want a range-spanning entry of their own can still write directly into ws.columnDimensions.

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

Parameters

Returns

ColumnDimension

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

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

Parameters

Returns

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. An entry that is not a usable width (not a number, non-finite, negative) is skipped, which is what lets a caller pass a sparse array.

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

Parameters

Returns

void

Add or replace the comment at ref.

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

Parameters

Returns

LegacyComment

Set many comments at once, with the same result as calling setComment for each entry in order.

Each single call scans the sheet's comments to find the ref it replaces, so putting a note on every row costs time quadratic in the number of rows. A batch resolves every ref against one index instead, which it builds and drops inside this call, so the cost is linear in the two lengths.

function setComments(ws: Worksheet, entries: readonly { author: string; ref: string; text: string }[]): LegacyComment[]

Parameters

Returns

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

Parameters

Returns

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

Parameters

Returns

void

Freeze rows / columns above + left of the given top-left cell. Takes either the A1 ref of the first unfrozen cell ("B2" freezes 1 row + 1 column) or the counts directly ({ rows: 1, cols: 0 } freezes the header row alone). Pass undefined to clear any existing freeze. Targets the workbook's primary SheetView (ws.views[0]); creates one if absent.

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

Parameters

Returns

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.

target is validated against what the rels Target attribute (xsd:anyURI) accepts — whitespace, control characters and a non-ASCII host throw an OpenXmlSchemaError here rather than producing a package Excel refuses.

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

Parameters

Returns

Hyperlink

Set many hyperlinks at once, with the same result as calling setHyperlink for each entry in order, and the same validation.

Each single call scans the sheet's hyperlinks to find the ref it replaces, so putting a link on every row of a sheet costs time quadratic in the number of rows. A batch resolves every ref against one index instead, which it builds and drops inside this call, so the cost is linear in the two lengths.

Nothing is written until every entry has been validated, so a bad target leaves the sheet untouched rather than half updated.

function setHyperlinks(ws: Worksheet, entries: readonly { display?: string; location?: string; ref: string; target?: string; tooltip?: string }[]): Hyperlink[]

Parameters

Returns

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.

Values past the range's bottom or right edge are dropped rather than written outside it, the way copyRange clips to its target extent. Use writeRange for the unbounded form, which takes an anchor and grows to fit the array.

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

Parameters

Returns

void

function setRowDimension(ws: Worksheet, row: number, opts: Partial<RowDimension>): RowDimension

Parameters

Returns

RowDimension

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

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

Parameters

Returns

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. An entry that is not a usable height (not a number, non-finite, negative) is skipped, the same way setColumnWidths treats widths.

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

Parameters

Returns

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

Parameters

Returns

Color

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

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

Parameters

Returns

void

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

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

Parameters

Returns

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

Parameters

Returns

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

Parameters

Returns

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

Parameters

Returns

void

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

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

Parameters

Returns

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

Parameters

Returns

void

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

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

Parameters

Returns

void

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

function unmergeCells(ws: Worksheet, refOrRange: RangeRef): boolean

Parameters

Returns

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

Parameters

Returns

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 | CellCoordinateNumeric, values: readonly readonly (CellValue | undefined)[][]): { maxCol: number; maxRow: number; minCol: number; minRow: number } | undefined

Parameters

Returns

{ maxCol: number; maxRow: number; minCol: number; minRow: number } | undefined

Create a PivotTable on ws and write its report cells. Field references are 0-based column offsets within source.ref, whose first row holds the field names.

function addPivotTable(wb: Workbook, ws: Worksheet, opts: AddPivotTableOptions): PivotTable

Parameters

Returns

PivotTable

Distinct items of source field field in the order the report lists them, e.g. for a filter drop-down.

function getPivotFieldItems(wb: Workbook, source: { ref: string; sheet: string }, field: number): PivotItemValue[]

Parameters

Returns

PivotItemValue[]

Field names for a source range: its header row, with duplicates numbered the way Excel does ("Sales", "Sales2"). Throws when the sheet is missing or a header cell is blank, since Excel requires every field to have a name.

function getPivotSourceFields(wb: Workbook, source: { ref: string; sheet: string }): string[]

Parameters

Returns

string[]

The PivotTable on ws whose report covers (row, col), if any.

function getPivotTableAt(ws: Worksheet, row: number, col: number): PivotTable | undefined

Parameters

Returns

PivotTable | undefined

The range a refresh of pt would write, filter rows included, without writing it — for checking what the report would cover first.

function getPivotTableOutputRef(wb: Workbook, pt: PivotTable): string

Parameters

Returns

string

First free "PivotTableN" name in the workbook.

function nextPivotTableName(wb: Workbook): string

Parameters

Returns

string

Recompute pt from its source and rewrite its report cells on ws: the range the previous refresh wrote is cleared first. Call after changing the definition or the source data.

function refreshPivotTable(wb: Workbook, ws: Worksheet, pt: PivotTable): PivotComputation

Parameters

Returns

PivotComputation

Remove pt from ws and clear the cells its last refresh wrote.

function removePivotTable(ws: Worksheet, pt: PivotTable): void

Parameters

Returns

void

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

Parameters

Returns

string

function makeHeaderFooter(opts?: HeaderFooter): HeaderFooter

Parameters

Returns

HeaderFooter

function makePageBreak(opts?: PageBreak): PageBreak

Parameters

Returns

PageBreak

function makePageMargins(opts?: Partial<PageMargins>): PageMargins

Parameters

Returns

PageMargins

function makePageSetup(opts?: PageSetup): PageSetup

Parameters

Returns

PageSetup

function makePrintOptions(opts?: PrintOptions): PrintOptions

Parameters

Returns

PrintOptions

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" }>

The selection of the view's active pane (a <selection> without pane is the top-left one).

function activeSelection(view: SheetView): Selection | undefined

Parameters

Returns

Selection | undefined

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

function freezePaneRef(view: SheetView): string | undefined

Parameters

Returns

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

Parameters

Returns

Pane

Build a SheetView with sensible defaults.

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

Parameters

Returns

SheetView

function makeScenario(opts: Scenario): Scenario

Parameters

Returns

Scenario

function makeScenarioInputCell(opts: ScenarioInputCell): ScenarioInputCell

Parameters

Returns

ScenarioInputCell

function makeScenarioList(opts?: Partial<ScenarioList>): ScenarioList

Parameters

Returns

ScenarioList

function makeCellSmartTag(opts: Partial<CellSmartTag> & { type: number }): CellSmartTag

Parameters

Returns

CellSmartTag

function makeCellSmartTagProperty(key: string, val: string): CellSmartTagProperty

Parameters

Returns

CellSmartTagProperty

function makeCellSmartTags(opts: Partial<CellSmartTags> & { ref: string }): CellSmartTags

Parameters

Returns

CellSmartTags

Build a single-column ColumnDimension entry covering col.

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

Parameters

Returns

ColumnDimension

function makeRowDimension(opts?: Partial<RowDimension>): RowDimension

Parameters

Returns

RowDimension

function makeCellWatch(ref: string): CellWatch

Parameters

Returns

CellWatch

function makeIgnoredError(opts: Partial<IgnoredError> & { sqref: MultiCellRange }): IgnoredError

Parameters

Returns

IgnoredError

function makeFormControl(opts: Partial<FormControl> & { shapeId: number }): FormControl

Parameters

Returns

FormControl

function makeOleObject(opts: Partial<OleObject> & { shapeId: number }): OleObject

Parameters

Returns

OleObject

function makeSortCondition(opts: SortCondition): SortCondition

Parameters

Returns

SortCondition

function makeSortState(opts: Partial<SortState> & { ref: string }): SortState

Parameters

Returns

SortState

function makeWebPublishItem(opts: WebPublishItem): WebPublishItem

Parameters

Returns

WebPublishItem

function makeWorksheetCustomProperty(opts: WorksheetCustomProperty): WorksheetCustomProperty

Parameters

Returns

WorksheetCustomProperty

function makeCustomSheetView(opts: Partial<CustomSheetView> & { guid: string }): CustomSheetView

Parameters

Returns

CustomSheetView

function makeDataConsolidate(opts?: DataConsolidate): DataConsolidate

Parameters

Returns

DataConsolidate

function makeWorksheetPhoneticProperties(opts?: WorksheetPhoneticProperties): WorksheetPhoneticProperties

Parameters

Returns

WorksheetPhoneticProperties

The pivots on ws that ride passthrough because the model can't express them.

function listPassthroughPivotTables(wb: Workbook, ws: Worksheet): PivotTableSummary[]

Parameters

Returns

PivotTableSummary[]

function makeSheetProperties(opts?: SheetProperties): SheetProperties

Parameters

Returns

SheetProperties

function makeProtectedRange(opts: Partial<ProtectedRange> & { name: string; sqref: MultiCellRange }): ProtectedRange

Parameters

Returns

ProtectedRange

function makeSheetProtection(opts?: SheetProtection): SheetProtection

Parameters

Returns

SheetProtection

A sparkline group with Excel's Insert ▸ Sparklines defaults: the theme's dark-blue series and red negatives / markers.

function makeSparklineGroup(opts: Partial<SparklineGroup> & { sparklines: Sparkline[] }): SparklineGroup

Parameters

Returns

SparklineGroup