https://blog.csdn.net/kk123a/article/details/49813559
https://www.jianshu.com/p/0dd8cc68a3c3
from xlwt import *
import xlwt
import urllib.request
import json
import time
#创建execl样式----------------------------
style_yellow = XFStyle()
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 5 #设置单元格背景色为黄色
style_yellow.pattern = pattern
style_green = XFStyle()
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 3 #设置单元格背景色为绿色
style_green.pattern = pattern
style_blue = XFStyle()
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 44 #设置单元格背景色为蓝色
style_blue.pattern = pattern
style_cyan = XFStyle()
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 35 #设置单元格背景色为青色
style_cyan.pattern = pattern
#pattern.pattern_fore_colour = 5 # May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray, the list goes on...
#-----------------------------------------
def json2execl(subject):#将接口查询结果写入execl文件
title = list(subject[0])
sheet.write(len(subject)+2,0,url)#在文件尾部写入调用的接口参数
sheet.write(len(subject)+3,0,date)#在文件尾部写入调用接口的时间
for i in range(len(title)):
if title[i] in ['percentage']:
sheet.write(0,i,title[i],style=style_yellow)#在文件头部写标题
elif title[i] in ['poster','opmark','still']:
sheet.write(0,i,title[i],style=style_green)#在文件头部写标题
elif title[i] in ['column','columnname']:
sheet.write(0,i,title[i],style=style_blue)#
elif title[i] in ['seriestype']:
sheet.write(0,i,title[i],style=style_cyan)#
else:
sheet.write(0,i,title[i])
for i in range(len(subject)):#逐行读取
line = list(subject[i].values())#获取list的值
for j in range(len(line)):#逐列读取
sheet.write(i+1,j,line[j])#逐行逐列写入文件
date=time.strftime('%Y_%m_%d_%H_%M_%S',time.localtime())
command= 'gettopn'
type = 's'
#type = 'v'
#type = 't'
#type = 'c'
count = 20
excel = xlwt.Workbook(encoding='utf-8')
sheet = excel.add_sheet('xlwt_sheet1')
url = "http://xxx.xxx.xxx.240:8082/idp/"+command+"?type="+type+"&count="+str(count)
response=urllib.request.urlopen(url)
req=response.read()xxx_s = str(req, encoding='utf-8')
req_j=json.loads(req_s)
subject = req_j['subject']
json2execl(subject)
file = command+"_"+type+"_"+str(count)+"_"+date+".xls"
excel.save(file)#保存文件