文件路径:项目目录/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)