Refresh PivotTable EPPlus

Question

I am editing an existing spreadsheet in C# using EPPlus. I am altering the raw data on the second worksheet which is being used as the data source for a Pivot table on the first worksheet. My edits all work perfectly, the problem I am having is that when I load the spreadsheet output I have to manually update the Pivot table by clicking the Refresh Data button on the Excel toolbar.

Is there anyway to do this with C# EPPlus?

I've tried:

package.Workbook.FullCalcOnLoad = true;

and

package.Workbook.Calculate();

without success.

UPDATE

I couldn't find a mechanism for doing this in EPPlus so would still like to know if there is an answer. However, because I am editing a pre-existing Excel file, I was able to edit the properties of the existing pivot table in Excel and change the setting to automatically update on first load.

Answer 1

I couldn't find a way to achieve this using EPPlus.

However, you can enable the "Refresh the data when opening the file" property on the PivotTable manually before modifying the file, so that when you open the file using Excel, the content of the PivotTable will be calculated based on the modified data. You can find this property under the Data tab in the PivotTable options.