python-爬取新冠肺炎病毒实时统计 并用xlsxwriter输出到execl

代码

# _*_ coding:utf-8 _*_
"""
file_name:py_infect
author:Sam
"""
from urllib import request
import operator
import json
import xlsxwriter


def main(request_url=""):
    # 设置头部信息
    request_url = request.Request(request_url)
    request_url.add_header('User-Agent', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/76.0.3809.132 Safari/537.36')
    # 请求地址
    response = request.urlopen(request_url)
    sea_data = json.loads(response.read())["data"]['areaTree']
    # print(sea_data)
    sea = []
    # 重组列表
    for info in sea_data:
        sea_dict = {}
        sea_dict['country'] = info['name']
        # 这里做了一堆的判断，有更简单的麻烦跟我说一下
        if info['lastUpdateTime'] is not None:
            sea_dict['lastUpdateTime'] = info['lastUpdateTime']
        else:
            sea_dict['lastUpdateTime'] = '暂无'
        if info['today']['confirm'] is not None:
            sea_dict['today_confirm'] = info['today']['confirm']
        else:
            sea_dict['today_confirm'] = 0
        if info['today']['dead'] is not None:
            sea_dict['today_dead'] = info['today']['dead']
        else:
            sea_dict['today_dead'] = 0
        if info['today']['heal'] is not None:
            sea_dict['today_heal'] = info['today']['heal']
        else:
            sea_dict['today_heal'] = 0
        if info['today']['suspect'] is not None:
            sea_dict['today_suspect'] = info['today']['suspect']
        else:
            sea_dict['today_suspect'] = 0
        if info['total']['confirm'] is not None:
            sea_dict['total_confirm'] = info['total']['confirm']
        else:
            sea_dict['total_confirm'] = 0
        if info['total']['dead'] is not None:
            sea_dict['total_dead'] = info['total']['dead']
        else:
            sea_dict['total_dead'] = 0
        if info['total']['heal'] is not None:
            sea_dict['total_heal'] = info['total']['heal']
        else:
            sea_dict['total_heal'] = 0
        if info['total']['suspect'] is not None:
            sea_dict['total_suspect'] = info['total']['suspect']
        else:
            sea_dict['total_suspect'] = 0
        sea.append(sea_dict)
    # 排序 reverse=True:倒序，key=operator.itemgetter('total_confirm')total_confirm指排序的字段
    sea.sort(key=operator.itemgetter('total_confirm'), reverse=True)
    export_execl(sea)


def export_execl(data):
    workbook = xlsxwriter.Workbook('infect.xlsx')
    # 创建一个sheet 参数就是sheet的名称，不加就默认为sheet1
    worksheet = workbook.add_worksheet()
    # 设置格式
    # 加粗
    bold = workbook.add_format({'bold': True})
    # 红色
    red = workbook.add_format({'font_color': '#DC143C'})
    # 灰色
    gray = workbook.add_format({'font_color': '#696969'})
    # 橙色
    orange = workbook.add_format({'font_color': '#FF8C00'})
    # 绿色
    green = workbook.add_format({'font_color': '#008000'})
    # 设置表头
    worksheet.write('A1', '序号', bold)
    worksheet.write('B1', '国家', bold)
    worksheet.write('C1', '今日确诊', bold)
    worksheet.write('D1', '今日疑似', bold)
    worksheet.write('E1', '今日死亡', bold)
    worksheet.write('F1', '今日治愈', bold)
    worksheet.write('G1', '总确诊', bold)
    worksheet.write('H1', '总疑似', bold)
    worksheet.write('I1', '总死亡', bold)
    worksheet.write('J1', '总治愈', bold)
    for info in data:
        num = data.index(info) + 2
        # 第一参数为位置，二为值，三为格式
        worksheet.write('A'+str(num), num - 1, bold)
        worksheet.write('B'+str(num), info['country'], bold)
        worksheet.write('C'+str(num), info['today_confirm'], red)
        worksheet.write('D'+str(num), info['today_suspect'], orange)
        worksheet.write('E'+str(num), info['today_dead'], gray)
        worksheet.write('F'+str(num), info['today_heal'], green)
        worksheet.write('G'+str(num), info['total_confirm'], red)
        worksheet.write('H'+str(num), info['total_suspect'], orange)
        worksheet.write('I'+str(num), info['total_dead'], gray)
        worksheet.write('J'+str(num), info['total_heal'], green)
    # 写完了要记得关掉
    workbook.close()
    print('导出execl成功')


if __name__ == '__main__':
    start = 0
    # 这里爬取的是网易的实时统计
    url = "https://c.m.163.com/ug/api/wuhan/app/data/list-total?t=317007688845"
    main(url)

结果：