python区分不同颜色单元格_openpyxl 设置单元格颜色

在处理excel数据格式的时候，需要对特定单元格进行颜色标注，方便相关人员查看 醒目

# -*- coding: utf-8 -*-

from openpyxl import load_workbook, Workbook

# 导入字体、边框、颜色以及对齐方式相关库

from openpyxl.styles import Font, Border, Side, PatternFill, colors, Alignment

有关颜色的设置具体可以查看  http://www.114la.com/other/rgb.htm

def MarkRedOverTime():

'''对上周已开船和未开船 超时数据字段进行标红'''

try:

abs_file = os.path.abspath(os.path.join(

os.path.dirname(__file__), outfile))

wb = load_workbook(abs_file)

# 获取工作表列表

sheets = wb.sheetnames

print(sheets)

# 获取某一特定的工作表

# 上一周新增的可发订单已开船数据 上一周新增的可发订单未开船数据

#

# 上一周新增的可发订单已开船数据ws = wb["上一周新增的可发订单未开船数据"]

# # 设置填充红色加粗

red_fill = PatternFill("solid", fgColor="FF0000")

# # 遍历每一行

# for index, row in enumerate(ws.rows):

# if index > 0:

# # 单证工作时间(时) > 24

# cell6 = row[5]

# print(cell6.value)

# if int(cell6.value) > 24:

# cell6.fill = red_fill

# # 采购工作时间(时) > 48

# cell9 = row[8]

# if int(cell9.value) > 48:

# cell9.fill = red_fill

# # 订舱用时(时) > 48

# cell12 = row[11]

# if int(cell12.value) > 48:

# cell12.fill = red_fill

# # booking是否超时(天) < 0

# cell14 = row[13]

# if int(cell14.value) < 0:

# cell14.fill = red_fill

wt = wb["上一周新增的可发订单已开船数据"]

# 遍历每一行

for index, row in enumerate(wt.rows):

if index > 0:

# 单证工作时间(时) > 24

cell6 = row[5]

if int(cell6.value if cell6.value else "0") > 24:

cell6.fill = red_fill

# 采购工作时间(时) > 48

cell9 = row[8]

if int(cell9.value if cell9.value else "0") > 48:

cell9.fill = red_fill

# 订舱用时(时) > 48

cell12 = row[11]

if int(cell12.value if cell12.value else "0") > 48:

cell12.fill = red_fill

# booking是否超时(天) < 0

cell14 = row[13]

if int(cell14.value if cell14.value else "0") < 0:

cell14.fill = red_fill

# shipping是否超时(天) < 0

cell18 = row[17]

if int(cell18.value if cell18.value else "0") < 0:

cell18.fill = red_fill

wb.save(abs_file)

except Exception as error_msg:

print(error_msg)

fs = traceback.format_exc()

print(fs)