Workbook Structure

Use workbook-structure APIs for changes that affect the workbook container, sheet set, named ranges, sheet view, row/column grid, or cell block layout. These operations preserve normal Excel workbook structure and trigger recalculation where affected.

Workbook Properties

Inspect workbook-level properties:

witan xlsx exec model.xlsx --expr 'await xlsx.getWorkbookProperties(wb)'

Update metadata, default font, theme colors, active sheet, or iterative-calculation settings:

await xlsx.setWorkbookProperties(wb, {
  metadata: {
    title: "Forecast model",
    author: "Finance",
    company: "Example Co"
  },
  iterativeCalculation: {
    enabled: true,
    maxIterations: 100,
    maxChange: 0.001
  }
})

Sheets

Create, delete, and rename sheets:

await xlsx.addSheet(wb, "Inputs")
await xlsx.renameSheet(wb, "Sheet1", "Summary")
await xlsx.deleteSheet(wb, "Scratch")

Use listSheets to inspect sheet names, used ranges, tile counts, and workbook features such as data-table addresses on each sheet.

Sheet Properties

Use getSheetProperties and setSheetProperties for sheet visibility, view settings, outline settings, defaults, and merges:

await xlsx.setSheetProperties(wb, "Summary", {
  visibility: "visible",
  view: {
    showGridLines: false,
    zoomScale: 90,
    freezeRows: 1,
    freezeColumns: 2
  },
  outline: {
    summaryRowsBelow: true,
    summaryColumnsRight: true,
    showSymbols: true
  }
})

Sheet visibility values are visible, hidden, and veryHidden. Frozen panes are represented as freezeRows and freezeColumns.

Sheet properties cover these Excel workbook features:

Pass row or column filters to getSheetProperties when a script only needs specific dimensions:

const props = await xlsx.getSheetProperties(wb, "Summary", {
  columns: ["A", "B", "C"],
  rows: [1, 2, 3]
})

Row and column geometry is changed through Styles and Layout, not through setSheetProperties.

Defined Names

List, add, and delete workbook-scoped or sheet-scoped names:

await xlsx.addDefinedName(wb, "RevenueInput", "Inputs!B2")
await xlsx.addDefinedName(wb, "LocalRate", "Inputs!B3", "Inputs")

const names = await xlsx.listDefinedNames(wb)

await xlsx.deleteDefinedName(wb, "LocalRate", "Inputs")

Defined names are used by formula calculation and dependency tracing. Names can refer to ranges or formulas, including named lambdas used by formulas.

Rows And Columns

Insert or delete grid rows and columns:

await xlsx.insertRowAfter(wb, "Summary", 10, 2)
await xlsx.deleteRows(wb, "Summary", 20, 3)

await xlsx.insertColumnAfter(wb, "Summary", "D", 1)
await xlsx.deleteColumns(wb, "Summary", "G", 2)

Row and column edits shift affected cells and update workbook structure in the same way normal Excel grid edits do.

Row and column property APIs cover height, width, hidden state, outline level, and collapsed state. Those properties are documented on Styles and Layout because they affect the visible worksheet layout.

Copy, Scale, And Sort Ranges

Use copyRange for block moves or duplication:

await xlsx.copyRange(wb, "Template!A1:D12", "Summary!A1", {
  pasteType: "all"
})

Paste types are all, values, formulas, and formats.

copyRange preserves Excel formula-block rules: legacy multi-cell CSE arrays cannot be partially overwritten, dynamic-array spill children are handled as spill children rather than independent formulas, and affected formulas are recalculated after copy operations that write values or formulas.

Use scaleRange for numeric adjustments:

await xlsx.scaleRange(wb, "Inputs!B2:B20", 1.05, {
  skipFormulas: true
})

Use sortRange for in-place sorting:

await xlsx.sortRange(wb, "Summary!A1:D50", [
  { column: "B", descending: true }
], { hasHeader: true })

sortRange supports one or more sort keys, column letters or zero-based column offsets within the sorted range, ascending or descending order, and optional header rows. It rejects merged cells and multi-cell array formulas in the sort body, matching the parts of Excel grid sorting that cannot be safely rearranged cell-by-cell.

See xlsx API for workbook and sheet signatures, and Writing and structure for row, column, copy, scale, and sort signatures.