Get filtered values of a specific column in Excel using Office.Interop.Excel in C#

Question

I have an Excel sheet named "mySheet" having the columns: "Name", "Gender", "Division".

I want to get an array of students Name whose Division is "D".

Following is my code:

Excel.Sheets ExcelSheets = excelWorkBook.Sheets;
Excel.Worksheet excelWorkSheet = ExcelSheets.get_Item(1);
Excel.Range excelRange = excelWorkSheet.UsedRange;
excelRange.AutoFilter(3, "D", Excel.XlAutoFilterOperator.xlAnd, Type.Missing, Type.Missing);
Excel.Range firstColumn = excelRange.Columns[1];
System.Array myvalues = (System.Array)firstColumn.Cells.Value;
strarrCreateExcel = myvalues.OfType<object>().Select(o => o.ToString()).ToArray();
strarrCreateDB = strarrCreateExcel;

So you tried something, what's wrong with your approach? – mason Mar 30 '16 at 13:21 — mason, Mar 30 '16 at 13:21

score 1 · Answer 1 · answered Apr 07 '16 at 09:05

    xl.Application xlApp = new xl.Application();
    xl.Workbooks xlWbks = xlApp.Workbooks;
    xl.Workbook xlWbk = xlWbks.Open(@"C:\Temp\Book1.xlsx");

    xl.Sheets xlSheets = xlWbk.Sheets;
    xl.Worksheet xlWorkSheet = xlSheets.get_Item(1);
    xl.Range xlUsedRange = xlWorkSheet.UsedRange;
    xlUsedRange.AutoFilter(3, "D", xl.XlAutoFilterOperator.xlAnd, Type.Missing, Type.Missing);

    xl.Range filteredRange = xlUsedRange.SpecialCells(xl.XlCellType.xlCellTypeVisible);
    var strarrCreateExcel = new List<string>();
    foreach (Excel.Range area in filteredRange.Areas)
        {
            foreach (Excel.Range row in area.Rows)
            {
                if (!strarrCreateExcel.Contains(((Excel.Range)row.Cells[1, 1]).Text))
                    strarrCreateExcel.Add(((Excel.Range)row.Cells[1, 1]).Text);
            }
        }

Using above code solve my issue.

score 0 · Answer 2 · edited May 23 '17 at 12:31

Try the following code:

        private void AutoFilterNames()
    {
        xl.Application xlApp = new xl.Application();
        xl.Workbooks xlWbks = xlApp.Workbooks;
        xl.Workbook xlWbk = xlWbks.Open(@"C:\Temp\Book1.xlsx");

        xl.Sheets xlSheets = xlWbk.Sheets;
        xl.Worksheet xlWorkSheet = xlSheets.get_Item(1);
        xl.Range xlUsedRange = xlWorkSheet.UsedRange;
        xlUsedRange.AutoFilter(3, "D", xl.XlAutoFilterOperator.xlAnd, Type.Missing, Type.Missing);

        xl.Range filteredRange = xlUsedRange.SpecialCells(xl.XlCellType.xlCellTypeVisible);
        var names = new List<string>();

        for (int areaId = 2; areaId <= filteredRange.Areas.Count; areaId++)
        {
            xl.Range areaRange = filteredRange.Areas[areaId];
            object[,] areaValues = areaRange.Value;
            names.Add(areaValues[1, 1].ToString());
        }

        var namesToArray = names.ToArray();
    }

Note: Do not forget to release excel objects. You can get help from here

i am getting filteredRange.Areas.Count = 2 only.. why it is ? i want complete list after filter :( — Bh00shan, Mar 31 '16 at 12:12
If you get count = 2, it means that only two rows are visible after the filter, isn't it? — ehh, Mar 31 '16 at 12:56

Get filtered values of a specific column in Excel using Office.Interop.Excel in C#

2 Answers2