C # Excel Automation: retrieving rows after AutoFilter () using SpecialCells () does not work properly

Question

The first poster is here :). I had the following problem: Excel 2010 automation from a WinForms C # application. My test pattern is as follows:

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 a book and load a 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 pattern (column names and all rows are “AAA”). If, however, I try to use AutoFilter to get all the "BBB" strings, for example,

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

Ultimately, I get only the first row of the table (column names) in the filterRange range. If I really open the table in Excel, I see that it is being correctly filtered (the "BBB" rows) are being filtered, but somehow the Range.SpecialCells () method does not behave as expected and returns only the first row. I tried everything I could think of, but since I'm new to Excel automation, I might have missed something, so I thought you guys could 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 rows "AAA" are 2, 3, and 4, respectively. In the second case, the names are indicated on line 1, but the lines "BBB" have an index of 5, 6 and 7, that is, there is a "hole" in the table. Could this affect the SpecialCells () method?

Thanks in advance for any input you have.

Answer 1

Well, I solved my problem, but I would like to share this solution, because some other poor soul can fight the same problem someday. Basically, my first thought that the problem might be related to a sequence of lines led me to the correct answer. As long as all filtered values ​​are located under the first row in the table, the SpecialCells () method returns one single area, and therefore filterRange shows all necessary values ​​in its Value2 element (in the test case, all “AAA” above). If, however, the filtered rows are even lower in the table, as in the case of "BBB", the SpecialCells () method returns several regions, in this case two - the first region containing only the column name row, and the second region containing three BBB rows . The solution is to iterate over all areas in filterRange and retrieve / 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 what is it. I hope this someday helps someone else ...

Answer 2

Try the following:

 var rowcount = filteredRange.Count / filteredRange.Columns.Count;