Freeze Panes & Auto Filter

#Freeze Panes

Freeze rows and/or columns to keep headers visible while scrolling:

JSON

{
  "worksheets": [
    {
      "name": "Sheet1",
      "freezePanes": { "row": 1 },
      "rows": [
        { "cells": [{ "value": "Header 1" }, { "value": "Header 2" }] },
        { "cells": [{ "value": "Data 1" }, { "value": "Data 2" }] },
        { "cells": [{ "value": "Data 3" }, { "value": "Data 4" }] }
      ]
    }
  ]
}

TypeScript

{
  name: "Sheet1",
  freezePanes: { row: 1 }, // Freeze top 1 row
  rows: [
    { cells: [{ value: "Header 1" }, { value: "Header 2" }] },
    { cells: [{ value: "Data 1" }, { value: "Data 2" }] },
    { cells: [{ value: "Data 3" }, { value: "Data 4" }] },
  ],
}

Freeze both rows and columns:

JSON

{
  "worksheets": [
    {
      "name": "Sheet1",
      "freezePanes": { "row": 2, "col": 1 },
      "rows": [
        { "cells": [{ "value": "Title" }, { "value": "A" }, { "value": "B" }] },
        { "cells": [{ "value": "Name" }, { "value": "Score" }, { "value": "Grade" }] },
        { "cells": [{ "value": "Alice" }, { "value": 95 }, { "value": "A" }] }
      ]
    }
  ]
}

TypeScript

freezePanes: {
  row: 2, // Freeze top 2 rows
  col: 1, // Freeze first column
},

#Auto Filter

Add auto-filter dropdowns to a range of columns:

JSON

{
  "worksheets": [
    {
      "name": "Sheet1",
      "autoFilter": "A1:D4",
      "rows": [
        {
          "cells": [
            { "value": "Name" },
            { "value": "Age" },
            { "value": "City" },
            { "value": "Score" }
          ]
        },
        { "cells": [{ "value": "Alice" }, { "value": 30 }, { "value": "NY" }, { "value": 95 }] },
        { "cells": [{ "value": "Bob" }, { "value": 25 }, { "value": "LA" }, { "value": 88 }] },
        { "cells": [{ "value": "Carol" }, { "value": 28 }, { "value": "SF" }, { "value": 72 }] }
      ]
    }
  ]
}

TypeScript

{
  name: "Sheet1",
  autoFilter: "A1:D4", // Filter arrows on header row
  rows: [
    { cells: [{ value: "Name" }, { value: "Age" }, { value: "City" }, { value: "Score" }] },
    { cells: [{ value: "Alice" }, { value: 30 }, { value: "NY" }, { value: 95 }] },
    { cells: [{ value: "Bob" }, { value: 25 }, { value: "LA" }, { value: 88 }] },
    { cells: [{ value: "Carol" }, { value: 28 }, { value: "SF" }, { value: 72 }] },
  ],
}

The autoFilter string specifies the cell range that will have filter dropdowns. Excel will show filter arrows on the header row.

#Structured Filter

Use the object form to record which values each column filters on (what Excel writes back after the user applies a filter):

JSON

{
  "worksheets": [
    {
      "name": "Sheet1",
      "autoFilter": {
        "ref": "A1:D4",
        "columns": [
          {
            "colId": 0,
            "filters": { "values": ["Alice", "Carol"] }
          },
          {
            "colId": 3,
            "top10": { "top": true, "val": 1 }
          }
        ]
      },
      "rows": [
        {
          "cells": [
            { "value": "Name" },
            { "value": "Age" },
            { "value": "City" },
            { "value": "Score" }
          ]
        },
        { "cells": [{ "value": "Alice" }, { "value": 30 }, { "value": "NY" }, { "value": 95 }] },
        { "cells": [{ "value": "Bob" }, { "value": 25 }, { "value": "LA" }, { "value": 88 }] },
        { "cells": [{ "value": "Carol" }, { "value": 28 }, { "value": "SF" }, { "value": 72 }] }
      ]
    }
  ]
}

TypeScript

{
  name: "Sheet1",
  autoFilter: {
    ref: "A1:D4",
    columns: [
      // Show only Alice and Carol in column 0
      { colId: 0, filters: { values: ["Alice", "Carol"] } },
      // Top-1 by Score (column 3)
      { colId: 3, top10: { top: true, val: 1 } },
    ],
  },
  rows: [
    { cells: [{ value: "Name" }, { value: "Age" }, { value: "City" }, { value: "Score" }] },
    { cells: [{ value: "Alice" }, { value: 30 }, { value: "NY" }, { value: 95 }] },
    { cells: [{ value: "Bob" }, { value: 25 }, { value: "LA" }, { value: 88 }] },
    { cells: [{ value: "Carol" }, { value: 28 }, { value: "SF" }, { value: 72 }] },
  ],
}

colId is the zero-based column index within the filter range. Each column carries exactly one filter kind:

Option	Type	Description
`ref`	`string`	Filter range, e.g. `"A1:D4"`
`columns`	`FilterColumnOptions[]`	One entry per filtered column
`sortState`	`SortStateOptions`	Sort state recorded with the filter

#FilterColumn Options

Option	Type	Description
`colId`	`number`	Zero-based column index within the filter range (required)
`hiddenButton`	`boolean`	Hide the filter dropdown button
`showButton`	`boolean`	Show the filter button (default `true`)
`filters`	`object`	Value/date filters: `{ values?: string[], dateGroupItems?: […], blank?, calendarType? }`
`customFilters`	`object`	Comparison filters: `{ and?: boolean, entries: [{ operator?, val }] }` with operators `equal`/`notEqual`/`greaterThan`/`greaterThanOrEqual`/`lessThan`/`lessThanOrEqual`
`top10`	`object`	Top-N filter: `{ top?: boolean, percent?: boolean, val: number, filterVal?: number }`
`dynamicFilter`	`object`	Dynamic filter: `{ type: "today" \| "thisMonth" \| "Q1" \| …, val?, maxVal?, valIso?, maxValIso? }`
`colorFilter`	`object`	Color filter: `{ dxfId?: number, cellColor?: boolean }`
`iconFilter`	`object`	Icon filter: `{ iconSet: IconSetType, iconId?: number }`

#FreezePane Options Reference

Option	Type	Description
`row`	`number`	Number of rows to freeze
`col`	`number`	Number of columns to freeze