xlwt 设置样式，多种样式

设置白色背景色、黄色背景色、实线边框等

import xlwt, time
from models import session, Sites, Filtered, Odm_id,Ww, Week_name, Week_data, func, Web_week_data_filtered, and_, case\
    , Sap_material, On_way
from config import convert_to_letter, current_day
from datetime import datetime
from config import current_day, current_week_num

def set_boarders():
    borders = xlwt.Borders()  # Create Borders
    # DASHED虚线
    # NO_LINE没有
    # THIN实线
    borders.left = xlwt.Borders.THIN
    borders.right = xlwt.Borders.THIN
    borders.top = xlwt.Borders.THIN
    borders.bottom = xlwt.Borders.THIN
    return borders


def to_excel():
    s = datetime.now()

    # 创建一个workbook 设置编码
    workbook = xlwt.Workbook(encoding='utf-8')
    # 创建一个worksheet
    worksheet = workbook.add_sheet('My Worksheet')

    # 设置背景色
    xlwt.add_palette_colour("custom_colour", 0x21)


    workbook.set_colour_RGB(0x21, 141, 180, 226)
    style = xlwt.easyxf('pattern: pattern solid, fore_colour custom_colour')
    style.borders = set_boarders()

    # 设置黄色背景色
    xlwt.add_palette_colour("custom_yellow", 0x22)
    workbook.set_colour_RGB(0x22, 255, 255, 0)
    style_yellow = xlwt.easyxf('pattern: pattern solid, fore_colour custom_yellow')
    style_yellow.borders = set_boarders()

    # 设置绿色背景色
    xlwt.add_palette_colour("custom_green", 0x23)
    workbook.set_colour_RGB(0x23, 153, 213, 51)
    style_green = xlwt.easyxf('pattern: pattern solid, fore_colour custom_green')
    style_green.borders = set_boarders()

    # 设置粉色背景色
    xlwt.add_palette_colour("custom_pink", 0x24)
    workbook.set_colour_RGB(0x24, 204, 170, 204)
    style_pink = xlwt.easyxf('pattern: pattern solid, fore_colour custom_pink')
    style_pink.borders = set_boarders()

    # 设置白色背景色
    xlwt.add_palette_colour("white_colour", 0x25)

    workbook.set_colour_RGB(0x25, 255, 255, 255)
    style_white = xlwt.easyxf('pattern: pattern solid, fore_colour white_colour')
    style_white.borders = set_boarders()


    # 设置列宽
    worksheet.col(1).width = 256 * 20
    worksheet.col(2).width = 256 * 20
    worksheet.col(3).width = 256 * 20
    worksheet.col(4).width = 256 * 20
    # worksheet.col(5).width = 256*20
    # worksheet.col(6).width = 256*20
    # worksheet.col(7).width = 256*20
    # worksheet.col(8).width = 256*20
    # worksheet.col(9).width = 256*20
    # worksheet.col(10).width = 256*20
    # worksheet.col(11).width = 256*20

    # 隐藏列
    # worksheet.col(0).hidden = 1

    # 写入标题
    worksheet.write_merge(0, 1, 0, 0, label='Site', style=style)
    worksheet.write_merge(0, 1, 1, 1, label='', style=style)
    worksheet.write_merge(0, 1, 2, 2, label='PN', style=style)
    worksheet.write_merge(0, 1, 3, 3, label='FOX PN', style=style)
    worksheet.write_merge(0, 1, 4, 4, label='Project', style=style)
    worksheet.write_merge(0, 1, 5, 5, label='Type', style=style)
    worksheet.write_merge(0, 1, 6, 6, label='Qty Reservation', style=style)
    worksheet.write_merge(0, 1, 7, 7, label='HUB Qty Picked', style=style)
    worksheet.write_merge(0, 1, 8, 8, label='HUB Qty On Hold', style=style)
    worksheet.write_merge(0, 1, 9, 9, label='HUB Qty Available', style=style)
    worksheet.write_merge(0, 1, 10, 10, label='Onway', style=style)
    worksheet.write_merge(0, 1, 11, 11, label='Total shipped QTY', style=style)

    # (Ww.site.startswith('Du'), 2),
    # (Ww.site.startswith('Ven'), 3),
    # (Ww.site.startswith('SGP'), 4),
    # (Ww.site.startswith('SLC'), 5),
    # (Ww.site.startswith('CMH'), 6),
    # (Ww.site.startswith('LUK'), 7),
    # (Ww.site.startswith('TJ'), 8),)

    cvg_items = session.query(Ww).order_by(case(value=Ww.site, whens={'CVG': 0,
                                                                      'Dub': 1,
                                                                      'Venlo': 3,
                                                                      'SGP': 4,
                                                                      'SLC': 5,
                                                                      'CMH': 6,
                                                                      'LUK': 7,
                                                                      'TJ Direct ship': 8
                                                                      }

                                                ), Ww.pn).all()
    print(cvg_items)
    week_items = session.query(Week_name.day, Week_name.week).all()
    print(len(week_items))

    week_len = len(week_items)

    # 用字母表示week最后一栏的栏号
    col_letter = convert_to_letter(11 + week_len)
    # 倒数第八周栏号
    last8_letter = convert_to_letter(11 + week_len - 7)

    # 最大值行号
    max_letter = convert_to_letter(11 + week_len + 1)
    worksheet.write_merge(0, 1, 11 + week_len + 1, 11 + week_len + 1, label='Sales MAX', style=style)
    # 近8周平均值行号
    average_letter = convert_to_letter(11 + week_len + 2)
    worksheet.write_merge(0, 1, 11 + week_len + 2, 11 + week_len + 2, label='8 Weeks Sales Average', style=style)

    # 隐藏近8周之前的周
    for i in range(12, week_len + 12 - 8):
        worksheet.col(i).hidden = 1

    for index, item in enumerate(cvg_items):
        index = index + 2
        print(index)
        site = item.site
        pn = item.pn
        fox_pn = item.fox_pn
        project = item.project
        type = item.type
        qty_reservation = item.qty_reservation
        qty_picked = item.qty_picked
        qty_on_hold = item.qty_on_hold
        qty_available = item.qty_available
        on_way = session.query(func.sum(On_way.qty)).filter(On_way.pn == pn, On_way.site == site).scalar()
        print(site)
        print(pn)

        try:
            qty_picked, qty_on_hold, qty_available = session.query(Filtered.qty_picked, Filtered.qty_on_hold,
                                                                   Filtered.qty_available).filter(Filtered.site == site,
                                                                                                  Filtered.aws_sku == pn).first()
        except:
            qty_picked = qty_on_hold = qty_available = 0

        try:
            qty_reservation = total_qty_available = session.query(func.sum(Sites.qty_available)).filter(
                Sites.aws_sku == pn, Sites.site == site, Sites.odm_id.startswith('000')).scalar()
        except:
            qty_reservation = 0
        if not qty_reservation:
            qty_reservation = 0

        print('-' * 30)
        print('qty_picked', qty_picked)

        # 写入excel
        # 参数对应 行, 列, 值
        worksheet.write(index, 0, label=site, style=style_white)
        worksheet.write(index, 1, label=xlwt.Formula(f'a{index + 1}&c{index + 1}'), style=style_white)
        worksheet.write(index, 2, label=pn, style=style_white)
        worksheet.write(index, 3, label=fox_pn, style=style_white)
        worksheet.write(index, 4, label=project, style=style_white)
        worksheet.write(index, 5, label=type, style=style_white)
        worksheet.write(index, 6, label=qty_reservation, style=style_white)
        worksheet.write(index, 7, label=qty_picked, style=style_white)
        worksheet.write(index, 8, label=qty_on_hold, style=style_white)
        worksheet.write(index, 9, label=qty_available, style=style_white)
        worksheet.write(index, 10, label=on_way, style=style_green)
        worksheet.write(index, 11, label=xlwt.Formula(f'SUM(M{index + 1}:{col_letter}{index + 1})'), style=style_pink)
        # max 数据
        worksheet.write(index, 11 + week_len + 1, label=xlwt.Formula(f'MAX(M{index + 1}:{col_letter}{index + 1})'), style=style_green)

        # average 数据
        worksheet.write(index, 11 + week_len + 2,
                        label=xlwt.Formula(f'SUM({last8_letter}{index + 1}:{col_letter}{index + 1})/8'), style=style_green)

        # 写入week数据
        for i, week_item in enumerate(week_items):
            day = week_item.day
            week = week_item.week
            try:
                try:
                    week_data = session.query(Week_data.total).filter(Week_data.day == day, Week_data.site == site,
                                                                      Week_data.pn == pn).first()[0]
                except:
                    week_data = 0
                # if day == current_day and site != 'TJ Direct ship':
                #     week_data = session.query(Web_week_data_filtered.total).filter(Web_week_data_filtered.day==day,
                #                                                                    Web_week_data_filtered.site==site,
                #                                                                    Web_week_data_filtered.sku==pn).first()[0]
                #
                # if day == current_day and site == 'TJ Direct ship':
                #     week_data = session.query(func.sum(Sap_material.quantity)).filter(Sap_material.material==fox_pn).scalar()
                #     if week_data < 0:
                #         week_data = 0

            except Exception as e:

                print(e)
                week_data = 0
                print('week_data', week_data)
                # time.sleep(15)
                pass
            print(i)
            print(day)
            try:
                worksheet.write(0, i + 12, label=week, style=style_yellow)
            except:
                pass
            try:
                worksheet.write(1, i + 12, label=day, style=style_yellow)
            except:
                pass
            if week_data:
                print('site---->', site)
                print('week--->', week)
                print('week data -------->', week_data)
                # if site == 'LUK' and pn == '504-002207-001':
                #     print(week_data)
                #     time.sleep(50)
                try:
                    week_data = int(week_data)
                except:
                    week_data = 0
            else:
                week_data = 0
            # if site =='SLC' and pn == '504-002385-002' and day=='20210905':
            #
            #     print('site---->', site)
            #     print('week--->', week)
            #     print('week data -------->', week_data)
            #     print('pn---->', pn)
            #     time.sleep(60)
            worksheet.write(index, i + 12, label=week_data, style=style_white)

    # 保存
    try:
        workbook.save(f'RPA({current_day})AWS WW shipping out qty---WK{current_week_num}.xls')
    except:
        print('have this xls')
    print(len(cvg_items))

    e = datetime.now()
    print(e - s)

if __name__ == '__main__':
    to_excel()