none
filtering the column of excel sheet in C# RRS feed

  • Question

  • Hi,

    I have an excel sheet and I want to filter the values of special column based on its values. I want to select rows which the value of 120th column is not 9710,9100,0 but I use 

    SourceWorksheet.Columns.AutoFilter(120, "<>9710", Excel.XlAutoFilterOperator.xlAnd, "<>0");

    that contains only 2 conditions and I can not consider 9100. how can I solve this problem?

    thantks.

    Tuesday, December 26, 2017 6:58 AM

Answers

  • Hi nadianaji,

    For your requirement, there is no built-in method.

    I would suggest you try code below to work around your issue. 

    private void button1_Click(object sender, EventArgs e)
            {
                Excel.Application app = new Excel.Application();
                app.Visible = true;
                app.Workbooks.Open(@"C:\file path\test.xlsx");
                Excel.Worksheet ws = app.ActiveWorkbook.Worksheets["SourceWorksheet"];
                Filter(ws, 120, new List<int> { 9710, 9100, 0 });
            }
            public void Filter(Excel.Worksheet ws, int columnIndex, List<int> values)
            {
                for(int i = 1; i<= ws.UsedRange.Rows.Count; i++)
                {
                    Excel.Range range = ws.Cells[i, columnIndex] as Excel.Range;
                    int value =int.Parse(range.Value.ToString());
                    if (values.Contains(value))
                    {
                        range.EntireRow.Hidden = true;
                    }
                }
            }

    Best Regards,

    Tao Zhou


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by nadianaji Tuesday, January 2, 2018 7:41 AM
    Monday, January 1, 2018 8:47 AM
  • Hello nadianaji,

    Please try to refer to below simply code and adjust it for your need.

                SourceWorkbook = xlApp.Workbooks.Open(@"C:\Users\Administrator\Desktop\Book1.xlsx");
                SourceWorksheet = SourceWorkbook.Worksheets[1];
                Excel.Worksheet TempWorkSheet = SourceWorkbook.Worksheets.Add();
                TargetWorkbook1 = xlApp.Workbooks.Add();
                TargetWorksheet1 = TargetWorkbook1.Worksheets[1];         
                
                //clear previous filter 
                SourceWorksheet.AutoFilterMode = false;
                //filter source worksheet and export result to temporary worksheet
                SourceWorksheet.Columns.AutoFilter(7, "<>2", Excel.XlAutoFilterOperator.xlAnd, "<>4");           
                SourceWorksheet.UsedRange.Copy();
                TempWorkSheet.Range["A1"].PasteSpecial(Excel.XlPasteType.xlPasteAllUsingSourceTheme);
    
                //filter temporary worksheet and export result to target worksheet
                TempWorkSheet.Columns.AutoFilter(7, "<>5");
                TempWorkSheet.UsedRange.Copy();
                TargetWorksheet1.Range["A1"].PasteSpecial(Excel.XlPasteType.xlPasteAllUsingSourceTheme);

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by nadianaji Tuesday, January 2, 2018 7:41 AM
    Tuesday, January 2, 2018 2:57 AM

All replies

  • Hello nadianaji,

    As far as I know, excel AutoFilter does not support filter NOT equal more than two criteria.

    According to your previous thread, I would suggest create a temporary worksheet in your source workbook and then filter original sheet with "<>9710" and "<>0". After filtering first time, you could copy and paste all the filter result to the temporary sheet and filter with "<>9100" in the temporary sheet. So you will get what you want in to temporary sheet. Copy and paste data in temporary sheet to your target worksheet and then delete the temporary sheet.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by nadianaji Sunday, December 31, 2017 11:33 AM
    • Unmarked as answer by nadianaji Sunday, December 31, 2017 12:50 PM
    Wednesday, December 27, 2017 10:39 AM
  • Hi, 

    Can you please write me a sample code that do it?

    thanks.

    Sunday, December 31, 2017 12:51 PM
  • Hi nadianaji,

    For your requirement, there is no built-in method.

    I would suggest you try code below to work around your issue. 

    private void button1_Click(object sender, EventArgs e)
            {
                Excel.Application app = new Excel.Application();
                app.Visible = true;
                app.Workbooks.Open(@"C:\file path\test.xlsx");
                Excel.Worksheet ws = app.ActiveWorkbook.Worksheets["SourceWorksheet"];
                Filter(ws, 120, new List<int> { 9710, 9100, 0 });
            }
            public void Filter(Excel.Worksheet ws, int columnIndex, List<int> values)
            {
                for(int i = 1; i<= ws.UsedRange.Rows.Count; i++)
                {
                    Excel.Range range = ws.Cells[i, columnIndex] as Excel.Range;
                    int value =int.Parse(range.Value.ToString());
                    if (values.Contains(value))
                    {
                        range.EntireRow.Hidden = true;
                    }
                }
            }

    Best Regards,

    Tao Zhou


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by nadianaji Tuesday, January 2, 2018 7:41 AM
    Monday, January 1, 2018 8:47 AM
  • Hi Tao Zhou,

    is this for loop time consuming or not? I have about 20000 rows in my excel file.

    Monday, January 1, 2018 11:30 AM
  • Hello nadianaji,

    Please try to refer to below simply code and adjust it for your need.

                SourceWorkbook = xlApp.Workbooks.Open(@"C:\Users\Administrator\Desktop\Book1.xlsx");
                SourceWorksheet = SourceWorkbook.Worksheets[1];
                Excel.Worksheet TempWorkSheet = SourceWorkbook.Worksheets.Add();
                TargetWorkbook1 = xlApp.Workbooks.Add();
                TargetWorksheet1 = TargetWorkbook1.Worksheets[1];         
                
                //clear previous filter 
                SourceWorksheet.AutoFilterMode = false;
                //filter source worksheet and export result to temporary worksheet
                SourceWorksheet.Columns.AutoFilter(7, "<>2", Excel.XlAutoFilterOperator.xlAnd, "<>4");           
                SourceWorksheet.UsedRange.Copy();
                TempWorkSheet.Range["A1"].PasteSpecial(Excel.XlPasteType.xlPasteAllUsingSourceTheme);
    
                //filter temporary worksheet and export result to target worksheet
                TempWorkSheet.Columns.AutoFilter(7, "<>5");
                TempWorkSheet.UsedRange.Copy();
                TargetWorksheet1.Range["A1"].PasteSpecial(Excel.XlPasteType.xlPasteAllUsingSourceTheme);

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by nadianaji Tuesday, January 2, 2018 7:41 AM
    Tuesday, January 2, 2018 2:57 AM
  • Hi Tao Zhou,

    is this for loop time consuming or not? I have about 20000 rows in my excel file.

    Hi nadianaji,

    Yes, it's loop to check cells value.

    If you meet performance issue, you may try the different way from Terry.

    Best Regards,

    Tao Zhou


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 2, 2018 5:32 AM
  • thank you, this works for not conditions. but I have a same problem with multiple conditions.

    I want to filter the value of excel column base on these conditions: 1000<=x<=4999 or x==7230 or 8000<=x<=8999 . how can I apply these conditions without using for loop? please guide me.

    Tuesday, January 2, 2018 7:45 AM