def __init__(
self,
bwMode=None,
xfrm=None,
noFill=None,
solidFill=None,
gradFill=None,
pattFill=None,
ln=None,
scene3d=None,
custGeom=None,
prstGeom=None,
sp3d=None,
extLst=None,
):
self.bwMode = bwMode
self.xfrm = xfrm
self.noFill = noFill
self.solidFill = solidFill
self.gradFill = gradFill
self.pattFill = pattFill
if ln is None:
ln = LineProperties()
self.ln = ln
self.custGeom = custGeom
self.prstGeom = prstGeom
self.scene3d = scene3d
self.sp3d = sp3d
def _create_chart(worksheet):
"""Create the f*****g chart"""
chart = ScatterChart()
chart.varyColors = True
chart.title = "Financial Analysis"
chart.style = 1
chart.height = 10
chart.width = 20
chart.x_axis.title = "Financial Quarter"
chart.y_axis.title = "Cost"
chart.legend = None
chart.x_axis.majorUnit = 0.5
chart.x_axis.minorGridlines = None
# chart.y_axis.majorUnit = 200
xvalues = Reference(worksheet, min_col=1, min_row=3, max_row=6)
picker = _color_gen()
for i in range(2, 7):
values = Reference(worksheet, min_col=i, min_row=2, max_row=6)
series = Series(values, xvalues, title_from_data=True)
series.smooth = True
series.marker.symbol = "circle"
line_prop = LineProperties(solidFill=next(picker))
series.graphicalProperties.line = line_prop
chart.series.append(series)
worksheet.add_chart(chart, "G1")
return worksheet
def B():
sheetB = wb.create_sheet('주요국가별 출원동향', 1)
B그래프data = Data.주요국출원동향()
for r in dataframe_to_rows(B그래프data, index=False, header=True):
sheetB.append(r)
sheetB.insert_cols(2)
for row, cellobj in enumerate(list(sheetB.columns)[1]):
n = '=right(A%d,2)' % (row + 1)
cellobj.value = n
sheetB['B22'] = '합계'
sheetB['C22'] = '=SUM(C2:C21)'
sheetB['D22'] = '=SUM(D2:D21)'
sheetB['E22'] = '=SUM(E2:E21)'
sheetB['F22'] = '=SUM(F2:F21)'
chartB1 = LineChart()
dataB1 = Reference(sheetB, min_col=3, min_row=1, max_row=21, max_col=6)
catsB1 = Reference(sheetB, min_col=2, min_row=2, max_row=21)
chartB1.add_data(dataB1, titles_from_data=True)
chartB1.set_categories(catsB1)
chartB1.y_axis.majorGridlines = None
chartB1.width = 15
chartB1.height = 10
chartB1.legend.position = 't'
chartB1.graphical_properties = GraphicalProperties(ln=LineProperties(
noFill=True))
sheetB.add_chart(chartB1, 'H2')
chartB2 = PieChart()
dataB2 = Reference(sheetB, min_col=3, min_row=22, max_col=6)
labelsB2 = Reference(sheetB, min_col=3, min_row=1, max_col=6)
chartB2.add_data(dataB2, from_rows=22, titles_from_data=False)
chartB2.set_categories(labelsB2)
chartB2.width = 5
chartB2.height = 5
chartB2.legend = None
chartB2.graphical_properties = GraphicalProperties(
ln=LineProperties(noFill=True, solidFill=None, gradFill=None))
chartB2.dLbls = DataLabelList()
chartB2.dLbls.showPercent = True
sheetB.add_chart(chartB2, 'M1')
def draw_lateral_pattern_scatterchart(data_work_book, data_columns_list):
# build a new work sheet for contrast charts
data_work_book.create_sheet(title='Lateral Contrast LineChart', index=0)
tmp_dict = get_pattern_rows_map(data_work_book)
#print(tmp_dict['4kread'].keys())
# plot chart
column_num = 0
for data_column in data_columns_list:
#column_position = cu.num2letter(column_num *8 +1)
column_position = cu.num2letter(column_num *9 +1)
column_num = column_num +1
# get pattern info in sheets combined together.
pattern_num = 0
for pattern_name in cu.bp_sort(tmp_dict.keys(), screening=True):
# 4mwrite
#row_position = str(pattern_num *16 +1)
#row_position = str(pattern_num *22 +1)
row_position = str(pattern_num *26 +1)
pattern_num = pattern_num +1
chart_position = column_position + row_position
#print(chart_position)
# chart format
chart = ScatterChart()
#chart.height = 10
chart.height = 12
chart.width = 17
chart.title = str(pattern_name)
chart.legend.position = 't'
tmp_sheet = tmp_dict[pattern_name].keys()[0]
chart.x_axis.title = wb[tmp_sheet][str(cu.num2letter(data_column)) + '1'].value
chart.y_axis.title = 'latency(ms)'
# turn majorGridlines off using shapes.GraphicalProperties and drawing.LineProperties
#chart.y_axis.majorGridlines.spPr = GraphicalProperties(noFill = 'True')
#chart.y_axis.majorGridlines.spPr.ln = LineProperties(solidFill = '000000')
#chart.x_axis.majorGridlines = ChartLines()
chart.x_axis.majorGridlines.spPr = GraphicalProperties(noFill=True)
chart.x_axis.majorGridlines.spPr.ln = LineProperties(solidFill = 'F0F0F0')
#chart.dLbls = DataLabelList()
#chart.dLbls.showVal = 0
# add info from different sheet for a certain pattern , 'sheet1':[n,n+1]
line_set_info = tmp_dict[pattern_name]
#print(line_set_info)
for sheetN_set_name in line_set_info.keys():
line_title = str(sheetN_set_name)
line_set = line_set_info[sheetN_set_name]
#print(sheetN_set_name,line_set)
# width (samples name)
xvalues = Reference(data_work_book[sheetN_set_name], min_col=1, min_row=line_set[0], max_row=line_set[-1])
# height (value point)
yvalues = Reference(data_work_book[sheetN_set_name], min_col=data_column, min_row=line_set[0], max_row=line_set[-1])
series = Series(yvalues, xvalues, title=line_title)
chart.series.append(series)
wb['Lateral Contrast LineChart'].add_chart(chart, chart_position)
def insert_graph(self):
c1 = LineChart()
c1.title = "Degradation Mode"
#c1.style = 12
c1.y_axis.title = 'degradation percent'
c1.x_axis.title = ''
data = Reference(self.worksheet,
min_col=9,
min_row=1,
max_col=12,
max_row=self.rows - 1)
cats = Reference(self.worksheet,
min_col=3,
min_row=2,
max_row=self.rows - 1)
c1.add_data(data, titles_from_data=True)
c1.set_categories(cats)
for serie in c1.series:
serie.graphicalProperties.line.width = 24000
sgp = GraphicalProperties(ln=LineProperties(noFill=True))
c1.y_axis.majorGridlines.spPr = sgp
self.worksheet.add_chart(c1, "C%s" % self.rows)
def A():
sheetA = wb.create_sheet('전체 출원동향', 0)
A그래프data = Data.전체출원동향()
for r in dataframe_to_rows(A그래프data, index=False, header=True):
sheetA.append(r)
sheetA.insert_cols(2)
for row, cellobj in enumerate(list(sheetA.columns)[1]):
n = '=right(A%d,2)' % (row + 1)
cellobj.value = n
chartA1 = BarChart()
dataA1 = Reference(sheetA, min_col=3, min_row=1, max_row=21)
catsA1 = Reference(sheetA, min_col=2, min_row=2, max_row=21)
chartA1.add_data(dataA1, titles_from_data=True)
chartA1.set_categories(catsA1)
chartA1.y_axis.majorGridlines = None
chartA2 = LineChart()
dataA2 = Reference(sheetA, min_col=4, min_row=1, max_row=21)
chartA2.add_data(dataA2, titles_from_data=True)
chartA2.y_axis.majorGridlines = None
chartA2.y_axis.axId = 2000
# y축 위치 변경
chartA2.y_axis.crosses = 'max'
# 그래프 합치기
chartA1 += chartA2
chartA1.width = 15
chartA1.height = 10
chartA1.legend.position = 't'
chartA1.graphical_properties = GraphicalProperties(
ln=LineProperties(noFill=True)) # 테두리 제거
sheetA.add_chart(chartA1, 'F2')
global savepath
savepath = Data.Save()
def mk_cpurelative_sheet(wb, _options):
"""
Purpose:
To create the Workbook(excel file) with Relative CPU usage graphs using the data gathered from vmstat
Parameters:
wb - The current Workbook
_options - the arguments passed to the script
"""
# --- 'CPU' Sheet
ws = wb.create_sheet('CPU_Relative')
looprow_marker = 1 # Keeps a track of the next row that a data record will be inserted
loopcol = 40 # increases in 4 columns for each server
graphpos = 2 # Which row to place the First Graph
# "Coloring" the sheet's background, to hide the data.
area = Reference(ws, min_col=1, min_row=1, max_row=500, max_col=40)
for cell in area.cells:
ws[cell].fill = PatternFill(bgColor=BGCOLOR,
fgColor=BGCOLOR,
fill_type="solid")
ws[cell].font = Font(color=BGCOLOR)
for server in _options['server']:
print('Generating "vmstat" reports for {} ...'.format(server))
looprow = looprow_marker
ws.cell(row=1, column=loopcol, value='Date')
ws.cell(row=1, column=loopcol + 1, value='Average Busy')
ws.cell(row=1,
column=loopcol + 2,
value='% of Samples above {}% Cpu Usage'.format(100 -
TRESHOLDW))
ws.cell(row=1,
column=loopcol + 3,
value='% of Samples above {}% Cpu Usage'.format(100 -
TRESHOLDC))
# Querying Server and its data from Database
server_entries = Vmstat().query_by('servername', server)
# Looping over server's entries
for entry in server_entries:
date = entry.date
average = entry.peak_avg_busy
warning = entry.peak_avg_warning
critical = entry.peak_avg_critical
# if the entry's date is newer than range's start and older then range's stop
if _options['startdate'] <= date <= _options['enddate']:
# if we want to skip weekend days
if not _options['dontskip'] and date.weekday() < 5:
looprow = looprow + 1
ws.cell(row=looprow, column=loopcol, value=date)
ws.cell(row=looprow, column=loopcol + 1, value=average)
ws.cell(row=looprow, column=loopcol + 2, value=warning)
ws.cell(row=looprow, column=loopcol + 3, value=critical)
# if we want to include weekends
elif _options['dontskip']:
looprow = looprow + 1
ws.cell(row=looprow, column=loopcol, value=date)
ws.cell(row=looprow, column=loopcol + 1, value=average)
ws.cell(row=looprow, column=loopcol + 2, value=warning)
ws.cell(row=looprow, column=loopcol + 3, value=critical)
try:
# --- Setting Chart Properties
chart = LineChart()
chart.title = '{} %CPU'.format(server)
chart.style = 3
chart.x_axis.number_format = 'dd/mm'
chart.x_axis.majorTimeUnit = "days"
chart.y_axis.scaling.min = 0
chart.y_axis.scaling.max = 100
# --- All this to rotate the 'date' axis in 270 degrees
rot = RichTextProperties(vert='vert270')
axis = CharacterProperties()
chart.x_axis.textProperties = \
RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=axis), endParaRPr=axis)], bodyPr=rot)
# ---
chart.height = 10
chart.width = 25
chart.legend.position = "t"
chart.legend.layout = Layout(manualLayout=ManualLayout(
yMode='edge', xMode='edge', h=0.1, w=0.8))
chart.plot_area.layout = Layout(
manualLayout=ManualLayout(yMode='edge', xMode='edge'))
pf = PatternFillProperties(prst='pct25',
fgClr=ColorChoice(srgbClr='8FAF52'))
chart.plot_area.graphicalProperties = GraphicalProperties(
pattFill=pf)
# --- Looping over Columns to build the Chart
# columns Average, Warning and Critical
for col in range(loopcol + 1, loopcol + 4):
values = Reference(ws, min_col=col, min_row=1, max_row=looprow)
series = Series(values,
title_from_data=True) # creating the Serie
chart.series.append(series)
# --- Setting Plot Area Format
# Formatting Series
chart.series[0].graphicalProperties.line = LineProperties(
solidFill=ColorChoice(prstClr='green'))
chart.series[1].graphicalProperties.line = LineProperties(
solidFill=ColorChoice(prstClr='orange'))
chart.series[2].graphicalProperties.line = LineProperties(
solidFill=ColorChoice(prstClr='red'))
# Creating and Formatting Trend Line
chart.series[0].trendline = Trendline(
forward=str(1 + int(looprow * FORECAST)))
chart.series[
0].trendline.graphicalProperties = GraphicalProperties()
chart.series[
0].trendline.graphicalProperties.line.solidFill = ColorChoice(
prstClr='purple')
chart.series[0].trendline.graphicalProperties.line.width = 25000
# Setting the 'date' x-axis, with forecast of 33% of the time range
i = 0
for i in range(looprow, 2 + int(looprow * (1 + FORECAST))):
ws.cell(row=i, column=loopcol).value = \
ws.cell(row=i - 1, column=loopcol).value + timedelta(days=1)
dates = Reference(ws, min_col=loopcol, min_row=2, max_row=i)
chart.set_categories(dates)
# Set the Starting column for the next server
loopcol = loopcol + 4
# Adding the Chart
ws.add_chart(chart, "A{}".format(graphpos))
# Adding The Comments Session
# Setting Analysis Column size ---
ws.column_dimensions['P'].width = 75
ws.merge_cells('P{}:P{}'.format(graphpos + 1, graphpos + 17))
ws['P{}'.format(graphpos)].font = Font(name=FONTNAME,
size=14,
color=FONTCOLOR)
ws['P{}'.format(graphpos)].value = '{} Analysis:'.format(server)
ws['P{}'.format(graphpos + 1)].alignment = Alignment(
horizontal='left', vertical='top', wrapText=True)
area = Reference(ws,
min_col=16,
min_row=graphpos + 1,
max_row=graphpos + 17,
max_col=16)
for cell in area.cells:
ws[cell].font = Font(name=FONTNAME, size=11, color=FONTCOLOR)
ws[cell].fill = PatternFill(fill_type="solid",
start_color='FFFFFF',
end_color='FFFFFF')
ws[cell].border = BORDER
# Updating the Graphic Positioner
graphpos = graphpos + 19
except ValueError as err_msg:
print 'Error while processing vmstat data for server {}! Could not create the Chart.\n' \
'Extend error message: \n' \
'{}'.format(server, err_msg)
def add_trend_line():
line_props = LineProperties(solidFill='1890ff', prstDash='dash', w=15010)
g_props = GraphicalProperties(ln=line_props)
linear_trendline = Trendline(spPr=g_props, forward=1, backward=1)
return linear_trendline
def set_spec_line(line):
line_props = LineProperties(solidFill='fa541c', w=15010)
g_props = GraphicalProperties(ln=line_props)
line.trendline = Trendline(spPr=g_props, forward=1, backward=1)
line.graphicalProperties.line.noFill = True
return 0
stopIfTrue=True,
font=Font(color="E93423")))
# Chart
chart = BarChart()
chart.type = "col"
chart.title = "Sales per Month and Store"
chart.height = 11.5
chart.width = 20.5
# Add each column as a series, ignoring total row and col
data = Reference(sheet,
min_col=startcol + 1,
min_row=startrow,
max_row=startrow + nrows - 1,
max_col=startcol + ncols - 1)
categories = Reference(sheet,
min_col=startcol,
min_row=startrow + 1,
max_row=startrow + nrows - 1)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
cell = sheet.cell(row=startrow + nrows + 2, column=startcol)
sheet.add_chart(chart=chart, anchor=cell.coordinate)
# Chart formatting
chart.y_axis.title = "Sales"
chart.x_axis.title = summary.index.name
# Hide y-axis line: spPR stands for ShapeProperties
chart.y_axis.spPr = GraphicalProperties(ln=LineProperties(noFill=True))
def D():
sheetD = wb.create_sheet('주요국 내외국인 출원점유율', 3)
D그래프data1 = Data.내외국인점유율()
for r in dataframe_to_rows(D그래프data1, index=False, header=True):
sheetD.append(r)
sheetD['A4'] = '합계'
sheetD['B4'] = '=SUM(B2:B3)'
sheetD['C4'] = '=SUM(C2:C3)'
sheetD['D4'] = '=SUM(D2:D3)'
sheetD['E4'] = '=SUM(E2:E3)'
sheetD['A5'] = ' '
D그래프data2 = Data.외국인점유율()
for r in dataframe_to_rows(D그래프data2, index=False, header=True):
sheetD.append(r)
# 전체 그래프
chartD1 = PieChart()
dataD1 = Reference(sheetD, min_col=2, min_row=4, max_col=5)
labelD1 = Reference(sheetD, min_col=2, min_row=1, max_col=5)
chartD1.add_data(dataD1, from_rows=4, titles_from_data=False)
chartD1.set_categories(labelD1)
chartD1.width = 8
chartD1.height = 8
chartD1.legend = None
chartD1.graphical_properties = GraphicalProperties(ln=LineProperties(
noFill=True))
chartD1.dLbls = DataLabelList()
chartD1.dLbls.showPercent = True
chartD1.dLbls.showCatName = True
sheetD.add_chart(chartD1, 'P2')
# KR 그래프
chartD2 = PieChart()
dataD2 = Reference(sheetD, min_col=2, min_row=2, max_row=3)
labelD2 = Reference(sheetD, min_col=1, min_row=2, max_row=3)
chartD2.add_data(dataD2, titles_from_data=False)
chartD2.set_categories(labelD2)
chartD2.width = 6.5
chartD2.height = 6.5
chartD2.legend = None
chartD2.graphical_properties = GraphicalProperties(ln=LineProperties(
noFill=True))
chartD2.dLbls = DataLabelList()
chartD2.dLbls.showPercent = True
chartD2.dLbls.showCatName = True
sheetD.add_chart(chartD2, 'K18')
# JP 그래프
chartD3 = PieChart()
dataD3 = Reference(sheetD, min_col=3, min_row=2, max_row=3)
labelD3 = Reference(sheetD, min_col=1, min_row=2, max_row=3)
chartD3.add_data(dataD3, titles_from_data=False)
chartD3.set_categories(labelD3)
chartD3.width = 6.5
chartD3.height = 6.5
chartD3.legend = None
chartD3.graphical_properties = GraphicalProperties(ln=LineProperties(
noFill=True))
chartD3.dLbls = DataLabelList()
chartD3.dLbls.showPercent = True
chartD3.dLbls.showCatName = True
sheetD.add_chart(chartD3, 'O18')
# US 그래프
chartD4 = PieChart()
dataD4 = Reference(sheetD, min_col=4, min_row=2, max_row=3)
labelD4 = Reference(sheetD, min_col=1, min_row=2, max_row=3)
chartD4.add_data(dataD4, titles_from_data=False)
chartD4.set_categories(labelD4)
chartD4.width = 6.5
chartD4.height = 6.5
chartD4.legend = None
chartD4.graphical_properties = GraphicalProperties(ln=LineProperties(
noFill=True))
chartD4.dLbls = DataLabelList()
chartD4.dLbls.showPercent = True
chartD4.dLbls.showCatName = True
sheetD.add_chart(chartD4, 'S18')
# EP 그래프
chartD5 = PieChart()
dataD5 = Reference(sheetD, min_col=5, min_row=2, max_row=3)
labelD5 = Reference(sheetD, min_col=1, min_row=2, max_row=3)
chartD5.add_data(dataD5, titles_from_data=False)
chartD5.set_categories(labelD5)
chartD5.width = 6.5
chartD5.height = 6.5
chartD5.legend = None
chartD5.graphical_properties = GraphicalProperties(ln=LineProperties(
noFill=True))
chartD5.dLbls = DataLabelList()
chartD5.dLbls.showPercent = True
chartD5.dLbls.showCatName = True
sheetD.add_chart(chartD5, 'W18')
# KR 외국인 그래프(상위 4개국)
chartD6 = PieChart()
dataD6 = Reference(sheetD, min_col=2, min_row=7, max_row=10)
labelD6 = Reference(sheetD, min_col=1, min_row=7, max_row=10)
chartD6.add_data(dataD6, titles_from_data=False)
chartD6.set_categories(labelD6)
chartD6.width = 6.5
chartD6.height = 6.5
chartD6.legend = None
chartD6.graphical_properties = GraphicalProperties(ln=LineProperties(
noFill=True))
chartD6.dLbls = DataLabelList()
chartD6.dLbls.showPercent = True
chartD6.dLbls.showCatName = True
sheetD.add_chart(chartD6, 'K31')
# JP 외국인 그래프(상위 4개국)
chartD7 = PieChart()
dataD7 = Reference(sheetD, min_col=4, min_row=7, max_row=10)
labelD7 = Reference(sheetD, min_col=3, min_row=7, max_row=10)
chartD7.add_data(dataD7, titles_from_data=False)
chartD7.set_categories(labelD7)
chartD7.width = 6.5
chartD7.height = 6.5
chartD7.legend = None
chartD7.graphical_properties = GraphicalProperties(ln=LineProperties(
noFill=True))
chartD7.dLbls = DataLabelList()
chartD7.dLbls.showPercent = True
chartD7.dLbls.showCatName = True
sheetD.add_chart(chartD7, 'O31')
# US 외국인 그래프(상위 4개국)
chartD8 = PieChart()
dataD8 = Reference(sheetD, min_col=6, min_row=7, max_row=10)
labelD8 = Reference(sheetD, min_col=5, min_row=7, max_row=10)
chartD8.add_data(dataD8, titles_from_data=False)
chartD8.set_categories(labelD8)
chartD8.width = 6.5
chartD8.height = 6.5
chartD8.legend = None
chartD8.graphical_properties = GraphicalProperties(ln=LineProperties(
noFill=True))
chartD8.dLbls = DataLabelList()
chartD8.dLbls.showPercent = True
chartD8.dLbls.showCatName = True
sheetD.add_chart(chartD8, 'S31')
# EP 외국인 그래프(상위 4개국)
chartD9 = PieChart()
dataD9 = Reference(sheetD, min_col=8, min_row=7, max_row=10)
labelD9 = Reference(sheetD, min_col=7, min_row=7, max_row=10)
chartD9.add_data(dataD9, titles_from_data=False)
chartD9.set_categories(labelD9)
chartD9.width = 6.5
chartD9.height = 6.5
chartD9.legend = None
chartD9.graphical_properties = GraphicalProperties(ln=LineProperties(
noFill=True))
chartD9.dLbls = DataLabelList()
chartD9.dLbls.showPercent = True
chartD9.dLbls.showCatName = True
sheetD.add_chart(chartD9, 'W31')
def C():
# 데이터 정리
sheetC = wb.create_sheet('주요국 내 상위다출원국가', 2)
C상위다출원국가KR = Data.상위다출원국가('KR')
C상위다출원국가JP = Data.상위다출원국가('JP')
C상위다출원국가US = Data.상위다출원국가('US')
C상위다출원국가EP = Data.상위다출원국가('EP')
for r in dataframe_to_rows(C상위다출원국가KR, index=False, header=True):
sheetC.append(r)
sheetC['A22'] = ''
sheetC['A23'] = ''
for r in dataframe_to_rows(C상위다출원국가JP, index=False, header=True):
sheetC.append(r)
sheetC['A45'] = ''
sheetC['A46'] = ''
for r in dataframe_to_rows(C상위다출원국가US, index=False, header=True):
sheetC.append(r)
sheetC['A68'] = ''
sheetC['A69'] = ''
for r in dataframe_to_rows(C상위다출원국가EP, index=False, header=True):
sheetC.append(r)
sheetC['A91'] = ''
sheetC['A92'] = ''
sheetC.insert_cols(2)
for row, cellobj in enumerate(list(sheetC.columns)[1]):
n = '=right(A%d,2)' % (row + 1)
cellobj.value = n
# 엑셀 함수
sheetC['B22'] = '합계'
sheetC['C22'] = '=SUM(C2:C21)'
sheetC['D22'] = '=SUM(D2:D21)'
sheetC['E22'] = '=SUM(E2:E21)'
sheetC['F22'] = '=SUM(F2:F21)'
sheetC['A23'] = ' '
sheetC['B45'] = '합계'
sheetC['C45'] = '=SUM(C25:C44)'
sheetC['D45'] = '=SUM(D25:D44)'
sheetC['E45'] = '=SUM(E25:E44)'
sheetC['F45'] = '=SUM(F25:F44)'
sheetC['A46'] = ' '
sheetC['B68'] = '합계'
sheetC['C68'] = '=SUM(C48:C67)'
sheetC['D68'] = '=SUM(D48:D67)'
sheetC['E68'] = '=SUM(E48:E67)'
sheetC['F68'] = '=SUM(F48:F67)'
sheetC['A69'] = ' '
sheetC['B91'] = '합계'
sheetC['C91'] = '=SUM(C71:C90)'
sheetC['D91'] = '=SUM(D71:D90)'
sheetC['E91'] = '=SUM(E71:E90)'
sheetC['F91'] = '=SUM(F71:F90)'
sheetC['A92'] = ' '
# 그래프 그리기
chartC11 = LineChart()
dataC11 = Reference(sheetC, min_col=3, min_row=1, max_col=6, max_row=21)
catsC11 = Reference(sheetC, min_col=2, min_row=2, max_row=21)
chartC11.add_data(dataC11, titles_from_data=True)
chartC11.set_categories(catsC11)
chartC11.y_axis.majorGridlines = None
chartC11.width = 15
chartC11.height = 10
chartC11.legend.position = 't'
chartC11.graphical_properties = GraphicalProperties(ln=LineProperties(
noFill=True))
sheetC.add_chart(chartC11, 'H2')
chartC12 = PieChart()
dataC12 = Reference(sheetC, min_col=3, min_row=22, max_col=6)
labelC12 = Reference(sheetC, min_col=3, min_row=1, max_col=6)
chartC12.add_data(dataC12, from_rows=22, titles_from_data=False)
chartC12.set_categories(labelC12)
chartC12.width = 5
chartC12.height = 5
chartC12.legend = None
chartC12.graphical_properties = GraphicalProperties(ln=LineProperties(
noFill=True))
chartC12.dLbls = DataLabelList()
chartC12.dLbls.showPercent = True
sheetC.add_chart(chartC12, 'M1')
chartC21 = LineChart()
dataC21 = Reference(sheetC, min_col=3, min_row=24, max_col=6, max_row=44)
catsC21 = Reference(sheetC, min_col=2, min_row=25, max_row=44)
chartC21.add_data(dataC21, titles_from_data=True)
chartC21.set_categories(catsC21)
chartC21.y_axis.majorGridlines = None
chartC21.width = 15
chartC21.height = 10
chartC21.legend.position = 't'
chartC21.graphical_properties = GraphicalProperties(ln=LineProperties(
noFill=True))
sheetC.add_chart(chartC21, 'H25')
chartC22 = PieChart()
dataC22 = Reference(sheetC, min_col=3, min_row=45, max_col=6)
labelC22 = Reference(sheetC, min_col=3, min_row=24, max_col=6)
chartC22.add_data(dataC22, from_rows=45, titles_from_data=False)
chartC22.set_categories(labelC22)
chartC22.width = 5
chartC22.height = 5
chartC22.legend = None
chartC22.graphical_properties = GraphicalProperties(ln=LineProperties(
noFill=True))
chartC22.dLbls = DataLabelList()
chartC22.dLbls.showPercent = True
sheetC.add_chart(chartC22, 'M24')
chartC31 = LineChart()
dataC31 = Reference(sheetC, min_col=3, min_row=47, max_col=6, max_row=67)
catsC31 = Reference(sheetC, min_col=2, min_row=48, max_row=67)
chartC31.add_data(dataC31, titles_from_data=True)
chartC31.set_categories(catsC31)
chartC31.y_axis.majorGridlines = None
chartC31.width = 15
chartC31.height = 10
chartC31.legend.position = 't'
chartC31.graphical_properties = GraphicalProperties(ln=LineProperties(
noFill=True))
sheetC.add_chart(chartC31, 'H48')
chartC32 = PieChart()
dataC32 = Reference(sheetC, min_col=3, min_row=68, max_col=6)
labelC32 = Reference(sheetC, min_col=3, min_row=47, max_col=6)
chartC32.add_data(dataC32, from_rows=68, titles_from_data=False)
chartC32.set_categories(labelC32)
chartC32.width = 5
chartC32.height = 5
chartC32.legend = None
chartC32.graphical_properties = GraphicalProperties(ln=LineProperties(
noFill=True))
chartC32.dLbls = DataLabelList()
chartC32.dLbls.showPercent = True
sheetC.add_chart(chartC32, 'M47')
chartC41 = LineChart()
dataC41 = Reference(sheetC, min_col=3, min_row=70, max_col=6, max_row=90)
catsC41 = Reference(sheetC, min_col=2, min_row=71, max_row=90)
chartC41.add_data(dataC41, titles_from_data=True)
chartC41.set_categories(catsC41)
chartC41.y_axis.majorGridlines = None
chartC41.width = 15
chartC41.height = 10
chartC41.legend.position = 't'
chartC41.graphical_properties = GraphicalProperties(ln=LineProperties(
noFill=True))
sheetC.add_chart(chartC41, 'H71')
chartC42 = PieChart()
dataC42 = Reference(sheetC, min_col=3, min_row=91, max_col=6)
labelC42 = Reference(sheetC, min_col=3, min_row=70, max_col=6)
chartC42.add_data(dataC42, from_rows=91, titles_from_data=False)
chartC42.set_categories(labelC42)
chartC42.width = 5
chartC42.height = 5
chartC42.legend = None
chartC42.graphical_properties = GraphicalProperties(ln=LineProperties(
noFill=True))
chartC42.dLbls = DataLabelList()
chartC42.dLbls.showPercent = True
sheetC.add_chart(chartC42, 'M70')
