Modify pivot table filter and refresh data in code without using Microsoft.Office.Interop RRS feed

  • Question

  • I have xlsx file with pivot table (with OLAP data source) and some filter (pivot field), I’m need to:

    -Open the file
    -Modify pivot field setting
    -Refresh data from OLAP using new filter value
    -Save new xlsx file, using the new data

    Is something like this possible without using Microsoft.Office.Interop? We want to use this functionality in our web application, but I can't find any other way to refresh the data in code, other then using Microsoft.Office.Interop - is there any?

    Thanks in advance.

    Best regards.

    Tuesday, September 9, 2014 7:30 AM

All replies

  • Hello James,

    You can try to use Open XML SDK or search the internet for any commercial components.

    Tuesday, September 9, 2014 10:55 AM
  • Thanks for the reply - I tried using OpenXML, but it will only allow me to manipulate the file, problem with data refresh still remains - I know I can set Excel to automatically refresh file during opening process, but we can't simply send modified files with our OLAP connection strings to our clients - we just want to send data.

    Are there any libraries (free or commercial) that would allow as to requery OLAP for data?

    Tuesday, September 9, 2014 11:29 AM
  • Tuesday, September 9, 2014 11:51 AM