import os
import openpyxl
from middle_ware.logging_class import log
from openpyxl.styles import PatternFill
from conf import constant
class DoExcel:
def __init__(self, file_name, sheet_name=None):
"""类初始化和类方法中,必须要必须选一个传入sheet_name参数,若都传入参数则以类方法为准
:param file_name:工作簿名字
:param sheet_name:工作表名字
"""
self.sheet_name = sheet_name
self.wb = openpyxl.load_workbook(file_name)
def read_top_row(self, sheet_name=""):
"""读取第首行行标题栏数据,返回列表"""
top_row = []
if sheet_name == "":
sheet = self.wb[self.sheet_name]
else:
sheet = self.wb[sheet_name]
for call in list(sheet.rows)[0]:
top_row.append(call.value)
return top_row
def read_data(self, sheet_name=""):
"""读取除首行外的所有数据"""
if sheet_name == "":
sheet = self.wb[self.sheet_name]
else:
sheet = self.wb[sheet_name]
top_row = self.read_top_row(sheet_name)
test_data = []
for row in range(2, sheet.max_row+1):
tow_data = {}
try:
for column in range(len(top_row)):
tow_data[top_row[column]] = sheet.cell(row, column+1).value
except Exception as e:
log.error(f"Excel读取数据出错了:{e}")
else:
test_data.append(tow_data)
wb.save(self.file_name)
log.debug("Excel读取数据完成")
return test_data
def write_data(self, row, column, data, sheet_name="", color="无填充"):
"""按单元格写入数据,在类初始化和类方法中选一个传入sheet_name参数,若都传入参数则以类方法为准
:param row:单元格坐标行
:param column:单元格坐标lie
:param data:要写入的数据
:param sheet_name:表单名字
:param color:设置单元格填充颜色,默认为不填充
@return:返回提示,type = str
"""
if sheet_name == "":
sheet = self.wb[self.sheet_name]
else:
sheet = self.wb[sheet_name]
colors = {"红色": "FF0000", "黑色": "0A0A0A", "白色": "FFFFFF", "绿色": "CAFF70", "黄色": "FFFF00"}
try:
if color == "无填充":
sheet.cell(row + 1, column, value=data).fill = PatternFill()
else:
fill = PatternFill(start_color=colors[color], end_color=colors[color], fill_type='solid')
sheet.cell(row + 1, column, value=data).fill = fill
except Exception as e:
log.error(f"Excel写入数据出错了:{e}")
else:
wb.save(self.file_name)
log.debug(f"向[{self.file_name}]-[{sheet_name}]:写入数据[{data}]成功!")
return "ok"
if __name__ == "__main__":
a = DoExcel(os.path.join(constant.DATA_PATH,
"nimeng_demo.xlsx"),
"登录")
b = a.read_data()
print(b)