python-openpyxl设置单元格填充颜色及字体颜色

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)#对手账户是否被冻结