Refreshing a pivot table

[Ryan]

I'm creating a report based on an xls template that contains a pivot table.

How can I go about dynamically refreshing the pivot table?

Thanks.

-Ryan

[Melba Winshia]

Hi Ryan,

It is possible to dynamically refresh the data in a pivot table by using the following steps.

Steps:
------

1) Create the pivot table using MS Excel GUI.
2) Specify the named range to be the DataSource of the Pivot Table.
3) Make sure that the "Refresh On Open" option of the Pivot Table options is selected.
4) Dynamically refresh the data in the named range

Use following code snippet:

[C#]

// Change the range values that the Pivot tables range refers to.
myWorkbook.Names["PivotRange"].RefersToRange = mySheet.Range["A1:D27"];


[VB.NET]

' Change the range values that the Pivot tables range refers to.
Private myWorkbook.Names("PivotRange").RefersToRange = mySheet.Range("A1:D27")

Here is the sample for your reference:

Pivot_table.zip

Please take a look at the sample above and let me know if you have any other questions.

Thanks,
Melba

[Ryan]

Melba,

I'm receiving an "Object reference is not set to an instance of an object on the following line:
myWorkbook.Names["PivotRange"].RefersToRange = sheet.Range["A1:D7"];

Here's my code:
------------------------------------
workbook = excelEngine.Excel.Workbooks.Open(Server.MapPath(@"SampleTemplate.xls"));
sheet = workbook.Worksheets["Sheet1"];
workbook.Names["PivotRange"].RefersToRange = sheet.Range[6, 18, 6, 25];
-------------------------------------
Also, I noticed you mentioned to select the "Refresh on Open" checkbox within the pivot table template. Is there any option within XlsIO to completely refresh it via code? I know in the "MS Office 11 Object Library", there is an option to reresh the pivot cache with the following code:
---------------------------
PivotCaches caches = Workbook.PivotCaches();
caches[0].Refresh();
--------------------------

This code will dynamically refresh the pivot tables without the need to select "Refresh on Open".

Is something like this doable in XlsIO?

Thanks.

-Ryan

[Melba Winshia]

Hi Ryan,

1) Regarding "Object reference is not set to an instance of an object"
----------------------------------------------------------------

Could you please provide me the xls file in which you are able to reproduce the issue? It would help me in investigating further on this issue.

2) Regarding refresh the pivot tables:
------------------------------------

It is not possible to dynamically refresh the pivot tables by using Essential XlsIO and Currenly we do not parse Pivot Table records at all so it is not possible to read/Modify Pivot tables. The only way that XlsIO can be used with Pivot tables is by creating the Pivot table using MS Excel and then dynamically fills in data using XlsIO. However, You can create named range and you can add new records to the range with the built in Data Form and the named range will be extended to include the new records.

Please let me know if you have any other questions.

Best Regards,
Melba

[Tommy]

Hi Melba,

I am testing the syncfusion XlsIO function (workbook.Names["PivotRange"].RefersToRange =sheet.Range[6, 18, 6, 25];)

As current the test result, this command can refresh the data range in the excel template, but I can not get refresh data from pivot table with these refresh data(I try to open excel file with syncfusion in the memory, I checked the pivot table's cell value still old ). ONLY I can see the refresh pivot table value is when I open the generated excel file by MS Excel Application ("Refresh On Open"=true).It looks the MS Excel Application do that refresh pivot data job not syncfusion. So is there any way i can get refresh data in the pivot table when I refresh the data range in the same table by Syncfusion. Because you know the pivot table can re-organize the data which I want .

I hope syncfusion has this function as MS Excel COM:
Microsoft.Office.Interop.Excel.PivotTable myPivot = (Microsoft.Office.Interop.Excel.PivotTable)oSheet.PivotTables(1);
Boolean flag = false ;
flag = myPivot.RefreshTable ();


Thanks

[Administrator]

Hi Ryan,

Thanks for your update.

Refreshing a pivot table:

It is not possible to refresh the pivot tables by using XlsIO. The only way is to create the pivot table by using MS Excel and specify the named range to be the DataSource of the Pivot Table and You must select the "Refresh On Open" option of the Pivot table in the MS Excel. We can only dynamically refresh the data in the named range using our XlsIO.

Please let me know if you have any other concerns.

Regards,
G.Yavana

[Claudio Masieri]

I set the "Refresh On Open" option using OpenXml in this way:

 IWorkbook wb = application.Workbooks.Open(System.Web.HttpContext.Current.Server.MapPath("/Content/ExcelTemplates/CurveBuilder.xlsx"));

//create with Xlsio the pivotTable and the data 

...

using (MemoryStream ms = new MemoryStream())

                {

                    wb.SaveAs(ms);

                    ms.Position = 0;

                    using (var document = SpreadsheetDocument.Open(ms, true))

                    {

                        var uriPartDictionary = BuildUriPartDictionary(document);

                        PivotTableCacheDefinitionPart pivotTableCacheDefinitionPart1 = (PivotTableCacheDefinitionPart)uriPartDictionary["/xl/pivotCache/pivotCacheDefinition1.xml"];

                        PivotCacheDefinition pivotCacheDefinition1 = pivotTableCacheDefinitionPart1.PivotCacheDefinition;

                        pivotCacheDefinition1.RefreshOnLoad = true;

                    }

                    downloadfile.Content = ms.ToArray();

                }

//this the called method

  protected Dictionary<String, OpenXmlPart> BuildUriPartDictionary(SpreadsheetDocument document)

        {

            var uriPartDictionary = new Dictionary<String, OpenXmlPart>();

            var queue = new Queue<OpenXmlPartContainer>();

            queue.Enqueue(document);

            while (queue.Count > 0)

            {

                foreach (var part in queue.Dequeue().Parts.Where(part => !uriPartDictionary.Keys.Contains(part.OpenXmlPart.Uri.ToString())))

                {

                    uriPartDictionary.Add(part.OpenXmlPart.Uri.ToString(), part.OpenXmlPart);

                    queue.Enqueue(part.OpenXmlPart);

                }

            }

            return uriPartDictionary;

        }

It works perfectly in my application

[Vikas Sekar]

Hi Claudio,

Thank you for updating us.

Changing pivot table source dynamically can be achieved by referring the Pivot table source to a named range. If the named range is modified, the property IsRefreshOnLoad in PivotCacheImpl has to be TRUE to refresh pivot table.

You can refer to the below code snippet to achieve this and also can refer to a simple sample from the following link.

Code Snippet:

PivotCacheImpl pivotCache = (workbook.PivotCaches[pivotTable.CacheIndex] as PivotCacheImpl);

pivotCache.IsRefreshOnLoad = true;

Sample : http://www.syncfusion.com/downloads/support/directtrac/131774/Sample-1006641213.zip

Please let us know if you need any clarification.

Regards,

Vikas