PivotTable from a range

About this example

Excel's Insert > PivotTable over a block of cells in a Svelte 5 spreadsheet, on the same pivot engine the grid uses for its own pivot mode. The sheet keeps the definition, the source block, where the result goes and which field is a row, a column or a measure; the result is plain cells written in one undo, so it can be formatted, charted, printed and saved to an .xlsx like any other block. Refresh rebuilds it from the source. This sheet opens with sales by region down the rows and quarters across the columns. Enterprise, in @svgrid/enterprise.

Excel's Insert > PivotTable over a block of cells, on the same pivot engine the grid uses for its own pivot mode.

The sheet keeps the DEFINITION: the source block, where the result goes, and which field is a row, a column or a measure. The RESULT is plain cells, written in one undo, so everything else in the shell works on it: format it, chart it, print it, save it to an .xlsx. Refresh rebuilds the block from the source, which is what a pivot over live cells owes you.

Insert > PivotTable the dialog on the selected block, or on the pivot the cursor is already in Insert > Refresh rebuild the one here

This sheet opens with a pivot already written at H1: sales by region down the rows, quarters across the columns, the amounts summed.

Try: change an Amount in E2:E25, put the cursor in the pivot and press Refresh. Click a number in the pivot and press Show Details for the rows behind it, on a sheet of their own. Open the dialog from inside it and move Rep into Rows under Region, or make Amount an Average. Select A1:E25 and build a second one somewhere else.

Imports, features and API used

Imports: @svgrid/enterprise

Frequently asked questions

Is the result live, like Excel's?

The definition is live, the cells are cells. The sheet remembers the source block, the axes and the measures; Refresh rebuilds the block from the source and clears whatever the last one wrote. Between refreshes the result is ordinary cells, which is what lets you format, chart, print and save it.

Can I change the fields after the fact?

Yes. Put the cursor inside the written block and press Insert > PivotTable: the dialog opens on that pivot, with the fields as they stand. Move a field between Rows, Columns and Values, change how a value is summarised, or turn the grand total and the subtotals off.

What happens when rows are inserted above it?

The definition moves with the cells: the source block, the target cell and the rectangle the last refresh wrote all shift. A pivot whose source is deleted, or whose target cell is, is dropped.

Can I see the rows behind a number?

Yes: put the cursor in the cell and press Insert > Show Details, Excel's drill-down. The source rows behind that cell are written to a sheet of their own with the field names across the top. A cell in a subtotal line opens its whole group, one in the grand total column opens the whole line, and the grand total opens every row.

Related documentation

• The spreadsheet shell

Related articles

• Spreadsheet-Style Cell Range Selection in SvGrid - How to enable drag-to-select cell ranges, read live selection state, and build a status-bar footer that sums and averages the selected values.
• A Fill Handle (Drag to Fill) in SvGrid - Build a working spreadsheet-style fill handle on top of SvGrid's cell selection and editing - pointer tracking, range highlighting, series fill, and undo/redo integration all covered.
• Copy a Cell Range to the Clipboard in SvGrid - How SvGrid copies selected cell ranges as tab-separated values that paste cleanly into Excel and Google Sheets, plus headers, programmatic copy, and format control.

Source code (487-sheet-pivot-range.svelte)

<script lang="ts">
  /**
   * 477. PivotTable from a range
   * ----------------------------
   * Excel's Insert > PivotTable over a block of cells, on the same pivot
   * engine the grid uses for its own pivot mode.
   *
   * The sheet keeps the DEFINITION: the source block, where the result
   * goes, and which field is a row, a column or a measure. The RESULT is
   * plain cells, written in one undo, so everything else in the shell
   * works on it: format it, chart it, print it, save it to an .xlsx.
   * Refresh rebuilds the block from the source, which is what a pivot over
   * live cells owes you.
   *
   *   Insert > PivotTable   the dialog on the selected block, or on the
   *                         pivot the cursor is already in
   *   Insert > Refresh      rebuild the one here
   *
   * This sheet opens with a pivot already written at H1: sales by region
   * down the rows, quarters across the columns, the amounts summed.
   *
   * Try: change an Amount in E2:E25, put the cursor in the pivot and press
   * Refresh. Click a number in the pivot and press Show Details for the
   * rows behind it, on a sheet of their own. Open the dialog from inside it
   * and move Rep into Rows under Region, or make Amount an Average. Select
   * A1:E25 and build a second one somewhere else.
   */
  import {
    SvSheet, createWorkbook, createSheetDocument,
    pivotBlock, pivotWrittenRect, pivotId, type SheetPivot,
  } from '@svgrid/enterprise'

  const regions = ['North', 'South', 'EMEA']
  const reps = ['Ada', 'Grace', 'Linus']
  const quarters = ['Q1', 'Q2', 'Q3', 'Q4']
  const products = ['Licence', 'Support']

  // A plain sales log: one row per deal, the shape a pivot is made for.
  const log: string[][] = []
  let seed = 7
  const next = () => (seed = (seed * 1103515245 + 12345) % 2147483648) / 2147483648
  for (const region of regions) {
    for (const quarter of quarters) {
      for (const product of products) {
        log.push([
          region,
          reps[Math.floor(next() * reps.length)]!,
          quarter,
          product,
          String(4000 + Math.round(next() * 9000)),
        ])
      }
    }
  }

  const header = ['Region', 'Rep', 'Quarter', 'Product', 'Amount']
  const cells: string[][] = [header, ...log]

  // The pivot this sheet opens with. Its block is computed here so the
  // document arrives with the summary already in the cells; from then on
  // Refresh rewrites it through the shell.
  const source = [0, 0, cells.length - 1, 4] as const
  const pivot: SheetPivot = {
    id: pivotId(),
    source: source as unknown as SheetPivot['source'],
    target: { row: 0, col: 7 },
    rows: ['Region'],
    cols: ['Quarter'],
    values: [{ field: 'Amount', agg: 'sum' }],
  }
  const valueAt = (r: number, c: number) => {
    const text = cells[r]?.[c] ?? ''
    const n = Number(text)
    return text !== '' && Number.isFinite(n) ? n : text
  }
  const textAt = (r: number, c: number) => cells[r]?.[c] ?? ''
  const block = pivotBlock(pivot, valueAt, textAt)
  pivot.written = pivotWrittenRect(pivot, block)
  block.forEach((line, i) => {
    const row = (cells[pivot.target.row + i] ??= [])
    line.forEach((text, j) => { row[pivot.target.col + j] = text })
  })
  // A workbook takes a dense block: the gap between the log and the pivot,
  // and any short row, has to be empty strings rather than holes.
  const width = cells.reduce((w, row) => Math.max(w, row.length), 0)
  for (const row of cells) {
    for (let c = 0; c < width; c += 1) row[c] ??= ''
  }

  const wb = createWorkbook([{ name: 'Sales', cells }])
  const doc = createSheetDocument({ workbook: wb })
  const sheet = doc.get('Sales')
  const at = { rowIdAt: (i: number) => `r${i}`, columnIdAt: (i: number) => String.fromCharCode(65 + i) }
  sheet.formats.set([[0, 0, 0, 4]], { bold: true, fill: '#e2e8f0', color: '#0f172a' }, at)
  sheet.formats.set([[1, 4, cells.length - 1, 4]], { numFmt: '$#,##0' }, at)
  // The written block: its header row, and the money inside it.
  sheet.formats.set([[0, 7, 0, 12]], { bold: true, fill: '#e2e8f0', color: '#0f172a' }, at)
  sheet.formats.set([[1, 8, pivot.written[2], 12]], { numFmt: '$#,##0' }, at)
  sheet.formats.set([[pivot.written[2], 7, pivot.written[2], 12]], { bold: true, border: { top: { width: 1 } } }, at)
  sheet.widths.A = 90
  sheet.widths.H = 120
  sheet.freeze = { rows: 1, cols: 0 }
  sheet.pivots = [pivot]
</script>

<SvSheet document={doc} height="100%" rows={30} columns={14} />

View this example on GitHub

More Spreadsheet examples

• Spreadsheet + Ribbon bar - The whole Excel surface as one component, <SvSheet workbook={wb} />: a six-tab ribbon (Home, Insert, Formulas, Data, Review, View), the Name Box and fx bar, sheet tabs and the Sum / Average / Count status bar. Format cells, merge them, comment on them, validate what goes in, colour them by rule, filter the region, protect the sheet; every button is the same call as its shortcut, so Ctrl+B and the Bold button cannot drift. A two-sheet P&L with the formats travelling in the document.
• Spreadsheet + formulas - Real formula engine inside the grid: cell refs (A1), ranges (A1:A10), SUM / AVG / IF / COUNTIF / ROUND, arithmetic, string concat, cycle detection.
• Per-cell custom borders (KPI) - Editable KPI scorecard. spreadsheetLayout paints spreadsheet-style per-edge custom borders via an absolute-positioned overlay (no border-collapse conflicts). Edit any quarter or target - the borders re-derive: green double = beat target, blue solid = hit, amber dotted = near miss, red dashed = bad miss; row champion gets a colored full frame.
• Cell merging (spreadsheet shell) - A real invoice rendered on an Excel-style shell: A / B / C / D / E column letters across the top, row numbers down the left. Brand band, bill-from / bill-to address blocks, meta block, line items, totals, notes, signatures - all assembled from MergeSpec + CellBorderSpec. Editable Qty / Rate / addresses / notes; totals recompute live.
• HyperFormula integration - Full HyperFormula engine wired into the grid as a peer-optional dep. Editable spreadsheet with A1-style cell refs, dozens of formulas across math (SUM / SUMIF), lookup (VLOOKUP / INDEX-MATCH), text (CONCAT / UPPER), date (TODAY / DATEDIF), logical (IF nests), financial (PMT / IRR / NPV), statistical (AVERAGE / MAX / RANK).