Python利用openpyxl操作excel（.xlsx）封装类

"""
================================================
Python模块：Python操作excel（.xlsx）封装类MyPyExcel
Author：捷创源科技
Date：2021年05月05日
================================================
"""


import openpyxl
from openpyxl.utils import column_index_from_string, get_column_letter
from openpyxl.worksheet.worksheet import Worksheet
from openpyxl.chart import Series, Reference, BarChart, LineChart, PieChart
from openpyxl.chart.series import DataPoint


# 先导入分别可指定单元格字体、颜色和对齐方式的类
from openpyxl.styles import Font, colors, Alignment
import os
import os.path
from win32com.client import Dispatch
import win32com.client as win32


# garbage collector
import gc




class MyPyXls:
    """
    Python操作将xls文件转换为xlsx文件格式类
    """


    def __init__(self, strOpenFilePath):
        xlsFilePath = os.path.join(os.getcwd() + "\\" + strOpenFilePath)
        # 文件存在就加载，不存在就创建工作簿。
        if os.path.exists(xlsFilePath):
            # 文件存在就加载，加载失败就报错。
            try:
                excel = win32.gencache.EnsureDispatch('Excel.Application')
                wb = excel.Workbooks.Open(xlsFilePath)
                # FileFormat = 51 is for .xlsx extension
                # FileFormat = 56 is for .xls extension
                wb.SaveAs(xlsFilePath + "x", FileFormat=51)
                wb.Close()
                excel.Application.Quit()
                print("已成功将xls文件转换为xlsx文件!!!\n")
                os.remove(xlsFilePath)  # 删除老的xls文件
            except:
                print("加载xls文件失败！请检查！")
                raise
        else:
            print("加载的xls文件不存在！请检查！")




class MyPyExcel:
    """
    Python操作excel（.xlsx）封装类MyPyExcel
    """


    def __init__(self, strOpenFilePath, read_only=False, data_only=True):
        """
        初始化功能，加载excel文件
        :param strOpenFilePath:
        :param read_only: True只读,False读写
        :param data_only: True读单元格数据，False读单元格公式
        : strOpenFilePath: 要打开的excel(.xlsx)文件路径
        """
        self.strOpenFilePath = strOpenFilePath  # 工作簿路径
        self.workbook = None  # 工作簿对象
        self.worksheet = None  # 工作表对象


        path = os.path.join(os.getcwd() + "\\" + strOpenFilePath)
        # 文件存在就加载，不存在就创建工作簿。
        if os.path.exists(path):
            # 文件存在就加载，加载失败就报错。
            try:
                self.workbook = openpyxl.load_workbook(strOpenFilePath, read_only=read_only, data_only=data_only)
            except:
                print("加载excel文件失败！请检查！")
                raise
        else:
            print("加载excel文件不存在！自动创建文件！")
            self.workbook = openpyxl.Workbook()  # 创建工作簿


    def openWorkSheet(self, strSheetName) -> Worksheet:  # 表示此函数的返回值，是一个这样的类型，函数注解
        """
        打开工作表功能
        :param strSheetName: 要打开的工作表名称
        :return: 返回打开的工作表Worksheet对象
        """
        if strSheetName in self.workbook.sheetnames:
            self.worksheet = self.workbook[strSheetName]
        else:
            print("表单名称在当前excel文件中不存在，请检查表单名称！")


        return self.worksheet


    def getActiveSheet(self):
        """
        获取当前活动工作表功能
        """
        return self.workbook.active


    def getDataByCell(self, nRow, ColumnIndex):
        """
        读取一个单元格的数据功能
        :param nRow: 行号1,2,...
        :param ColumnIndex: 列索引,整数1,2,...或者字符串'A','B'...
        :return: 指定单元格数值，否则返回False
        """
        # 数字检测
        if type(ColumnIndex) is str:
            try:
                ColumnIndex = column_index_from_string(ColumnIndex)  # 列字母转列号
            except:
                print("ColumnIndex参数非法！请确认是输入数据为整数数字！")
                return False


        # 判断行号、列号有效
        if self.isCheckNumValid(nRow, self.worksheet.max_row) is True and \
                self.isCheckNumValid(ColumnIndex, self.worksheet.max_column) is True:
            return self.worksheet.cell(nRow, ColumnIndex).value
        else:
            return False


    def putDataByCell(self, nRow, ColumnIndex, value):
        """
        写入一个单元格数据功能
        :param nRow: 行号1,2,...
        :param ColumnIndex: 列索引,整数1,2,...或者字符串'A','B'...
        :param value: 写入指定单元格的数值
        :return: 设置成功，返回True；否则反
        """
        # 数字检测
        if type(ColumnIndex) is str:
            try:
                ColumnIndex = column_index_from_string(ColumnIndex)  # 列字母转列号
            except:
                print("ColumnIndex参数非法！请确认是输入数据为整数数字！")
                return False


        self.worksheet.cell(nRow, ColumnIndex).value = value
        return True


    def getDataByRow(self, nRow):
        """
        读取一行数据功能
        :param nRow: 行号1,2,...
        :return: 指定行的列表类型数据
        """
        listRowData = []  # 定义列表类型
        # 判断行号有效
        if self.isCheckNumValid(nRow, self.worksheet.max_row) is True:
            # 有，则读取一行数据
            for nCol in range(1, self.worksheet.max_column + 1):
                listRowData.append(self.worksheet.cell(nRow, nCol).value)
        return listRowData


    def getDataAllByRow(self):
        """
        读取所有行数据功能
        :return: 所有行的字典类型数据
        """
        dictAllRowData = {}  # 定义字典类型
        for nRow in range(1, self.worksheet.max_row + 1):
            listRowData = self.getDataByRow(nRow)
            dictAllRowData["第{}行: ".format(nRow)] = listRowData
        return dictAllRowData


    def getDataByColumn(self, nColumn):
        """
        读取一列数据功能
        :param nColumn: 列号1,2...
        :return: 指定列的列表类型数据
        """
        listColumnData = []  # 定义列表类型
        # 判断列号有效
        if self.isCheckNumValid(nColumn, self.worksheet.max_column) is True:
            # 有，则读取一列数据
            for nRow in range(1, self.worksheet.max_row + 1):
                listColumnData.append(self.worksheet.cell(nRow, nColumn).value)
        return listColumnData


    def getDataAllByColumn(self):
        """
        读取所有列数据功能
        :return: 所有列的字典类型数据
        """
        dictAllColumnData = {}  # 定义字典类型
        for nColumn in range(1, self.worksheet.max_column + 1):
            listColumnData = self.getDataByColumn(nColumn)
            dictAllColumnData["第{}列: ".format(nColumn)] = listColumnData
        return dictAllColumnData


    def setFontStyleByCell(self, nRow, ColumnIndex, name="宋体", size=11,
                           bold=False, italic=False, color="FF000000"):
        """
        设置单元格的字体样式风格
        配置字体格式为：样式（宋体）、尺寸（11）、粗体（flase）、斜体（flase）、颜色（黑色）
        这里可以根据自己需求修改
        :param nRow: 行号1,2...
        :param ColumnIndex: 列索引,整数1,2,...或者字符串'A','B'...
        :param name: 字体名称
        :param size: 字体大小点数
        :param bold: True表示粗体
        :param italic: True表示斜体
        :param color: 字体颜色
                ===常用字体颜色配置数值===
                BLACK = "FF000000"
                WHITE = "FFFFFFFF"
                RED = "FFFF0000"
                DARKRED = "FF800000"
                BLUE = "FF0000FF"
                DARKBLUE = "FF000080"
                GREEN = "FF00FF00"
                DARKGREEN = "FF008000"
                YELLOW = "FFFFFF00"
                DARKYELLOW = "FF808000"
        :return: 设置成功，返回True；否则反
        """
        # 数字检测
        if type(ColumnIndex) is str:
            try:
                ColumnIndex = column_index_from_string(ColumnIndex)  # 列字母转列号
            except:
                print("ColumnIndex参数非法！请确认是输入数据为整数数字！")
                return False


        # 设置单元格字体样式
        objFontStyle = Font(name=name, size=size, bold=bold, italic=italic, color=color)
        self.worksheet.cell(nRow, ColumnIndex).font = objFontStyle
        return True


    def setFontAlignmentByCell(self, nRow, ColumnIndex, horizontal="center", vertical="center"):
        """
        设置单元格的对齐方式功能。这里为垂直居中跟水平居中，除了center，还可以用right、left等参数
        :param nRow: 行号1,2...
        :param ColumnIndex: 列索引,整数1,2,...或者字符串'A','B'...
        :param horizontal: 水平对齐，取值left、right、center
        :param vertical: 垂直对齐，取值top、botton、center
        :return: 设置成功，返回True；否则反
        """
        # 数字检测
        if type(ColumnIndex) is str:
            try:
                ColumnIndex = column_index_from_string(ColumnIndex)  # 列字母转列号
            except:
                print("ColumnIndex参数非法！请确认是输入数据为整数数字！")
                return False


        self.worksheet.cell(nRow, ColumnIndex).alignment = Alignment(horizontal, vertical)
        return True


    def saveWorkBookData(self, strSaveFilePath):
        """
        保存工作簿数据功能
        :param strSaveFilePath: 保存或另存工作簿的路径
        """
        try:
            self.workbook.save(strSaveFilePath)
        except PermissionError:
            print("要操作的文件，没有写入权限，请检查权限！")
            raise
        except FileNotFoundError:
            print("文件路径不存在，请确保路径正确！")
            raise
        except:
            print("保存写入的数据失败！请检查异常!")
            raise


    def closeWorkBook(self):
        """
        关闭工作簿功能
        """
        self.workbook.close()
        del self.workbook, self.worksheet  # workbook为打开的工作簿
        gc.collect()  # 内存马上就释放


    @staticmethod
    def isCheckNumValid(cur_num, max_num):
        """
        检测数据是否有效功能
        :param cur_num: 当前指定的行号或列号1，2...
        :param max_num: 当前工作表的最大行号或最大列号1，2...
        :return: True表示数据有效，否则反
        """
        # 类型检测
        if type(cur_num) is not int and type(cur_num) is not str:
            print("cur_num 数据类型错误！请确认为整数类型，或者为字符串类型！")
            return False
        # 数字检测
        if type(cur_num) is str:
            try:
                cur_num = int(cur_num)
            except:
                print("cur_num参数非法！请确认是输入数据为整数数字！")
                return False
        # 数字是否出范围
        if cur_num in range(1, max_num + 1):
            return True
        else:
            print("行号或者列号，超出了目前最大行号，或者最大列号！！")
            return False


    def insertSheet(self, strNewSheetName, index=None):
        """
        插入新的工作表
        :param index:0到num之间整数取值,num为工作表个数
        :param strNewSheetName:插入的工作表名称
        :return:插入的工作表对象,否则为False
        """
        if index is None:
            return self.workbook.create_sheet(strNewSheetName)  # insert a new sheet at the end (default)
        else:
            # 类型检测
            if type(index) is not int:
                print("cur_num 数据类型错误！请确认为整数类型！")
                return False
            # 数字是否出范围
            if index in range(0, len(self.workbook.worksheets) + 1):
                return self.workbook.create_sheet(strNewSheetName, index)
            else:
                print("工作表索引超出了目前最大索引！")
                return False


    def deleteSheet(self, strDeleteSheetName):
        """
        删除工作表
        :param strDeleteSheetName: 待删除的工作表名称
        """
        if strDeleteSheetName in self.workbook.sheetnames:
            self.workbook.remove(self.workbook[strDeleteSheetName])
        else:
            print("表单名称在当前excel文件中不存在，请检查表单名称！")


    @staticmethod
    def justOpen(strFileName):
        """
        模拟手工打开excel表
        :param strFileName: 打开的excel文件名（后缀.xlsx格式）
        """
        try:
            xlApp = Dispatch("Excel.Application")
            xlApp.Visible = False
            xlBook = xlApp.Workbooks.Open(os.path.join(os.getcwd(), strFileName))
            xlBook.Save()
            xlBook.Close()
        except:
            print("请手工打开 %s 后，随便修改一个空值并保存" % strFileName)


    def setRowHeight(self, nRowNumber, valRowHeight):
        """
        设置行高
        :param nRowNumber: 行号，取值1,2...整数
        :param valRowHeight: 行高度值，0~409之间的整数或浮点数
        :return: 设置成功，返回True；否则反
        """
        # 类型检测
        if type(nRowNumber) is not int:
            print("nRowNumber 数据类型错误！请确认为整数类型")
            return False


        if type(valRowHeight) is not int and type(valRowHeight) is not float:
            print("cur_num 数据类型错误！请确认为整数类型，或者为浮点数类型！")
            return False


        # 数字是否出范围
        if valRowHeight in range(0, 409 + 1):
            self.worksheet.row_dimensions[nRowNumber].height = valRowHeight
            return True
        else:
            print("行高度值，超出了目前最大行高度！")
            return False


    def setColumnWidth(self, ColumnIndex, valColumnWidth):
        """
        设置列宽
        :param ColumnIndex: 列索引，取值'A','B'...
        :param valColumnWidth: 列宽度值，0~255之间的整数或浮点数
        :return: 设置成功，返回True；否则反
        """
        # 数字检测
        if type(ColumnIndex) is int:
            try:
                ColumnIndex = get_column_letter(ColumnIndex)  # 列号转列字母
            except:
                print("ColumnIndex参数非法！请确认是输入数据为列索引字符串！")
                return False


        if type(valColumnWidth) is not int and type(valColumnWidth) is not float:
            print("valColumnWidth 数据类型错误！请确认为整数类型，或者为浮点数类型！")
            return False


        # 数字是否出范围
        if valColumnWidth in range(0, 255 + 1):
            self.worksheet.column_dimensions[ColumnIndex].width = valColumnWidth
            return True
        else:
            print("列宽度值，超出了目前最大列宽度！")
            return False


    def setMergeCells(self, strCellsRange):
        """
        设置合并单元格功能
        :param strCellsRange: 合并单元格的范围，例："A1:D3"
        :return: 设置成功，返回True；否则反
        """
        # 字符串检测
        try:
            if type(strCellsRange) is str:
                self.worksheet.merge_cells(strCellsRange)
                return True
        except:
            print("strCellsRange参数非法！请确认是输入数据为合并单元格范围的字符串！")
            return False


    def setUnMergeCells(self, strCellsRange):
        """
        设置拆分单元格功能
        :param strCellsRange: 拆分单元格的范围，例："A1:D3"
        :return: 设置成功，返回True；否则反
        """
        # 字符串检测
        try:
            if type(strCellsRange) is str:
                self.worksheet.unmerge_cells(strCellsRange)
                return True
        except:
            print("strCellsRange参数非法！请确认是输入数据为拆分单元格范围的字符串！")
            return False


    def setFreezePanes(self, strFreezeCell):
        """
        设置冻结窗格功能
        :param strFreezeCell: 拆分单元格的范围，
            【利用freeze_panes属性，可以设置为一个Cell对象，
            请注意，单元格上边的所有行和左边的所有列都会冻结，但单元格所在的行和列不会冻结】
            以下是冻结窗格的例子
            freeze_panes的设置         冻结的行和列
            sheet.freeze_panes='A2'       行1
            sheet.freeze_panes='B1'       列A
            sheet.freeze_panes='C1'       列A和列B
            sheet.freeze_panes='C2'       行1和列A和列B
            sheet.freeze_panes='A1'       没有冻结窗格
            sheet.freeze_panes=None       没有冻结窗格
        :return: 设置成功，返回True；否则反
        """
        # 字符串检测
        try:
            if type(strFreezeCell) is str:
                self.worksheet.freeze_panes = strFreezeCell
                return True
        except:
            print("strFreezeCell参数非法！请确认是输入数据为冻结窗格的单元格字符串！")
            return False


    def setUnFreezePanes(self):
        """
        设置解冻窗格功能
        """
        # self.worksheet.freeze_panes = 'A1'
        self.worksheet.freeze_panes = None


    def addLineChart(self, strChartTitle, strX_axisTitle, strY_axisTitle, min_col, min_row, max_col, max_row):
        """
        添加折线图
        :param strChartTitle: 图表标题
        :param strX_axisTitle: X轴标题
        :param strY_axisTitle: Y轴标题
        :param min_col: 数据最小列号 （除去X轴分类数据）
        :param min_row: 数据最小行号 （除去X轴分类数据）
        :param max_col: 数据最大列号 （除去X轴分类数据）
        :param max_row: 数据最大行号 （除去X轴分类数据）
        """
        line = LineChart()
        line.title = strChartTitle  # 图表的标题
        line.style = 10  # 线条的style, Max value is 48
        line.x_axis.title = strX_axisTitle  # X坐标的标题
        line.y_axis.title = strY_axisTitle  # Y坐标的标题
        wsActiveSheet = self.getActiveSheet()  # 获取活动工作表
        # 添加图表数据
        chartData = Reference(wsActiveSheet, min_col=min_col, min_row=min_row, max_col=max_col,
                              max_row=max_row)  # 图像的数据 起始行、起始列、终止行、终止列
        line.add_data(chartData, titles_from_data=True, from_rows=True)
        # 在X轴上设置分类数据
        categoriesX_axis = Reference(wsActiveSheet, min_col=2, min_row=1, max_col=max_col)
        line.set_categories(categoriesX_axis)
        # 将图表添加到sheet中数据下面一行
        wsActiveSheet.add_chart(line, "A" + str(self.worksheet.max_row + 3))


    def addBarChart(self, strChartTitle, strX_axisTitle, strY_axisTitle, min_col, min_row, max_col, max_row):
        """
        添加条形图
        :param strChartTitle: 图表标题
        :param strX_axisTitle: X轴标题
        :param strY_axisTitle: Y轴标题
        :param min_col: 数据最小列号（除去X轴分类数据）
        :param min_row: 数据最小行号（除去X轴分类数据）
        :param max_col: 数据最大列号（除去X轴分类数据）
        :param max_row: 数据最大行号（除去X轴分类数据）
        """
        bar = BarChart()
        bar.type = "col"
        bar.style = 10  # 线条的style, Max value is 48
        bar.title = strChartTitle  # 图表的标题
        bar.x_axis.title = strX_axisTitle  # X坐标的标题
        bar.y_axis.title = strY_axisTitle  # Y坐标的标题
        wsActiveSheet = self.getActiveSheet()  # 获取活动工作表
        # 添加图表数据
        chartData = Reference(wsActiveSheet, min_col=min_col, min_row=min_row, max_col=max_col,
                              max_row=max_row)  # 图像的数据 起始行、起始列、终止行、终止列
        bar.add_data(chartData, titles_from_data=True, from_rows=True)
        # 在X轴上设置分类数据
        categoriesX_axis = Reference(wsActiveSheet, min_col=2, min_row=1, max_col=max_col)
        bar.set_categories(categoriesX_axis)
        bar.shape = 4
        # 将图表添加到sheet中数据下面一行
        wsActiveSheet.add_chart(bar, "A" + str(self.worksheet.max_row + 3))


    def addPieChart(self, strChartTitle, min_col, min_row, max_col, max_row):
        """
        添加饼图
        :param strChartTitle: 图表标题
        :param min_col: 数据最小列号（除去X轴分类数据）
        :param min_row: 数据最小行号（除去X轴分类数据）
        :param max_col: 数据最大列号（除去X轴分类数据）
        :param max_row: 数据最大行号（除去X轴分类数据）
        """
        pie = PieChart()
        wsActiveSheet = self.getActiveSheet()  # 获取活动工作表
        pie.title = strChartTitle
        # 添加图表数据
        chartData = Reference(wsActiveSheet, min_col=min_col, min_row=min_row, max_col=max_col,
                              max_row=max_row)  # 图像的数据 起始行、起始列、终止行、终止列
        pie.add_data(chartData, titles_from_data=True, from_rows=True)
        # 设置分类数据
        categories_labels = Reference(wsActiveSheet, min_col=2, min_row=1, max_col=max_col)
        pie.set_categories(categories_labels)
        # 将图表添加到sheet中数据下面一行
        wsActiveSheet.add_chart(pie, "A" + str(self.worksheet.max_row + 3))




"""
========================================
主函数功能测试
========================================
"""
if __name__ == '__main__':
    # 对自己定义的功能类进行测试
    # 正常功能测试
    """    
    myExcel = MyPyExcel("example.xlsx")  # 加载文件
    myExcel.openWorkSheet("Sheet1")  # 选择工作表


    print(myExcel.getDataByRow(2))  # 读取第2行数据
    print(myExcel.getDataAllByRow())  # 读取所有行数据


    print(myExcel.getDataAllByColumn())  # 读取第2列数据
    print(myExcel.getDataAllByColumn())  # 读取所有列数据


    print(myExcel.getDataByCell(3, 1))  # 读取某一个单元格数据
    myExcel.putDataByCell(4, 5, "hello Word!")  # 写入一个单元格数据


    # 设置字体样式
    myExcel.setFontStyleByCell(7, 8, name="Times New Roman", size=24, bold=False, italic=False, color="FFFF0000")
    # 设置对齐方式
    myExcel.setFontAlignmentByCell(7, 8)
    # 写入数据
    myExcel.putDataByCell(7, 8, "华")


    # 插入工作表
    # myExcel.insertSheet("my", 3)
    # 删除工作表
    # myExcel.deleteSheet("Sheet3")


    myExcel.saveWorkBookData("example.xlsx")  # 保存数据
    myExcel.closeWorkBook()  # 关闭工作簿
    """


    """    
    myExcel = MyPyExcel("test.xlsx")  # 加载文件
    myExcel.openWorkSheet("Sheet")  # 选择工作表
    myExcel.putDataByCell(1, 1, 100)  # 写入一个单元格数据
    myExcel.putDataByCell(2, 1, 400)  # 写入一个单元格数据
    myExcel.putDataByCell(3, 1, "=SUM(A1:A2)")  # 写入一个单元格公式
    myExcel.saveWorkBookData("test.xlsx")  # 保存数据
    myExcel.closeWorkBook()  # 关闭工作簿


    myExcel.justOpen("test.xlsx")  # 模拟手工打开excel表


    # 只读方式打开文件，读取单元格的数值。read_only=True, data_only=True
    myExcel = MyPyExcel("test.xlsx", True)  # 加载文件
    myExcel.openWorkSheet("Sheet")  # 选择工作表
    print(myExcel.getDataByCell(3, 1))  # 读取某一个单元格数据
    myExcel.closeWorkBook()  # 关闭工作簿


    # 只读方式打开文件，读取单元格的公式。read_only=True, data_only=False
    myExcel = MyPyExcel("test.xlsx", True, False)  # 加载文件
    myExcel.openWorkSheet("Sheet")  # 选择工作表
    print(myExcel.getDataByCell(3, 1))  # 读取某一个单元格公式
    myExcel.closeWorkBook()  # 关闭工作簿
    """


    """    
    myExcel = MyPyExcel("test.xlsx")  # 加载文件
    myExcel.openWorkSheet("Sheet")  # 选择工作表
    myExcel.putDataByCell(5, 'B', "Hello Word!")  # 写入一个单元格数据
    myExcel.putDataByCell(4, 'B', "Hello Python")  # 写入一个单元格数据


    myExcel.setMergeCells("A1:D3")  # 合并单元格
    myExcel.setUnMergeCells("A1:D3")  # 拆分单元格


    myExcel.setFreezePanes('A2')  # 冻结窗格
    myExcel.setUnFreezePanes()  # 解冻窗格


    myExcel.saveWorkBookData("test.xlsx")  # 保存数据
    myExcel.closeWorkBook()  # 关闭工作簿
    """


    # 添加折线图
    myExcel1 = MyPyExcel("line.xlsx")  # 加载文件
    myExcel1.openWorkSheet("Sheet")  # 选择工作表
    myExcel1.addLineChart("Phone销量表", "日期", "销量", 1, 2, 6, 5)
    myExcel1.saveWorkBookData("line.xlsx")  # 保存数据
    myExcel1.closeWorkBook()  # 关闭工作簿


    # 添加条形图
    myExcel2 = MyPyExcel("bar.xlsx")  # 加载文件
    myExcel2.openWorkSheet("Sheet")  # 选择工作表
    myExcel2.addBarChart("Phone销量表", "日期", "销量", 1, 2, 6, 5)
    myExcel2.saveWorkBookData("bar.xlsx")  # 保存数据
    myExcel2.closeWorkBook()  # 关闭工作簿


    # 添加饼图
    myExcel3 = MyPyExcel("pie.xlsx")  # 加载文件
    myExcel3.openWorkSheet("Sheet")  # 选择工作表
    myExcel3.addPieChart("Phone销量表", 1, 2, 5, 2)
    myExcel3.saveWorkBookData("pie.xlsx")  # 保存数据
    myExcel3.closeWorkBook()  # 关闭工作簿