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)

巴特西

openpyxl 设置单元格颜色

最新文章

热门文章