读取EXCEl文件中筛选内容


#1

EXCEL文件开启筛选后,筛选显示的字段与筛选隐藏的字段,有对应的属性作区分吗?是哪个属性?


#2

@philipqian.163,

谢谢你的查询。

我不完全确定你的要求。请详细说明您的要求,并提供示例Excel文件和一些屏幕截图,以显示您需要的内容,我们会尽快检查。


#3

见附件word,帮忙看下,谢谢EXCEL.zip (347.3 KB)


#4

@philipqian.163,

感谢您的截图。

好的,您可以使用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.