requests+unittest接口自动化-excel方法封装

文件路径：项目目录/common/excel_handler.py

from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet

class ExcelHandler():
    """操作 Excel"""

    def __init__(self, file):
        """初始化函数"""
        self.file = file

    def open_sheet(self, name) -> Worksheet:
        """打开表单"""
        self.wb = load_workbook(self.file)
        sheet = self.wb[name]
        return sheet

    def header(self, sheet_name):
        """获取表单的表头"""
        sheet = self.open_sheet(sheet_name)
        headers = []
        for i in sheet[1]:
            headers.append(i.value)
        return headers

    def read(self, sheet_name):
        """读取所有数据"""
        sheet = self.open_sheet(sheet_name)
        rows = list(sheet.rows)
        data = []
        for row in rows[1:]:
            row_data = []
            for cell in row:
                row_data.append(cell.value)
                '列表转成字典'
                data_dict = dict(zip(self.header(sheet_name), row_data))
            data.append(data_dict)
        return data

    @staticmethod
    def write(file, sheet_name, row, column, data):
        """写入Excel数据"""
        wb = load_workbook(file)
        sheet = wb[sheet_name]
        '修改单元格'
        sheet.cell(row, column).value = data
        '保存'
        wb.save(file)

    def close(self):
        self.wb.close()


if __name__ == '__main__':
    import os
    excel = ExcelHandler(
        os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), r"testdatas\testdatas.xlsx"))
    cases = excel.read("sheet1")
    print(cases)