def createChartByType(type):
if type == "bar":
chart = BarChart()
elif type == "line":
chart = LineChart()
elif type == "radar":
chart = RadarChart()
return chart
wb = openpyxl.load_workbook('재고장.xlsx')
ws = wb['물류재고장']
# chart 데이터 참조범위 지정
data1 = Reference(ws, min_col=5, min_row=1, max_col=6, max_row=12)
# data2 = Reference(ws, 5, 2, 5, 12)
labels = Reference(ws, 1, 2, 1, 12)
# 차트 종류 지정
# chart = BarChart() # 막대그래프
# chart.type = 'bar' # 가로(bar), 세로(col) - 디폴트값
# chart = LineChart() # 꺾은 선 그래프
# chart = AreaChart() # 영역형 그래프
# chart = PieChart() # 원형 차트
chart = RadarChart() # 방사형 차트
chart.grouping = 'stacked' # 누적
# chart.overlap = 100 # for barchart: 100
chart.add_data(data1,
titles_from_data=True) # title...=True: 첫 셀값을 계열값(범례)로 사용
# chart.add_data(data2)
chart.set_categories(labels)
chart.title = '재고현황' # 차트 제목
# chart.x_axis.title = '랙번호' # x축 제목
# chart.y_axis.title = '재고량' # y축 제목
chart.height = 10 # 차트 가로 크기
chart.width = 20 # 차트 세로 크기
import openpyxl from openpyxl.chart import RadarChart, Reference wb = openpyxl.load_workbook(r"..\data\radar_chart.xlsx") sh = wb.active data = Reference(sh, min_col=2, max_col=4, min_row=1, max_row=sh.max_row) labels = Reference(sh, min_col=1, min_row=2, max_row=sh.max_row) chart = RadarChart() #デフォルトはstandard #filledは塗りつぶし #chart.type = "filled" chart.title = "部門別売上" chart.add_data(data, titles_from_data=True) chart.set_categories(labels) sh.add_chart(chart, "F2") wb.save(r"..\data\radar_chart.xlsx")
['Jan', 0, 2500, 500, 0,],
['Feb', 0, 5500, 750, 1500],
['Mar', 0, 9000, 1500, 2500],
['Apr', 0, 6500, 2000, 4000],
['May', 0, 3500, 5500, 3500],
['Jun', 0, 0, 7500, 1500],
['Jul', 0, 0, 8500, 800],
['Aug', 1500, 0, 7000, 550],
['Sep', 5000, 0, 3500, 2500],
['Oct', 8500, 0, 2500, 6000],
['Nov', 3500, 0, 500, 5500],
['Dec', 500, 0, 100, 3000 ],
]
for row in rows:
ws.append(row)
chart = RadarChart()
chart.type = "filled"
labels = Reference(ws, min_col=1, min_row=2, max_row=13)
data = Reference(ws, min_col=2, max_col=5, min_row=1, max_row=13)
chart.add_data(data, titles_from_data=True)
chart.set_categories(labels)
chart.style = 26
chart.title = "Garden Centre Sales"
chart.y_axis.delete = True
ws.add_chart(chart, "A17")
wb.save("radar.xlsx")
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图表')
0,
],
['Feb', 0, 5500, 750, 1500],
['Mar', 0, 9000, 1500, 2500],
['Apr', 0, 6500, 2000, 4000],
['May', 0, 3500, 5500, 3500],
['Jun', 0, 0, 7500, 1500],
['Jul', 0, 0, 8500, 800],
['Aug', 1500, 0, 7000, 550],
['Sep', 5000, 0, 3500, 2500],
['Oct', 8500, 0, 2500, 6000],
['Nov', 3500, 0, 500, 5500],
['Dec', 500, 0, 100, 3000],
]
for row in rows:
ws.append(row)
chart = RadarChart()
chart.type = "filled"
labels = Reference(ws, min_col=1, min_row=2, max_row=13)
data = Reference(ws, min_col=2, max_col=5, min_row=1, max_row=13)
chart.add_data(data, titles_from_data=True)
chart.set_categories(labels)
chart.style = 26
chart.title = "Garden Centre Sales"
chart.y_axis.delete = True
ws.add_chart(chart, "A17")
wb.save("radar.xlsx")
graph_list.append(line_dict)'''
# Build Rate vs Atten vs Orientation
for intColumn in range(len(attens)):
c = 2 + intColumn
this_atten = attens[intColumn]
for intRow in range(len(angles)):
r = 2 + intRow
this_angle = angles[intRow]
cell_value = 0
for datum in data_set:
if datum['rot_angle'] == this_angle and datum['atten'] == this_atten:
cell_value = datum['thru_avg']
break
ws.cell(row = r, column = c, value = cell_value)
chart = RadarChart()
#chart.type = "filled"
labels = Reference(ws, min_col=1, min_row=2, max_row=len(angles) + 1)
data = Reference(ws, min_col=2, max_col=len(attens) + 1, min_row= 1, max_row=len(angles) + 1)
chart.add_data(data, titles_from_data=True)
chart.set_categories(labels)
for intN in range(len(attens)):
chart.series[intN].graphicalProperties.line.width = 1
#chart.style = 26
chart.title = "Rate vs Attenuation vs Orientation"
chart.width = chart.width * 1.5
chart.height = chart.height * 2
chart.y_axis.majorUnit = 5
#chart.y_axis.delete = True
ws.add_chart(chart, "A17")
['Mar', 0, 9000, 1500, 2500],
['Apr', 0, 6500, 2000, 4000],
['May', 0, 3500, 5500, 3500],
['Jun', 0, 0, 7500, 1500],
['Jul', 0, 0, 8500, 800],
['Aug', 1500, 0, 7000, 550],
['Sep', 5000, 0, 3500, 2500],
['Oct', 8500, 0, 2500, 6000],
['Nov', 3500, 0, 500, 5500],
['Dec', 500, 0, 100, 3000 ],
]
for row in rows:
ws13.append(row)
chart = RadarChart()
chart.type = "filled"
labels = Reference(ws13, min_col=1, min_row=2, max_row=13)
data = Reference(ws13, min_col=2, max_col=5, min_row=1, max_row=13)
chart.add_data(data, titles_from_data=True)
chart.set_categories(labels)
chart.style = 26
chart.title = "Garden Centre Sales"
chart.y_axis.delete = True
ws13.add_chart(chart, "A17")