Refresh existing Pivot Table without opening workbook

Aspose.Cells Product Family
1

Hello Team,

Im using Aspose Cells for .NET. (version 7.2.0)

I want to refresh an existing Pivot Table in a template workbook without opening it.

I know that we can refresh Pivot tables when opening workbooks by setting the property pivotTable.Cache.IsRefreshOnLoad = true, and then when we open the workbook the pivot table data will be refreshed based on the data in the data source worksheet.

But in my case i want to refresh the Pivot Table in code, so i can send it in email.

Is there a solution for this ? If not is there a workaround to achieve this ? (Recreate the pivot table maybe ? or maybe save the workbook and reopen it in code)

Thanks,
Oussama

2

@Oussama_Mokni,

Thanks for your your query.

Please try using the following methods to refresh the Pivottable in the template file:
i.e.,
pivotTable.RefreshData();
pivotTable.CalculateData();

Hope, this helps a bit.

1 Like
3

Hello,

Thanks for your reply.

It works :slight_smile: and I exported only my pivot worksheet to one Html file successfully with refreshed data :slight_smile:

Just one last point, since my pivot worksheet contains other data, can I export only the pivot table to Html file ? Like we can do with Images by exporting only the range we want.

Thanks a lot,
Oussama

4

@Oussama_Mokni,

Well, you got to remove or hide your unwanted data range by yourself in code and then render to HTML file format.

By the way, in the newer versions (e.g v19.2, you may simply set the printable area (for PivotTable report range) in code and then use HtmlSaveOptions.ExportPrintAreaOnly attribute to render your desired area only.

1 Like
5

Thanks for your reply.

Waw, the HtmlSaveOptions.ExportPrintAreaOnly is pretty good. I will see with my comany if we can migrate to the last version.

Else I will Remove unwanted data range in code. But I think in 7.2.0 version we can’t remove PivotTables from worksheet. The method Remove does not exist and The method RemoveAt() didn’t worked for me. I used ClearRange() medhod like this :

workbook.Worksheets[pivotTableWorkSheetName].PivotTables.RemoveAt(1); – Didn’t work
workbook.Worksheets[pivotTableWorkSheetName].Cells.ClearRange(pivotTable2.TableRange2); – work

Thanks a lot, It’s ok for me.

Regards,
Oussama

6

@Oussama_Mokni,

We are sorry but we cannot evaluate or check any issue in older versions. Neither we can include any fixes in the older versions. The fixes are based on latest APIs set only.

It seems like you have sorted out your issue, so you may use this method for your needs.

7

Its ok. The better is to migrate to the last version.

Thanks.