none
How can programmatically change the pivot chart data by setting filter values in C# code. RRS feed

  • Question

  • Hi all,

    I have a excel workbook having two sheets.

    In sheet 1, there is 'Region' filter dropdown having America, Emea and Asia as a filter values.

    In sheet 2, we have pivot table and few charts which display data based on 'Region' filter value selection (multi selection) from sheet 1.

    My question is how can I Programmatically set the filter values so that the charts and table should show the data based on what we set the filter values through the code.

    I am using Microsoft.Office.Interop.Excel 2015 library.


    Sonu88

    Wednesday, May 3, 2017 9:18 AM

Answers

  • Hello,

    Do you want to change the datasource of the pivot table or filter the pivot table based the filtered value in Sheet1?

    I think there is no need to change the datasource. To filter the pivottable, you could see the following sample. The code is programmatically filtering the sheet1 with strList and filter the pivot table at the same time.

    //ws1 is sheet1, ws2 is sheet2
    String[] strList = { "America", "Emea", "Asia" };
    //filter sheet1 data , A1:C17 is data range.
    ws1.Range["A1:C17"].AutoFilter(1, strlist, Excel.XlAutoFilterOperator.xlFilterValues);
    //filter pivot table
                Excel.PivotTable pt;
                Excel.PivotField pf;
                pt = ws2.PivotTables(1);
                pf = pt.PivotFields("Region");
                pf.ClearAllFilters();
                int count = pt.PivotFields(1).PivotItems.Count;
                for (int i = 1; i <= count; i++)
                // string nm = pf.PivotItems(i).Name;
                {
                    if (CheckList(strlist, pf.PivotItems(i).Name)) {
                        pf.PivotItems(i).visible = true;
                    }
                    else
                    {
                        pf.PivotItems(i).visible = false;
                    }
    
                }
    
    public  bool CheckList(String[] arr, String targetValue)
            {
                foreach(String s in arr)
                {
                    if (s.Equals(targetValue))
                        return true;
                }
                return false;
            }
    

    Regards,

    Celeste


    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 Sonu888 Sunday, May 7, 2017 12:47 PM
    Thursday, May 4, 2017 5:29 AM
    Moderator