none
OpenXml - Refresh pivot table programatically. RRS feed

  • Question

  • Hi

    I have a pre-defined excel template with two worksheets

    1. RAW_DATA - as name suggests its the raw data.

    2. Pivot  - contains a pivot created on the Raw data.

    The template itself does not have any Raw data and hence the pivot is empty.

    Based on the parameters selected by the user on a web form, I need to populate

    1. Populate the Raw_data worksheet - basically fetch data from SQL and write it here.

    2. Refresh/Recalculate the Pivoit.

    I wanted to know if the OpenXML SDK 2.0 provides the ability the refresh/recalculate the pivot. If yes, a code snippet/direction would be helpfull.

    Thanks

    Nakul Ringshia

    Monday, April 8, 2013 2:39 PM

Answers

  • You can refer to Working with PivotTables (Open XML SDK) in which it mentioned

    The pivot cache definition contains the definitions of all fields in the PivotTable. If you create a PivotTable based on a regular table, each column in the table becomes a field of the pivot cache definition. The pivot cache contains the field definitions and information about the type of content found in that field. It also maintains a reference to the source data in the cache markup so that the pivot cache can be refreshed along with the cached data in the pivot cache records part.

    So, I think you'll not need to force a refresh action. Excel will do that. 


    with regards, Yuri

    Tuesday, April 9, 2013 12:12 PM

All replies

  • You can refer to Working with PivotTables (Open XML SDK) in which it mentioned

    The pivot cache definition contains the definitions of all fields in the PivotTable. If you create a PivotTable based on a regular table, each column in the table becomes a field of the pivot cache definition. The pivot cache contains the field definitions and information about the type of content found in that field. It also maintains a reference to the source data in the cache markup so that the pivot cache can be refreshed along with the cached data in the pivot cache records part.

    So, I think you'll not need to force a refresh action. Excel will do that. 


    with regards, Yuri

    Tuesday, April 9, 2013 12:12 PM
  • Hi Nakul,

    I temporarily marked the reply as answer. You can unmark it if it provides no help.

    Please feel free to let us know if you need any help.

    Have a nice day.


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, April 17, 2013 12:42 PM
    Moderator