Excel（win32）

前言

• 嗯，最新记录

导包，初始化（基类）

import re, os
import win32com.client as win32


class Excel:
    def __init__(self):
    	# 此方法会导致缓存问题, 之后的相关操作会强制要求严格区分大小写
    	# 清除缓存, 一般目录: "C:\Users\$用户名$\AppDate\Local\Temp\gen_py\$Python版本号$"
    	# 删除 一串字母加数字 的文件夹即可
        # self.obj = win32.gencache.EnsureDispatch("Excel.Application")
        self.obj = win32.Dispatch("Excel.Application")
        self.workbook = None

    @staticmethod
    def int2col(col_int):
        """
        数字转字母
        :param col_int: 列数
        """
        abc = list(map(lambda x: chr(x), [i for i in range(65, 91)]))
        if col_int <= 26:
            col_str = abc[col_int - 1]
        elif col_int <= 702:
            col_int -= 27
            col_str = abc[int(col_int / 26)] + abc[int(col_int % 26)]
        else:
            col_int -= 703
            col_str = abc[int(col_int / 676)] + abc[int(int(col_int / 26)) % 26] + abc[int(col_int % 26)]
        return col_str

    @staticmethod
    def col2int(col_str):
        """
        字母转数字
        :param col_str: 列号
        """
        abc = list(map(lambda x: chr(x), [i for i in range(65, 91)]))
        cols = re.findall(r".", col_str)
        start = 1
        if len(col_str) == 2:
            col_int = start + (abc.index(cols[0]) + 1) * 26 + abc.index(cols[-1])
        elif len(col_str) == 3:
            col_int = start + (abc.index(cols[0]) + 1) * 26 + abc.index(cols[1]) * 26 + abc.index(cols[-1])
        else:
            col_int = start + abc.index(col_str)
        return col_int

    def open(self, filepath=None):
        """
        打开 Excel 文档
        :param filepath: 文件路径, 默认打开新 Excel 文档
        """
        self.workbook = self.obj.Workbooks.Open(filepath) if filepath else self.obj.Workbooks.Add()

    def save(self, path, name):
        """
        另存为 Excel 文档
        :param path: 保存路径
        :param name: 保存名称(包含后缀)
        """
        self.workbook.SaveAs(os.path.join(path, name))

    def close(self, save=False):
        """
        关闭 Excel 文档
        :param save: 是否保存修改
        """
        try:
            self.workbook.Close(SaveChanges=save)
        except:
            pass
        self.obj.Application.Quit()

Sheet （工作表 处理）

class Sheet(Excel):
    def __init__(self):
        super().__init__()

    def sheets(self):
        """获取所有 sheet 页名"""
        return list(map(lambda x: x.Name, self.workbook.Worksheets))

    def __default_sheet(self, sheet=None):
        """默认 sheet 页"""
        return sheet if sheet else self.sheets()[0]

	def activate_sheet(self, sheet=None):
		"""激活 sheet 页"""
		self.get_sheet(sheet=sheet).activate

    def get_sheet(self, sheet=None):
        """获取 sheet 页"""
        return self.workbook.Worksheets(self.__default_sheet(sheet))

    def copy_sheet(self, obj, new_sheet_name=None, location=None, relative="after", sheet=None):
        """
        复制 sheet 页
        :param obj: 要粘贴的 Excel 对象
        :param new_sheet_name: 是否重命名 sheet 页
        :param location: 用于定位的 sheet, 默认第一个
        :param relative: 粘贴位置, before | after
        :param sheet: 要复制的 sheet, 默认第一个
        """
        assert relative in ["before", "after"]
        sheet = sheet if sheet else self.sheets()[0]
        before, after = None, None
        if relative == "before":
            before = location if location else obj.get_sheet(location)
        elif relative == "after":
            after = location if location else obj.get_sheet(location)
        self.get_sheet(sheet).Copy(before, after)
        if new_sheet_name:
            obj.rename_sheet(old_name=sheet, new_name=new_sheet_name)

    def create_sheet(self, sheet=None):
        """创建新 sheet 页"""
        new_sht = self.workbook.Worksheets.Add()
        if sheet and sheet not in self.sheets():
            new_sht.Name = sheet
        elif sheet is None:
            new_sht.Name = new_sht.Name

    def delete_sheet(self, sheet=None):
        """删除 sheet 页"""
        if sheet in self.sheets():
            self.get_sheet(sheet).Delete()

    def rename_sheet(self, old_name, new_name):
        """重命名 sheet 页"""
        if old_name in self.sheets():
            self.get_sheet(old_name).Name = new_name

Read （读取数据 处理）

class Read(Sheet):
    def __init__(self):
        super().__init__()

    def read(self, area=None, sheet=None):
        """
        读取内容
        :param area: 区域, "A1", "A1:C4", 默认全部
        :param sheet:
        """
        area = area if area else f"A1:{self.int2col(self.col_count())}{self.row_count()}"
        if ":" in area:
            return list(map(lambda x: list(x), self.get_sheet(sheet).Range(area).Value))
        return self.get_sheet(sheet).Range(area).Value

    def find(self, value, sheet=None):
        """
        查找 内容
        :param value: 内容
        :param sheet: 
        :return: [行号, 列号, 内容] 
        """
        cell = self.get_sheet(sheet).UsedRange.Find(value)
        return [cell.Row, self.int2col(cell.Column), cell.Value]        

    def read_col(self, col, sheet=None):
        """
        读取 列 内容
        :param col: 列号
        :param sheet:
        """
        return list(map(lambda x: list(x), self.get_sheet(sheet).UsedRange.Columns(col).Value))

    def read_row(self, row, sheet=None):
        """
        读取 行 内容
        :param row: 行号
        :param sheet:
        """
        assert row > 0
        return list(self.get_sheet(sheet).UsedRange.Rows(row).Value[0])

    def col_count(self, sheet=None):
        """获取 总 列数"""
        return self.get_sheet(sheet).UsedRange.Columns.Count

    def row_count(self, sheet=None):
        """获取 总 行数"""
        return self.get_sheet(sheet).UsedRange.Rows.Count

Write （写入数据 处理）

class Write(Sheet):
    def __init__(self):
        super().__init__()

    def clear_col(self, col, sheet=None):
        """
        清空 列 内容
        :param col: 列号
        :param sheet:
        """
        self.get_sheet(sheet).UsedRange.Columns(col).Clear()

    def clear_row(self, row, sheet=None):
        """
        清空 行 内容
        :param row: 行号
        :param sheet:
        """
        assert row > 0
        self.get_sheet(sheet).UsedRange.Rows(row).Clear()

    def delete_col(self, col, sheet=None):
        """
        删除 列
        :param col: 列号
        :param sheet:
        """
        self.get_sheet(sheet).Columns(col).Delete()

    def delete_row(self, row, sheet=None):
        """
        删除 行
        :param row: 行号
        :param sheet:
        """
        assert row > 0
        self.get_sheet(sheet).Rows(row).Delete()

    def insert_col(self, col, sheet=None):
        """
        在左侧 插入 列
        :param col: 列号
        :param sheet:
        """
        self.get_sheet(sheet).Columns(col).Insert()

    def insert_row(self, row, sheet=None):
        """
        在上方 插入 行
        :param row: 行号
        :param sheet:
        """
        assert row > 0
        self.get_sheet(sheet).Rows(row).Insert()

    def copy_range(self, area, sheet=None):
        """
        区域 复制
        :param area:
        :param sheet:
        """
        self.get_sheet(sheet).Range(area).Copy()

    def paste_range(self, area=None, sheet=None):
        """
        区域 粘贴
        :param area:
        :param sheet:
        """
        sht = self.get_sheet(sheet)
        area = area if area else "A1"
        sht.Paste(sht.Range(area))

    def write(self, area, data, sheet=None):
        """
        写入数据
        :param area: 区域, "A1" | "A1:C4"
        :param data: 数据, 一维数组 | 二维数组
        :param sheet:
        """
        if ":" not in area:
            areas = re.findall(r"(\D+)(\d+)", area)[0]
            area = f"{area}:{self.int2col(len(data[0]) + self.col2int(areas[0]) - 1)}{len(data) + int(areas[1]) - 1}"
        self.get_sheet(sheet).Range(area).Value = data

Style （格式 处理）

class Style(Sheet):
    def __init__(self):
        super().__init__()

    def __get_ws(self, area, sheet=None):
        """
        获取 工作 区域
        :param area:
        :param sheet:
        """
        sht = self.get_sheet(sheet)
        if ":" in area:
            ws = sht.Range(area)
        elif type(area) == int:
            assert area > 0
            ws = sht.Rows(area)
        else:
            ws = sht.Columns(area)
        return ws

    def set_number(self, area, style, sheet=None):
        """
        设置 数字 格式
        :param area:
        :param style: 格式, "$###,##0.00"
        :param sheet:
        """
        self.__get_ws(area, sheet).NumberFormat = style

        def set_font(self, area, size=None, bold=False, name=None, color=None, sheet=None):
        """
        设置 字体 样式
        :param area:
        :param size: 字体大小
        :param bold: 是否加粗
        :param name: 字体名称
        :param color: 字体颜色
        :param sheet:
        """
        ws = self.__get_ws(area, sheet)
        if size:
            ws.Font.Size = size
        if bold:
            ws.Font.Bold = bold
        if name:
            ws.Font.Name = name
        if color:
            ws.Font.Color = color

    def set_bg_color(self, area, color, sheet=None):
        """
        设置 背景色
        :param area:
        :param color: 颜色
        :param sheet:
        """
        assert 0 <= color <= 56.5
        self.__get_ws(area, sheet).Interior.ColorIndex = color

    def set_col_style(self, area, align, width=None, fit=False, sheet=None):
        """
        设置 列 格式
        :param area:
        :param align: 对齐方式, left | center | right
        :param width: 列宽
        :param fit: 是否自适应
        :param sheet:
        """
        assert align in ["left", "center", "right"]
        ws = self.__get_ws(area, sheet)
        ws.HorizontalAlignment = {
            "left": win32.constants.xlLeft,
            "right": win32.constants.xlRight,
            "center": win32.constants.xlCenter
        }[align]
        if width:
            ws.ColumnWidth = width
            fit = False
        if fit:
            ws.Columns.AutoFit()

    def set_row_style(self, area, align=False, height=None, fit=False, sheet=None):
        """
        设置 行 格式
        :param area:
        :param align: 是否上下居中
        :param height: 行高
        :param fit: 是否自适应
        :param sheet:
        """
        ws = self.__get_ws(area, sheet)
        if align:
            ws.VerticalAlignment = win32.constants.xlCenter
        if height:
            ws.RowHeight = height
            fit = False
        if fit:
            ws.Rows.AutoFit()

    def set_border(self, area, kind, width=1, sheet=None):
        """
        设置 边框样式
        :param area:
        :param kind: 样式
        :param width: 宽度
        :param sheet:
        """
        assert 0 <= kind < 14
        assert 0 < width <= 4
        self.__get_ws(area, sheet).BorderAround(kind, width)

综合

class OpenExcel(Read, Write, Style):
    def __init__(self, filepath=None, visible=True):
        super().__init__()
        self.path = filepath                # 文件路径
        self.obj.visible = visible          # 是否可视
        self.obj.DisplayAlerts = False      # 是否警告(覆盖同名文件不弹出确认框)

    def __enter__(self):
        self.open(self.path)
        return self

    def __exit__(self, exc_type, exc_value, traceback):
        self.close(save=False)


if __name__ == '__main__':
    file = r"C:\Users\Aiden\Desktop\test.xlsx"
    with OpenExcel(filepath=file, visible=True) as f:
        info = f.read()

    print(info)