from openpyxl.styles import fills

 from openpyxl import load_workbook

 class DoExcel:

     def __init__(self,filename):

         '''

         :param filename: excel文件名

         '''

         self.file = filename

         self.wk = load_workbook(self.file)

     def do_excel(self,sheetname):

         '''

         :param sheetname: 工作簿名称

         :return:

         '''

         sheet = self.wk[sheetname]

         max_row = sheet.max_row #最大行

         max_column = sheet.max_column #最大列

         data = [] #定义一个空列表,用于存储所有数据

         for r in range(2,max_row+1):

             subdata = {} #定义一个字典,用于存储每行数据

             for c in range(1,max_column+1):

                 key = sheet.cell(1,c).value  #取第一行表头数据

                 subdata[key] = sheet.cell(r,c).value #字典格式，表头作为key

             data.append(subdata)

         return data

     #将返回结果回写到excel文件单元格中

     def write_back(self,sheet_name,row,col,value,color):

         '''

         :param sheet_name: 工作簿名称

         :param row: 写入行

         :param col: 写入列

         :param value: 写入值

         :param color: 背景颜色

         :return:

         '''

         sheet = self.wk[sheet_name]

         sheet.cell(row, col).value = value

         sheet.cell(row, col).fill = fills.GradientFill(stop=(color, color))

         self.wk.save(self.file)

 if __name__ == '__main__':

     file_name = "test.xlsx"

     datas = DoExcel(file_name).do_excel('students')

     i = 2

     for data in datas:

         score = int(data.get('考试分数'))

         if score >= 60:

             t_pass = '及格'

             color = 'FF0000'

         else:

             t_pass = '不及格'

             color = ''

         DoExcel(file_name).write_back('students',i,5,t_pass,color)

         i = i+1