生成当日运行日报

https://www.cnblogs.com/sunfankun/p/17578113.html

• 使用以上接口获取上一交易日

#V1.0 zhy 代码第一版，支持选择自动/输入沪深交易额，支持自动导入excel
#V20221118 修正股票期权数据库CPU使用率为手动输入
#V20230106 各系统成交汇总-新增银海量化、云泰量化
#V20230422 各系统成交汇总-新增云驰量化
#V20230517 支持内网通过ESB获取上一交易日
#V20230517 修改通过调用内部接口获取上一交易日

import xlrd
import xlwt
from xlutils.copy import copy
from selenium import webdriver
from selenium.webdriver.common.by import By
from selenium.webdriver.chrome.service import Service
import linecache
import datetime
import win32com.client as win32
import os
import marshmallow
from scrapy import Field, Item
import openpyxl
#import requests
import json
from jsonpath import jsonpath
import urllib

def xlsx_to_xls(fname, export_name, delete_flag=True):
    """
    将xlsx文件转化为xls文件
    :param fname: 传入待转换的文件路径(可传绝对路径，也可传入相对路径，都可以)
    :param export_name: 传入转换后到哪个目录下的路径(可传绝对路径，也可传入相对路径，都可以)
    :param delete_flag: 转换成功后，是否删除原来的xlsx的文件,默认删除 布尔类型
    :return:    无返回值
    """
    excel = win32.gencache.EnsureDispatch('Excel.Application')
    excel.Visible = False
    excel.DisplayAlerts = False
    absolute_path = os.path.join(os.path.dirname(os.path.abspath(fname)), os.path.basename(fname))
    save_path = os.path.join(os.path.dirname(os.path.abspath(export_name)), os.path.basename(export_name))
    wb = excel.Workbooks.Open(absolute_path)
    wb.SaveAs(save_path, FileFormat=56)  # FileFormat = 51 is for .xlsx extension
    wb.Close()  # FileFormat = 56 is for .xls extension
    excel.Application.Quit()
    if delete_flag:
        os.remove(absolute_path)

def xls_to_xlsx(fname, export_name, delete_flag=True):
    """
    将xlsx文件转化为xls文件
    :param fname: 传入待转换的文件路径(可传绝对路径，也可传入相对路径，都可以)
    :param export_name: 传入转换后到哪个目录下的路径(可传绝对路径，也可传入相对路径，都可以)
    :param delete_flag: 转换成功后，是否删除原来的xlsx的文件,默认删除 布尔类型
    :return:    无返回值
    """
    excel = win32.gencache.EnsureDispatch('Excel.Application')
    excel.Visible = False
    excel.DisplayAlerts = False
    absolute_path = os.path.join(os.path.dirname(os.path.abspath(fname)), os.path.basename(fname))
    save_path = os.path.join(os.path.dirname(os.path.abspath(export_name)), os.path.basename(export_name))
    wb = excel.Workbooks.Open(absolute_path)
    wb.SaveAs(save_path, FileFormat=51)  # FileFormat = 51 is for .xlsx extension
    wb.Close()  # FileFormat = 56 is for .xls extension
    excel.Application.Quit()
    if delete_flag:
        os.remove(absolute_path)
def call_esb_api(url,inputdata):
	headers = {"Content-Type": "application/json"}
	res = requests.post(url, data=inputdata, headers=headers, verify=False)
	esb_response=json.loads(urllib.parse.unquote(res.text))
	if str(1) in str(esb_response['code']):
		 print("调用ESB接口成功")
		 return esb_response
	else:
		print("调用ESB接口失败")

def lastjyr():
	string = (datetime.datetime.now().strftime("%Y%m%d"))
	count = 1
	f = open('jyr.txt','r',encoding='utf8')
	for line in f.readlines():
		if string in line:
			new_count = count
		count += 1
	lastjyr = linecache.getline('jyr.txt',new_count-1)
	return lastjyr.strip()
	f.close()

#豁免因不认证证书的报错
#requests.packages.urllib3.disable_warnings()
today = (datetime.datetime.now().strftime("%Y%m%d"))
#定义包头
headers = {"Content-Type": "application/json"}
#ESBLGOIN
login_url = "https://192.168.33.45:7002/login"
#ESB接口调用
esburl="https://192.168.33.45:7002/service"

#定义style 样式
Style = xlwt.XFStyle()
Style_1 = xlwt.XFStyle()

#定义Font 字体
Font = xlwt.Font()
Font.name = '宋体'
Font.bold = False
Font.height = 220 #11号字体。11*20
Style.font = Font
Style_1.font = Font
#定义框线
border = xlwt.Borders()#给单元格加框线
border.left = xlwt.Borders.THIN#左
border.top = xlwt.Borders.THIN#上
border.right = xlwt.Borders.THIN#右
border.bottom = xlwt.Borders.THIN#下

border.leftcolour=0x40#设置框线颜色，0x40是黑色，颜色真的巨多，都晕了
border.rightcolour=0x40
border.top_colour=0x40
border.bottomcolour=0x40
Style.borders = border

#获取日期格式
dateFormat = xlwt.XFStyle()
dateFormat.num_format_str = 'dd/mm/yyyy'
dateFormat.borders = border
dateFormat.font = Font

#获取需要输入的值
jzjy_db=input('请输入集中交易数据库利用率（不带百分号,eg：1.23)')
rzrq_db=input('请输入融资融券数据库利用率（不带百分号,eg：1.23)')
gpqq_db=input('请输入股票期权数据库利用率（不带百分号,eg：1.23)')
#ygt_db=input('请输入一柜通数据库利用率（不带百分号）')
#jzjy_cpu=input('请输入集中交易中间件利用率（不带百分号）')
#rzrq_cpu=input('请输入融资融券中间件利用率（不带百分号）')
#gpqq_cpu=input('请输入股票期权中间件利用率（不带百分号）')
#ygt_cpu=input('请输入一柜通中间件利用率（不带百分号）')

#filename_1 =  ('20220818当日交易时间段CPU利用率.xls'.encode('utf-8'). decode('utf-8') )
#filename_2 =  ('20220818各系统成交汇总.xls'.encode('utf-8'). decode('utf-8') )
#filename_3 =  ('20220818活跃客户统计.xls'.encode('utf-8'). decode('utf-8') )
#filename_4 =  ('20220818终端委托成交统计.xls'.encode('utf-8'). decode('utf-8') )

today = (datetime.datetime.now().strftime("%Y%m%d"))

#按照当前日期取文件名
filename_1 = (today + '当日交易时间段CPU利用率.xls')
filename_2 = (today + '各系统成交汇总.xls')
filename_3 = (today + '活跃客户统计.xls')
filename_4 = (today + '终端委托成交统计.xls')

#获取交易日信息
#ESB登录用户名密码
#login_data = json.dumps({'loginId':'ygt3','loginPwd':'96e79218965eb72c92a549dd5a330112'})
#res = requests.post(login_url, data=login_data, headers=headers, verify=False)
#login_response=res.json()
#print(login_response['note'])
#if '[A.UA]ok' in login_response['note']:
#    print("登录ESB成功")
#    sessionid=login_response['sessionId']
#else:
#	print("登录ESB失败")

#定义获取上一交易日的入参
#last_jyr_input=json.dumps({
#	'czzd':'127.0.0.1',
#	'sessionId':sessionid,
#	'serviceId':'esb.ygt.cxjyr',
#	'rq':today,
#	'ts':'-1'
#})
#last_jyr_res=call_esb_api(url=esburl,inputdata=last_jyr_input)
#last_jyr_list=jsonpath(last_jyr_res, '$..jyr')
#last_jyr=''.join(last_jyr_list)
last_jyr = str(lastjyr())
print('取上一交易日('+last_jyr+')交易系统基础信息统计表，取表中')

#转换格式
xlsx_to_xls(last_jyr + '交易系统基础信息统计表.xlsx',last_jyr + '交易系统基础信息统计表.xls',delete_flag:=False)
last_excel_xlsx = xlrd.open_workbook(last_jyr + '交易系统基础信息统计表.xlsx');
last_excel_xls= xlrd.open_workbook(last_jyr + '交易系统基础信息统计表.xls',formatting_info=True);
current_excel = copy(last_excel_xls)

#读取活跃客户统计并写入基础信息表
hyyh_data_excel=xlrd.open_workbook(filename_3,formatting_info=True)
hyyh_table=hyyh_data_excel.sheet_by_name('Sheet11')
data_list=[]
hyyh_table_row=hyyh_table.nrows
current_hyyh_table_xlsx = last_excel_xlsx.sheet_by_name('活跃客户统计')
current_hyyh_table_row =current_hyyh_table_xlsx.nrows
current_hyyh_table = current_excel.get_sheet(2)
for i in range(hyyh_table_row):
 	data_list.append(hyyh_table.row_values(i))
 	data_list1 = data_list[::-1]
 	item = data_list1.pop(-1)
 	data_list1.insert(0,item)
 	for i in range(3,0,-1): 
 		for j in range(4):
 			try: 
 				current_hyyh_table.write(current_hyyh_table_row-1+i,j,data_list1[4-i][j],Style_1)
 			except:
 				break
print('活跃客户统计导入完成')

#读取各系统成交汇总并写入基础信息表
cjhz_data_excel=xlrd.open_workbook(filename_2,formatting_info=True)
cjhz_table=cjhz_data_excel.sheet_by_name('Sheet11')
data_list=[]
cjhz_table_row=cjhz_table.nrows
current_cjhz_table_xlsx = last_excel_xlsx.sheet_by_name('委托成交统计')
current_cjhz_table = current_excel.get_sheet(0)
for i in range(cjhz_table_row):
 	data_list.append(cjhz_table.row_values(i))
 	data_list1 = data_list[::-1]
 	item = data_list1.pop(-1)
 	data_list1.insert(0,item)
 	for i in range(4,0,-1): 
 		for j in range(6):
 			try: 
 				current_cjhz_table.write(1+i,j,data_list1[5-i][j],Style)
 			except:
 				break
print('各系统成交汇总导入完成')

#读取CPU利用率表
cpu_data_excel=xlrd.open_workbook(filename_1,formatting_info=True)
cpu_table=cpu_data_excel.sheet_by_name('Sheet11')
data_list=[]
current_cpu_table_xlsx = last_excel_xlsx.sheet_by_name('委托成交统计')
current_cpu_table = current_excel.get_sheet(0)
cpu_table_row=cpu_table.nrows
for i in range(cpu_table_row):
    data_list.append(cpu_table.row_values(i))
gpqq_cpu = data_list[1][5]
jzjy_cpu = data_list[3][5]
rzrq_cpu = data_list[4][5]
ygt_cpu = data_list[13][5]
#gpqq_db_1 = data_list[5][5]
#gpqq_db_2 = data_list[6][5]
#gpqq_db =  format((round(float(gpqq_db_1.strip('%'))/100,4)+round(float(gpqq_db_2.strip('%'))/100,4))/2,'.2%')
ygt_db = data_list[11][5]

#读取终端委托成交统计并写入基础信息表
zdwt_data_excel=xlrd.open_workbook(filename_4,formatting_info=True)
zdwt_table=zdwt_data_excel.sheet_by_name('Sheet11')
data_list=[]
zdwt_table_row=zdwt_table.nrows
current_zdwt_table_xlsx = last_excel_xlsx.sheet_by_name('委托成交统计')
current_zdwt_table = current_excel.get_sheet(0)
for i in range(zdwt_table_row):
 	data_list.append(zdwt_table.row_values(i))
 	data_list1 = data_list[::-1]
 	item = data_list1.pop(-1)
 	data_list1.insert(0,item)
 	for i in range(zdwt_table_row-1,0,-1): 
 		for j in range(2,11):
 			try: 
 				current_zdwt_table.write(7+i,j,data_list1[zdwt_table_row-i][j+1],Style)
 			except:
 				break
print('终端委托成交汇总导入完成')

#获取沪深交易额
ifauto=input('是否自动获取沪深市场成交额，自动获取需要本机安装最新版edge浏览器和外网。（y/n）')
if ifauto == 'y':
    s = Service("chromedriver.exe")
    driver = webdriver.Chrome(service=s)
    driver.get('http://q.10jqka.com.cn/zs/detail/code/1A0001/')   #打开网页
    a = driver.find_element(by=By.XPATH, value="/html/body/div[2]/div[3]/div[2]/div/div/div[1]/div[2]/dl[7]/dd").text 
    #深圳成交额
    driver.get('http://q.10jqka.com.cn/zs/detail/code/399001/')   #打开网页
    b = driver.find_element(by=By.XPATH, value="/html/body/div[2]/div[3]/div[2]/div/div/div[1]/div[2]/dl[7]/dd").text 
else:
    a = input('请输入沪市成交额(单位：亿元)')
    b = input('请输入深市成交额(单位：亿元)')  
    pass
print('沪深成交额获取成功')

#写入基础信息表:中间件利用率、DB利用率、沪深交易额
current_zdwt_table.write(0, 1, datetime.date.today(),dateFormat)
current_zdwt_table.write(0, 2, datetime.date.today(),dateFormat)
current_zdwt_table.write(0, 5, "沪 " + a,Style)
current_zdwt_table.write(0, 6, "深 " + b,Style)
current_zdwt_table.write(2, 7, jzjy_db + '%',Style)
current_zdwt_table.write(3, 7, rzrq_db + '%',Style)
current_zdwt_table.write(4, 7, gpqq_db + '%',Style)
current_zdwt_table.write(5, 7, ygt_db,Style)
current_zdwt_table.write(2, 8, jzjy_cpu,Style)
current_zdwt_table.write(3, 8, rzrq_cpu,Style)
current_zdwt_table.write(4, 8, gpqq_cpu,Style)
current_zdwt_table.write(5, 8, ygt_cpu,Style)  

current_excel.save(datetime.datetime.now().strftime("%Y%m%d")+'交易系统基础信息统计表.xls')
xls_to_xlsx(today + '交易系统基础信息统计表.xls', today + '交易系统基础信息统计表.xlsx',delete_flag:=True)
#补充由于copy丢失的公式
final=openpyxl.load_workbook(today+'交易系统基础信息统计表.xlsx')
function_excel_sheet= final.get_sheet_by_name('委托成交统计')
function_excel_sheet['C7'].value = '=VALUE(C3)+VALUE(C4)+VALUE(C5)+VALUE(C6)'
function_excel_sheet['D7'].value = '=VALUE(D3)+VALUE(D4)+VALUE(D5)+VALUE(D6)'
function_excel_sheet['E7'].value = '=VALUE(E3)+VALUE(E4)+VALUE(E5)+VALUE(E6)'
function_excel_sheet['F7'].value = '=VALUE(F3)+VALUE(F4)+VALUE(F5)+VALUE(F6)'
function_excel_sheet['G3'].value = '=VALUE(F3)/VALUE(RIGHT(F1,LEN(F1)-FIND("沪",F1))+RIGHT(G1,LEN(G1)-FIND("深",G1)))'
function_excel_sheet['G4'].value = '=VALUE(F4)/VALUE(RIGHT(F1,LEN(F1)-FIND("沪",F1))+RIGHT(G1,LEN(G1)-FIND("深",G1)))'
function_excel_sheet['G5'].value = '=VALUE(F5)/VALUE(RIGHT(F1,LEN(F1)-FIND("沪",F1))+RIGHT(G1,LEN(G1)-FIND("深",G1)))'
function_excel_sheet['G6'].value = '=VALUE(F6)/VALUE(RIGHT(F1,LEN(F1)-FIND("沪",F1))+RIGHT(G1,LEN(G1)-FIND("深",G1)))'
function_excel_sheet['G7'].value = '=SUM(G3:G6)'

final.save(today + '交易系统基础信息统计表.xlsx')
print(today + '交易系统基础信息统计表.xlsx生成成功')