Pivot Tables

Witan supports Excel pivot tables through the Office Scripts surface. Scripts can create pivots from worksheet ranges or tables, arrange fields on row, column, filter, and data axes, refresh their output, and save the result as a normal Excel pivot table.

Run pivot scripts with witan xlsx exec:

function main(workbook: ExcelScript.Workbook) {
  const data = workbook.getWorksheet("Data")
  const report = workbook.getWorksheet("Report")
  const pivot = workbook.addPivotTable(
    "SalesPivot",
    data.getUsedRange(),
    report.getRange("A3")
  )

  pivot.addRowHierarchy(pivot.getHierarchy("Region"))
  pivot.addColumnHierarchy(pivot.getHierarchy("Quarter"))

  const sales = pivot.addDataHierarchy(pivot.getHierarchy("Sales"))
  sales.setName("Total sales")
  sales.setSummarizeBy(ExcelScript.AggregationFunction.sum)
  sales.setNumberFormat("$#,##0")

  pivot.getLayout().setLayoutType(ExcelScript.PivotLayoutType.tabular)
  pivot.getLayout().setShowRowGrandTotals(true)
  pivot.refresh()
}

The workbook and worksheet objects expose addPivotTable, getPivotTable, getPivotTables, and refreshAllPivotTables. A range can use getPivotTables(fullyContained) to find pivots that intersect it.

Fields And Axes

Every source column is exposed as a pivot hierarchy. Move a hierarchy onto an axis with:

• addRowHierarchy and removeRowHierarchy
• addColumnHierarchy and removeColumnHierarchy
• addFilterHierarchy and removeFilterHierarchy
• addDataHierarchy and removeDataHierarchy

Row, column, and filter hierarchies can be reordered. Hierarchies, fields, data hierarchies, and individual pivot items expose their display names, so a report can use readable labels without changing the source column names.

Fields support item visibility, expansion, subtotals, showAllItems, label sorting, and value sorting. Value sorting can target a data hierarchy and a nested row or column scope.

Values And Calculations

Data hierarchies support Excel aggregation functions such as automatic, sum, count, average, minimum, maximum, product, standard deviation, and variance. They also expose number formats and the Office Scripts Show Values As calculations, including:

• Percent of grand, row, or column total
• Running total and percent running total
• Difference and percent difference from a base item
• Percent of a base item or parent total
• Ascending and descending rank
• Index

Refreshing a pivot recalculates and materializes its output cells. Formulas that use GETPIVOTDATA participate in workbook recalculation, so downstream formulas update when pivot output changes.

Filtering And Sorting

Pivot fields support manual item filters, label filters, value filters, and Excel date filters. Date filtering includes relative periods and calendar comparisons such as today, this week, this month, quarters, years, before, after, and between.

A field can combine filter types when the pivot allows multiple filters per field. Filter changes are applied atomically: if a requested filter cannot be represented, Witan leaves the previous filter state intact.

Use sortByLabels for ascending or descending item labels and sortByValues to order items by a selected data hierarchy. If a loaded value-sort definition cannot be evaluated, Witan preserves the workbook and reports that it fell back to label order.

Layout

pivot.getLayout() controls the visible report layout:

Styles

New pivots use the workbook's default pivot-table style. Workbook style APIs can inspect built-in and custom styles, create a custom style, select the default, and rename, duplicate, or delete a custom style:

function main(workbook: ExcelScript.Workbook) {
  const style = workbook.addPivotTableStyle("Finance Pivot", true)
  workbook.setDefaultPivotTableStyle(style)

  const copy = style.duplicate()
  copy.setName("Finance Pivot Alternate")
}

Built-in styles are read-only. Renaming a custom style updates references to it; deleting one moves affected pivots and the workbook default back to the built-in default style.

Rendering And Preservation

Pivot output renders as worksheet cells, including headers, totals, filters, number formats, and supported pivot styles. Use Witan Render to inspect the resulting report.

Witan preserves pivot cache details and field settings when saving or refreshing supported pivots. Slicers, pivot charts, and timelines attached to an existing workbook are preserved across unrelated edits, but their authoring APIs are not currently exposed.

Current Limits

The Office Scripts surface does not expose a stable PivotTable.getId value, and PivotLayout.setAutoSortOnCell is not implemented. Slicer and timeline creation, pivot-chart-specific options, and connection-backed or external-data refresh are also outside the supported headless pivot workflow.

See Office Scripts for runtime behavior and compatibility boundaries.