pywin操作excel类修改版

# -*- coding =utf-8 -*-
# @Time : 2021/2/19 11:51
# @Author :Mr
# @File :ReadExcel.py
# @Software :PyCharm
import os
import time
import win32com.client

class ReadExcel:
    """处理 Excel 的类"""
    __file_name = ''  # 定义 文件名
    __excel = None  # 定义 Excel
    __work_book = None  # 定义 工作表
    __exists = False  # 定义 是否存在

    def __init__(self):
        """PyExcel 对象 初始化"""
        self.__work_book = None
        self.__file_name = ''
        self.__excel = win32com.client.DispatchEx('Excel.Application')  # 也可以用 Dispatch。DispatchEx 开启新进程，Dispatch
        # 会复用进程中的 Excel 进程
        self.__excel.Visible = False  # 后台运行，不显示
        # self.__excel.DispalyAlerts = 0  # 不发出警告

    def open(self, file_name=''):
        """
        打开 Excel文件\n\r
        file_name 为绝对路径，否则出错。
        """
        if getattr(self, '__work_book', False):
            self.__work_book.Close()
        if file_name.strip() == '':
            # 工作簿文件路径
            workbook_file_path = os.path.join(os.getcwd(), "newbook.xlsx")
            # 工作簿当前目录
            workbook_dir_path = os.path.dirname(workbook_file_path)
            if not os.path.exists(workbook_dir_path):
                os.makedirs(workbook_dir_path)
            if not os.path.exists(workbook_file_path):
                self.__work_book = self.__excel.Workbooks.Add()
                self.__work_book.SaveAs(workbook_file_path)
        elif os.path.dirname(file_name) and not os.path.exists(file_name):
            self.__work_book = self.__excel.Workbooks.Add()
            self.__work_book.SaveAs(file_name)
        else:
            self.__work_book = self.__excel.Workbooks.Open(file_name)

    def sheet(self, sheet_name='', sheet_index=1):
        if sheet_name:
            self.sheet = self.__work_book.Sheets(sheet_name)
            print(1)
        else:
            self.sheet = self.__work_book.Sheets(sheet_index)
            print(2)
        return self.sheet

    def reset(self):
        """复位"""
        self.__work_book = None
        self.__file_name = ''
        self.__excel = None
        self.__exists = False

    def save(self):
        """save the excel content"""
        return self.__work_book.Save()

    def close(self):
        """关闭应用"""
        self.__work_book.Close()
        self.__excel.Quit()
        time.sleep(2)
        self.reset()

    def add_sheet(self, sheet_name='sheet1'):
        """添加新页，页名可以自定义"""
        sheet = self.__work_book.Sheets.Add()
        sheet.Name = sheet_name if sheet_name else sheet.Name


    def get_sheet_by_index(self, sheet_index=1):
        """通过页索引获取页对象"""
        assert sheet_index > 0, '页索引必须大于0'
        return self.sheet(sheet_index)

    def get_sheet_by_name(self, sheet_name=''):
        """通过页名称获取页对象"""
        for each_worksheet in self.__work_book.Sheets:
            if each_worksheet.Name == sheet_name:
                return each_worksheet
        return None

    def get_sheet_name_index(self, sheet_name=''):
        """通过页名称获取顺序"""
        return int(self.__work_book.Sheets(sheet_name).Index)

    def get_cell(self, row=1, column=1):
        """获取 Cell 对象"""
        assert row > 0 and column > 0, '行索引 和 列索引 必须大于0'
        return self.sheet.Cells(row, column)

    def get_row(self, row=1):
        """获取 Row 对象"""
        assert row > 0, '行索引 必须大于0'
        return self.sheet.Rows(row)

    def get_column(self, column=1):
        """获取 Column 对象"""
        assert column > 0, '列索引必须大于0'
        return self.sheet.Columns(column)

    def get_range(self, row1=1, column1=1, row2=1, column2=1):
        """获取 Range 对象"""
        sheet = self.sheet
        return sheet.Range(self.get_cell(row1, column1), self.get_cell(row2, column2))

    def get_cell_value(self, row, column):
        """获取一个 cell 的值"""
        return self.get_cell(row, column).Value

    def set_cell_value(self, row, column, value):
        """设置一个 cell 的值"""
        self.get_cell(row=row, column=column).Value = value

    def get_cell_text(self, row, column):
        """获取一个 cell 的文本"""
        return self.get_cell(row, column).Text

    def set_cell_text(self, row, column, text):
        """设置一个 cell 的文本"""
        self.get_cell(row=row, column=column).Test = text

    def get_cell_interior_color_index(self, row, column):
        """获取一个 cell 的颜色值"""
        return self.get_cell(row=row, column=column).Interior.ColorIndex

    def set_cell_interior_color_index(self, row, column, color_index):
        """设置一个 cell 的颜色值"""
        self.get_cell(row=row, column=column).Interior.ColorIndex = color_index

    def get_cell_horizongtal_alignment(self, row, column):
        """获取一个 cell 的水平对齐方式"""
        return self.get_cell(row=row, column=column).HorizontalAlignment

    def set_cell_horizongtal_alignment(self, row, column, horizontal_alignment):
        """设置一个 cell 的水平对齐方式"""
        self.get_cell(row=row, column=column).HorizontalAlignment = horizontal_alignment

    def get_cell_vertical_alignment(self, row, column):
        """获取一个 cell 的竖直对齐方式"""
        return self.get_cell(row=row, column=column).VerticalAlignment

    def set_cell_vertical_alignment(self, row, column, vertical_alignment):
        """设置一个 cell 的竖直对齐方式"""
        self.get_cell(row=row, column=column).VerticalAlignment = vertical_alignment

    def set_cell_number_format_local(self, row, column, number_format_local):
        """设置一个 cell 的 数字格式"""
        self.get_cell(row=row, column=column).NumberFormatLocal = number_format_local

    def get_row_values(self, row):
        """获取 row 对象的值"""
        return self.get_row(row=row).Value

    def set_row_values(self, row, values):
        """设置 row 对象的值"""
        self.get_row(row=row).Value = values

    def get_row_interior_color_index(self, row):
        """获取 row 对象的颜色值"""
        return self.get_row(row=row).Interior.ColorIndex

    def set_row_interior_color_index(self, row, color_index):
        """设置 row 对象的颜色值"""
        self.get_row(row=row).Interior.ColorIndex = color_index

    def get_row_horizongtal_alignment(self, row):
        """获取一个 row 的水平对齐方式"""
        return self.get_row(row=row).HorizontalAlignment

    def set_row_horizongtal_alignment(self, row, horizontal_alignment):
        """设置一个 row 的水平对齐方式"""
        self.get_row(row=row).HorizontalAlignment = horizontal_alignment

    def get_row_vertical_alignment(self, row):
        """获取一个 row 的竖直对齐方式"""
        return self.get_row(row=row).VerticalAlignment

    def set_row_vertical_alignment(self, row, vertical_alignment):
        """设置一个 row 的竖直对齐方式"""
        self.get_row(row=row).VerticalAlignment = vertical_alignment

    def get_column_values(self, column):
        """获取 column 对象的值"""
        return self.get_column(column=column).values

    def set_column_values(self, column, values):
        """设置 column 对象的值"""
        self.get_column(column=column).Value = values

    def set_column_width(self, column, width):
        """设置 column 对象的 宽度"""
        self.get_column(column=column).ColumnWidth = width

    def get_column_interior_color_index(self, column):
        """获取 column 对象的颜色值"""
        return self.get_column(column=column).Interior.ColorIndex

    def set_column_interior_color_index(self, column, color_index):
        """设置 column 对象的颜色值"""
        self.get_column(column=column).Interior.ColorIndex = color_index

    def get_column_horizongtal_alignment(self, column):
        """获取一个 column 的水平对齐方式"""
        return self.get_column(column=column).HorizontalAlignment

    def set_column_horizongtal_alignment(self, column, horizontal_alignment):
        """设置一个 column 的水平对齐方式"""
        self.get_column(column=column).HorizontalAlignment = horizontal_alignment

    def get_column_vertical_alignment(self, column):
        """获取一个 column 的竖直对齐方式"""
        return self.get_column(column=column).VerticalAlignment

    def set_column_vertical_alignment(self, column, vertical_alignment):
        """设置一个 column 的竖直对齐方式"""
        self.get_column(column=column).VerticalAlignment = vertical_alignment

    def get_range_values(self, row1, column1, row2, column2):
        """获取 range 对象的值"""
        return self.get_range(row1=row1, column1=column1, row2=row2, column2=column2)

    def set_range_values(self, row1, column1, data):
        """设置 range 对象的值"""
        row2 = row1 + len(data) - 1
        column2 = column1 + len(data[0]) - 1
        range = self.get_range(row1, column1, row2, column2)
        range.Clear()
        range.Value = data

    def get_sheet_count(self):
        """获取 页 数量"""
        return self.__work_book.Worksheets.Count

    def set_sheet_row_height(self, row_height):
        """设置 sheet 对象的所有 行高"""
        self.get_sheet_by_index().Rows.RowHeight = row_height

    def set_sheet_column_width(self, column_width):
        """设置 sheet 对象的所有 列宽"""
        self.sheet.Columns.ColumnWidth = column_width

    def set_sheet_horizontal_alignment(self, horizontal_alignment):
        """设置 sheet 对象的水平对齐方式"""
        self.sheet.Columns.HorizontalAlignment = horizontal_alignment

    def get_max_row(self):
        """获取最大行数量，非最大已使用行数量"""
        return self.sheet.Rows.Count

    def get_max_column(self):
        """获取最大列数量，非最大已使用列数量"""
        return self.sheet.Columns.Count

    def clear_cell(self, row, column):
        """清除 cell 对象的内容"""
        self.get_cell(row=row, column=column).Clear()

    def delete_cell(self, row, column):
        """删除 cell"""
        self.get_cell(row=row, column=column).Delete()

    def clear_row(self, row):
        """清除 row 的内容"""
        self.get_row(row=row).Clear()

    def delete_row(self, row):
        """删除 row"""
        self.get_row(row=row).Delete()

    def clear_column(self, column):
        """清除 column 的内容"""
        self.get_column(column=column).Clear()

    def delete_column(self, column):
        """删除 column"""
        self.get_column(column=column).Delete()

    def clear_sheet(self, sheet_index):
        """清除 sheet 的内容"""
        self.get_sheet_by_index(sheet_index=sheet_index).Clear()

    def delete_sheet(self, sheet_index):
        """删除 sheet"""
        self.get_sheet_by_index(sheet_index=sheet_index).Delete()

    def delete_rows(self, start_row, count):
        """ 删除指定数量的 行"""
        max_row = self.get_max_row()
        max_column = self.get_max_column()
        end_row = start_row + count - 1
        if start_row > max_row or end_row < 1:
            return
        self.get_range(row1=start_row, column1=1, row2=end_row,
                       column2=max_column).Delete()

    def delete_columns(self, start_column, count):
        """删除指定数量的 列"""
        max_row = self.get_max_row()
        max_column = self.get_max_column()
        end_column = start_column + count - 1
        if start_column > max_column or end_column < 1:
            return
        self.get_range(row1=1, column1=start_column, row2=max_row,
                       column2=end_column).Delete()