测试派 Give testers a home.

手把手教你 openpyxl 入门

一、安装

• pip install openpyxl

二、Excel 构成

• Excel 文件

• 表单(sheet)

• 单元格

三、读取表格

• 导入模块
  from openpyxl import load_workbook
• 实例化
  • 语法
    wb=load_workbook(filename=path,read_only=True)
  • 参数：
    filename：文件名
    read_only：默认 False，可以读写
    data_only：data_only=False，只会显示公式而已，默认 False
    data_only=True，有公式单元格显示公式最近计算结果
    keep_vba：默认 KEEP_VBA 保留 vba 代码
    keep_links：默认 True 保留外部链接
• sheet 操作

  • 获取所有 sheet

    • 方法一语法：
      wb.sheetnames
      返回值：返回包含所有 sheetname 的 list
    • 方法二语法：

    for sheet_name in wb:
    print(sheet_name.title)
    返回值：循环输出 sheet_name

    • 获取 sheet 对象

      • 获取第一个表单对象
        ws = wb.active
      • 获取当前活动的 sheet 对象
        ws=wb.get_active_sheet()
      • 通过索引来获取 sheet 对象（sheet 索引从 0 开始）
        ws=wb.worksheets[0]
      • 通过 sheet_name 获取 sheet 对象
        ws=wb['Sheet1']
ws=wb.get_sheet_by_name('Sheet1')
      • 获取受保护的表单对象
        ws=wb._sheets[0]

  • 单元格操作

    • 获取单元格对象
      • 方法一语法
        wcell=ws['A1']
      • 方法二语法
        wcell=ws.cell(row, column, value=None)
    • 获取单元格内容
      wcell.value

  • 行操作

    • 获取行对象
      wrows=ws.rows

    • 获取行内容
      for val in wrows:
      print(val.value)

    • 行切片

      • 语法

      ws.iter_rows(min_row=None, max_row=None, min_col=None, max_col=None, values_only=False)

      • 参数
        min_row=None：行起始索引(1 开始，不是 0，int 类型，默认为 1)
        max_row=None：行结束索引(1 开始，不是 0，int 类型，默认为行最大值)
        min_col=None：列起始索引(1 开始，不是 0，int 类型，默认为 1)
        max_col=None：列结束索引(1 开始，不是 0，int 类型)，默认为列最大值
        values_only=False：False，返回单元格对象，需要 value 方法获取数据
        True，直接返回单元格数据（元组形式）
      • 注意点
        索引从 1 开始
        索引参数类型必须是 int
        起始索引不传默认为 1
        结束索引不传默认为最大值
        索引取值为闭区间(两头都包含)
        values_only=False 需要对单元格进行操作
        values_only=True 只读取数据
      • 代码演示
        • Values_only=False 场景
          for row in ws.iter_rows(min_row=1,max_row=2,min_col=1,max_col=2,values_only=False):
          for val in row:
          print(val.value)
        • values_only=True 场景
          for row in ws.iter_rows(min_row=1,max_row=2,min_col=1,max_col=2,values_only=True):
          for val in row:
          print(val)

  • 列操作

    • 获取列对象
      wcolumns=ws.columns
    • 获取列内容
      for val in wcolumns:
      print(val.value)
    • 列切片

      • 语法
        ws.iter_cols(min_col=None, max_col=None, min_row=None, max_row=None, values_only=False)

      • 参数
        min_row=None：行起始索引(1 开始，不是 0，int 类型，默认为 1)
        max_row=None：行结束索引(1 开始，不是 0，int 类型，默认为行最大值)
        min_col=None：列起始索引(1 开始，不是 0，int 类型，默认为 1)
        max_col=None：列结束索引(1 开始，不是 0，int 类型)，默认为列最大值
        values_only=False：False，返回单元格对象，需要 value 方法获取数据
        True，直接返回单元格数据（元组形式）

      • 注意点
        索引从 1 开始
        索引参数类型必须是 int
        起始索引不传默认为 1
        结束索引不传默认为最大值
        索引取值为闭区间(两头都包含)
        values_only=False 需要对单元格进行操作
        values_only=True 只读取数据
        load_workbook 实例化的时候，参数 read_only=True，不支持获取列内容

      • 代码演示

        • values_only=False 场景

        for col in ws.iter_cols(min_col=1, max_col=2, min_row=1, max_row=2, values_only=False):
        for val in col:
        print(val.value)

        • values_only=True 场景
          for col in ws.iter_cols(min_col=1, max_col=2, min_row=1, max_row=2, values_only=True):
          for val in col:
          print(val)

四、写入表格* 导入模块

from openpyxl import Workbook

• 实例化
  wb=Workbook()
• sheet 操作
  • 创建表（sheet）
  • 选择表（sheet）
  • 修改 sheet 名称
• 单元格赋值
  • 方法一语法
    ws['A4'] = 'test_value'
  • 方法二语法
    ws.cell(row=2,column=5).value='test_value'
ws.cell(row=3,column=5,value='test_value')
    • 参数
      row：行索引值，int 类型，起始索引为 1
      column：列索引值，int 类型，起始索引为 1
      value：写入的值
  • 注意点
    • 行、列索引值，int 类型，起始索引为 1
    • 方法一、方法二如果指定的单元格有数据，则会被覆盖
• 行操作
  • 语法
    ws.append(iterable)
    • 参数
      iterable：list、tuple、range 、 generator、dict
  • 传入 list

    • 代码演示 # 单个 list

      ws.append(['用例 id','用例名称','用例数据'])
      ws.append([i for i in range(0,10)])# list 中嵌套多 tuple
      test_data=[(1001,'登陆成功','{"user_name":"test01","password":"Aa123456"}'),
      (1002,'登陆成功','{"user_name":"test02","password":"Aa123456"}'),
      (1003,'登陆成功','{"user_name":"test03","password":"Aa123456"}','这个元素也会写入')
      ]
      for val in test_data:
      ws.append([val[i] for i in range(len(val))])

    • 注意点
      不接收 set 类型数据
      在表格最下面按行写入，无数据覆盖风险
      每一个可迭代对象写入一行
      可迭代对象中每个元素写入一个单元格

  • 传入 dict
    • 代码演示
      test_data={'A':"test03","B":"Aa123456"}
      ws.append(test_data)
    • 注意点
      value 数据类型只能是字符串
      字典的 key 是表格的列，value 是表格的值
      一个键值对为一组（一行），存入对应列的第一个表格内
• 列操作
  • 按列写入
    for val in range(len(test_data1)):
    ws.cell(row=val+1,column=1).value=test_data1[val]
• 删除
  • 删除工作表

    • 方法一语法

      wb.remove(sheet_name)

      • 方法二语法

        del wb[sheet_name]

  • 保存
    wb.save('filename')
    注意点：
    • filename 为文件名，格式为.xlsx，.xls
    • 如果文件名已存在，数据会被直接覆盖