将字典存为xlsx文件(爬取comp旷工 前一百位数据)

import asyncio
import random
# from asyncio import sleep
from pprint import pprint

import aiohttp
import xlwt
import pandas as pd

from asynic_study.read_page import read_page

url='https://comp.ccore.online/zh/distribution'


async def get_page(url):
    async with aiohttp.ClientSession() as session:
        # async with session.get('https://www.baidu.com') as resp:
        async with session.get(url) as resp:
            print(resp.status)
            data=await resp.text()
            pprint(data)
            await session.close()


#
# loop = asyncio.get_event_loop()
# loop.run_until_complete(get_page(url))
# loop.close()

# 将字典列表导出为Excel文件
def export_excel(dic_data):
    # 将字典列表转换为DataFrame
    pf=pd.DataFrame(list(dic_data))
    # 指定字段顺序
    order=['top', 'Address', 'miner_recieved','miner_sent', 'miner_balance','turnover_rate']
    pf=pf[order]
    # 将列名替换为中文
    columns_map={
        'i': '矿工排名',
        'Address': '矿工地址',
        'miner_recieved': '收到金额',
        'miner_sent': '发出金额',
        'miner_balance': '账户余额',
        'turnover_rate': '转手率'
    }
    pf.rename(columns=columns_map, inplace=True)
    # 指定生成的Excel表格名称
    file_path=pd.ExcelWriter('compdata.xlsx')
    # 替换空单元格
    pf.fillna(' ', inplace=True)
    # 输出
    pf.to_excel(file_path, encoding='utf-8', index=False)
    # 保存表格
    file_path.save()


def get_data(url):
    tree=read_page(url)
    data=[]
    for i in range(1, 101):
        Address=f"/html/body/div/div[1]/div/div[2]/div/div[5]/div[2]/table/tbody/tr[{i}]/td[2]/a/text()"

        Sent=f"/html/body/div/div[1]/div/div[2]/div/div[5]/div[2]/table/tbody/tr[{i}]/td[4]/text()"

        Balance=f"/html/body/div/div[1]/div/div[2]/div/div[5]/div[2]/table/tbody/tr[{i}]/td[5]/b/text()"

        Recieved=f"/html/body/div/div[1]/div/div[2]/div/div[5]/div[2]/table/tbody/tr[{i}]/td[3]/text()"

        miner_address=tree.xpath(Address)[0].strip()
        miner_sent=tree.xpath(Sent)[0].strip()
        miner_recieved=tree.xpath(Balance)[0].strip()
        miner_balance=tree.xpath(Recieved)[0].strip()
        rate = round(-(int(miner_sent) / int(miner_balance)), 4)
        turnover_rate = format(rate)
        top=i
        # dic = {
        #     "矿工排名":i,
        #     "矿工地址": str(miner_address),
        #     "收到金额" : int(miner_recieved),
        #     "发出金额": int(miner_sent),
        #     "账户余额" : int(miner_balance),
        #     "转手率": round(-(int(miner_sent)/ int(miner_recieved)), 4)
        # }

        dic={
            "top": i,
            "Address": str(miner_address),
            "miner_recieved": int(miner_recieved),
            "miner_sent": -int(miner_sent),
            "miner_balance": int(miner_balance),
            "turnover_rate": turnover_rate
        }

        data.append(dic)

    pprint(data)
    export_excel(data)
    return data


if __name__ == '__main__':
    get_data(url)