none
Excel Pivot Table - Get data in Pivot Field

    Question

  •  Hi -

    Basic question: How can I iterate through the items in a Pivot Table report filter?

    So lets say a pivot table contains a "Car Model" report filter with elements such as "Chevy", "Olds", "Toyota", etc.  For starters I just want to be iterate through the Car Model filter and show each element in a MessageBox (just to get the hang of it).

    Other notes:
    I'm automating Excel 2007 via C# and VSTO.  
    The PivotTable data source is an OLAP cube stored on a server
    While I'd like a C# snippet I'll take VB.Net (and even VBA, but third choice)
    What I really want to do is change the selected element to "Chevy", print the page, select "Olds", print the page, select "Toyota", print the page ....

    My code so far:

    Excel.PivotTable pvt = (Excel.PivotTable)Globals.Sheet1.PivotTables("PivotTable1");
    Excel.PivotField fld = (Excel.PivotField) pvt.PivotFields("[Contract Info].[Model].[Model]");
    fld.CurrentPageName = "[Contract Info].[Model].&[Chevy]"; //<- Changes the filter from "All" to "Chevy"
    // Code works to this point

    But I can't figure out how to expose the values in the PivotField (psudo code along the lines of:)
    foreach (PivotItem item in fld.PivotItems)
         MessageBox.Show(item.Name)


    Thanks for any suggestions
    Mark
    Friday, October 24, 2008 8:04 PM

Answers

All replies

  • Could this forum thread be of help to your issue?

    Here is the thread:
    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=999524&SiteID=1

    Although I read in this thread that they have a somewhat similar issue as yours and not sure it was fixed in the end ...

    Cheerz,

    -= Maarten =-
    Software Engineer * MVP-Visual Developer-VSTO
    Friday, October 24, 2008 8:42 PM
  • I'll take a look, thanks for the post.
    Sunday, October 26, 2008 9:32 PM
  • Maarten -

    From what I can see, "PageFields" does not seem to be exposed in the C# world (or more likely I can't figure out how to access/find it).  The link you provided is VBA.

    Maybe this is a PIA issue [I'm not an expert at PIA at all, just have a sense that Interop.Excel != Excel].

    I looked at PivotItem and PivotItems() and had success using these to look at the rowlabels and columnlabels of the pivottable, but not the page (i.e., filter) level of the pivottable.

    At this point I need to think about a different approach.  Maybe I write the code in VBA and call it from C# or I bring the values for the page or filter values into the workbook via a call to SQL database (which is more likely the approach I'll take).  

    It would've been nice to work with the data in the PivotTable ....

    Thanks for looking at this issue.

    Mark
    Monday, October 27, 2008 12:36 PM
  • Hi Mark,

    Could you by any chance provide me with some sample PIA C# code you use to generate your pivot? I'm trying to create a pivot from C#, but I get stuck long before I get to the point where you are!

    It seems impossible to add fields to the RowFields and ColumnFields of the pivot from C# - VBA no problem...

    Many thanks!
    Tuesday, October 28, 2008 6:55 AM
  • Zavi said:

    Could you by any chance provide me with some sample PIA C# code you use to generate your pivot? I'm trying to create a pivot from C#, but I get stuck long before I get to the point where you are!

    It seems impossible to add fields to the RowFields and ColumnFields of the pivot from C# - VBA no problem...


    Not everyone who knows Excel well can drop in regularly. But if you can post the (basic) VBA code for what you need to do we can probably help you "translate" it to C#.

    It would also help if you could copy/paste in a small example of sample data to base the pivot table on, then if there are still problems we can better discuss the results we see...

    Cindy Meister, VSTO/Word MVP
    Tuesday, October 28, 2008 12:50 PM
  • Zavi -

    Apologies for not seeing your question sooner.  Essentially, I couldn't figure out how to expose the PivotFields in C#.  VBA is full of simple examples of how to do this.

    Instead, I queried the database for the items that would be in the pivot table when the application first starts up and stored them in an ArrayList.  And basically looped through the arraylist, building the "CurrentPageName" with each iteration.

    I've included some code that illustrates.  Hope it helps.


    Mark

    //Define PivotTable and reset filters
    Excel.PivotTable pvt = (Excel.PivotTable)Globals.Sheet1.PivotTables("PivotTable1");
    ResetFilters(pvt);

    //Define PivotFields
    Excel.PivotField pivFldVar = (Excel.PivotField)pvt.PivotFields("[Contract Info].[" + pivotFieldVar + "].[" + pivotFieldVar + "]");
    Excel.
    PivotField pivFldYear = (Excel.PivotField)pvt.PivotFields("[Contract Info].[Contract Year Adj].[Contract Year Adj]");

    //Define variables
    Excel.Range printRange;
    string pageNameVar;
    string pageNameYear;

    int p = 0; //page counter
    int rowCnt = 55; //number of rows for each Facility/Year in the printed worksheet
    int scale = 60; //print scale for printed worksheet

    for (int v = 0; v < arrayList.Count; v++)
    //for (int v = 0; v < 2; v++)
    {

    //Assign the PageName ... And get rid of the "&" if we are viewing the "All" value
    pageNameVar = "[Contract Info].[" + pivotFieldVar + "].&[" + arrayList[v].ToString() + "]";
    if (arrayList[v].ToString() == "All")
       pageNameVar = pageNameVar.Replace(
    "&", "");

    //Change Curent PageName
    pivFldVar.CurrentPageName = pageNameVar;

    //Refresh data
    WorkingForm();

    for (int y = 0; y < Globals.Sheet3.AListContractYearAdj.Count; y++)
    //for (int y = 0; y < 2; y++)
    {

    //Increment page number
    p++;

    //Assign the PageName ... And get rid of the "&" if we are viewing the "All" value
    pageNameYear = "[Contract Info].[Contract Year Adj].&[" + Globals.Sheet3.AListContractYearAdj[y].ToString() + "]";
    if (Globals.Sheet3.AListContractYearAdj[y].ToString() == "All")
        pageNameYear = pageNameYear.Replace(
    "&", "");

    //Activate Sheet1
    Globals.Sheet1.Activate();

    //Change the CurrentPageName and Refresh data
    pivFldYear.CurrentPageName = pageNameYear;
    WorkingForm();

    //Copy the results from the template
    Globals.Sheet1.Sheet1_Print_Area.Copy(oMissing);

    //Activate the print worksheet and select the cell that we are pasting the results to
    ((Excel._Worksheet)reportSheet).Activate();
    printRange = (Excel.
    Range)reportSheet.get_Range("a" + Convert.ToString((p - 1) * rowCnt + 1), oMissing);
    printRange.Select();

    //For the first iteration we have to paste the column widths ... after that we have to put in a page break
    if (p == 1)
    printRange.PasteSpecial(Microsoft.Office.Interop.Excel.
    XlPasteType.xlPasteColumnWidths, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, oMissing, oMissing);

    //else
    // reportSheet.HPageBreaks.Add(printRange);
    //Paste results then paste values the results

    // The first paste results is used to get the text box to copy ... and I'm not aware if any other way to bring them over; it also brings number formats.
    reportSheet.Paste(oMissing, oMissing);

    printRange.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, oMissing, oMissing);

    //Add PageBreak and Solid black line

    //printRange = (Excel.Range)reportSheet.get_Range("a" + Convert.ToString((p) * rowCnt + 1), "t" + Convert.ToString((p) * rowCnt + 1));

    //reportSheet.HPageBreaks.Add(printRange);

    //Add Solid black line and PageBreak

    printRange = (Excel.Range)reportSheet.get_Range("b" + Convert.ToString((p) * rowCnt), "t" + Convert.ToString((p) * rowCnt));

    printRange.Select();

    printRange.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;

    printRange.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).Weight = Excel.XlBorderWeight.xlMedium;

    printRange = (Excel.Range)reportSheet.get_Range("a" + Convert.ToString((p) * rowCnt + 1), "t" + Convert.ToString((p) * rowCnt + 1));

    printRange.Select();

    reportSheet.HPageBreaks.Add(printRange);

    }

    }

    Thursday, November 27, 2008 2:37 AM