#### chart.x_axis = DateAxis()
#### chart.x_axis.majorTimeUnit = "days"
## chart.x_axis.number_format ='mm/dd/yyyy'
## data = Reference(sheet, min_col=4, min_row=2, max_row=sheet.max_row,)
## title = Reference(sheet, min_col=3, min_row=2, max_row=sheet.max_row)
## chart.add_data(data)
## chart.set_categories(title)
## sheet.add_chart(chart, "A10")
chart = BarChart()
chart.title = name
chart.style = 10
chart.x_axis.title = 'Date'
chart.y_axis.title = 'Count'
chart.y_axis.crossAx = 500
chart.x_axis = DateAxis(crossAx=100)
chart.x_axis.number_format = 'yyyy/mm/dd'
chart.x_axis.majorTimeUnit = "days"
data = Reference(sheet, min_col=4, min_row=1, max_row=sheet.max_row)
chart.add_data(data, titles_from_data=True)
dates = Reference(sheet, min_col=3, min_row=2, max_row=sheet.max_row)
chart.set_categories(dates)
sheet.add_chart(chart, "G9")
sheet[
'G2'].value = 'SUM(D:D)/NETWORKDAYS(MIN(C:C),TODAY(),DATEVALUE({"2017/09/04","2017/09/21","2017/10/19","2017/10/20","2017/11/10","2017/11/22","2017/11/23","2017/11/24","2017/12/22","2017/12/25","2017/12/26","2017/12/27","2017/12/28","2018/12/29","2018/01/01","2018/01/02","2018/01/03","2018/01/04","2018/01/08","2018/01/15","2018/02/19","2018/03/23","2018/03/26","2018/03/27","2018/03/28","2018/03/29","2018/03/30","2018/05/25","2018/05/28","2018/09/03","2018/09/10","2018/09/19","2018/10/19","2018/11/06","2018/11/12","2018/11/22","2018/11/21","2018/11/23","2018/12/24","2018/12/25","2018/12/26","2018/12/27","2018/12/28","2018/12/31","2019/01/01","2019/01/02","2019/01/03","2019/01/04","2019/01/07","2019/01/21","2019/02/18","2019/03/22","2019/03/28","2019/03/27","2019/03/25","2019/03/26","2019/03/27","2019/03/29","2019/04/19","2019/05/27","2018/06/06","2018/06/07","2018/06/08","2018/06/09","2018/06/10","2018/06/11","2018/06/12","2018/06/13","2018/06/14","2018/06/15","2018/06/16","2018/06/17","2018/06/18","2018/06/19","2018/06/20","2018/06/21","2018/06/22","2018/06/23","2018/06/24","2018/06/25","2018/06/26","2018/06/27","2018/06/28","2018/06/29","2018/06/30","2018/07/01","2018/07/02","2018/07/03","2018/07/04","2018/07/05","2018/07/06","2018/07/07","2018/07/08","2018/07/09","2018/07/10","2018/07/11","2018/07/12","2018/07/13","2018/07/14","2018/07/15","2018/07/16","2018/07/17","2018/07/18","2018/07/19","2018/07/20","2018/07/21","2018/07/22","2018/07/23","2018/07/24","2018/07/25","2018/07/26","2018/07/27","2018/07/28","2018/07/29","2018/07/30","2018/07/31","2018/08/01","2018/08/02","2018/08/03","2018/08/04","2018/08/05","2018/08/06","2018/08/07","2018/08/08","2018/08/09","2018/08/10","2018/08/11","2018/08/12","2018/08/13","2018/08/14","2018/08/15","2019/06/05","2019/06/06","2019/06/07","2019/06/08","2019/06/09","2019/06/10","2019/06/11","2019/06/12","2019/06/13","2019/06/14","2019/06/15","2019/06/16","2019/06/17","2019/06/18","2019/06/19","2019/06/20","2019/06/21","2019/06/22","2019/06/23","2019/06/24","2019/06/25","2019/06/26","2019/06/27","2019/06/28","2019/06/29","2019/06/30","2019/07/01","2019/07/02","2019/07/03","2019/07/04","2019/07/05","2019/07/06","2019/07/07","2019/07/08","2019/07/09","2019/07/10","2019/07/11","2019/07/12","2019/07/13","2019/07/14","2019/07/15","2019/07/16","2019/07/17","2019/07/18","2019/07/19","2019/07/20","2019/07/21","2019/07/22","2019/07/23","2019/07/24","2019/07/25","2019/07/26","2019/07/27","2019/07/28","2019/07/29","2019/07/30","2019/07/31","2019/08/01","2019/08/02","2019/08/03","2019/08/04","2019/08/05","2019/08/06","2019/08/07","2019/08/08","2019/08/09","2019/08/10","2019/08/11","2019/08/12","2019/08/13","2019/08/14","2019/08/15","2019/08/16","2019/08/17","2019/08/18","2019/08/19","2019/08/20"}))'
sheet['F1'].value = 'Active Rate'
sheet['F2'].value = 'SUM(D:D)/SUMPRODUCT(1/COUNTIF(%s[Date],%s[Date]))' % (
newname, newname)
sheet['G1'].value = 'Overall Rate'
sheet['H1'].value = 'Closing Speed'
def excel_create_chart(excel_file):
"""
生成图表
:param excel_file:
:return:
"""
wb = load_workbook(excel_file)
print(f'{datetime.now()} | 信息 | 开始Excel图表渲染')
# 进行净值走势图渲染
net_worth_sheet = wb['账户净值']
net_worth_chart_sheet = wb.create_chartsheet(title='净值走势图')
chart1 = LineChart()
dates1 = Reference(net_worth_sheet,
min_col=1,
min_row=2,
max_row=len(net_worth_sheet['A']))
data1 = Reference(net_worth_sheet,
min_col=3,
min_row=1,
max_row=len(net_worth_sheet['C']))
chart1.add_data(data1, titles_from_data=True)
chart1.y_axis = NumericAxis(title='净值', majorTickMark='out')
chart1.x_axis = TextAxis(majorTickMark='out',
tickLblSkip=10,
tickMarkSkip=10,
noMultiLvlLbl=True,
numFmt='yyyy-mm-dd')
chart1.legend = None
chart1.title = str()
chart1.style = 1
chart1.set_categories(dates1)
net_worth_chart_sheet.add_chart(chart1)
# 进行权益走势图渲染
account_sheet = wb['账户统计']
account_chart_sheet = wb.create_chartsheet(title='权益走势图')
dates2 = Reference(account_sheet,
min_col=1,
min_row=2,
max_row=len(account_sheet['A']))
chart2 = LineChart(varyColors=True)
data2 = Reference(account_sheet,
min_col=8,
min_row=1,
max_row=len(account_sheet['H']))
chart2.add_data(data2, titles_from_data=True)
chart2.y_axis = NumericAxis(title='权益', majorTickMark='out')
chart2.legend = None
chart2.set_categories(dates2)
chart3 = BarChart()
data3 = Reference(account_sheet,
min_col=10,
min_row=1,
max_row=len(account_sheet['J']))
chart3.add_data(data3, titles_from_data=True)
chart3.y_axis = NumericAxis(axId=200,
title='风险度',
majorGridlines=None,
majorTickMark='out',
crosses='max')
chart3.x_axis = TextAxis(majorTickMark='out',
tickLblSkip=10,
tickMarkSkip=10,
noMultiLvlLbl=True,
numFmt='yyyy-mm-dd')
chart3.legend = None
chart3.set_categories(dates2)
chart2 += chart3
account_chart_sheet.add_chart(chart2)
# 进行交易分布图的渲染
categories_analysis_sheet = wb['交易分析(按品种)']
trading_frequency_analysis_sheet = wb.create_chartsheet(title='交易分布图')
labels = Reference(categories_analysis_sheet,
min_col=1,
min_row=2,
max_row=len(categories_analysis_sheet['A']))
chart4 = PieChart(varyColors=True)
chart4.style = 34
data4 = Reference(categories_analysis_sheet,
min_col=4,
min_row=2,
max_row=len(categories_analysis_sheet['D']))
chart4.add_data(data4)
chart4.set_categories(labels)
chart4.legend = None
# chart4.series[0].data_points = [DataPoint(idx=i, explosion=8)
# for i in range(len(categories_analysis_sheet['D']) - 1)]
chart4.series[0].dLbls = DataLabelList(dLblPos='bestFit',
showPercent=True,
showCatName=True,
showVal=True,
showLeaderLines=True)
chart4.layout = Layout(manualLayout=ManualLayout(
x=0, y=0, h=0.75, w=0.75, xMode='factor', yMode='factor'))
trading_frequency_analysis_sheet.add_chart(chart4)
# 进行品种盈亏图的渲染
categories_win_and_loss_chart_sheet = wb.create_chartsheet(title='品种盈亏图')
chart5 = BarChart(barDir='col')
chart5.style = 18
data5 = Reference(categories_analysis_sheet,
min_col=2,
min_row=2,
max_row=len(categories_analysis_sheet['B']))
chart5.add_data(data5)
chart5.set_categories(labels)
chart5.legend = None
chart5.series[0].dLbls = DataLabelList(showVal=True)
chart5.y_axis = NumericAxis(title='平仓盈亏',
majorTickMark='out',
minorTickMark='out')
categories_win_and_loss_chart_sheet.add_chart(chart5)
# 进行交易盈亏图的渲染
trading_win_and_loss_chart_sheet = wb.create_chartsheet(title='交易盈亏图')
chart6 = RadarChart()
chart6.style = 24
data6 = Reference(categories_analysis_sheet,
min_col=8,
max_col=9,
min_row=1,
max_row=categories_analysis_sheet.max_row)
chart6.add_data(data6, titles_from_data=True)
chart6.set_categories(labels)
trading_win_and_loss_chart_sheet.add_chart(chart6)
# 图表保存
wb.save(excel_file)
wb.close()
# 输出信息
print(f'{datetime.now()} | 信息 | 已生成Excel图表')
