使用openpyxl刷新Excle中的数据透视表 [英] Using openpyxl to refresh pivot tables in Excle

问题描述

我有一个文件，其中包含多个选项卡，这些选项卡具有基于一个数据选项卡的数据透视表.我能够毫无问题地将数据写入数据"标签，但是我不知道如何获取所有带有数据透视表的标签以刷新.

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.

如果可以使用openpyxl做到这一点，那将是理想的选择.

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

推荐答案

如果数据源范围始终相同，则可以将每个数据透视表设置为打开时刷新".为此，只需转到数据透视表选项卡，单击数据透视表，在分析"->选项"->选项"->数据"->打开文件时刷新数据"下.

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".

因此，无需使用任何python软件包即可实现上述目的，或者可以使用openpyxl进行刷新.但是，请确保您使用的是2.5版或更高版本，否则数据透视表格式将丢失.

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.

这篇关于使用openpyxl刷新Excle中的数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！