We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on : Nov 16th 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Refreshing a pivot table

Thread ID:

Created:

Updated:

Platform:

Replies:

54635 Jan 12,2007 06:12 PM UTC May 14,2015 02:06 PM UTC ASP.NET Web Forms (Classic) 7
loading
Tags: XlsIO
Ryan
Asked On January 12, 2007 06:12 PM UTC

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 [Syncfusion]
Replied On January 15, 2007 05:44 AM UTC

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
Replied On January 15, 2007 04:19 PM UTC

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 [Syncfusion]
Replied On January 16, 2007 12:50 PM UTC

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
Replied On February 28, 2008 05:55 PM UTC

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 [Syncfusion]
Replied On March 3, 2008 08:04 AM UTC

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
Replied On May 13, 2015 08:20 AM UTC

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 [Syncfusion]
Replied On May 14, 2015 02:06 PM UTC

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


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

;