python 通过openpyxl 操作Excel 简单封装

from openpyxl import load_workbook, Workbook
from openpyxl.utils import get_column_letter
from openpyxl.cell.cell import Cell as OpenpyxlCell

class Excel:
    def openExcelFile(self, filename):
        """打开指定的 Excel 文件并返回一个 WorkBook 对象。"""
        return WorkBook(load_workbook(filename))

    def newExcelFile(self):
        """创建一个新的 Excel 文件并返回一个 WorkBook 对象。"""
        return WorkBook(Workbook())

class WorkBook:
    def __init__(self, wb):
        self.__wb = wb

    def add_sheet(self, sheet_name=None):
        """在工作簿中添加一个新的工作表。"""
        if sheet_name:
            self.__wb.create_sheet(title=sheet_name)
        else:
            self.__wb.create_sheet()

    def copy_sheet(self, source_index, name=None):
        """复制工作表。"""
        source_sheet = self.__wb.worksheets[source_index - 1]
        new_sheet = self.__wb.copy_worksheet(source_sheet)
        if name:
            new_sheet.title = name

    def delete_sheet(self, index):
        """删除工作簿中指定索引位置的工作表。"""
        sheet = self.__wb.worksheets[index - 1]
        self.__wb.remove(sheet)

    def activateSheet(self, index):
        """激活工作簿中指定索引位置的工作表，并返回一个 Sheet 对象。"""
        return Sheet(self.__wb.worksheets[index - 1])

    def save(self, filename):
        """保存工作簿。"""
        self.__wb.save(filename)

    def close(self):
        """关闭工作簿（无需保存）。"""
        self.__wb.close()

    @property
    def sheetCount(self):
        """获取工作簿中的工作表数量。"""
        return len(self.__wb.worksheets)

    @property
    def sheetsNames(self):
        """获取工作簿中的所有工作表名称。"""
        return [sheet.title for sheet in self.__wb.worksheets]

class Sheet:
    def __init__(self, sheet):
        self.__sheet = sheet

    def setCellValue(self, row, col, value):
        """设置单元格的值。"""
        self.__sheet.cell(row=row, column=col, value=value)

    def getCellValue(self, row, col):
        """获取单元格的值。"""
        return self.__sheet.cell(row=row, column=col).value

    def setCellTextFormat(self, row, col):
        """设置单元格为文本格式。"""
        cell = self.__sheet.cell(row=row, column=col)
        cell.number_format = '@'

    @property
    def maxRow(self):
        """获取最大行数。"""
        return self.__sheet.max_row

    @property
    def maxColumn(self):
        """获取最大列数。"""
        return self.__sheet.max_column

if __name__ == '__main__':
    s1 = Excel()
    wb1 = s1.openExcelFile(r"C:\22.xlsx")
    print(wb1.sheetsNames)
    sht1 = wb1.activateSheet(3)

    s2 = Excel()
    wb2 = s2.openExcelFile(r"C:\555.xlsx")
    print(wb2.sheetsNames)
    sht2 = wb2.activateSheet(1)

    for x in range(2, sht1.maxRow + 1):
        name = sht1.getCellValue(x, 4)
        id = sht1.getCellValue(x, 5)
        phone = sht1.getCellValue(x, 7)
        num = sht1.getCellValue(x, 8)
        card = sht1.getCellValue(x, 9)
        print(name, id, phone, num, card)

        wb2.copy_sheet(source_index=1, name=name)
        sht2 = wb2.activateSheet(wb2.sheetCount)

        sht2.setCellTextFormat(2, 2)
        sht2.setCellTextFormat(4, 2)
        sht2.setCellTextFormat(3, 6)
        sht2.setCellTextFormat(3, 9)
        sht2.setCellTextFormat(4, 7)

        sht2.setCellValue(3, 2, name)
        sht2.setCellValue(4, 2, id)
        sht2.setCellValue(3, 6, phone)
        sht2.setCellValue(3, 9, num)
        sht2.setCellValue(4, 7, card)

    wb2.save(r"C:\555.xlsx")
    wb1.close()
    s1 = None
    s2 = None