CODEWITHSUNDEEP
c#excelinteropoffice-interop
Bh00shan
Bh00shan

Reputation: 508

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

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;

Upvotes: 0

Views: 5128

Answers (2)

Bh00shan
Bh00shan

Reputation: 508

    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.

Upvotes: 1

ehh
ehh

Reputation: 3480

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

Upvotes: 0

Related Questions