Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Mon Feb 26, 2018 3:13 pm

Hello,

I want to refresh PivotTable.
I thought it was automatic so i have do that :
Code: Select all
var workbook = new Workbook();
workbook.LoadFromFile(Path + "Sample.xlsx");
var ws = workbook.Worksheets[0];

ws.Range["B4"].Value = "1";

workbook.SaveToFile(Chemin + "Sample - Out.xlsx", ExcelVersion.Version2016);


But PivotTable is not refresh :
SampleOut1.png
SampleOut1.png (6.89 KiB) Viewed 3722 times


So i have try this :
Code: Select all
var workbook = new Workbook();
workbook.LoadFromFile(Chemin + "Sample.xlsx");
var ws = workbook.Worksheets[0];

ws.Range["B4"].Value = "1";

// https://www.e-iceblue.com/forum/how-to-refresh-pivot-table-t5062-15.html
XlsPivotTable pt = workbook.Worksheets[0].PivotTables["TCD"] as XlsPivotTable;
pt.Cache.IsRefreshOnLoad = true;

workbook.SaveToFile(Chemin + "Sample - Out.xlsx", ExcelVersion.Version2016);


but in output, Source data of PivotTable have change :
SampleOut2.png
SampleOut2.png (17.64 KiB) Viewed 3722 times


Do you know how can i refresh a PivotTable ?

Thanks
Attachments
Sample.zip
(8.85 KiB) Downloaded 345 times

VincentB
 
Posts: 120
Joined: Thu Sep 15, 2016 9:44 am

Tue Feb 27, 2018 5:56 am

Hello Vincent,

Thanks for your inquiry.
Actually, the code pt.Cache.IsRefreshOnLoad = true; you used to refresh PivotTable is correct. I checked your input document and found the source data is Feuil1!$A$1:$A$5, which is the same as the source data of PivotTable in output. Please check it on your side. Or you could directly refresh the PivotTable in MS Excel, you will also get the same result as our product. If there is any confusion, welcome to write back.
The source data of PivotTable in your input document:
Sample.png
Sample.png (22.63 KiB) Viewed 3709 times

Sincerely,
Nina
E-iceblue
User avatar

Nina.Tang
 
Posts: 1182
Joined: Tue Sep 27, 2016 1:06 am

Tue Feb 27, 2018 8:27 am

Hello,

Indead, i make a mistake on the source Data or the Pivot Table and the code pt.Cache.IsRefreshOnLoad = true; is correct.

Sorry for inconvenant.

Thanks

VincentB
 
Posts: 120
Joined: Thu Sep 15, 2016 9:44 am

Tue Feb 27, 2018 8:32 am

Hi,

Thanks for your quick response.
If you need further assistance, please feel free to contact us.

Sincerely,
Nina
E-iceblue support team
User avatar

Nina.Tang
 
Posts: 1182
Joined: Tue Sep 27, 2016 1:06 am

Wed Oct 17, 2018 9:51 pm

I have two sheet in excel file. One sheet contain all the data and other sheet contain pivotal table. I need to set datasource in pivotal table then refresh it. How Can I use different sheet as data source in pivotal table. Please help me with this.

pradhan.subhankar@gmail.com
 
Posts: 8
Joined: Fri Oct 12, 2018 12:42 am

Thu Oct 18, 2018 8:24 am

Hi,

Thanks for your inquiry.
Our Spire.XLS supports changing the value of the PivotTable data source and then refreshing the PivotTable. Please refer to the following guidance. But if you want to re-specific the range of data source, sorry to tell you that it is not available at present. We will add the new feature to our future upgrade list. Once it is achieved in the future, we will let you know.
Refresh PivotTable in Excel in C#.


Sincerely
Anna
E- iceblue support team
User avatar

Anna.Zhang
 
Posts: 73
Joined: Thu Sep 27, 2018 3:20 am

Tue Feb 23, 2021 9:29 am

Hello,

Hope you are doing well.
Glad to inform you that we just released Spire.XLS Pack(Hotfix) Version:11.2.6 which implements this feature. Welcome to download it from the following links.
Our website: https://www.e-iceblue.com/Download/down ... t-now.html
Nuget: https://www.nuget.org/packages/Spire.Xls/11.2.6

Sincerely,
Elena
E-iceblue support team
User avatar

Elena.Zhang
 
Posts: 279
Joined: Thu Jul 23, 2020 1:18 am

Return to Spire.XLS