XLSX
Freeze Panes & Auto Filter
Freeze rows/columns and add auto-filter dropdowns
Freeze Panes
Freeze rows and/or columns to keep headers visible while scrolling:
{
"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" }] }
]
}
]
}
{
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:
{
"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" }] }
]
}
]
}
freezePanes: {
row: 2, // Freeze top 2 rows
col: 1, // Freeze first column
},
Auto Filter
Add auto-filter dropdowns to a range of columns:
{
"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 }] }
]
}
]
}
{
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):
{
"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 }] }
]
}
]
}
{
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 |