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


class ParseExcel(object):
    """解析excel文件"""

    def __init__(self, filename, sheet_name=''):
        """
        excel读取和写入
        :param filename:
        :param sheet_name:
        """
        try:
            self.sheet_name = sheet_name
            self.filename = filename
            self.__wb = load_workbook(self.filename)  # 打开excel
        except FileNotFoundError as e:
            raise e

    def get_max_row_num(self):
        """获取最大行号"""
        ws = self.get_ws_by_sheet()
        max_row_num = ws.max_row
        return max_row_num

    def get_max_col_num(self):
        """获取最大列号"""
        ws = self.get_ws_by_sheet()
        max_col_num = ws.max_column
        return max_col_num

    def get_head(self):
        """
        获取表头
        :return:
        """
        ws = self.get_ws_by_sheet()
        head = next(ws.iter_rows(max_row=1, values_only=True))
        return head

    def get_cell_value(self, coordinate=None, row=None, column=None):
        ws = self.get_ws_by_sheet()
        """获取指定单元格的数据"""
        if coordinate is not None:
            try:
                return ws[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 ws.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, row):
        """获取某一行的数据"""
        ws = self.get_ws_by_sheet()
        col_num = self.get_max_col_num()
        row_value = []
        if isinstance(row, int):
            for col in range(1, col_num + 1):
                values_row = ws.cell(row, col).value
                row_value.append(values_row)
            return row_value
        else:
            raise TypeError('row must be type int')

    def get_col_value(self, col):
        """获取某一列数据"""
        ws = self.get_ws_by_sheet()
        row_num = self.get_max_row_num()
        col_value = []
        if isinstance(col, int):
            for row in range(1, row_num + 1):
                values_column = ws.cell(row, col).value
                col_value.append(values_column)
            return col_value
        else:
            raise TypeError('column must be type int')

    def get_all_value(self, exclude_head=True):
        """
        获取指定表单的所有数据
        :param exclude_head:是否去掉表头
        :return:
        """
        ws = self.get_ws_by_sheet()
        min_row = 2 if exclude_head else 1
        row = ws.iter_rows(min_row=min_row, max_row=ws.max_row, values_only=True)
        values = []
        for row_tuple in row:
            if self.is_row_none(row_tuple):
                continue
            value_list = []
            for value in row_tuple:
                value_list.append(value)
            values.append(value_list)
        return values

    def is_row_none(self, row):
        """
        判断当前行是否全为None
        :param row:
        :return:
        """
        return not list(filter(None, row))

    def get_excel_title(self):
        """获取sheet表头"""
        ws = self.get_ws_by_sheet()
        title_key = tuple(ws.iter_rows(max_row=1, values_only=True))[0]
        return title_key

    def get_list_dict_all_value(self, exclude_head=True):
        """
        返回字典列表
        :param exclude_head:是否去掉表头
        :return:
        """
        head = self.get_head()
        all_values = self.get_all_value(exclude_head)
        value_list = []
        for value in all_values:
            value_list.append(dict(zip(head, value)))
        return value_list

    def write_cell_excel(self, row, column, value='', bold=True, color=BLACK):
        if isinstance(row, int) and isinstance(column, int):
            ws = self.get_ws_by_sheet()
            cell = ws.cell(row, column)
            cell.font = Font(color=color, bold=bold)
            cell.value = value
        else:
            raise TypeError('row and column must be type int')

    def get_ws_by_sheet(self):
        if self.sheet_name:
            ws = self.__wb[self.sheet_name]
        else:
            ws = self.__wb[self.__wb.sheetnames[0]]
        return ws

    def write_cell(self, row, column, value=None, bold=True, color=BLACK):
        ws = self.get_ws_by_sheet()
        if isinstance(row, int) and isinstance(column, int):
            try:
                cell_obj = ws.cell(row, column)
                cell_obj.font = Font(color=color, bold=bold)
                cell_obj.value = value
                self.__wb.save(self.filename)
            except Exception as e:
                raise e
        else:
            raise TypeError('row and column must be type int')

测试用例

from django.test import TestCase

from home_application.bo.excel import ParseExcel


class ModelTest(TestCase):
    # @unittest.skip
    def test_excel(self):
        from config import BASE_DIR
        file_name = BASE_DIR + '/home_application/demo.xlsx'
        pe = ParseExcel(file_name)
        column_row = pe.get_max_col_num()
        print('最大列号:', column_row)
        max_row = pe.get_max_row_num()
        print('最大行号:', max_row)
        cell_value_1 = pe.get_cell_value(row=2, column=3)
        print('第%d行, 第%d列的数据为: %s' % (2, 3, cell_value_1))
        cell_value_2 = pe.get_cell_value(coordinate='A5')  # coordinate单元格名称
        print('A5单元格的数据为: {}'.format(cell_value_2))
        value_row = pe.get_row_value(3)
        print('第{}行的数据为:{}'.format(3, value_row))
        value_column = pe.get_col_value(2)
        print('第{}列的数据为:{}'.format(2, value_column))
        values_1 = pe.get_all_value()
        print('获取所有数据\n', values_1)
        title = pe.get_excel_title()  # 显示所有的title
        print('表头为\n{}'.format(title))
        dict_value = pe.get_list_dict_all_value()
        print('所有数据组成的嵌套字典的列表:\n', dict_value)
        pe.write_cell(1, 2, '选择题')  # 更换excel中的title

运行结果