8.1数据透视表的创建和引用

本页目录

通过编程创建Excel数据透视表，主要有两种方法，一种是使用工作表对象的PivotTableWizard方法，通过向导进行创建；另一种是使用缓存对象的CreatePivotTable方法进行创建。[大谦Excel，dqexcel点com]

用PivotTableWizard方法创建数据透视表

图8-1所示工作表中为订购各种蔬菜水果的数据，现使用工作表对象的PivotTableWizard方法创建数据透视表。将数据中的“类别”作为页字段、将“产品”作为列字段、将“产地”作为行字段、将“金额”作为值字段。

Document Image

图8-1 创建数据透视表的数据源

脚本中首先导入xlwings包和os包，创建Excel应用并打开指定路径下的数据文件，获取数据源工作表。

code.python 
import xlwings as xw  #导入xlwings
import os  #导入os
root = os.getcwd()  #获取当前路径
#创建Excel应用窗口，可见，不添加工作簿
app=xw.App(visible=True, add_book=False)
#打开数据文件，可写
bk=app.books.open(fullname=root+r'\创建透视表.xlsx',read_only=False)
#获取数据源工作表
sht_data=bk.sheets.active

获取数据所在的单元格区域，新建存放数据透视表的工作表。

code.python 
rng_data=sht_data.api.Range('A1').CurrentRegion
#新建透视表所在工作表
sht_pvt=bk.sheets.add()
sht_pvt.name='数据透视表'

使用xlwings的API使用方式创建数据透视表。

code.python 
Pvt=sht_pvt.api.PivotTableWizard(\
    SourceType=xw.constants.PivotTableSourceType.xlDatabase,\
    SourceData=rng_data)
pvt.Name=’透视表’

给数据透视表设置字段及该字段在所属类别字段中的位置。将数据中的“类别”作为页字段、将“产品”作为列字段、将“产地”作为行字段、将“金额”作为值字段。

code.python 
pvt.PivotFields('类别').Orientation=\
    xw.constants.PivotFieldOrientation.xlPageField  #页字段
pvt.PivotFields('类别').Position=1  #页字段中的第1个字段
pvt.PivotFields('产品').Orientation=\
    xw.constants.PivotFieldOrientation.xlColumnField  #列字段
pvt.PivotFields('产品').Position=1  #列字段中的第1个字段
pvt.PivotFields('产地').Orientation=\
    xw.constants.PivotFieldOrientation.xlRowField  #行字段
pvt.PivotFields('产地').Position=1  #行字段中的第1个字段
pvt.PivotFields('金额').Orientation=\
    xw.constants.PivotFieldOrientation.xlDataField  #值字段

运行脚本，生成如图8-2所示的数据透视表。

Document Image

图8-2 创建数据透视表

用缓存创建数据透视表

用缓存创建数据透视表，Excel会给数据透视表建立一个缓存，通过该缓存，可以实现对数据源中数据的快速读取。用PivotCaches集合的Create方法创建PivotCache对象，即缓存对象，然后用缓存对象的CreatePivotTable方法创建数据透视表。

Document Image

图8-3 用缓存创建数据透视表

编写Python脚本。

code.python 
#前面代码省略，请参见Python文件
#......
#放透视表的位置
rng_pvt=sht_pvt.api.Range('A1')
#创建透视表关联的缓冲区
pvc=bk.api.PivotCaches.Create(\
          SourceType=xw.constants.PivotTableSourceType.xlDatabase,\
          SourceData=rng_data)
#创建透视表
pvt=sht_pvt.api.CreatePivotTable(\
          TableDestination=rng_pvt,\
          TableName='透视表')

运行脚本，在Python Shell窗口返回下面的出错信息。

code.python 
>>> = RESTART: …\Samples\ch19\Python\创建透视表2.py
Traceback(most recent call last):
  File "…\Samples\ch19\Python\创建透视表2.py", line 19, in <module>
    pvc=bk.api.PivotCaches.Create(\
AttributeError: 'function' object has no attribute 'Create'

可见，使用xlwings包，用缓存创建数据透视表时存在问题，创建失败。所以，本章后面讨论的数据透视表都是用工作表对象的PivotTableWizard方法创建的。

数据透视表的引用

创建数据透视表以后，数据透视表对象存储在所在工作表的PivotTables集合中。如果需要对该集合中的某个数据透视表进行修改，要首先从集合中找到它并提取出来。所以，数据透视表的引用就是从集合中找到需要的数据透视表。

数据透视表的引用有使用索引号和使用名称两种方法。

编写Python脚本。

code.python 
#前面代码省略，请参见Python文件
#......
#透视表的引用
#print(sht_pvt.api.PivotTables.Count)  #出错
print(sht_pvt.api.PivotTables(1).Name)  #用索引号引用
print(sht_pvt.api.PivotTables("透视表";).Name)  #用名称引用

运行过程，在Python Shell窗口输出数据透视表的名称。

code.python 
>>> = RESTART: …\Samples\ch19\Python\透视表的引用.py
透视表
透视表

注意，上面代码中第1行被注释掉了。因为执行该行代码会触发类似用缓存创建数据透视表的function错误。可见，Python的xlwings包在处理数据透视表时还存在少量bug。

刷新数据透视表

刷新数据透视表使用PivotTable对象的RefreshTable方法。

编写Python脚本。

code.python 
#前面代码省略，请参见Python文件
#......
#刷新透视表
pvt.RefreshTable

运行脚本，刷新数据透视表。