none
Advanced Pivot Table in VSTO C# RRS feed

  • Question

  • I'm developing an excel addin in c# and have to create a pivot table, which I don't know how to organize the data, how it should be.

    This is, how the final Pivot Table should looks like:

    But I only get something like this and I have no Idea where I have to how to change my code:

    Finally my code to arrange the data source columns:

    /* PIVOT RowFields */
    Excel.PivotField nameField = pTable.PivotFields("Name");
    nameField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
    nameField.Position = 1;
    
    Excel.PivotField monthField = pTable.PivotFields("Monat");
    monthField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
    monthField.Position = 2;
    
    /* PIVOT Data */
    Excel.PivotField sum200Field = pTable.PivotFields("Summe 1");
    sum200Field.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
    
    Excel.PivotField sum700Field = pTable.PivotFields("Summe 2");
    sum700Field.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
    
    Excel.PivotField sumDiffField = pTable.PivotFields("Differenz");
    sumDiffField.Orientation = Excel.XlPivotFieldOrientation.xlDataField;

    I'm using .NET Framework 4 ... I read a lot of articles in the internet, but nothing was useful...

    Does anyone have an Idea?

    Tuesday, June 3, 2014 12:01 PM

Answers

  • Hi,

    In fact, to creating a pivot table automatically in Excel add-in, we need to know how to create such pivot table manually in Excel with the data source firstly. Because automation is a simulation for the manually steps.

    From the data source and the target pivot table you provided, it's hard for us to understand them clearly since I cannot find some pivot fields in your data source (e.g. "Summe von Summe_Betrag_200").

    So I suggest you learning to create such a pivot table as you want manually, then record a macro for all the steps to help you learn more about Excel Object Model. The macro recorder will record all the steps required to complete the actions that you want your macro to perform. After that, you could start writing your add-in with C# according to the VBA macro. If you have any questions about converting the VBA language to C# in your Add-in, please feel free to let me know or post a new thread to involve more partners of this community.

    If you have any question about how to create such a pivot table manually, you could post the requirement with more details in Excel IT product forum for more effective responses.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, June 4, 2014 3:18 AM
    Moderator

All replies

  • This is my data source, I had to added it here, because not more than two images are allowed:

    Tuesday, June 3, 2014 12:03 PM
  • Hi,

    In fact, to creating a pivot table automatically in Excel add-in, we need to know how to create such pivot table manually in Excel with the data source firstly. Because automation is a simulation for the manually steps.

    From the data source and the target pivot table you provided, it's hard for us to understand them clearly since I cannot find some pivot fields in your data source (e.g. "Summe von Summe_Betrag_200").

    So I suggest you learning to create such a pivot table as you want manually, then record a macro for all the steps to help you learn more about Excel Object Model. The macro recorder will record all the steps required to complete the actions that you want your macro to perform. After that, you could start writing your add-in with C# according to the VBA macro. If you have any questions about converting the VBA language to C# in your Add-in, please feel free to let me know or post a new thread to involve more partners of this community.

    If you have any question about how to create such a pivot table manually, you could post the requirement with more details in Excel IT product forum for more effective responses.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, June 4, 2014 3:18 AM
    Moderator