8. 自动筛选¶
本章介绍自动筛选:写出筛选区域与列条件、条件类型与比较操作符、手动隐藏行,以及读回筛选。
Warning
自动筛选仅支持 xlsx / xlsm。写出到 xls / xlsb / csv 时筛选被丢弃,经 OnDegradation 上报(见第 22 章)。
8.1 筛选条件类型与操作符¶
FilterColumn.Type 指定某列的筛选条件类型,FilterColumn.Operator 在 Type = Compare 时指定比较关系:
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)}");
}
输出: