python 访问excel 的封装类

ExcelFile.py

import openpyxl

class ExcelFile:

    def __init__(self, filepath):
        self.filepath = filepath
        self.workbook = openpyxl.load_workbook(filepath)
        self.sheet = self.workbook.active
    
    def getSheet(self) -> list:
        """获取所有Sheet页"""
        return self.workbook.sheetnames

    def setSheet(self, sheetName: str):
        """设置Sheet页"""
        self.sheet = self.workbook[sheetName]

    def getCell(self, column: str, row):
        """获取指定的单元格对象"""
        return self.sheet[column+ str(row)]

    def readCell(self, column: str, row=""):
        """获取指定的单元格值"""
        return self.getCell(column, row).value

    def writeCell(self, column: str, row, value):
        """给指定单元格写入值"""
        self.sheet[column + str(row)] = str(value)

    def clearCell(self, column: str, row):
        """清空单元格的值"""
        self.writeCell(column, row, "")

    def getMaxRow(self):
        """获取最大行"""
        return self.sheet.max_row

    def getMaxColumn(self):
        """获取最大列"""
        return self.sheet.max_column

    def saveFile(self):
        self.workbook.save(self.filepath)

调用

ef = ExcelFile.ExcelFile("./data/学生信息数据.xlsx")

row = ef.getMaxRow()

print(row)

for rn in range(3,322):
    ##print(rn)
    xh = ef.getCell("B",rn).value
    kh = ef.getCell("C",rn).value

    print(kh)

    sql = "update XX set F1= '{}' where F2= '{}'".format(kh,xh)
    db.execute(sql)
    

参考

python读取excel文件