[Python知识库]openpyxl封装

* coding:utf-8 *

@Time : 2019/10/1 0023

@Author :Chen

@ :do_excel.py

@ :PyCharm

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.file_name = file_name
        self.sheet_name = sheet_name

    def read_top_row(self, sheet_name):
        """读取第首行行标题栏数据,返回列表"""
        top_row = []
        wb = openpyxl.load_workbook(self.file_name)
        if sheet_name == "None":
            sheet = wb[self.sheet_name]
        else:
            sheet = wb[sheet_name]
        for call in list(sheet.rows)[0]:
            top_row.append(call.value)
        return top_row

    def read_data(self, sheet_name="None"):
        """读取除首行外的所有数据"""
        # @return:返回表单的所有数据，type = list[dict]
        wb = openpyxl.load_workbook(self.file_name)
        if sheet_name == "None":
            sheet = wb[self.sheet_name]
        else:
            sheet = 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="None", color="无填充"):
        """按单元格写入数据,在类初始化和类方法中选一个传入sheet_name参数，若都传入参数则以类方法为准

        :param row:单元格坐标行
        :param column:单元格坐标lie
        :param data:要写入的数据
        :param sheet_name:表单名字
        :param color：设置单元格填充颜色，默认为不填充

        @return:返回提示，type = str
        """
        wb = openpyxl.load_workbook(self.file_name)
        if sheet_name == "None":
            sheet = wb[self.sheet_name]
        else:
            sheet = 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)
    # ss = os.path.join(constant.DATA_PATH, "工作表.xlsx")
    # print(ss)

    # a.write_data(2, 14, "PASS", sheet_name="注册")
    # print(f"第2行写入数据完成")