使用openpyxl的styles，实现写入值时加背景色

 1 from openpyxl.styles import fills
 2 from openpyxl import load_workbook
 3 class DoExcel:
 4     def __init__(self,filename):
 5         '''
 6         :param filename: excel文件名
 7         '''
 8         self.file = filename
 9         self.wk = load_workbook(self.file)
10 
11     def do_excel(self,sheetname):
12         '''
13         :param sheetname: 工作簿名称
14         :return:
15         '''
16         sheet = self.wk[sheetname]
17         max_row = sheet.max_row #最大行
18         max_column = sheet.max_column #最大列
19         data = [] #定义一个空列表,用于存储所有数据
20         for r in range(2,max_row+1):
21             subdata = {} #定义一个字典,用于存储每行数据
22             for c in range(1,max_column+1):
23                 key = sheet.cell(1,c).value  #取第一行表头数据
24                 subdata[key] = sheet.cell(r,c).value #字典格式，表头作为key
25             data.append(subdata)
26         return data
27     #将返回结果回写到excel文件单元格中
28     def write_back(self,sheet_name,row,col,value,color):
29         '''
30         :param sheet_name: 工作簿名称
31         :param row: 写入行
32         :param col: 写入列
33         :param value: 写入值
34         :param color: 背景颜色
35         :return:
36         '''
37         sheet = self.wk[sheet_name]
38         sheet.cell(row,col).value = value
39         sheet.cell(row,col).fill = fills.GradientFill(stop=(color,color))
40         self.wk.save(self.file)
41 
42 if __name__ == '__main__':
43     file_name = "test.xlsx"
44     datas = DoExcel(file_name).do_excel('students')
45     i = 2
46     for data in datas:
47         score = int(data.get('考试分数'))
48         if score >= 60:
49             t_pass = '及格'
50             color = 'FF0000'
51         else:
52             t_pass = '不及格'
53             color = '006030'
54         DoExcel(file_name).write_back('students',i,5,t_pass,color)
55         i = i+1