C＃Excel自动化：在使用SpecialCells（）的AutoFilter（）之后检索行看起来不正常 [英] C# Excel Automation: Retrieving rows after AutoFilter() with SpecialCells() does not seem to work properly

问题描述

第一次海报在这里:)。从WinForms C＃应用程序自动执行Excel 2010时遇到以下问题。我的测试表看起来如下：

 索引值1 Value2 
 AAA 2 3 
 AAA 3 4 
 AAA 3 8 
 BBB 2 2 
 BBB 5 6 
 BBB 3 5 
 CCC 1 2 
 CCC 2 2 
 DDD 2 5 
  

我成功打开工作簿，并从我的C＃应用​​程序加载工作表。之后，我运行以下代码：

  Excel.Range range = xlSheet.UsedRange; 
 range.AutoFilter（1，AAA，Excel.XlAutoFilterOperator.xlAnd，Type.Missing，true）; 
 Excel.Range filteredRange = range.SpecialCells（Excel.XlCellType.xlCellTypeVisible）; 
  

这样可以预期，filterRange现在包含测试表的前四行（列名称和所有AAA行）。然而，如果我尝试使用AutoFilter获取所有的BBB行，就像这样

  range.AutoFilter（1， BBB，Excel.XlAutoFilterOperator.xlAnd，Type.Missing，true）; 
 Excel.Range filteredRange = range.SpecialCells（Excel.XlCellType.xlCellTypeVisible）; 
  

我最后只得到filteredRange范围中的表的第一行（列名）。如果我实际上在Excel中打开表，我可以看到它被正确过滤（BBB行）被过滤，但是不知何故，Range.SpecialCells（）方法的行为不如预期的那样，只返回第一行。
我已经尝试了我能想到的一切，但由于我是Excel自动化的新手，也许我错过了一些东西，所以我以为你们可能会有所帮助。我唯一想到的是，在第一种情况（AAA）中，所有可见的行是连续的 - 列名称在行1上，AAA行分别是2,3和4。在第二种情况下，名称在行1上，但是BBB行具有索引5,6和7，即表中存在孔。这可能会影响SpecialCells（）方法？

提前感谢您可能有任何输入！

解决方案

好的，我解决了自己的问题，但是我想分享一下解决方案，因为有一天其他可怜的灵魂可能会遇到同样的问题。基本上，我的第一个想法，问题可能是与行不连续导致我的正确答案。只要所有过滤的值都在表格的第一行之下，SpecialCells（）方法返回一个单一区域，因此filteredRange在其Value2成员中显示所有所需的值（在上面的测试用例中，全部为AAA）行。然而，如果过滤的行在表中进一步向下，就像BBB一样，SpecialCells（）方法返回多个区域，在这种情况下，仅包含列名称行的第一个区域，第二个区域包含三个BBB行。解决方案是迭代filterRange中的所有区域，并从中提取/处理值：

  for（int areaId = 1 ; areaId< = filteredRange.Areas.Count; areaId ++）
 {
 Excel.Range areaRange = filteredRange.Areas.get_Item（areaId）; 
 object [，] areaValues = areaRange.Value2; 
 //用这里的值做一些事情... 
  

就这样。我希望这有助于别人...

First time poster here :). I'm having the following trouble automating Excel 2010 from a WinForms C# application. My test table looks the following way:

Index    Value1  Value2
AAA        2       3   
AAA        3       4
AAA        3       8
BBB        2       2
BBB        5       6
BBB        3       5
CCC        1       2
CCC        2       2
DDD        2       5

I successfully open the workbook and load the sheet from my C# application. After that I run the following code:

        Excel.Range range = xlSheet.UsedRange;
        range.AutoFilter(1, "AAA", Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);
        Excel.Range filteredRange = range.SpecialCells(Excel.XlCellType.xlCellTypeVisible);

This works as expected, and filterRange now contains the first four rows of my test table (the column names and all the "AAA" rows). If, however, I try to use AutoFilter to get all the "BBB" rows, like this

        range.AutoFilter(1, "BBB", Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);
        Excel.Range filteredRange = range.SpecialCells(Excel.XlCellType.xlCellTypeVisible);

I end up with only the firs row of the table (column names) in the filteredRange Range. If I actually open the table in Excel I can see that it is correctly filtered (the "BBB" rows) are filtered, but somehow the Range.SpecialCells() method does not behave as expected and only returns the first row. I've tried everything I can think of, but since I'm new to Excel automation, maybe I'm missing something, so I thought you guys might be able to help. The only thing that comes to my mind is that in the first case ("AAA"), all visible rows are consecutive - the column names are on row 1 and the "AAA" rows are 2, 3, and 4 respectively. In the second case, the names are on row 1 but the "BBB" rows are with index 5, 6 and 7, i.e. there is an "hole" in the table. Could this perhaps affect the SpecialCells() method?

Thanks in advance for any input you might have!

解决方案

OK, I solved my own problem, but I would like to share the solution because some other poor soul might struggle with the same problem someday. Basically, my first idea that the problem might be with the rows not being consecutive led me to the right answer. As long as all the filtered values are right under the first row in the table, the SpecialCells() method returns one single area, and so the filteredRange shows in its Value2 member all of the desired values (in the test case from above, all the "AAA") rows. If, however, the filtered rows are further down in the table, as is the case with "BBB", the SpecialCells() method returns multiple areas, in this case two - first area containing only the column names line, and the second area containing the three "BBB" rows. The solution is to iterate over all the areas in filteredRange and extract/manipulate the values from there:

        for (int areaId = 1; areaId <= filteredRange.Areas.Count; areaId++)
        {
            Excel.Range areaRange = filteredRange.Areas.get_Item(areaId);
            object[,] areaValues = areaRange.Value2;
            // Do something with the values here...

So that's it. I hope that this helps someone else someday...

这篇关于C＃Excel自动化：在使用SpecialCells（）的AutoFilter（）之后检索行看起来不正常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！