2022-11-22 用python导出接口返回的数据并添加自定

工作中需要一些数据，但系统没有，用python自己写一个，虽然比较菜，但基本可用，代码如下:

import requests
import xlwt

#下一个接口需要先登录获取cookie
url_login = 'https://xxxxxxx/login'
data_login = {'username': 'xxxxx', 'password': 'xxxxxx'}
session = requests.Session()
res_login = session.post(url_login, data=data_login)
request_cookies = session.cookies.get_dict()

#这个是自定义后面写入到表格的样式
def set_style(name, height, color, bold=False):
    style = xlwt.XFStyle()   # 初始化样式
    font = xlwt.Font()       # 为样式创建字体
    font.name = name
    font.bold = bold
    font.colour_index = color
    font.height = height
    style.font = font
'''
这部分是另外一个填充用的代码，还不清楚怎么用
    # # 设置背景颜色
    # pattern = xlwt.Pattern()
    # # 设置背景颜色的模式
    # pattern.pattern = xlwt.Pattern.SOLID_PATTERN
    # # 背景颜色
    # pattern.pattern_fore_colour = color
    # style.pattern = pattern
'''
    return style


def write_excel(path):
    # 创建工作簿
    workbook = xlwt.Workbook(encoding='utf-8')
    # 创建sheet
    data_sheet = workbook.add_sheet('工单创建记录')
    # 指定各列的宽度
    data_sheet.col(0).width = 8000
    data_sheet.col(1).width = 7000
    data_sheet.col(2).width = 4000
    data_sheet.col(3).width = 6000
    data_sheet.col(4).width = 6000
    data_sheet.col(5).width = 15000

    #先把表头标题写好
    row0 = [u'计划单号', u'创建时间', '计划单状态', '计划开始日期','计划结束日期','备注']
    for i in range(len(row0)):
        data_sheet.write(0, i, row0[i], set_style('Times New Roman', 220, 0, True))

    #从接口中获取待写入的数据
    base_url = 'https://xxxxxxxx'
    api_path = '/xxx/xxxx'
    plandata_url = base_url + api_path
    params_search = {'page': '1', 'rows': '2000'}
    res_getplandata = requests.post(url=plandata_url, data=params_search, cookies=request_cookies)
    res_json = res_getplandata.json()
    total = res_json["total"]

    for t in range(total):
        statedsp = res_json["rows"][t]["statedsp"]
        note = res_json["rows"][t]["note"]
        mescreatetime = res_json["rows"][t]["mescreatetime"]
        orderno = res_json["rows"][t]["orderno"]
        planstartdate = res_json["rows"][t]["planstartdate"]
        planenddate = res_json["rows"][t]["planenddate"]
        # 写入数据
        rows_content = [orderno, mescreatetime, statedsp, planstartdate, planenddate, note]
        for i in range(len(rows_content)):
            if statedsp == '待排产':
                data_sheet.write(t+1, i, rows_content[i], set_style('Times New Roman', 220, 53, False))  #这里控制的是整行的字体颜色，我还不知道怎么处理单个单元格的颜色
            elif statedsp == '已排产':
                data_sheet.write(t+1, i, rows_content[i], set_style('Times New Roman', 220, 46, False))
            elif statedsp == '完成':
                data_sheet.write(t+1, i, rows_content[i], set_style('Times New Roman', 220, 4, False))
            else:
                data_sheet.write(t+1, i, rows_content[i], set_style('Times New Roman', 220, 2, False))

        # 保存文件
        workbook.save(path)



if __name__ == '__main__':
    # 设置路径
    path = 'C:/Users/xxxx/Desktop/工单制定列表.xls'
    write_excel(path)
    print(u'创建《工单制定列表.xls》文件成功')