创建excel工作簿：

def excel(workbook_1, sheet_text):
    sheet = workbook_1.add_sheet(sheet_text)
    head = ['标题', '副标题', '评分', '播放量', '是否会员', '剧集信息', '链接']
    for h in range(len(head)):
        sheet.write(0, h, head[h])
    return sheet

获取b站上架影视资源：

def bili(type_1, sheet_1):
    page = 1
    status_type = {'电影': 2, '电视剧': 5, '纪录片': 3}
    value = ['title', 'subTitle', 'score', 'order', 'badge', 'index_show', 'link']
    num = 1
    while True:
        try:
            print(type_1, page, '页')
            url = f'https://api.bilibili.com/pgc/season/index/result?st=2&order=2&area=-1&style_id=-1&release_date=-1&season_status=-1&sort=0&' \
                  f'page={page}&season_type={status_type[type_1]}&pagesize=1000&type=1'
            r = requests.get(url)
            data_list = r.json()['data']['list']
            for data in data_list:
                for j in range(len(value)):
                    sheet_1.write(num, j, data[value[j]])
                num += 1
            page += 1
        except KeyError:
            break

实现效果：