c# Collapse Fields in Excel PivotTable

Question

I have a pre-made Excel with PivotTable that gets Data from another worksheet, I fill via c# code.

In the PivotTable there are three Row fields, which after getting populated and the Pivot refreshed via code, make all the rows expanded after opening the Excel.

I already tried using the DrilledDown method (in the fields, but I get an error), the ShowDetails only works in the items (inside the fields).

I got it to work, but it takes too much time because it uses the ShowDetails in each item, is there any other way, and quick for collapsing the fields? (opening the Excel and doing it manually is not an option).

My Code (which collapses the fields are):

listItems.Add("Data");
listItems.Add("Months");
listItems.Add("Grocery");
    
Microsoft.Office.Interop.Excel.PivotFields pfs = (Microsoft.Office.Interop.Excel.PivotFields)pivot.PivotFields();
    
foreach (String s in listItems)
{
    Microsoft.Office.Interop.Excel.PivotField pf = (Microsoft.Office.Interop.Excel.PivotField)pivot.PivotFields(s);
                    
    foreach (Microsoft.Office.Interop.Excel.PivotItem item in (Microsoft.Office.Interop.Excel.PivotItems)pf.PivotItems())
    {
        if (item.Value == "(blank)")
            item.Visible = false;

        item.ShowDetail = false;
    }  
}

The thing is, maybe there's an easy and quicker way? I was trying something like

pf.DrilledDown = False;

or

pf.ShowDetail = False;

But it doesn't work. Any Ideas?

score 0 · Answer 1 · answered Jul 17 '15 at 14:16

Well, manage from somehow to only collapse the first item in each field (that way the cycle doesn't go until the end, and since Excel collapses automatically the other similar items (when you collapse one of the same type field), it works).

PS: Since the first field from the groceries is allways the "_blank" one, i know for sure there's allways something after the "_blank" one, but I cannot apply only the collapse to the "_blank", because then it doesn't apply to the other items (I supposed it must be something with a Value inside).

So...got this (it's a lot more quick, but still, I think it shall exist another way (with simpler code in c# (without using VB macros inside the Excel)), but if there is, it's hard to find - few people manipulate pivot tables programatically.

                listItems.Add("Date");
                listItems.Add("Months");
                listItems.Add("Groceries");

                Microsoft.Office.Interop.Excel.PivotFields pfs = (Microsoft.Office.Interop.Excel.PivotFields)pivot.PivotFields();

                foreach (String s in listItems)
                {
                    Microsoft.Office.Interop.Excel.PivotField pf = (Microsoft.Office.Interop.Excel.PivotField)pivot.PivotFields(s);



                    foreach (Microsoft.Office.Interop.Excel.PivotItem item in (Microsoft.Office.Interop.Excel.PivotItems)pf.PivotItems())
                    {
                        if (pf.Value == "Date")
                        {
                            item.ShowDetail = false;
                            break;
                        }
                        if (pf.Value == "Months")
                        {
                            item.ShowDetail = false;
                            break;
                        }
                        if (pf.Value == "Groceries")
                        {

                            if (item.Value == "(blank)")
                            {
                                item.Visible = false;
                                continue;
                            }
                            item.ShowDetail = false;
                            break;

                        }
                    }
                }

c# Collapse Fields in Excel PivotTable

1 Answers1