EXCEL文件开启筛选后,筛选显示的字段与筛选隐藏的字段,有对应的属性作区分吗?是哪个属性?
感谢您的截图。
好的,您可以使用Row.IsHidden属性轻松地将已过滤和未过滤的行分开。请注意,一旦应用了过滤器,所有可见行(在过滤器范围内)都是已过滤的行,而所有其他行(隐藏)将不被过滤。请参阅示例代码以获取完整参考:
例如
样例代码:
//Create a new workbook
Workbook workbook = new Workbook();
//Get the first worksheet in the workbook
Worksheet sheet = workbook.Worksheets[0];
//Get the cells collection in the sheet
Cells cells = sheet.Cells;
//Put some values into cells
cells["A1"].PutValue("Fruit");
cells["B1"].PutValue("Total");
cells["A2"].PutValue("Apple");
cells["B2"].PutValue(1000);
cells["A3"].PutValue("Orange");
cells["B3"].PutValue(2500);
cells["A4"].PutValue("Bananas");
cells["B4"].PutValue(2500);
cells["A5"].PutValue("Orange");
cells["B5"].PutValue(1000);
cells["A6"].PutValue("Grape");
cells["B6"].PutValue(2000);
cells["D1"].PutValue("Count:");
//Set a formula to E1 cell
cells["E1"].Formula = "=SUBTOTAL(2,B1:B6)";
workbook.CalculateFormula();
Console.WriteLine(cells["E1"].StringValue);//5 - Ok
//Represents the range to which the specified AutoFilter applies
sheet.AutoFilter.Range = "A1:B6";
//Add your desired filters
sheet.AutoFilter.AddFilter(0, "Orange");
sheet.AutoFilter.Refresh();
workbook.CalculateFormula();
//Get the changed value for count.
Console.WriteLine(cells["E1"].StringValue);//2 - Ok
//The filtered rows are visible and non filtered cells are invisible,
//So you may easily get to know (using Row.IsHidden attribute) the filtered cells.
//Scan the autofilter range: A2:B6 to get the filtered cell names
for (int i = 1; i <= cells.MaxDataRow; i++)
{
Row row = cells.Rows[i];
if (!row.IsHidden)
{
for (int j = 0; j < 2; j++)
{
Console.WriteLine(cells[i, j].Name);
}
}
}
//all the visible rows are filtered rows and all the invisible rows are non filtered, so you may save to some array or collection accordingly.
workbook.Save("e:\\test2\\out1myfilters1.xlsx");
Hope, this helps a bit.