python-excel读写封装

import os
from openpyxl import load_workbook


import logging
from utils import project_path


class DoExcel:
    def __init__(self, file_name, sheet_name):
        self.file_name = file_name
        self.sheet_name = sheet_name

    def get_data(self):  # 获取测试数据
        wb = load_workbook(self.file_name)  # 打开工作簿
        sheet = wb[self.sheet_name]  # 测试用例的表单  也可固定这个表单   使传参数量减少

        test_data = []
        for i in range(2, sheet.max_row + 1):  # sheet.max_row    sheet.max_column获取表单最大行   列
            sub_data = {}
            sub_data['id'] = sheet.cell(i, 1).value  #
            sub_data['user'] = sheet.cell(i, 2).value
            sub_data['pwd'] = sheet.cell(i, 3).value
            sub_data['status'] = sheet.cell(i, 4).value
            sub_data['cookie'] = sheet.cell(i, 5).value
            test_data.append(sub_data)

        return test_data



    def write_back(self, sheet_name, row, status, cookie):  # 写回数据到Excel
        wb = load_workbook(self.file_name)
        sheet = wb[sheet_name]
        print("正在写入status")
        sheet.cell(row, 4).value = status
        print("正在写入cookie")
        sheet.cell(row, 5).value = cookie
        wb.save(self.file_name)


if __name__ == '__main__':



    # DoExcel(project_path.google_user_path, "Sheet1").write_back("Sheet1",2,'1111','2222')
    res = DoExcel(project_path.google_user_path, "Sheet1").get_data()
    print(res)