自动化测试中如何操作excel

openpyxl操作excel

测试时经常需要把数据写入excel表，或者从表中读取数据，那怎么用代码操作表格呢？方法有三：

import openpyxl
from openpyxl import load_workbook

1、写入excel表格

def write_excel(cell, value, filename):
    workbook = load_workbook(filename=filename)
    sheet = workbook.active
    sheet[cell] = value
    workbook.save(filename=filename)

2、读取excel

def read_excel(cell, file):
    workbook = openpyxl.load_workbook(file)
    sheet = workbook.active
    cell_value = sheet[cell].value
    return cell_value

3、读取excel中整个sheet表中的测试数据

def read_excel_rows(file):
    workbook = openpyxl.load_workbook(file)
    sheet = workbook.active
    case_list = []
    datas = list(sheet.iter_rows(values_only=True))
    datas_title = datas[0]
    datas_datas = datas[1:]
    for data in datas_datas:
        data_keyval = zip(datas_title, data)
        data_keyval = dict(data_keyval)
        case_list.append(data_keyval)
    return case_list