2019-03-15 使用Request POST获取CNABS网站上JSON格式的表格数据，并解析出来用xlwt写到Excel中

import requests
import xlwturl = 'https://v1.cn-abs.com/ajax/ChartMarketHandler.ashx'
headers={
'User-Agent':'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/71.0.3578.98 Safari/537.36'
}
# 每个交易场所每一年的发行金额
def get_marketInventory(): FromData={
'type':'marketInventory'
}
rep=requests.post(url,data=FromData).json()
for i in rep:
# print(i['SeriesName'],i['Points'])
for j in i['Points']:
result={
'交易场所':i['SeriesName'],
'发行年份':j['X'],
'发行金额':j['Y']
}
yield result
def parse_marketInventory():
wb = xlwt.Workbook()
sheet = wb.add_sheet('marketInventory')
sheet.write(0, 0, label='交易场所')
sheet.write(0, 1, label='发行年份')
sheet.write(0, 2, label='发行金额')
# sheet.write()
result = get_marketInventory()
# print(result)
i = 1
for res in result:
# print(res)
sheet.write(i, 0, label=res['交易场所'])
sheet.write(i, 1, label=res['发行年份'])
sheet.write(i, 2, label=res['发行金额'])
wb.save('marketInventory.xls')
i = i + 1
# print('共有' + str(i) + '行数据')
# 各个资产类型每一年的发行金额
def get_marketTotal():
FromData = {
'type': 'marketTotal'
}
rep = requests.post(url, data=FromData).json()
for i in rep:
# print(i['SeriesName'],i['Points'])
result = {
'资产类型': i['SeriesName'],
'发行金额':""+str(i['Points'][0]['Y'][0]).replace("[","'").replace("]","'"),
'存量金额':""+str(i['Points'][1]['Y'][0]).replace("[","'").replace("]","'")
}
yield result
def parse_marketTotal():
wb = xlwt.Workbook()
sheet = wb.add_sheet('marketTotal')
sheet.write(0, 0, label='资产类型')
sheet.write(0, 1, label='发行金额')
sheet.write(0, 2, label='存量金额')
# sheet.write()
result = get_marketTotal()
i = 1
for res in result:
# print(res)
sheet.write(i, 0, label=res['资产类型'])
sheet.write(i, 1, label=float(res['发行金额']))
sheet.write(i, 2, label=float(res['存量金额']))
wb.save('marketTotal.xls')
i = i + 1
# print('共有' + str(i) + '行数据')
if __name__ == '__main__':
parse_marketInventory()
parse_marketTotal()