冻结窗格与自动筛选

#冻结窗格

冻结行和/或列，使标题在滚动时保持可见：

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

import { generateWorkbook } from "@office-open/xlsx";

await generateWorkbook({
  worksheets: [
    {
      name: "Sheet1",
      freezePanes: { row: 1 }, // 冻结顶部 1 行
      rows: [
        { cells: [{ value: "Header 1" }, { value: "Header 2" }] },
        { cells: [{ value: "Data 1" }, { value: "Data 2" }] },
        { cells: [{ value: "Data 3" }, { value: "Data 4" }] },
      ],
    },
  ],
});

同时冻结行和列：

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

import { generateWorkbook } from "@office-open/xlsx";

await generateWorkbook({
  worksheets: [
    {
      name: "Sheet1",
      freezePanes: {
        row: 2, // 冻结顶部 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" }] },
      ],
    },
  ],
});

#自动筛选

为一列或多列添加自动筛选下拉框：

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

import { generateWorkbook } from "@office-open/xlsx";

await generateWorkbook({
  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 }] },
      ],
    },
  ],
});

autoFilter 字符串指定将显示筛选下拉框的单元格区域。Excel 会在标题行上显示筛选箭头。

#结构化筛选

使用对象形态记录每列的筛选条件（即用户应用筛选后 Excel 回写的内容）：

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: [
      // 第 0 列只显示 Alice 和 Carol
      { colId: 0, filters: { values: ["Alice", "Carol"] } },
      // 按 Score（第 3 列）取前 1 名
      { 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 是筛选区域内的零基列索引，每列只承载一种筛选类型：

选项	类型	说明
`ref`	`string`	筛选区域，如 `"A1:D4"`
`columns`	`FilterColumnOptions[]`	每个被筛选列一项
`sortState`	`SortStateOptions`	随筛选记录的排序状态

#FilterColumn 选项

选项	类型	说明
`colId`	`number`	筛选区域内的零基列索引（必填）
`hiddenButton`	`boolean`	隐藏筛选下拉按钮
`showButton`	`boolean`	显示筛选按钮（默认 `true`）
`filters`	`object`	值/日期筛选：`{ values?: string[], dateGroupItems?: […], blank?, calendarType? }`
`customFilters`	`object`	比较筛选：`{ and?: boolean, entries: [{ operator?, val }] }`，operator 为 `equal`/`notEqual`/`greaterThan`/`greaterThanOrEqual`/`lessThan`/`lessThanOrEqual`
`top10`	`object`	前 N 项筛选：`{ top?: boolean, percent?: boolean, val: number, filterVal?: number }`
`dynamicFilter`	`object`	动态筛选：`{ type: "today" \| "thisMonth" \| "Q1" \| …, val?, maxVal?, valIso?, maxValIso? }`
`colorFilter`	`object`	颜色筛选：`{ dxfId?: number, cellColor?: boolean }`
`iconFilter`	`object`	图标筛选：`{ iconSet: IconSetType, iconId?: number }`

#冻结窗格选项参考

选项	类型	说明
`row`	`number`	冻结的行数
`col`	`number`	冻结的列数