openpyxl设置单元格填充颜色及字体颜色
接上一篇,表合并后需根据某列字段指定值将整行填充颜色或设置字体颜色。
from openpyxl import load_workbook
from openpyxl.styles import PatternFill
from openpyxl.styles import Font
import os
#按照某一列指定条件对单元格填充颜色
def fill_cell(path,column,value,fill_color):
"""
path:文件路径
column:指定列
value:指定值
fill_color:填充颜色
"""
files=os.listdir(path)
for file in files:
wb = load_workbook(path+'/'+file)
ws = wb['按人汇总']
rows = ws.max_row#行数
columns = ws.max_column#列数
for i in range(1, rows + 1):
cell_value = ws.cell(row=i, column=column).value
if cell_value == value:
for j in range(1,columns+1):
ws.cell(row=i,column=j).fill = fill_color
wb.save(path+'/'+file)
#按照某一列指定条件对单元格设置字体颜色
def font_cell(path,column,value,font_color):
"""
path:文件路径
column:指定列
value:指定值
font_color:字体颜色
"""
files=os.listdir(path)
for file in files:
wb = load_workbook(path+'/'+file)
ws = wb['按人汇总']
rows = ws.max_row#行数
columns = ws.max_column#列数
for i in range(1, rows + 1):
cell_value = ws.cell(row=i, column=column).value
if cell_value == value:
for j in range(1,columns+1):
ws.cell(row=i,column=j).font = font_color
wb.save(path+'/'+file)
#填充颜色
yellow_fill = PatternFill(patternType='solid',fgColor='FFFF00')#黄
green_fill = PatternFill(patternType='solid',fgColor='90EE90')#淡绿色
blue_fill = PatternFill(patternType='solid',fgColor='00CCFF')
#字体颜色
red_font = Font(color='c00000')#红色字体
fill_cell(r'E:\测试,15,'是',yellow_fill)#是否对手账户是否为目标账户
font_cell(r'E:\测试',16,'是',red_font)#对手账户是否涉案
fill_cell(r'E:\测试.xlsx',17,'是',green_fill)#对手账户是否被冻结
fill_cell(r'E:\测试',17,'待确定',blue_fill)#对手账户是否被冻结