记一次读取excel，日志记录异常。

from openpyxl import load_workbook
from openpyxl.styles import Font
from openpyxl.styles.colors import BLACK
from collections import namedtuple

from config.config import DATA_PATH
from common.RecordLog import log


class ParseExcel():
    #解析excel文件
    def __init__(self,filename):
        try:
            self.filename = filename
            self.__wb = load_workbook(self.filename)
        except FileNotFoundError as e:
            log.error("解析Excel文件{}失败\n{}".format(self.filename,e))

    def get_max_row_num(self,sheet_name):
        #获取最大行号
        max_row_num = self.__wb[sheet_name].max_row
        return max_row_num

    def get_max_column_num(self, sheet_name):
        # 获取最大列数
        max_column = self.__wb[sheet_name].max_column
        return max_column

    def get_cell_value(self, sheet_name, coordinate=None, row=None, column=None):
#获取指定单元格的而数据
        if coordinate is not None:
            try:
                return self.__wb[sheet_name][coordinate].value
            except Exception as e:
                raise e
        elif coordinate is None and row is not None and column is not None:
            if isinstance(row,int) and isinstance(column,int):
                return self.__wb[sheet_name].cell(row=row,column=column).value
            else:
                raise TypeError('row and column must be type int')
        else:
            raise Exception('Insufficient Coordinate of cell')

    def get_row_value(self, sheet_name,row):
        """获取某一行的数据"""
        column_num = self.get_max_column_num(sheet_name)
        row_value = []
        if isinstance(row, int):
            for column in range(1, column_num + 1):
                values_row = self.__wb[sheet_name].cell(row, column).value
                row_value.append(values_row)
            return row_value
        else:
            raise TypeError('row must be type int')

    def get_column_value(self,sheet_name,column):
        """获取某一列的数据"""
        row_num = self.get_max_column_num(sheet_name)
        column_value = []
        if isinstance(column, int):
            for row in range(1, row_num+1):
                values_column = self.__wb[sheet_name].cell(row, column).value
                column_value.append(values_column)
            return column_value
        else:
            raise Exception("column must be type int")

    def get_all_value(self,sheet_name):
        """获取指定表单的所有数据（除去表头）"""
        rows_obj = self.__wb[sheet_name].iter_rows(min_row=2,max_row=self.__wb[sheet_name].max_row,values_only=TypeError)
        values = []
        for row_tuple in rows_obj:
            value_list = []
            for value in row_tuple:
                value_list.append(value)
            values.append(value_list)
        log.info("读取{}文件，表单{}的所有数据\n{}".format(self.filename, sheet_name, values))
        return values

    def get_excel_title(self, sheet_name):
        """获取sheet表头"""
        title_key = tuple(self.__wb[sheet_name].iter_rows(max_row=1,values_only=True))[0]
        log.info("解析{}文件表单{}的标题：\n你{}".format(self.filename, sheet_name, title_key))
        return title_key

    def get_list_dict_all_value(self, sheet_name):
        """获取所有数据，返回嵌套字典的列表"""
        sheet_title = self.get_excel_title(sheet_name)
        all_values = self.get_all_value(sheet_name)
        value_list = []
        for value in all_values:
            value_list.append(dict(zip(sheet_title,value)))
        return value_list

    def get_name_tuple_all_value(self,sheet_name):
        """获取所有数据，返回嵌套命名元组的列表 """
        sheet_title = self.get_excel_title(sheet_name)
        values = self.get_all_value(sheet_name)
        excel = namedtuple('excel', sheet_name)
        value_list = []
        for value in values:
            e = excel(*value)
            value_list.append(e)
        return value_list
    def write_cell(self,sheet_name, row,column, value=None, bold=True, color=BLACK):
        if isinstance(row, int) and isinstance(column, int):
            try:
                log.info("{}文件，表单{}，第{}行第{}列写入数据{}".format(self.filename, sheet_name, row, column, value))
                cell_obj = self.__wb[sheet_name].cell(row,column)
                cell_obj.font = Font(color=color,bold=bold)
                cell_obj.value = value
                self.__wb.save(self.filename)
            except Exception as e:
                log.error("{}文件，表单{}，第{}行第{}列写入数据{}失败\n{}".format(self.filename, sheet_name, row, color, value, e))
                raise e
            else:
                log.error(           log.error("{}文件写数据失败：row and column must be type int".format(self.filename)))
                raise TypeError('row and column must be type int')


do_excel = ParseExcel(DATA_PATH)
if __name__ == '__main__':
    pe = ParseExcel(DATA_PATH)
    print(pe.get_all_value('login'))
    pe.get_name_tuple_all_value('login')
    column_row = pe.get_max_column_num('login')
    print('最大列号:', column_row)
    max_row = pe.get_max_row_num('login')
    print('最大行号:', max_row)
    cell_value_1 = pe.get_cell_value('login', row=2, column=3)
    print('第%d行, 第%d列的数据为: %s' % (2, 3, cell_value_1))
    cell_value_2 = pe.get_cell_value('login', coordinate='A5')
    print('A5单元格的数据为: {}'.format(cell_value_2))
    value_row = pe.get_row_value('login', 3)
    print('第{}行的数据为:{}'.format(3, value_row))
    value_column = pe.get_column_value('login', 2)
    print('第{}列的数据为:{}'.format(2, value_column))
    values_2 = pe.get_all_value('login')
    print('第二种方式获取所有数据\n', values_2)
    title = pe.get_excel_title('login')
    print('表头为\n{}'.format(title))
    dict_value = pe.get_list_dict_all_value('login')
    print('所有数据组成的嵌套字典的列表:\n', dict_value)
    namedtuple_value = pe.get_list_dict_all_value('login')
    print('所有数据组成的嵌套命名元组的列表:\n', namedtuple_value)