全部产品
云市场

9. 透视表相关操作

更新时间:2019-09-18 15:36:33

注: 客户端版本需要在3.3.26及以上

创建数据视图

  1. xls = rpa.excel.open(r'C:\data\案例数据.xlsx')
  2. sheet = xls.get_sheet('口碑汇总数据')
  3. pivot_settings = rpa.excel.PivotTableSettings('MyPivotTable')
  4. pivot_settings.columns['税额'] = {} # 添加"列标签"
  5. pivot_settings.filters['不含税金额'] = {} # 添加"筛选字段"
  6. pivot_settings.rows['税率'] = {} # 添加"行标签"
  7. pivot_settings.values['税额2'] = {"Function": "xlAverage"} # 添加"数值"
  8. pivot_settings.values['购方纳税人识别号'] = {"Function": "xlSum"} # 添加"数值"
  9. sheet.create_pivottable('口碑汇总数据', 'G1:J4952', 'Sanco', 'A1', pivot_settings)
  10. xls.save()

Function取值范围参考https://docs.microsoft.com/zh-cn/dotnet/api/microsoft.office.interop.excel.xlconsolidationfunction?redirectedfrom=MSDN&view=excel-pia

刷新数据视图

  1. xls = rpa.excel.open(r'C:\Work\案例数据.xlsx')
  2. sheet = xls.get_sheet('Sanco')
  3. sheet.refresh_pivottable(index=1)
  4. xls.save()

获取透视表筛选列的所有项

  1. xls = rpa.excel.open(r'C:\Work\案例数据.xlsx')
  2. sheet = xls.get_sheet('Sanco')
  3. items = sheet.get_all_pivot_field_items('不含税金额')
  4. print(items)

选择/取消选择一组透视表筛选项

  1. xls = rpa.excel.open(r'C:\Work\案例数据.xlsx')
  2. sheet = xls.get_sheet('Sanco')
  3. sheet.select_pivot_field_items('不含税金额', [0.02, 0.03, 0.07], select=False)
  4. xls.save()