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.

Demodena

Did you found a solution with code? – Lightman

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.

Miltonmilty

I am using excel as template and generating tabs which have pivot table but copying it to new file and then those tabs should be deleted. But when that done it doesn't have refresh info – Affect

Answer 2

A bit late I know, but thought I'd post in case anybody else is looking for the same solution. You can do this in EPPlus by directly amending the XML for the pivot tables cache definition.

pivotTable.CacheDefinition.CacheDefinitionXml.DocumentElement?.SetAttribute(
                        "refreshOnLoad", 1.ToString());

Using EPPlus version 4.5.3.3.

Hermosillo

Answer 3

Pivot tables have it's own calc context in Excel file. You manipulate calculation of formulas with FullCalcOnLoad property and Calculate() method

I bet this code from here will help you.

foreach (Worksheet sheet in package.Workbook.Sheets)
{
    foreach (PivotTable pivotTable in sheet.PivotTables())
    {
        pivotTable.PivotCache().Refresh(); //could be some other method, but i hope you find right one
    }
}

Sutphin

I do not want to use Office Interop so this solution does not suffice – Demodena

Answer 4

Add this to ExcelPivotCacheDefinition.cs

public bool RefreshOnLoad
{
    get
    {
        return GetXmlNodeString("@refreshOnLoad") == "1";
    }
    set
    {
        SetXmlNodeString("@refreshOnLoad", value?"1":"0");
    }
}

Coincidental

Answer 5

1. Go to Pivot Table options and check 'Refresh data when opening the file': https://www.extendoffice.com/documents/excel/1859-excel-refresh-pivot-table-on-open.html.

2. (Optional) If desired, users can disable 'Protected View' inside of their Excel installation: http://www.corporatefocus.com/support/how-to-disable-protected-view-in-microsoft-excel#:~:text=In%20Excel%20go%20to%20File,Enable%20All%20Macros%20by%20default.

Inlet