Python-openpyxl-MjExcel

Python操作Excel文件工具类：

• 获取工作表
• 创建工作表、复制工作表、删除工作表
• 获取单元格
• 合并单元格、解除合并单元格
• 向工作表里写入数据，占位符${name}
• 读取工作表里的数据
• 字母索引和数字索引的相互转换

"""
@Title: MjExcel工具类
@Time: 2024/1/30 9:45
@Author: Michael
"""

import os

from openpyxl import Workbook, load_workbook
from openpyxl.cell import Cell
from openpyxl.worksheet.worksheet import Worksheet

from office.MjUtil import MjUtil


class MjExcel:
    # 字母表
    letters = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J",
               "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T",
               "U", "V", "W", "X", "Y", "Z"]

    # 构造函数
    def __init__(self,
                 path: str = None):
        """
        :param path: 文件路径，若为空则创建新文件，默认创建一个名为"Sheet"的工作表
        """
        self.path = path
        if MjUtil.is_none(path):
            # 新建文件
            self.xlsx = Workbook()
        else:
            if not os.path.isfile(path):
                raise Exception("非文件路径！")
            post = path.lower().split(".")[-1]
            if post != "xlsx":
                raise Exception("非xlsx文件！")
            self.xlsx = load_workbook(path)
        self.sheets = self.get_sheets()  # 所有的工作表
        self.cur_sheet = None  # 当前的工作表
        self.set_cur_sheet()

    # 设置当前的工作表
    def set_cur_sheet(self,
                      inx: int | str = None):
        """
        :param inx: 工作表索引/名称，若为空则返回第一个
        :return:
        """
        self.cur_sheet = self.get_sheet(0) if inx is None else self.get_sheet(inx)

    # 获取所有的工作表
    def get_sheets(self,
                   empty: bool = False) -> list[Worksheet]:
        """
        :param empty: 是否剔除空白工作表，默认不剔除
        :return: list[Worksheet]
        """
        self.sheets = self.xlsx.worksheets
        if not empty:
            return self.sheets
        # 根据工作表大小判断是否为空
        sheets = []
        for worksheet in self.sheets:
            cell = self.get_cell("A1", worksheet)
            if self.get_sheet_shape(worksheet) == (1, 1) and MjUtil.is_none(cell.value):
                continue
            sheets.append(worksheet)
        return sheets

    # 获取所有的工作表名称
    def get_sheets_name(self) -> list[str]:
        return self.xlsx.sheetnames

    # 获取特定的工作表
    def get_sheet(self,
                  inx: int | str) -> Worksheet:
        """
        :param inx: 工作表索引/名称
        :return: Worksheet
        """
        sheets = self.get_sheets()
        return sheets[inx] if type(inx).__name__ == "int" else self.xlsx[inx]

    # 获取工作表的工作区域大小，空工作表大小为(1, 1)
    def get_sheet_shape(self,
                        sheet: Worksheet | int | str = None) -> tuple:
        """
        :param sheet: 工作表/名称/索引，若为空则返回当前工作表大小
        :return: tuple
        """
        sheet = self.__get_sheet_by_type(sheet)
        return sheet.max_row, sheet.max_column

    # 创建工作表
    def create_sheet(self,
                     name: str,
                     inx: int = None) -> Worksheet:
        """
        :param name: 工作表名称
        :param inx: 新工作表位置
        :return: Worksheet
        """
        if self.__judge_sheet_is_exist(name):
            raise Exception("名称为" + name + "的工作表已存在！")
        new_sheet = self.xlsx.create_sheet(name, inx)
        # 更新
        self.sheets = self.get_sheets()
        return new_sheet

    # 复制工作表
    def copy_sheet(self,
                   inx: int | str) -> Worksheet:
        """
        :param inx: 工作表索引/名称
        :return: Worksheet
        """
        sheet = self.get_sheet(inx)
        sheet_copy = self.xlsx.copy_worksheet(sheet)
        # 更新
        self.sheets = self.get_sheets()
        return sheet_copy

    # 删除工作表
    def remove_sheet(self,
                     inx: int | str):
        """
        :param inx: 工作表索引/名称
        :return:
        """
        sheet = self.get_sheet(inx)
        self.xlsx.remove(sheet)
        # 更新
        self.sheets = self.get_sheets()

    # 获取特定的单元格，(1, 1)/“A1”
    def get_cell(self,
                 inx: tuple | str,
                 sheet: Worksheet | int | str = None) -> Cell:
        """
        :param inx: 单元格索引
        :param sheet: 工作表/索引/名称，若为空则返回当前工作表
        :return: Cell
        """
        sheet = self.__get_sheet_by_type(sheet)
        # 判断索引类型
        if type(inx).__name__ == "tuple":
            # 索引从1开始
            cell = sheet.cell(int(inx[0]) + 1, int(inx[1]) + 1)
        else:
            cell = sheet[inx.upper()]
        return cell

    # 获取切片单元格，(0, 0, 1, 3)/("A1", "D2")
    def get_cells(self,
                  inx: tuple = None,
                  sheet: Worksheet | int | str = None,
                  tf: bool = True) -> list[Cell] | tuple:
        """
        :param inx: 单元格索引，若为空则返回所有单元格
        :param sheet: 工作表/索引/名称，若为空则返回当前工作表
        :param tf: 是否将元组转换成列表
        :return: list[Cell] | tuple
        """
        if inx is None:
            shape = self.get_sheet_shape()
            inx = (0, 0, shape[0], shape[1])
        sheet = self.__get_sheet_by_type(sheet)
        # 获取的单元格以行列元组返回
        cell_tuple = None
        if len(inx) == 4:
            start_cell = self.get_cell((inx[0], inx[1]), sheet)
            end_cell = self.get_cell((inx[2], inx[3]), sheet)
            cell_tuple = sheet[start_cell.coordinate:end_cell.coordinate]
        elif len(inx) == 2:
            cell_tuple = sheet[inx[0].upper():inx[1].upper()]
        # 将((row_cells1), (row_cell2))转换成列表
        if tf:
            cell_list = []
            for row_cells in cell_tuple:
                for row_cell in row_cells:
                    cell_list.append(row_cell)
            return cell_list
        else:
            return cell_tuple

    # 合并单元格，(0, 0, 1, 3)/("A1", "D2")
    def merge_cells(self,
                    inx: tuple,
                    sheet: Worksheet | int | str = None):
        """
        :param inx: 单元格索引
        :param sheet: 工作表/索引/名称，若为空则返回当前工作表
        :return:
        """
        sheet = self.__get_sheet_by_type(sheet)
        if len(inx) == 4:
            sheet.merge_cells(start_row=inx[0], start_column=inx[1], end_row=inx[2], end_column=inx[3])
        elif len(inx) == 2:
            sheet.merge_cells(str(inx[0]).upper() + ":" + str(inx[1]).upper())

    # 解除合并单元格，(0, 0, 1, 3)/("A1", "D2")
    def unmerge_cells(self,
                      inx: tuple,
                      sheet: Worksheet | int | str = None):
        """
        :param inx: 单元格索引
        :param sheet: 工作表/索引/名称，若为空则返回当前工作表
        :return:
        """
        sheet = self.__get_sheet_by_type(sheet)
        if len(inx) == 4:
            sheet.unmerge_cells(start_row=inx[0], start_column=inx[1], end_row=inx[2], end_column=inx[3])
        elif len(inx) == 2:
            sheet.unmerge_cells(str(inx[0]).upper() + ":" + str(inx[1]).upper())

    # 向块区域写入数据，(0, 0, 1, 3)/("A1", "D2")
    def write_data_to_block(self,
                            data: dict,
                            inx: tuple = None,
                            sheet: Worksheet | str | int = None):
        """
        :param data: 数据
        :param inx: 单元格索引
        :param sheet: 工作表/名称/索引，若为空则返回当前工作表
        :return:
        """
        sheet = self.__get_sheet_by_type(sheet)
        block_cell = self.get_cells(inx, sheet)
        for cell in block_cell:
            text = cell.value
            cell.value = MjUtil.write_data(text, data)

    # 向块区域写入多头数据，(0, 0, 1, 3)/("A1", "D2")
    def write_data_list_to_block(self,
                                 data_list: list,
                                 sheet: Worksheet | str | int = None,
                                 head_inx: int = 0):
        """
        :param data_list: 数据
        :param sheet: 工作表/名称/索引，若为空则返回当前工作表
        :param head_inx: 占位符行索引
        :return:
        """
        sheet = self.__get_sheet_by_type(sheet)
        # 获取占位符
        temp_list = []
        head_cells = sheet[head_inx + 1]
        for head_cell in head_cells:
            temp_list.append(head_cell.value)
        # 写入数据
        for i in range(len(data_list)):
            cells = sheet[head_inx + i + 1]
            for j in range(len(cells)):
                if not MjUtil.is_none(temp_list[j]):
                    cells[j].value = MjUtil.write_data(temp_list[j], data_list[i])

    # 读取工作表中的数据
    def read_sheet(self,
                   temp: Worksheet,
                   text: Worksheet | str | int = None,
                   inx: tuple = None,
                   data: dict = None) -> dict:
        """
        :param temp: 模板工作表
        :param text: 数据工作表/名称/索引，若为空则返回当前工作表
        :param inx: 单元格索引
        :param data: 数据
        :return:
        """
        text = self.__get_sheet_by_type(text)
        # 获取工作区域
        temp_block_cell = self.get_cells(inx, temp)
        text_block_cell = self.get_cells(inx, text)
        for i, temp_cell in enumerate(temp_block_cell):
            MjUtil.read_data(temp_cell.value, text_block_cell[i].value, data)
        return data

    # 读取工作表中的数据
    def read_sheet_list(self,
                        temp: Worksheet,
                        text: Worksheet | str | int = None,
                        head_inx: int = 0) -> list:
        """
        :param temp: 模板工作表
        :param text: 数据工作表/名称/索引，若为空则返回当前工作表
        :param head_inx: 占位符行索引
        :return: list
        """
        text = self.__get_sheet_by_type(text)
        # 获取占位符
        temp_list = []
        head_cells = temp[head_inx + 1]
        for head_cell in head_cells:
            temp_list.append(head_cell.value)
        # 判断写入区域是否有限制
        data_list = []
        text_rows = text.rows
        for i in range(head_inx, len(text_rows)):
            # 一行对应一条数据
            data = {}
            text_cells = text_rows[i].cells
            for j, text_cell in enumerate(text_cells):
                MjUtil.read_data(temp_list[j], text_cell.text, data)
            data_list.append(data)
        return data_list

    # 保存
    def save(self,
             path: str = None):
        """
        :param path: 保存路径
        :return:
        """
        if path is None or path == "":
            self.xlsx.save(self.path)
        else:
            # 另存为
            self.xlsx.save(path)

    # 转换列索引，"A" -> 0
    @staticmethod
    def tf_letter_to_figure(inx: str) -> int:
        """
        :param inx: 字母索引，"A"
        :return: int
        """
        inx = inx.upper()  # 转换大写
        figure = 0
        # 从后向前遍历索引字符串，676 * (x + 1) + 26 * (y + 1) + z
        for index, every_char in enumerate(reversed(inx)):
            num = MjExcel.letters.index(every_char)
            if index == 0:
                figure += num
            else:
                figure += (26 ** index) * (num + 1)
        if figure > 16383:
            raise Exception("索引超出！")

        return figure

    # 转换列索引，0 -> "A"
    @staticmethod
    def tf_figure_to_letter(inx: int) -> str:
        """
        :param inx: 数字索引，26
        :return: str
        """
        letter = ""
        if inx > 16383 or inx < 0:
            raise Exception("索引超出！")
        # 计算末尾字母
        letter += MjExcel.letters[inx % 26]
        inx -= MjExcel.letters.index(letter)
        if inx == 0:
            return letter
        # 从后向前循环计算
        power = 1
        while True:
            if inx // (26 ** power) > 26:
                temp = (inx % (26 ** (power + 1))) // 26 ** power
                # 前一位对应的数值刚好是后一位的最大公倍数
                if temp == 0:
                    temp = 26
                letter += MjExcel.letters[temp - 1]
                inx -= 26 ** power * temp
                power += 1
            else:
                letter += MjExcel.letters[inx // 26 ** power - 1]
                break

        return letter[::-1]

    # 根据名称判断工作表是否存在
    def __judge_sheet_is_exist(self,
                               name: str) -> bool:
        """
        :param name: 工作表名称
        :return: bool
        """
        sheets_name = self.get_sheets_name()
        return name in sheets_name

    # 根据参数类型获取特定的工作表
    def __get_sheet_by_type(self,
                            inx: Worksheet | int | str = None) -> Worksheet:
        """
        :param inx: 工作表/索引/名称，若为空则返回当前工作表
        :return: Worksheet
        """
        if inx is None:
            sheet = self.cur_sheet
        elif type(inx).__name__ == "int" or type(inx).__name__ == "str":
            sheet = self.get_sheet(inx)
        else:
            sheet = inx

        return sheet