python利用xlwt收集股票数据出现问题
这是我代码生成出来的文件的截图
我觉得有点寡淡，想加点颜色，就是涨的标红，跌的标绿
改了一下代码后重新生成
只有一部分数据标出了颜色，而且还提示超出字数限制？
该怎么解决呢？下面附上代码
import requests
from urllib.parse import urlencode
import xlwt
def genid(rawcode: str) -> str: # 生成东方财富专用的id
if rawcode[:3] == '000':
return f'1.{rawcode}'
if rawcode[:3] == '399':
return f'0,{rawcode}'
if rawcode[0] != '6':
return f'0.{rawcode}'
return f'1.{rawcode}'
# 传入股票代码，获取时间段的开始日期何结束日期，k线的间隔，复权方式
# 获取历史数据的函数
def get_link(code: str, begin_date: str = '20210603', end_date: str = '20210603', klt: int = 1,
fqt: int = 1) -> str:
EastmoneyKlines = {
'f51': '日期',
'f52': '开盘',
'f53': '收盘',
'f54': '最高',
'f55': '最低',
'f56': '成交量',
'f57': '成交额',
'f58': '振幅',
'f59': '涨跌幅',
'f60': '涨跌额',
'f61': '换手率',
}
fields = list(EastmoneyKlines.keys())
columns = list(EastmoneyKlines.values())
fields2 = ",".join(fields) # 用，间隔开
secid = genid(code)
params = {'fields1': 'f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13',
'fields2': fields2,
'beg': begin_date,
'end': end_date,
'type': '6',
'secid': secid,
'klt': f'{klt}',
'fqt': f'{fqt}',
}
base_url = 'https://push2his.eastmoney.com/api/qt/stock/kline/get'
return base_url + '?' + urlencode(params)
def get_excel(ticket: list):
headers = { # 反防爬
'Host': '19.push2.eastmoney.com',
'User-Agent': 'Mozilla/5.0 (Windows NT 6.3; WOW64; Trident/7.0; Touch; rv:11.0) like Gecko',
'Accept': '*/*',
'Accept-Language': 'zh-CN,zh;q=0.8,zh-TW;q=0.7,zh-HK;q=0.5,en-US;q=0.3,en;q=0.2',
'Referer': 'http://quote.eastmoney.com/center/gridlist.html',
}
# 创建一个Excel并初始化
wb = xlwt.Workbook(encoding='utf-8')
sheet = wb.add_sheet("sheet1", cell_overwrite_ok=True)
sheet.write(0, 0, '股票代码')
sheet.write(1, 0, '股票名称')
sheet.write(2, 0, '昨收/今开')
sheet.write(3, 0, '最高/最低')
sheet.write(4, 0, '现价/时间')
hour = 9
minn = 31
cnt = 5
while True:
strmin = str(minn)
strhou = str(hour)
if len(strhou) < 2:
strhou = "0" + strhou
if len(strmin) < 2:
strmin = "0" + strmin
sheet.write(int(cnt), 0, strhou + ":" + strmin)
cnt += 1
minn += 1
if hour == 11 and minn == 31:
hour = 13
minn = 1
if minn == 60:
hour += 1
minn = 0
if hour == 15:
break
sheet.write(cnt, 0, '收盘')
# 创建完毕
col = 1
for ti_code in ticket:
url = get_link(ti_code)
page = dict(requests.get(url, headers=headers).json())
data = page.get('data')
if data is None:
print(ti_code + "是非法的！")
else:
print("ok " + ti_code)
sheet.write(0, col, data['code'])
sheet.write(1, col, data['name'])
sheet.write(2, col, data['prePrice'])
now_data = list(data['klines'][0].split(','))
start_price = float(now_data[1])
last_price = float(data['prePrice'])
sheet.write(2, col + 1, start_price)
sheet.write(4, col, "--------------------------")
print(data)
if data['code'][:3] == '000':
sheet.write(1, col + 1, "沪市指数")
elif data['code'][:3] == '399':
sheet.write(1, col + 1, "深市指数")
elif data['code'][:3] == '300':
sheet.write(1, col + 1, "创业板")
elif data['code'][0] != '6':
sheet.write(1, col + 1, "沪市")
else:
sheet.write(1, col + 1, "深市")
max_price = 0
min_price = 999999
row = 5
for data in data['klines']:
data = list(data.split(','))
style = xlwt.XFStyle()
font = xlwt.Font()
now_price = float(data[1])
if now_price > last_price:
font.colour_index = 0x0A
elif now_price < last_price:
font.colour_index = 0x11
else:
font.colour_index = 0x3F
style.font = font
sheet.write(row, col, data[1])
max_price = max(float(data[1]), max_price)
min_price = min(float(data[1]), min_price)
up = (float(data[1]) / last_price - 1) * 100
sheet.write(row, col + 1, str('%.02f' % up) + "%",style)
row += 1
sheet.write(3, col, max_price)
sheet.write(3, col + 1, min_price)
col += 2
wb.save('data.xls')
f = open("target.txt", 'r')
Ticket = []
for line in f.readlines():
ticket_code = line[0:6]
if ticket_code in Ticket:
continue
Ticket.append(ticket_code)
get_excel(Ticket)
不知道你这个问题是否已经解决, 如果还没有解决的话:
- 这篇文章讲的很详细，请看：python xlwt 模块的初级使用
如果你已经解决了该问题, 非常希望你能够分享一下解决方案, 以帮助更多的人 ^-^