none
problem with autofilter in excel in c# windows form RRS feed

  • Question

  • Hi,

    I have a strange problem with autofilter function. I put bellow part of my code that uses autofilter function but at implementation step the first filter dose not execute and I do not have any output in result excel sheet but when I copy this part again after other filter it works. if I delete the first filter, this problem occur for second filter code. what should I do and how can I solve this problem?

    xlApp.Visible = true;
                                Excel.Workbook TargetWorkbook1;
                                Excel.Worksheet SourceWorksheet;
                                Excel.Worksheet TargetWorksheet1;
                                Excel.Worksheet TargetWorksheet2;
                                Excel.Worksheet TargetWorksheet3;
                                SourceWorksheet = SourceWorkbook.Worksheets[1];
                                TargetWorkbook1 = xlApp.Workbooks.Add();
                                TargetWorksheet1 = TargetWorkbook1.Worksheets[1];
                                TargetWorksheet2 = TargetWorkbook1.Worksheets.Add();
                                TargetWorksheet3 = TargetWorkbook1.Worksheets.Add();
                                xlApp.ScreenUpdating = false;
                                //replace 4 with 25, 5 with 16, 6 with 120 in your case
                                SourceWorksheet.Columns.AutoFilter(25, "1");
                                SourceWorksheet.Columns.AutoFilter(16, "9710");
                                SourceWorksheet.Columns.AutoFilter(120, "9710");
                                SourceWorksheet.UsedRange.Copy(TargetWorksheet1.Range["A1"]);
                                TargetWorksheet1.Name = "   PPC   ";
                                SourceWorksheet.AutoFilterMode = false;
                                xlApp.ScreenUpdating = true;
                                int number = 0;
                                if (TargetWorksheet1 != null)
                                {
                                    number = TargetWorksheet1.Rows.Count;
                                }
                                SourceWorksheet.Columns.AutoFilter(25, "1");
                                SourceWorksheet.Columns.AutoFilter(16, "9710");//
                                SourceWorksheet.Columns.AutoFilter(120, "<>9710");
                                SourceWorksheet.UsedRange.Copy(TargetWorksheet2.Range["A1"]);
                                TargetWorksheet2.Name = "PPCآمار کارمندان خروجی";
                                SourceWorksheet.AutoFilterMode = false;
                                xlApp.ScreenUpdating = true;
                                int number2 = 0;
                                if (TargetWorksheet2 != null)
                                {
                                    number2 = TargetWorksheet2.Rows.Count;
                                }
                                SourceWorksheet.Columns.AutoFilter(25, "1");
                                SourceWorksheet.Columns.AutoFilter(16, "<>9710");//
                                SourceWorksheet.Columns.AutoFilter(120, "9710");
                                SourceWorksheet.UsedRange.Copy(TargetWorksheet3.Range["A1"]);
                                TargetWorksheet3.Name = "PPC آمار کارمندان ورودی";
                                SourceWorksheet.AutoFilterMode = false;
                                xlApp.ScreenUpdating = true;
                                int number3 = 0;
                                if (TargetWorksheet3 != null)
                                {
                                    number3 = TargetWorksheet3.Rows.Count;
                                }
                                SourceWorksheet.Columns.AutoFilter(25, "1");
                                SourceWorksheet.Columns.AutoFilter(16, "9710");//
                                SourceWorksheet.Columns.AutoFilter(120, "9710");
                                SourceWorksheet.UsedRange.Copy(TargetWorksheet1.Range["A1"]);
                                TargetWorksheet1.Name = "   PPC آمار  قطعی کارمندان  ";
                                SourceWorksheet.AutoFilterMode = false;
                                xlApp.ScreenUpdating = true;

                               
                                Marshal.ReleaseComObject(TargetWorksheet1);
                                Marshal.ReleaseComObject(TargetWorksheet2);
                                Marshal.ReleaseComObject(TargetWorksheet3);
                                Marshal.ReleaseComObject(SourceWorksheet);
                                Marshal.ReleaseComObject(TargetWorkbook1);
                                object misValue = System.Reflection.Missing.Value;
                                SourceWorkbook.Close(true, misValue, misValue);
                                xlApp.Quit();
                                comboBox1.Visible = false;
                                comboBox2.Visible = false;

    Wednesday, December 27, 2017 12:07 PM

Answers

  • Hi, Thank you for your response. my excel file was wrong and I received wrong results. but my problem was solved now. 
    • Marked as answer by nadianaji Tuesday, January 2, 2018 3:30 AM
    Monday, January 1, 2018 11:28 AM

All replies

  • Hello nadianaji,

    Could you check that if the source work sheet is in filter mode while opening? If it is in filter mode and then code will continue filter on the previous result data. That may cause that these is no data match the filter condition. 

    In the code, I would suggest you clear the filter before filtering, just like it will clear filter each time once exporting data to target sheet.

    Here is the simply code.

                                TargetWorksheet1 = TargetWorkbook1.Worksheets[1];
                                TargetWorksheet2 = TargetWorkbook1.Worksheets.Add();
                                TargetWorksheet3 = TargetWorkbook1.Worksheets.Add();
                                xlApp.ScreenUpdating = false;
                                //clear previous filter
                                SourceWorksheet.AutoFilterMode = false;
                                //replace 4 with 25, 5 with 16, 6 with 120 in your case
                                SourceWorksheet.Columns.AutoFilter(25, "1");
                                SourceWorksheet.Columns.AutoFilter(16, "9710");
                                SourceWorksheet.Columns.AutoFilter(120, "9710");

    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.

    Thursday, December 28, 2017 6:41 AM
  • Hi nadianaji,

    Has your issue been resolved? If the suggestion from Terry did not work for you, it would be helpful if you could share us your winform project and test Exce file, then we could try to reproduce your issue to find the root cause.

    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.

    Monday, January 1, 2018 8:49 AM
  • Hi, Thank you for your response. my excel file was wrong and I received wrong results. but my problem was solved now. 
    • Marked as answer by nadianaji Tuesday, January 2, 2018 3:30 AM
    Monday, January 1, 2018 11:28 AM
  • Hello nadianaji,

    I'm glad to hear that your original issue has been resolved. I would suggest you mark help reply or provide your solution and mark it to close this thread. If you have any other issue, please feel free to post threads to let use know.

    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.

    Tuesday, January 2, 2018 3:01 AM