import openpyxl
import requests

url='https://api.bilibili.com/x/v2/reply/main?csrf=9e1a61c984801379382903865edb8344&mode=3&next=0&oid=697023311&plat=1&seek_rpid=&type=1'

headers={
    'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/111.0.0.0 Safari/537.36'
}

response=requests.get(url=url,headers=headers)

mid_list=[i['member']['mid'] for i in response.json()['data']['replies']]
uname_list = [i['member']['uname'] for i in response.json()['data']['replies']]
sign_list=[i['member']['sign'] for i in response.json()['data']['replies']]
content_list=[i['content']['message'] for i in response.json()['data']['replies']]

dict1={}
dict1["Uid"] = mid_list
dict1["uname"] = uname_list
dict1["sign"] = sign_list
dict1["content"] = content_list

def write_excel():
    work_book=openpyxl.Workbook()
    sheet=work_book.create_sheet('评论')
    for index,(key,value) in enumerate(dict1.items()):
        sheet.cell(1,index+1,key)
        for i in range(len(value)):
            sheet.cell(i+2,index+1,value[i])


    work_book.save('评论.xlsx')

write_excel()