Using Excel-Template with pivot and change the data sheet

[Varauccc]

Hello everyone,

I have an Excel file with a pivot table on the first sheet and the source data on a second sheet. I would like to use this Excel file as a template and automatically exchange the sheet with the source data.
This works so far without any problems.
Unfortunately, the source data range to which the pivot table refers does not update.
There were 100 records in the template. If I then overwrite the source data sheet with 120 records, the pivot table still only refers to the first 100 rows.
Is there a possibility to adjust the source data range afterwards?

Thank you and best regards
Varauccc

[Triste.Dai]

Hi Varauccc,

Thank you for your inquiry.

You can utilize the method Spire.Xls.PivotTable.ChangeDataSource(Spire.Xls.Core.IXLSRange dataSource) to modify the data source of your pivot table. This method automatically recalculates the pivot table, eliminating the need to call the CalculateData() method separately.

To assist you further, please refer to the following example code:

Code: Select all

Workbook book = new Workbook();
book.LoadFromFile(inputFile);
Worksheet sheet = book.Worksheets[0];
CellRange Range = sheet.Range["A1:C15"];
Spire.Xls.PivotTable table = book.Worksheets[1].PivotTables[0] as Spire.Xls.PivotTable;
table.ChangeDataSource(Range);
table.Cache.IsRefreshOnLoad = false;
book.SaveToFile(outputFile, ExcelVersion.Version2010);
book.Dispose();

If you have any additional questions or require further assistance, please feel free to reach out to us. We are here to assist you.

Best regards,
Triste
E-iceblue support team

[DaMiller]

Note that so far when I attempted to update data in the data range of an existing pivot table, it did NOT work if using MarkerDesigner to insert the data into the source range. It got an index out of range error. I suspect the marker designer of attempting to update inside of the pivot table itself where the data cache has picked up on the &= markers.

[Triste.Dai]

Note that so far when I attempted to update data in the data range of an existing pivot table, it did NOT work if using MarkerDesigner to insert the data into the source range. It got an index out of range error. I suspect the marker designer of attempting to update inside of the pivot table itself where the data cache has picked up on the &= markers.

Hi,

Thanks for your feedback.
Could you please share us with your test code and document? So that we can investigate further and work out a solution for you. You can share them with us via email(support@e-iceblue.com) or attach them here. Thanks for you assistance in advance.

Best regards,
Triste
E-iceblue support team