#### 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图表')