Using openpyxl to refresh pivot tables in Excle

Question

I have a file that has several tabs that have pivot tables that are based on one data tab. I am able to write the data to the data tab without issue, but I can't figure out how to get all of the tabs with pivot tables to refresh.

If this can be accomplished with openpyxl that would be ideal.

Answer 1

I have similar problem but i thinking that this is thing very particularity of Excel, then, i have two solutions:

Firts, mark the pivottables for that updating when open file excel, for example: right click on pivot table, then, "Options of Pivot table", then, section "Data", then, mark checkbox "Update when on open file"

Second, create one macro for that update all sheets with pivot table, for example:

Dim Hoja As Worksheet
Dim TD As PivotTable
'
'read each sheet of file
For Each Hoja In ActiveWorkbook.Sheets
    'read each pivot table of each sheet
    For Each TD In Hoja.PivotTables
        'update pivot tble
        TD.RefreshTable
    Next TD
Next Hoja

Answer 2

If the data source range is always the same, you can set each pivot table as "refresh when open". To do that, just go to the pivot table tab, click on the pivot table, under "Analyze" - > Options -> Options -> Data -> select "Refresh data when opening the file".

If the data source range is dynamic, you can set a named range, and in the pivot table tab, Change Data Source to the named range. And again set "refresh when open".

So the above is achieved without using any python package, alternatively you can use openpyxl to refresh. However make sure that you're using the 2.5 release or above, because otherwise the pivot table format will be lost.

Answer 3

Currently what I do is in my template I create a dynamic data range that gets the data from the raw data sheet and then I set that named range to the tables data source. Then in the pivot table options there is a "refresh on open" parameter and I enable that. When the excel file opens it refreshes and you can see it refresh. Currently looking for a way to do it in openpyxl but this is where im at

Answer 4

I had the same issue and was able to update my pivot table by doing the following:

from openpyxl import load_workbook
wb = load_workbook("sample.xlsx")
pivot_sheet = wb["pivot tab name"]
pivot = pivot_sheet._pivots[0]
pivot.cache.refreshOnLoad = True