全部产品
云市场

第十三课:excel的操作方法

更新时间:2019-09-18 15:38:58

一、excel介绍

excel模块是用来向用户提供一系列操作Windows控件的SDK,让用户通过简单的参数和方法调用来实现一些复杂的操作。要使用excel模块,需要使用rpa.excel进行操作。

二、视频专区

1、视频案例

业务场景:抓取天猫商品信息数据,并保存到excel中
案例流程:打开excel-设置字段-打开天猫-搜索目标商品信息-抓取数据-填充到excel中

爬取天猫demo.mov (24.16MB)

2、视频案例中源代码分享

  1. def start():
  2. '''
  3. 爬取淘宝商品信息sdk
  4. 商品名称
  5. '''
  6. #打开excel表格
  7. xls = rpa.excel.open(file = r'\\Mac\Home\Desktop\淘宝男装.xlsx',visible = True)
  8. sheet = xls.get_sheet('sheet2')
  9. #激活当前sheet
  10. sheet.activate()
  11. #设置A、B、C列列宽
  12. sheet.set_col_width(col = 'A',width = '6')
  13. sheet.set_col_width(col = 'B',width = '65')
  14. sheet.set_col_width(col = 'C',width = '7')
  15. #向A1、B1、C1分别写入序号、男装名称、价格
  16. var = '序号'
  17. sheet.write(range ='A1',value = var)
  18. var = '商品名称'
  19. sheet.write(range ='B1',value = var)
  20. var = '价格'
  21. sheet.write(range ='C1',value = var)
  22. #打开chrome 网页
  23. page = rpa.chrome.create('https://www.tmall.com/?from=tianmao.com')
  24. #天猫输入框输入-搜索男装t恤短袖
  25. var = '男装t恤短袖'
  26. page.input("天猫 - 搜索输入框",value = var)
  27. #点击搜索
  28. page.click("天猫—搜索")
  29. page.wait(element = '男装t恤短袖')
  30. flag = 0
  31. count_data = 1
  32. while True:
  33. if flag =='8':
  34. break
  35. else:
  36. #获取所有男装-价格-名称-成交笔数#
  37. count = 2
  38. for i in range(60):
  39. var = page.text("男装衣服名称",index = i)
  40. sheet.write(range = 'B'+str(count+count_data),value = var)
  41. sheet.write(range = 'A'+str(count+count_data),value = str(count_data))
  42. var = page.text("男装衣服价格")
  43. sheet.write(range = 'C'+str(count+count_data),value = var)
  44. count_data+=1
  45. flag += 1
  46. page.click("男装t恤短袖—下一页")
  47. xls.save(file = r'\\Mac\Home\Desktop\淘宝男装.xlsx')

三、代码编写

1、create新建

  1. def start():
  2. #新建excel表
  3. xls = rpa.excel.create(visible = True)
  4. sheet = xls.get_sheet()
  5. #向B1单元格写入12345
  6. data = sheet.write(range = 'B1',value = '12345')
  7. #保存
  8. xls.save(file = r'xxxxxxxx')

2、打开excel操作

  1. def start():
  2. #打开excel
  3. xls = rpa.excel.open(file = r'xxxxx.xlsx',visible = False)
  4. sheet = xls.get_sheet()

案例:
execl 基本操作002.PNG

3、捕获已打开的excel操作

  1. def start():
  2. #捕获已经打开的excel表格
  3. xls = rpa.excel.catch(name = r'xxxxxx.xlsx',mode = 'filepath')
  4. sheet = xls.get_sheet()
  5. #向B2单元格写入12345
  6. data = sheet.write(range = 'B2',value = '12345')
  7. #保存
  8. xls.save(file = 'xxxx')

4、复制、粘贴excel 操作

  1. def start():
  2. #打开excel表格
  3. xls = rpa.excel.open(file = r'xxxx.xlsx',visible = False)
  4. sheet = xls.get_sheet()
  5. #复制B1内单元格数据
  6. data = sheet.copy(range = 'B1')
  7. #向B2单元格内粘贴数据
  8. #sheet.paste(col = 'B',row = '2')
  9. #保存
  10. xls.save(file = 'xxxx')


5、复制、粘贴范围内容excel 操作

  1. def start():
  2. #打开excel表格
  3. xls = rpa.excel.open(file = r'xxxx.xlsx',visible = False)
  4. sheet = xls.get_sheet()
  5. #复制B1B2内单元格数据
  6. data = sheet.copy(range = 'B1:B2')
  7. #向B3B4单元格内粘贴数据
  8. sheet.paste_range(range = 'B3:B4')
  9. #保存
  10. xls.save(file = 'xxxx')

6、获得指定行数的sheet行数

  1. def start():
  2. #打开excel表格
  3. xls = rpa.excel.open(file = r'xxxx.xlsx',visible = False)
  4. sheet = xls.get_sheet()
  5. #获取sheet的行数
  6. data = sheet.row_count()
  7. print(data)

案例:

" class="reference-link">excel 006.PNG

excel 006_001.PNG

7、获得指定行数的sheet列数

  1. def start():
  2. #打开excel表格
  3. xls = rpa.excel.open(file = r'xxxx.xlsx',visible = False)
  4. sheet = xls.get_sheet()
  5. #获得sheet的列数
  6. data = sheet.col_count()
  7. print(data)

案例:

" class="reference-link">excel 007.PNG

excel 007_001.PNG

8、fifter 对列的基本操作

  1. def start():
  2. #打开excel表格
  3. xls = rpa.excel.open(file = r'xxxx.xlsx',visible = False)
  4. sheet = xls.get_sheet()
  5. #filter 对列进行晒选,筛选后删除
  6. sheet.filter(col = 'B',array = [1],delete = True)
  7. #上述col = ‘B’,array = [1] 表示 B行1列 ,delete = True 删除
  8. #保存
  9. xls.save(file = r'xxxxxxxx')

9、移除筛选基本操作

  1. def start():
  2. #打开excel表格
  3. xls = rpa.excel.open(file = r'xxxx.xlsx',visible = False)
  4. sheet = xls.get_sheet()
  5. #filter 对列进行筛选
  6. sheet.filter(col = 'B',array = [1],delete = False)
  7. #移除筛选
  8. sheet.remove_fiter()
  9. #保存
  10. xls.save(file = r'xxxxxxxx')

10、multi_filter对多列进行筛选

  1. def start():
  2. #打开excel表格
  3. xls = rpa.excel.open(file = r'xxxx.xlsx',visible = False)
  4. sheet = xls.get_sheet()
  5. #对多列进行筛选range 不填写 默认为整个文件
  6. #筛选B1
  7. sheet.multi_filter(filter_val = {'B':['1']},delete = True)
  8. #保存
  9. xls.save(file = r'xxxxxxxx')

11、运行宏文件

  1. def start():
  2. #打开excel表格
  3. xls = rpa.excel.open(file = r'xxxx.xlsx',visible = False)
  4. sheet = xls.get_sheet('sheet3')
  5. #运行宏
  6. sheet.run_macro(file = r'xxxxx.xlsm',macro_name = 'xxxxx')

12、merge_cell合并单元格操作

  1. def start():
  2. #打开excel表格
  3. xls = rpa.excel.open(file = r'xxxx.xlsx',visible = False)
  4. sheet = xls.get_sheet()
  5. #合并单元格 range
  6. sheet.merge_cell(range = 'A1:B2',each_row = True)

案例:
excel 009.PNG
当each_row = False
excel 010.PNG

13、insert基础使用方法

  1. def start():
  2. #打开excel表格
  3. xls = rpa.excel.open(file = r'xxxx.xlsx',visible = False)
  4. sheet = xls.get_sheet()
  5. #插入功能 插入A1:B2 insertDirection 移动方向
  6. sheet.insert(range = 'A1:B2',insertDirection = '下移')

案例:
excel 011.PNG

14、delete删除功能

  1. def start():
  2. #打开excel表格
  3. xls = rpa.excel.open(file = r'xxxx.xlsx',visible = False)
  4. sheet = xls.get_sheet()
  5. #删除功能 删除A1:B2
  6. sheet.delete(range = 'A1:B2')
  7. #保存
  8. xls.save(file = r'xxxxxxxx')


15、remove_duplicate 列去重功能

  1. def start():
  2. #打开excel表格
  3. xls = rpa.excel.open(file = r'\\Mac\Home\Desktop\抽奖数据.xlsx',visible = False)
  4. sheet = xls.get_sheet('sheet3')
  5. #列去重
  6. sheet.remove_duplicate(range = 'A1:A4',cols = ['A'] )
  7. xls.save(file = r'\\Mac\Home\Desktop\抽奖数据.xlsx')

16、创建透视表

  1. def start():
  2. #打开excel表格
  3. xls = rpa.excel.open(file = r'xxxx.xlsx',visible = False)
  4. sheet = xls.get_sheet()
  5. pivot_settings = rpa.excel.PivotTableSettings('MyPivotTable')
  6. pivot_settings.colums['购买者'] = {}
  7. pivot_settings.filters['日期'] = {}
  8. pivot_settings.rows['类型'] = {}
  9. pivot_setting.values['金额'] = {}

案例:
excel 013_001.PNG

17、刷新透视表

  1. def start():
  2. #打开excel表格
  3. xls = rpa.excel.open(file = r'xxxx.xlsx',visible = False)
  4. sheet = xls.get_sheet('xxxx')
  5. #刷新数据透视表
  6. sheet.refresh_pivottable(index = 1)

案例:
excel 014.PNG

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

  1. def start():
  2. #打开excel表格
  3. xls = rpa.excel.open(file = r'xxxx.xlsx',visible = False)
  4. sheet = xls.get_sheet('xxxx')
  5. data = sheet.get_all_pivot_field_items(index = 1,name = 'xxx')
  6. print(data)

案例:
图片001.png

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

  1. def start():
  2. #打开excel表格
  3. xls = rpa.excel.open(file = r'xxxx.xlsx',visible = False)
  4. sheet = xls.get_sheet('xxxx')
  5. #选择/取消 选择一组透视表筛选项
  6. sheet.select_pivot_field_items(name = 'xxxx',select = False ,array = ['xx'])

案例:
执行前
excel 016.PNG
执行后(False是取消,Ture 是选择)

excel 016__001.PNG

20、find 查询内容 返回一个二维数组

  1. def start():
  2. #打开excel表格
  3. xls = rpa.excel.open(file = r'xxxx.xlsx',visible = False)
  4. sheet = xls.get_sheet()
  5. #find 查询内容 返回一个二维数组
  6. data = sheet.find(text = 'xxx',range = 'A1:B2')
  7. print(data)
  8. #range = ‘’ 参数填写查询范围

案例:
excel 017.PNG

21、向指定位置插入图片 file 插入图片路径

  1. def start():
  2. #打开excel表格
  3. xls = rpa.excel.open(file = r'xxxx.xlsx',visible = False)
  4. sheet = xls.get_sheet()
  5. #向指定位置插入图片 file 插入图片路径
  6. sheet.add_pictture(file = r'xxxx',col = 'xx',row = 'xx',widtg = 'xx',height = 'xx')

案例:

excel 019.PNG

22 、获得指定行高度

  1. def start():
  2. #打开excel表格
  3. xls = rpa.excel.open(file = r'xxxx.xlsx',visible = False)
  4. sheet = xls.get_sheet()
  5. #获得指定行高度
  6. var = sheet.get_row_height(row = '1')
  7. print(var)

案例:写的第一行 获得第一行高度为43.5

excel 020.PNG

23、设置指定行高度

  1. def start():
  2. #打开excel表格
  3. xls = rpa.excel.open(file = r'xxxx.xlsx',visible = False)
  4. sheet = xls.get_sheet()
  5. #设置指定行高度 第一行 高度 20
  6. sheet.set_row_height(row = '1',height = '20')

24、获得指定列宽度

  1. def start():
  2. #打开excel表格
  3. xls = rpa.excel.open(file = r'xxxx.xlsx',visible = False)
  4. sheet = xls.get_sheet()
  5. #获得指定列表宽度
  6. var = sheet.get_col_width(col = 'A')
  7. print(var)


案例:
A列
excel 023.PNG


25、设置\获取指定范围的公式

  1. def start():
  2. #打开excel表格
  3. xls = rpa.excel.open(file = r'\\Mac\Home\Desktop\抽奖数据.xlsx',visible = False)
  4. sheet = xls.get_sheet('sheet3')
  5. #设置指定范围的公式
  6. sheet.set_formula(range = 'A3',formula = '=SUM(A1:A2)')
  7. #获取控件公式
  8. var = sheet.get_formula(range = 'A3')
  9. print(var)

案例:
excel 025.PNG

26、获取指定范围样式

  1. def start():
  2. #打开的excel表格
  3. xls = rpa.excel.open(file = r'xxxx.xlsx',visible = False)
  4. sheet = xls.get_sheet()
  5. #获取指定范围样式 fontname 字体 bgcolor 背景颜色 fontcolor字体颜色 fontsize 文字大小
  6. data = sheet.get_style(range = 'xx',style = 'fontsize')
  7. print(data)

27、设置指定范围样式

  1. def start():
  2. xls = rpa.excel.open(file = r'xxxx.xlsx',visible = False)
  3. sheet = xls.get_sheet()
  4. #设置指定范围样式 设置字体大小
  5. data = sheet.set_style(range = 'xxx',style = 'fontsize',value = '20')
  6. #设置指定范围样式 设置字体颜色
  7. #如果style为fontcolor/bgcolor,则value可以为'#FFFFFF'或者'255,255,255
  8. ` # format` : 颜色类型,如果style为fontcolor/bgcolor,需设置format为'HEX'或者'RGB'
  9. data = sheet.set_style(range = 'xxx',style = 'fontcolor',value = '#FFFFFF',format = 'HEX')

28、获取指定范围的注释

  1. def start():
  2. xls = rpa.excel.open(file = r'xxxx.xlsx',visible = False)
  3. sheet = xls.get_sheet()
  4. #获取指定范围的注释
  5. var = sheet.get_comment(range = 'xxxx')
  6. print(var)

29、往指定范围插入注释

  1. def start():
  2. xls = rpa.excel.open(file = r'xxxx.xlsx',visible = False)
  3. sheet = xls.get_sheet()
  4. #往指定范围插入注释 comment 插入内容
  5. sheet.set_comment(range = 'xxx',comment = 'hello_world')
  6. xls.save(file = r'xxxxxxxx.xlsx')

30、replace 替换

  1. def start()
  2. xls = rpa.excel.open(file = r'xxxx.xlsx',visible = False)
  3. sheet = xls.get_sheet()
  4. #替换的内容 text 要替换的内容 replacement 替换成的内容
  5. #range查找范围(如果为空则查找整个Sheet) match_case 是否区分大小写<bool>
  6. sheet.replace(text = '123',replacement = 'hello_world',range = 'A1:C5',\
  7. match_case = False)
  8. xls.save(file = r'\\Mac\Home\Desktop\抽奖数据.xlsx')

31、to_pdf转换成pdf

  1. def start()
  2. xls = rpa.excel.open(file = r'xxxx.xlsx',visible = False)
  3. sheet = xls.get_sheet()
  4. #转换成pdf
  5. sheet.to_pdf(file =r'\\Mac\Home\Desktop\抽奖数据.pdf')

32、activate激活当前sheet

  1. def start():
  2. #打开excel表格
  3. xls = rpa.excel.open(file = r'xxxx.xlsx',visible = False)
  4. sheet = xls.get_sheet('sheet2')
  5. sheet.activate()