全部产品
云市场

第十九课:Excel数据透视表操作

更新时间:2019-09-18 15:39:08

1、创建透视表

  1. xls = rpa.excel.open(r'C:\temp\excel\数据透视表-demo.xlsx',visible = True)
  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['金额'] = {"Function": "xlStandardDeviation"} # 添加"数值"
  8. sheet.create_pivottable('原数据', 'A1:D8', '透视表', 'A1', pivot_settings)
  9. xls.save(file=r'C:\temp\excel\数据透视表-demo-1.xlsx')

image.png
image.png

2、刷新透视表

  1. xls = rpa.excel.open(r'C:\temp\excel\数据透视表-demo-1.xlsx',visible = True)
  2. sheet1 = xls.get_sheet("原数据")
  3. sheet1.write("D3", "1000") # 修改原数据的值
  4. sheet2 = xls.get_sheet('透视表')
  5. sheet2.refresh_pivottable(index=1)
  6. xls.save(file=r'C:\temp\excel\数据透视表-demo-2.xlsx')

image.pngimage.png

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

  1. xls = rpa.excel.open(r'C:\temp\excel\数据透视表-demo-1.xlsx')
  2. sheet = xls.get_sheet('透视表')
  3. items = sheet.get_all_pivot_field_items('购买者')
  4. print(items)
  5. =============================================================
  6. ['爸爸', '柏隼', '妈妈']

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

  1. xls = rpa.excel.open(r'C:\temp\excel\数据透视表-demo-1.xlsx',visible = True)
  2. sheet = xls.get_sheet('透视表')
  3. sheet.select_pivot_field_items('购买者', ["爸爸"], select=False)
  4. sheet.select_pivot_field_items('类型', ["门票", "食品", "书籍", "音乐"],select = True)
  5. xls.save(file=r'C:\temp\excel\数据透视表-demo-3.xlsx')

image.pngimage.png