python3读取excel方法封装_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)