跳转至

8. 自动筛选

本章介绍自动筛选:写出筛选区域与列条件、条件类型与比较操作符、手动隐藏行,以及读回筛选。

Warning

自动筛选仅支持 xlsx / xlsm。写出到 xls / xlsb / csv 时筛选被丢弃,经 OnDegradation 上报(见第 22 章)。


8.1 筛选条件类型与操作符

FilterColumn.Type 指定某列的筛选条件类型,FilterColumn.Operator 在 Type = Compare 时指定比较关系:

public enum FilterType { Equals, Compare, Contains, BeginsWith, EndsWith, Blank }
public enum FilterOperator { GreaterThan, GreaterThanOrEqual, LessThan, LessThanOrEqual, Between }

FilterColumn 关键成员:

参数 类型 说明
ColumnIndex int 0 基列索引(第 1 列 = 0)
Type FilterType 条件类型
Values List<string> 匹配值集合
Operator FilterOperator Type = Compare 时的比较操作符
MinValue / MaxValue string? Between 的下 / 上界

Type = Compare 并用 GreaterThan 过滤:

new FilterColumn
{
    ColumnIndex = 2,
    Type = FilterType.Compare,
    Operator = FilterOperator.GreaterThan,
    Values = new List<string> { "500" },
};

Between 区间筛选:Operator = FilterOperator.Between 配合 MinValue / MaxValue 指定下界与上界:

new FilterColumn
{
    ColumnIndex = 2,
    Type = FilterType.Compare,
    Operator = FilterOperator.Between,
    MinValue = "100",
    MaxValue = "500",
};

8.2 写出筛选 AutoFilter

Worksheet.Filter 为 AutoFilter 对象,含筛选区域 Range、每列条件 Columns 与手动隐藏行 HiddenRows。Excel 的筛选区域第一行始终是表头,先写入表头行,数据从第 2 行开始:

var ws = Excel.Create().Worksheets["Sheet1"];

ws.SetValue("A1", "Name");
ws.SetValue("B1", "Type");
ws.SetValue("C1", "Score");

for (int r = 2; r <= 542; r++)
{
    ws.SetValue(r, 1, $"Name {r - 1}");
    ws.SetValue(r, 2, r % 2 == 0 ? "Active" : "Inactive");
    ws.SetValue(r, 3, (r - 1) * 10);
}

ws.Filter = new AutoFilter
{
    Range = "A1:C542",
    Columns = new List<FilterColumn>
    {
        new FilterColumn
        {
            ColumnIndex = 1,                 // 0-based 列索引(第 2 列)
            Type = FilterType.Equals,
            Values = new List<string> { "Active" },
        },
    },
};

AutoFilter 关键成员:

参数 类型 说明
Range string 筛选区域 A1 风格引用,首行为表头
Columns List<FilterColumn> 每列筛选条件,0 基列索引
HiddenRows HashSet<int> 手动隐藏的 0 基行索引集合(可选,见 8.3)

⚠️ 不要把数据写进筛选区域的第一行。Excel 会把该行当作表头(显示筛选箭头),数据从第 2 行起才能正确参与筛选。

多列条件(AND):多个 FilterColumn 同时生效,同一行需满足所有列条件:

ws.Filter = new AutoFilter
{
    Range = "A1:D542",
    Columns = new List<FilterColumn>
    {
        new FilterColumn { ColumnIndex = 1, Type = FilterType.Equals, Values = new() { "Active" } },
        new FilterColumn { ColumnIndex = 2, Type = FilterType.Compare, Operator = FilterOperator.GreaterThan, Values = new() { "500" } },
    },
};

8.3 手动隐藏行 HiddenRows

HiddenRows 为 0-based 行索引集合(相对 Rows):

ws.Filter = new AutoFilter
{
    Range = "A1:D542",
    HiddenRows = new HashSet<int> { 1, 3, 5 },   // 隐藏第 2、4、6 行
};

8.4 读取筛选

打开文件后通过 Worksheet.Filter 读取筛选区域与各列条件:

var opened = Excel.Open("filtered.xlsx");
var filter = opened.Worksheets[0].Filter;
if (filter is not null)
{
    Console.WriteLine(filter.Range);
    foreach (var col in filter.Columns)
        Console.WriteLine($"{col.ColumnIndex}: {col.Type} {string.Join(",", col.Values)}");
}

输出:

A1:C542
1: Equals Active