Python-openpyxl工具类（未完成）

使用Python操作Excel表格写入数据，后续更新Java版。

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

import os
import re

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


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: excel文件路径，若为空则创建新文件，默认创建一个名为"Sheet"的工作表
        """
        self.path = path
        if MjExcel.isNoneStr(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, key: str | int = None):
        """
        :param key: 工作表名称/索引，若为空则返回第一个
        :return:
        """
        self.cur_sheet = self.get_sheet(0) if key is None else self.get_sheet(key)

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

    # 获取所有的工作表
    def get_sheets(self, remove_empty: bool = False) -> list[Worksheet]:
        """
        :param remove_empty: 是否剔除空白的工作表
        :return: list[Worksheet]
        """
        self.sheets = self.xlsx.worksheets
        if not remove_empty:
            return self.sheets
        # 根据工作表大小判断
        sheets = []
        for worksheet in self.sheets:
            if self.get_sheet_shape(worksheet) == (1, 1):
                continue
            sheets.append(worksheet)
        return sheets

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

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

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

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

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

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

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

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

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

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

    # 向块区域写入多头数据，(1, 0, 1, 3)
    def write_data_list_to_block(self, key: tuple, obj: dict, sheet: Worksheet | str | None = None):
        ...

    # 读取工作表中的数据
    def read_sheet(self, sheet: Worksheet, key: tuple, temp):
        ...

    # 保存
    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)

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

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

        return sheet

    # 转换列索引，"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]

    # 替换文本，我叫${name} -> 我叫michael
    @staticmethod
    def replace_text(text: str, obj: dict, clear_temp: bool = True) -> str:
        """
        :param text: 需要被替换文本
        :param obj: 数据
        :param clear_temp: 是否清除空值模板
        :return: str
        """
        if MjExcel.isNoneStr(text):
            return ""

        # 获取占位符列表，${name}
        replace_list = re.findall(r"\${\w+}", text)
        if replace_list is None or len(replace_list) == 0:
            return text
        # 获取替换字段列表，name
        field_list = []
        for replace in replace_list:
            field = re.findall(r"{(.*?)}", replace)
            if field is None or len(field) == 0:
                replace_list.remove(replace)
                continue
            field_list.append(field[0])
        # 替换，${name} -> value
        for index in range(len(replace_list)):
            replace = replace_list[index]
            field = field_list[index]
            # 判断是否存在字段
            if field in obj.keys():
                value = obj.get(field)
                # 当数据为空时，是否删除占位符
                if MjExcel.isNoneStr(value) and not clear_temp:
                    continue
                text = text.replace(replace, str(MjExcel.toNotNoneStr(value)))

        return text

    # 判断字符串是否为空
    @staticmethod
    def isNoneStr(text: str | None) -> bool:
        return text is None or text == ""

    # 判断是否为空，若为空则返回空字符
    @staticmethod
    def toNotNoneStr(text: str | int | float | None) -> str | int | float:
        return "" if text is None else text


if __name__ == '__main__':
    excel = MjExcel("text.xlsx")
    print(excel.sheets)
    print(excel.cur_sheet)

    print("--设置当前的工作表--")
    excel.set_cur_sheet(1)
    print(excel.cur_sheet)

    print("--获取工作表的工作区域大小--")
    print(excel.get_sheet_shape(excel.cur_sheet))

    print("--获取所有的工作表--")
    print(excel.get_sheets(True))

    print("--获取所有的工作表名称--")
    print(excel.get_sheets_name())

    print("--获取特定的工作表--")
    print(excel.get_sheet("Sheet3"))

    print("--创建工作表--")
    excel.create_sheet("Sheet4")
    print(excel.get_sheets_name())

    print("--复制工作表--")
    excel.copy_sheet("Sheet2")
    print(excel.get_sheets_name())

    print("--删除工作表--")
    excel.remove_sheet("Sheet4")
    print(excel.get_sheets_name())

    print("--转换列索引--")
    print(MjExcel.tf_letter_to_figure("aza"))
    print(MjExcel.tf_figure_to_letter(1352))

    print("--替换文本--")
    print(MjExcel.replace_text("我叫${name}，今年{${age}}岁，考了$${mark}，家住${city}，喜欢${kun}",
                               {"name": "michael", "age": 18.9, "mark": 99, "city": None}))

    print("--判断字符串是否为空--")
    print(MjExcel.toNotNoneStr("123"))
    print(MjExcel.isNoneStr(""))