Refresh PivotTable in Excel in C#

By default, the data source of PivotTable won't be refreshed automatically. If we update the data source of our PivotTable, the PivotTable that was built on that data source needs to be refreshed. This article is going to elaborate how to refresh PivotTable in Excel programmatically in c# using Spire.XLS.

Below is the screenshot of the example Excel file:

Detail steps:

Step 1: Instantiate a Workbook object and load the Excel file.

Workbook workbook = new Workbook();
workbook.LoadFromFile(@"Sample.xlsx");

Step 2: Get the first worksheet.

Worksheet sheet = workbook.Worksheets[0];

Step 3: update the data source of PivotTable.

sheet.Range["C2"].Value = "199";

Step 4: Get the PivotTable that was built on the data source.

XlsPivotTable pt = workbook.Worksheets[0].PivotTables[0] as XlsPivotTable;

Step 5: Refresh the data of PivotTable.

pt.Cache.IsRefreshOnLoad = true;

Step 6: Save the file.

workbook.SaveToFile("Output.xlsx", ExcelVersion.Version2013);

Output after updating data source and refreshing the PivotTable:

Refresh PivotTable in Excel in C#

Full code:

using Spire.Xls;
using Spire.Xls.Core.Spreadsheet.PivotTables;

namespace Refresh_Pivot_Table_in_Excel
{
    class Program
    {
        static void Main(string[] args)
        {
            //Instantiate a Workbook object 
            Workbook workbook = new Workbook();
            //Load the Excel file
            workbook.LoadFromFile(@"Sample.xlsx");

            //Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            //Update the data source of PivotTable
            sheet.Range["C2"].Value = "199";

            //Get the PivotTable that was built on the data source
            XlsPivotTable pt = workbook.Worksheets[0].PivotTables[0] as XlsPivotTable;

            //Refresh the data of PivotTable
            pt.Cache.IsRefreshOnLoad = true;

            //Save the file
            workbook.SaveToFile("Output.xlsx", ExcelVersion.Version2013);
        }
    }
}

Refresh PivotTable in Excel in C#

See Also