def _set_swiss7_chart(self, workbook):
ws = workbook['SWISS7_G']
ws_data = workbook['Data_category']
chart1 = BarChart()
chart1.type = "col"
# chart1.style = 12
chart1.width = 21
chart1.height = 12
chart1.grouping = "stacked"
chart1.overlap = 100
chart1.y_axis.title = 'Volume du trafic en %'
# chart1.x_axis.title = 'Heure'
chart1.gapWidth = 2
data = Reference(ws_data, min_col=2, min_row=4, max_row=28, max_col=8)
# cats = Reference(ws_data, min_col=2, min_row=4, max_row=28)
chart1.add_data(data, titles_from_data=True)
# chart1.set_categories(cats)
chart1.shape = 4
chart1.legend = None
chart1.series[0].graphicalProperties.solidFill = "00a9ff"
chart1.series[1].graphicalProperties.solidFill = "bce273"
chart1.series[2].graphicalProperties.solidFill = "ff708c"
chart1.series[3].graphicalProperties.solidFill = "003366"
chart1.series[4].graphicalProperties.solidFill = "ff00ff"
chart1.series[5].graphicalProperties.solidFill = "ff3399"
chart1.series[6].graphicalProperties.solidFill = "ff99cc"
ws.add_chart(chart1, "A11")
chart1 = BarChart()
chart1.type = "col"
# chart1.style = 12
chart1.width = 21
chart1.height = 12
chart1.grouping = "stacked"
chart1.overlap = 100
chart1.y_axis.title = 'Volume du trafic en %'
# chart1.x_axis.title = 'Heure'
chart1.gapWidth = 2
data = Reference(ws_data, min_col=2, min_row=32, max_row=56, max_col=8)
# cats = Reference(ws_data, min_col=2, min_row=32, max_row=56)
chart1.add_data(data, titles_from_data=True)
# chart1.set_categories(cats)
chart1.shape = 4
chart1.legend = None
chart1.series[0].graphicalProperties.solidFill = "00a9ff"
chart1.series[1].graphicalProperties.solidFill = "bce273"
chart1.series[2].graphicalProperties.solidFill = "ff708c"
chart1.series[3].graphicalProperties.solidFill = "003366"
chart1.series[4].graphicalProperties.solidFill = "ff00ff"
chart1.series[5].graphicalProperties.solidFill = "ff3399"
chart1.series[6].graphicalProperties.solidFill = "ff99cc"
ws.add_chart(chart1, "A46")
def plot_stack_mult(self, sheetname, column, value, unit):
df = self._df[[value, column]].pivot_table(index = "DateTime", values = value, columns = column, fill_value = 0)
df = df.resample(unit).sum()
with pd.ExcelWriter(self._file) as writer:
# excelファイルがあるなら追加書きする
if os.path.isfile(self._file):
writer.book = load_workbook(self._file)
# write the df on excel at once
df.to_excel(writer, sheet_name = sheetname)
sheet = writer.book.active # 対象シートを設定
# step列数ずつ複数のグラフを作成
step = 3 # 一つの図に描画する列数
max_col = sheet.max_column # 最大列数
chart_num = int(max_col / step) # 図の数
for i in range(chart_num):
start = i * step + 2
end = start + (step - 1)
pos = i * 18 + 1
title = "積み上げ棒グラフ" + str(i + 1) # 図ごとにタイトルを変える
chart = BarChart()
chart.type = "col"
chart.grouping = "stacked"
chart.overlap = 100
self._plot(sheet, chart, title = title, xlabel = "月", ylabel = "回数", ymax = 100, start_col = start, end_col = end, position = pos)
def insertTotalMoneyCPChart(sheet_obj, num_of_exp, symbol, finalOptionExpDate,
final_chart_col_loc, final_chart_insert_pos,
getNumberColsData):
if (Commonapi.debug == 1):
print("insertTotalMoneyCPChart started")
for i in range(0, (int)(num_of_exp)):
min_cols = (int)(final_chart_col_loc[i])
max_cols = min_cols + 1
m_rows = min_cols + getNumberColsData
print("m_rows min_cols is :", m_rows, min_cols)
chart = BarChart()
chart.type = "col"
chart.grouping = "stacked"
chart.overlap = 100
chart.title = symbol + '_' + finalOptionExpDate[i]
chart.y_axis.title = 'TOTAL_CALL_PUT_TM x K'
chart.x_axis.title = 'Dates'
data = Reference(sheet_obj,
min_col=min_cols,
min_row=1,
max_row=m_rows,
max_col=max_cols)
cats = Reference(sheet_obj, min_col=1, min_row=2, max_row=m_rows)
chart.add_data(data, titles_from_data=True)
chart.dataLabels = DataLabelList()
chart.dataLabels.showVal = True
chart.set_categories(cats)
sheet_obj.add_chart(chart, final_chart_insert_pos[i])
title = symbol + '_' + finalOptionExpDate[i]
print("Bar chart :", title)
if (Commonapi.debug == 1):
print("insertTotalMoneyCPChart ended")
def generate_hourly_chart(df_hourly, workbook):
num_rows = df_hourly.shape[0]
num_columns = df_hourly.shape[1] + 1
print("Generating hourly chart for {} rows and {} columns".format(
num_rows, num_columns))
chart = BarChart()
chart.type = "col"
chart.grouping = "stacked"
chart.overlap = 100
chart.style = 12
chart.title = "Hourly"
chart.y_axis.title = "Picks"
chart.x_axis.title = "Hour"
datasheet = workbook['Hourly']
data = Reference(datasheet,
min_col=3,
max_col=num_columns,
min_row=1,
max_row=num_rows)
titles = Reference(datasheet, min_col=2, min_row=2, max_row=num_rows)
chart.add_data(data=data, titles_from_data=True)
chart.set_categories(titles)
return chart
def generate_availability_chart(workbook):
print("Generating availability chart")
chart = BarChart()
chart.type = "bar"
chart.grouping = "stacked"
chart.overlap = 100
chart.style = 12
chart.title = "Availability Details"
chart.y_axis.title = "Hours"
datasheet = workbook['Summary']
data = Reference(datasheet, min_col=2, max_col=3, min_row=13, max_row=17)
titles = Reference(datasheet, min_col=1, min_row=14, max_row=17)
chart.add_data(data=data, titles_from_data=True)
chart.set_categories(titles)
return chart
def plot_stack(self, sheetname, column, value, unit):
df = self._df[[value, column]].pivot_table(index = "DateTime", values = value, columns = column, fill_value = 0)
df = df.resample(unit).sum()
# self._df.to_csv("a.csv", index = True, header = True, encoding='utf_8_sig')
with pd.ExcelWriter(self._file) as writer:
# excelファイルがあるなら追加書きする
if os.path.isfile(self._file):
writer.book = load_workbook(self._file)
# write the df on excel at once
df.to_excel(writer, sheet_name = sheetname)
# plot Area chart
chart = BarChart()
chart.type = "col"
chart.grouping = "stacked"
chart.overlap = 100
self._plot(writer.book[sheetname], chart, title = "積み上げ棒グラフ", xlabel = "月", ylabel = "回数")
def insertCPIOMOIchartToCPMISheet(sheet_obj, symbol, finalOptionExpDate,
num_of_exp, final_OTMOI_chart_insert_pos,
iotmio_data_col_loc, getNumberColsData,
iotmio_row_loc):
if (Commonapi.debug == 1):
print("insertCPIOMOIchartToCPMISheet started")
for i in range(0, num_of_exp):
min_rows = (int)(iotmio_row_loc[1])
min_cols = (int)(iotmio_data_col_loc[i])
max_cols = min_cols + 3
max_rows = min_rows + getNumberColsData
chart = BarChart()
chart.type = "col"
chart.grouping = "stacked"
chart.overlap = 100
chart.title = symbol + '_' + finalOptionExpDate[i]
chart.y_axis.title = final_OTM_chart_y_axis[0]
chart.x_axis.title = 'Dates'
data = Reference(sheet_obj,
min_col=min_cols,
min_row=min_rows,
max_row=max_rows,
max_col=max_cols)
cats = Reference(sheet_obj,
min_col=1,
min_row=min_rows + 1,
max_row=max_rows)
chart.add_data(data, titles_from_data=True)
chart.dataLabels = DataLabelList()
chart.dataLabels.showVal = True
chart.set_categories(cats)
sheet_obj.add_chart(chart, final_OTMOI_chart_insert_pos[i])
title = symbol + '_' + finalOptionExpDate[i]
print("Bar chart :", title)
if (Commonapi.debug == 1):
print("insertCPIOMOIchartToCPMISheet ended")
cats = Reference(wsheet,
min_col=wsheet.min_column,
min_row=wsheet.min_row + 1,
max_row=wsheet.max_row)
chart3.add_data(data, titles_from_data=True)
chart3.set_categories(cats)
wsheet.add_chart(chart3, "C2")
wsheet = wbook["By區和時段"]
chart4 = BarChart()
chart4.styles = range(0, len(idx3) + 1)
chart4.height = 14
chart4.width = 22
chart4.title = "計數-區和時段"
chart4.type = "col"
chart4.grouping = "percentStacked" #百分比堆疊圖
chart4.overlap = 100
data = Reference(wsheet,
min_col=wsheet.min_column + 1,
max_col=wsheet.max_column,
min_row=wsheet.min_row,
max_row=wsheet.max_row)
cats = Reference(wsheet,
min_col=wsheet.min_column,
min_row=wsheet.min_row + 1,
max_row=wsheet.max_row)
chart4.add_data(data, titles_from_data=True)
chart4.set_categories(cats)
wsheet.add_chart(chart4, "B14")
wbook.save(fileName)
path = os.getcwd() + "\\" + fileName
ws8.cell(row=v_offset + col_num2,
column=cx8 + 1).value = round(
(100 * rfu) / minus_2_rfu_c[col_num2], 1)
cx8 = cx8 + 1
# calculate placement of data - link to chart
ws8.column_dimensions['A'].width = 50
chart1 = BarChart()
chart1.type = "col"
chart1.style = 2
chart1.width = 0.7 * sites
chart1.height = 15
chart1.gapWidth = 10
chart1.overlap = 100
chart1.grouping = "percentStacked"
chart1.legend = None
# chart1.title = 'Percent Stacked Chart'
# chart1.y_axis.title = 'Percentage'
# chart1.x_axis.title = 'Sample Sites'
# chart1.varyColors = True
# chart1.shape = 4
site_list = Reference(ws8, min_col=1, min_row=35, max_row=35 + sites)
data = Reference(ws8, min_col=1, min_row=34, max_row=34 + sites, max_col=cx8)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(site_list)
#chart1.add_data(data)
ws8.add_chart(chart1, "A1")
def __init__(self,
filename,
sheet_name=[],
chart_title=[],
xaxis_title=[],
yaxis_title=[],
stack=[],
chart_position="F10",
whiten_all_area=True,
font_name='Calibri',
axis_title_font_size=10,
output_filename=None):
wb = load_workbook(filename)
counts = len(sheet_name)
wsDict = {}
for count in range(counts):
wsDict['ws' + str(count)] = wb[sheet_name[count]]
chart = BarChart()
chart.style = 10
chart.type = "col"
if stack[count] == True:
chart.grouping = "stacked"
chart.overlap = 100
chart.title = chart_title[count]
chart.y_axis.title = xaxis_title[count]
chart.x_axis.title = yaxis_title[count]
data = Reference(wsDict['ws' + str(count)],
min_col=wsDict['ws' + str(count)].min_column + 1,
min_row=wsDict['ws' + str(count)].min_row,
max_row=wsDict['ws' + str(count)].max_row,
max_col=wsDict['ws' + str(count)].max_column)
cats = Reference(wsDict['ws' + str(count)],
min_col=wsDict['ws' + str(count)].min_column,
min_row=wsDict['ws' + str(count)].min_row + 1,
max_row=wsDict['ws' + str(count)].max_row,
max_col=wsDict['ws' + str(count)].min_column)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
## set axis title property??
font_test = draw_Font(typeface=font_name)
cp = CharacterProperties(latin=font_test,
sz=axis_title_font_size * 100)
chart.x_axis.txPr = RichText(p=[
Paragraph(pPr=ParagraphProperties(defRPr=cp), endParaRPr=cp)
])
chart.y_axis.txPr = RichText(p=[
Paragraph(pPr=ParagraphProperties(defRPr=cp), endParaRPr=cp)
])
xPara = [
Paragraph(pPr=ParagraphProperties(defRPr=cp),
r=RegularTextRun(t=s))
for s in xaxis_title[count].split("\n")
]
yPara = [
Paragraph(pPr=ParagraphProperties(defRPr=cp),
r=RegularTextRun(t=s))
for s in yaxis_title[count].split("\n")
]
chart.x_axis.title.tx.rich.paragraphs = xPara
chart.y_axis.title.tx.rich.paragraphs = yPara
## hide legend
if not stack[count]:
chart.legend = None
wsDict['ws' + str(count)].add_chart(chart, chart_position)
## whiten data
if whiten_all_area:
table = wsDict['ws' + str(count)]["A1":"AZ200"]
color = 'FFFFFFFF'
font = Font(bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color=color)
fill = PatternFill(start_color=color,
end_color=color,
fill_type="solid")
for tr in table:
for td in tr:
td.font = font
td.border = None
td.fill = fill
### data label
chart.dataLabels = DataLabelList()
chart.dataLabels.showVal = True
if output_filename is None:
wb.save(filename=filename)
else:
wb.save(filename=output_filename)
#
# barchart_creater(filename = 'temp0.xlsx',sheet_name=["Issue Count by Due Date","Issue Count By Owner","MTM Issue Comparison"],
# chart_title=["Number of Issues By Due Date","Number of outstanding Issues by Owner","Number of Issues comparison month to month by Source"],
# xaxis_title = ["Number of Issues",'Issue Owner','Month'],
# yaxis_title=['Number of Days till Due','Number of Issues','Number Of Issues'],stack = [False,False,True])
def sheet_partecipazione(filename, start_time, participants, workbook):
worksheet = workbook.create_sheet('Partecipazione')
titles = ['Partecipante']
max_interruptions = 0
for pp in participants:
if len(pp['periods']) > max_interruptions:
max_interruptions = len(pp['periods'])
for i in range(max_interruptions):
titles.append(f'Inizio {i+1}')
titles.append(f'Fine {i+1}')
worksheet.append(titles)
for cell in worksheet["1:1"]:
cell.font = Font(bold=True)
i = 1
for pp in participants:
i = i + 1
cur_start = start_time
row = [pp['name']]
for j in range(len(pp['periods'])):
p = pp['periods'][j]
row.append(p[0] - cur_start)
row.append(p[1] - p[0])
cur_start = p[1]
worksheet.append(row)
for ii in range(1, len(row) + 1):
cell = worksheet.cell(i, ii)
cell.number_format = 'hh "ore e" mm "minuti"'
column_widths = [30]
for _i in range(max_interruptions * 2):
column_widths.append(20)
for ii, column_width in enumerate(column_widths):
worksheet.column_dimensions[get_column_letter(ii +
1)].width = column_width
max_col = get_column_letter((max_interruptions * 2) + 1)
mediumStyle = TableStyleInfo(name='TableStyleMedium2', showRowStripes=True)
worksheet.add_table(
Table(ref=f'A1:{max_col}{i}',
displayName='RegistroPartecipazione',
tableStyleInfo=mediumStyle))
worksheet.sheet_view.showGridLines = False
report_id = os.path.basename(filename).replace('.json', '')
worksheet.append([f''])
worksheet.append([f'Report generato per il meeting con ID: {report_id}'])
data = Reference(worksheet,
min_col=2,
min_row=1,
max_row=i,
max_col=(max_interruptions * 2) + 1)
cats = Reference(worksheet, min_col=1, min_row=2, max_row=i)
chart = BarChart()
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
chart.type = "bar"
chart.grouping = "stacked"
chart.overlap = 100
chart.legend = None
chart.height = 20
chart.width = 40
chart.x_axis.scaling.orientation = "maxMin"
for ii, s in enumerate(chart.series):
s.graphicalProperties.line.nofill = True
s.graphicalProperties.line.shadow = None
if ii % 2 == 0:
s.graphicalProperties.noFill = True
else:
s.graphicalProperties.solidFill = "008F00"
worksheet.add_chart(chart, "A43")
import pandas as pd
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
wb = Workbook()
ws = wb.active
df = pd.read_csv('store_sales.csv')
ws.append(df.columns.tolist())
for row in df.values:
ws.append(list(row))
row_length = 1 + len(df.values)
data = Reference(ws, min_col=2, max_col=4, min_row=1, max_row=row_length)
categories = Reference(ws, min_col=1, min_row=2, max_row=row_length)
chart = BarChart()
chart.type = 'col'
chart.shape = 4
chart.grouping = 'stacked' #←積み上げ棒グラフを表すstackedを指定
chart.overlap = 100 #←積み上げ棒グラフのときは100固定
chart.title = '支店別売り上げ (積み上げ)'
chart.x_axis.title = '支店'
chart.y_axis.title = '売上'
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
ws.add_chart(chart, 'A9')
wb.save('store_sales_vertical_stacked.xlsx')
def writeOutput2(outfile,records): #################################### wb = Workbook() summaryColumn = 3 for record in records: sheetname = str(label[record]) wb.create_sheet(sheetname) ws = wb[str(sheetname)] wsummary = wb.worksheets[0] # write labels ws.cell(row=1, column=1).value = "code" ws.cell(row=1, column=2).value = "name" columnVar = 1 rowVar = 2 for x in sorted(major): ws.cell(row=rowVar, column=columnVar).value = x ws.cell(row=rowVar, column=columnVar+1).value = label[x] rowVar += 1 columnVar = 3 rowVar = 2 for r in records[record]: # r is column ws.cell(row=1, column=columnVar).value = label[r] for x in xrange(0,len(major)): code = str(ws.cell(row=rowVar+x, column=1).value) if code in records[record][r]: ws.cell(row=rowVar+x, column=columnVar).value = records[record][r][code] if isinstance(wsummary.cell(row=rowVar+x, column=summaryColumn).value,float): wsummary.cell(row=rowVar+x, column=summaryColumn).value += float(records[record][r][code]) else: wsummary.cell(row=rowVar+x, column=summaryColumn).value = float(records[record][r][code]) columnVar += 1 for col in ws.columns: max_length = 0 column = col[0].column # Get the column name for cell in col: try: # Necessary to avoid error on empty cells if len(str(cell.value)) > max_length: max_length = len(cell.value) except: pass adjusted_width = (max_length) * 1 ws.column_dimensions[column].width = adjusted_width summaryColumn+=1 data = Reference(ws, min_col=3, min_row=1, max_col=columnVar-1, max_row=6) titles = Reference(ws, min_col=2, min_row=2, max_row=6) chart = BarChart() chart.title = label[record] chart.add_data(data=data, titles_from_data=True) chart.set_categories(titles) chart.x_axis.delete = False chart.y_axis.delete = False ws.add_chart(chart, "A10") #################################### # write values ws = wb.worksheets[0] ws.title="summary" columnVar = 1 rowVar = 2 # write labels ws.cell(row=1, column=1).value = "code" ws.cell(row=1, column=2).value = "name" for x in sorted(major): ws.cell(row=rowVar, column=columnVar).value = x ws.cell(row=rowVar, column=columnVar+1).value = label[x] rowVar +=1 # make multiple outputs per project and by per employee columnVar = 1 rowVar = 1 for record in records: ws.cell(row=rowVar, column=columnVar+2).value = label[record] columnVar+=1 for col in ws.columns: max_length = 0 column = col[0].column # Get the column name for cell in col: try: # Necessary to avoid error on empty cells if len(str(cell.value)) > max_length: max_length = len(cell.value) except: pass adjusted_width = (max_length) * 1 ws.column_dimensions[column].width = adjusted_width print outfile, columnVar data = Reference(ws, min_col=3, min_row=1, max_col=columnVar+1, max_row=6) titles = Reference(ws, min_col=2, min_row=2, max_row=6) chart = BarChart3D() chart.title = "3D Bar Chart" chart.add_data(data=data, titles_from_data=True) chart.set_categories(titles) ws.add_chart(chart, "A10") chart3 = BarChart() chart3 = deepcopy(chart) chart3.type = "col" chart3.style = 10 chart3.grouping = "stacked" chart3.overlap = 100 chart3.title = 'Stacked Chart' chart3.set_categories(titles) ws.add_chart(chart3, "A30") chart.title.delete = False chart.x_axis.delete = False chart.y_axis.delete = False chart3.title.delete = False chart3.x_axis.delete = False chart3.y_axis.delete = False wb.save(year+"_"+month+"_"+outfile)
def enregistre_resultat_resume(recap, ligne, colonne, feuille, nb_postes):
styleFond, styleTeteTab, styleVal, styleTableau, styleEntree, styleTitre = p.STYLES_EXCEL
feuille.cell(row=ligne, column=1+colonne).value ="Regroupement de résultats, les postes principaux"
feuille.merge_cells('B'+str(ligne)+':L'+str(ligne))
feuille.cell(row=ligne, column=1+colonne).style = styleEntree
feuille.cell(row=ligne, column=1+colonne).font = Font(bold=True)
ligne += 2
for li in range(len(recap)):
for co in range(len(recap[0])):
feuille.cell(row=ligne, column=1+colonne+co).value = recap[li][co]
if li == 0:
feuille.cell(row=ligne, column=1+colonne+co).style = styleTeteTab
elif co<1:
feuille.cell(row=ligne, column=1+colonne+co).style = styleEntree
else:
feuille.cell(row=ligne, column=1+colonne+co).style = styleVal
feuille.cell(row = ligne, column = 1+colonne+co).number_format = '0.0'
ligne+=1
feuille.cell(row=ligne, column=2).value = "EGES tot dues à la tourbe"
for c in range(3, 11):
feuille.cell(row=ligne, column=c).style = styleVal
feuille.cell(row=ligne, column=8).number_format = '#.0'
feuille.cell(row=ligne, column=2).style = styleEntree
feuille.cell(row=ligne, column=3).value = p.COMPARAISON_TOURBE[0]
feuille.cell(row=ligne, column=3).number_format = '#.0'
feuille.cell(row=ligne, column=6).value = p.COMPARAISON_TOURBE[1]
feuille.cell(row=ligne, column=6).number_format = '#.0'
feuille.cell(row=ligne, column=8).value = p.COMPARAISON_TOURBE[2]
feuille.cell(row=ligne, column=8).number_format = '#.0'
feuille.cell(row=ligne, column=10).value = "=SUM(C"+str(ligne)+":I"+str(ligne)+")"
feuille.cell(row=ligne, column=10).number_format = '#.0'
ligne +=1
for li in range(1,len(recap)-1):
for co in range(len(recap[0])):
if co<1:
feuille.cell(row=ligne, column=1+colonne+co).value = recap[li][co]+ " (%)"
feuille.cell(row=ligne, column=1+colonne+co).style = styleEntree
elif co <=len(recap[0])-1 and li ==1:
feuille.cell(row=ligne, column=1+colonne+co).value = recap[li][co]/recap[1][-1]
feuille.cell(row=ligne, column=1+colonne+co).style = styleVal
feuille.cell(row=ligne, column=1+colonne+co).number_format = '0.0%'
elif co <=len(recap[0])-1 and li ==2:
feuille.cell(row=ligne, column=1+colonne+co).value = recap[li][co]/recap[2][-1]
feuille.cell(row=ligne, column=1+colonne+co).style = styleVal
feuille.cell(row=ligne, column=1+colonne+co).number_format = '0.0%'
ligne+=1
offset3 = 20+(2*nb_postes)
sites = Reference(feuille, min_row = offset3, min_col = 2+colonne, max_col = len(recap[0])+colonne-1)
graphique3 = BarChart()
graphique3.title = "Emissions totales des sites par poste"
graphique3.type = "col";graphique3.style= 10;graphique3.grouping='standard';graphique3.overlap = 25
graphique3.height = 12;graphique3.width = 25
graphique3.x_axis.scaling.orientation = "maxMin"
cp = CharacterProperties(sz=1000) #Taille du label entre 100 et 400 000
graphique3.x_axis.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp), endParaRPr=cp)])
graphique3.varyColors=True
valeurs3 = Reference(feuille, min_row=1+offset3, min_col=1+colonne,
max_col=len(recap[0])+colonne-1, max_row =1+offset3 )
graphique3.y_axis.crosses = "max"
graphique3.add_data(valeurs3, titles_from_data=True, from_rows=True)
graphique3.set_categories(sites)
graphique3.y_axis.title = 'Emissions Carbones globales (tCO2e)'
# graphique3.layout=Layout(manualLayout=ManualLayout(x=0.1, y=0.050,h=0.80, w=0.90,))
graphique3.legend.layout = Layout(manualLayout=ManualLayout(yMode='edge',xMode='edge',x=0, y=0.9,h=0.1, w=0.5))
feuille.add_chart(graphique3, "B"+str(offset3+7))
tarte = PieChart()
labels = Reference(feuille, min_row = offset3, min_col = 2+colonne, max_col = len(recap[0])+colonne-1)
for i in [0]:
data = Reference(feuille, min_row=offset3+1+i, min_col=1+colonne, max_col=len(recap[0])+colonne-1)
tarte.add_data(data, titles_from_data=True, from_rows=True)
tarte.set_categories(labels)
tarte.dataLabels = DataLabelList()
tarte.dataLabels.position ='bestFit'
tarte.dataLabels.showCat = True
tarte.dataLabels.showPercent = True
tarte.dataLabels.separator = ":"
tarte.height = 8.15;tarte.width = 9
tarte.title = "Part des postes dans les émissions totales"
set_chart_title_size(tarte, size=1200)
feuille.add_chart(tarte, "M"+str(offset3+16))
return ligne
def sauveTout(nom:str, recap:list):
try:
log_fret = openpyxl.load_workbook(p.CHEMIN_ECRITURE_RESULTATS+nom)
except FileNotFoundError:
fonctionsMatrices.print_log_erreur("Le document résultat n'est pas trouvé à l'emplacement "+p.CHEMIN_ECRITURE_RESULTATS+nom, inspect.stack()[0][3])
sys.exit("Le document résultat n'est pas trouvé à l'emplacement "+ p.CHEMIN_ECRITURE_RESULTATS+nom)
feuille = log_fret.get_sheet_by_name(log_fret.get_sheet_names()[0])
styleFond, styleTeteTab, styleVal, styleTableau, styleEntree, styleTitre = p.STYLES_EXCEL
alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
tableau = [[u[0], u[1][1], u[2][0], u[2][1], u[2][2], u[2][3]]for u in p.MATRICE_RESULTAT]
for li in range(1,len(tableau[1:])+1):
for col in range(len(tableau[li])):
if col>0:
feuille.cell(row = li+10, column = col+2).value=tableau[li][col]
else:
feuille.cell(row = li+10, column = col+2).value=tableau[li][col]+" - "+tableau[li][col+1]
if col<=1:
feuille.cell(row=li+10, column=col+2).style = styleEntree
else:
feuille.cell(row=li+10, column=col+2).style = styleVal
if col in [2,4]:
feuille.cell(row = li+10, column = col+2).number_format = '0.0'
tab1 = Table(displayName="Tableau_general", ref="A2:F"+str(len(p.MATRICE_RESULTAT)))
style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False,
showLastColumn=False, showRowStripes=True, showColumnStripes=True)
tab1.tableStyleInfo = style
# rule = DataBarRule(start_type='num', start_value=0, end_type='percentile', end_value='100',color="FF638EC6", showValue="None", minLength=None, maxLength=None)
# feuille.conditional_formatting.add("C2:C"+str(len(p.MATRICE_RESULTAT)), rule)
feuille.column_dimensions['A'].width = 10
feuille.column_dimensions['B'].width = 17
feuille.column_dimensions['C'].width = 0
feuille.column_dimensions['D'].width = 25
feuille.column_dimensions['E'].width = 6
feuille.column_dimensions['F'].width = 25
feuille.column_dimensions['G'].width = 10
#Affichage du premier graphe général (avec tous les postes)
if True:
graphique = BarChart()
graphique.type = "bar"
graphique.style = 12
graphique.title = "Bilan carbone total par poste et par site"
graphique.grouping = "standard"
graphique.overlap = 00
position_row = 9
position_col = 1
postes = Reference(feuille, min_col = position_col+1, min_row = position_row+2, max_row = position_row+len(p.MATRICE_RESULTAT)-1)
for i in [3]:
valeurs = Reference(feuille, min_col=position_col+i, min_row=position_row+1, max_row=position_row+len(p.MATRICE_RESULTAT)-1)
graphique.add_data(valeurs, titles_from_data=True)
graphique.set_categories(postes)
graphique.height = 103
graphique.width = 20
secaxe = BarChart()
valsecond = Reference(feuille, min_col=position_col+i+2, min_row=position_row+1, max_row=position_row+len(p.MATRICE_RESULTAT)-1)
secaxe.add_data(valsecond, titles_from_data=True)
secaxe.y_axis.axId = 200
graphique.x_axis.scaling.orientation = "maxMin"
secaxe.type = "bar"
secaxe.y_axis.title = "Ramenées à la production (kgeCO2/m3)"
cp = CharacterProperties(sz=800) #Taille du label entre 100 et 400 000
graphique.x_axis.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp), endParaRPr=cp)])
graphique.legend.legendPos = 't'
graphique.legend.tagname= 'Légende'
graphique.y_axis.title = 'Emissions Carbones (teCO2)'
graphique.y_axis.crosses = "min"
serie = secaxe.series[0]
serie.graphicalProperties.line.solidFill = "FF0000"
serie.graphicalProperties.noFill = True
graphique += secaxe
feuille.add_chart(graphique, "I10")
matRes_inv_tot,matRes_inv_m3 = renversermatres(p.MATRICE_RESULTAT)
nb_postes = len(matRes_inv_tot)-1
nb_sites = len(matRes_inv_tot[0])-1
# Changement de feuille du classeur
feuille = log_fret.create_sheet("Resultats detailles",1)
for lin in range(1,200):
for col in [x for x in "ABCDEFGHIJKLMNOPQRSTUVWXYZ"]:
case = col+str(lin)
feuille[case].style=styleFond
for col in [x+y for x in "ABCDEFGHIJKLMNOPQRSTUVWXYZ" for y in "ABCDEFGHIJKLMNOPQRSTUVWXYZ"]:
case = col+str(lin)
feuille[case].style=styleFond
elif j in [3,4,5,6,7,8,9,10,11,12]:
feuilleFret.cell(row=i+2, column=j+1).number_format = '0.0'
emphase_colonne_n(feuilleFret, 15)
emphase_colonne_n(feuilleFret, 16)
emphase_colonne_n(feuilleFret, 19)
emphase_colonne_n(feuilleFret, 20)
#Affichage de graphe
if True:
#On crée un graphique en barre
graphique = BarChart()
#Style horizontal
graphique.type = "bar"
graphique.style = 12 #whatever
graphique.title = "FE des matières premières, fabrication + fret moyen "+str(p.ANNEE)+" (tCO2e/t)"
graphique.grouping = "stacked"
#100% de recouvrement pour avoir la seconde catégorie (axe2) par dessus
graphique.overlap = 100
#On prend en abscisses la première colonne
mp = Reference(feuilleFret, min_col = 1, min_row = 2, max_row = len(fret_FE))
#Les ordonnées sont en colonnes 10 11 13
for i in [11,12,14,18,19]:
#On ajoute une à une les séries
valeurs = Reference(feuilleFret, min_col=i, min_row=1, max_row=len(fret_FE))
graphique.add_data(valeurs, titles_from_data=True)
graphique.set_categories(mp)
#Dimensions du graphique
graphique.height = 103
graphique.width = 20
graphique.x_axis.scaling.orientation = "maxMin"
def format_output_file(chart_records):
read_wb = load_workbook(filename=TEST_RESULTS)
logging.info("Compile graphs and reports=> %s", read_wb)
total_sheets = len(read_wb.sheetnames)
# Css Style
thin = Side(border_style="thin", color="000000")
fontsize = Font(size=EXCELSHEET_FONT_SIZE)
for sheet in range(total_sheets):
read_wb.active = sheet
ws = read_wb.active
sheetRange = ws.calculate_dimension()
# set sheet active with index
for col in ws.iter_cols(min_row=1,
max_col=ws.max_column,
max_row=ws.max_row):
for cell in col:
# Apply excel styles for formating output
# Apply border on excel cells
cell.alignment = Alignment(horizontal="left",
vertical="center",
shrinkToFit=True,
wrapText=True)
cell.border = Border(top=thin,
left=thin,
right=thin,
bottom=thin)
# Apply font size on all rows of excel sheet
cell.font = fontsize
# apply Color on cells w.r.t pass or failed cases
if cell.value == PASS_CELL_VALUE:
cell.fill = PatternFill(start_color=PASSED_CELL_COLOR,
fill_type="solid")
cell.font = Font(bold=True)
elif cell.value == FAILED_CELL_VALUE:
cell.fill = PatternFill(start_color=FAILED_CELL_COLOR,
fill_type="solid")
cell.font = Font(bold=True)
# Dashboard sheet styling
sheets = read_wb.sheetnames
if 'Sheet' in sheets:
read_wb.active = sheets.index(sheets[-1])
ws = read_wb.active
# Dashboard heading style
ws.merge_cells('A1:Q2')
cell = ws['A1']
cell.value = "API Automation Test Results"
cell.fill = PatternFill(start_color='35C4F1', fill_type="solid")
cell.font = Font(bold=True, size=16)
cell.alignment = Alignment(horizontal="left", vertical="center")
# print API analysis records in table
# add column headings
#ws.append(["APINames","TotalTestCases","Executed","Skipped","Passed","Failed"])
# output example [['BalanceInquiry-POST', 3, 3, 0, 3, 0], ['TransactionInquiry-POST', 2, 2, 0, 2, 0]]
ws.cell(row=API_TABLE_STARTS_FROM_ROW, column=1).value = "APINames"
ws.cell(row=API_TABLE_STARTS_FROM_ROW,
column=2).value = "TotalTestCases"
ws.cell(row=API_TABLE_STARTS_FROM_ROW, column=3).value = "Executed"
ws.cell(row=API_TABLE_STARTS_FROM_ROW, column=4).value = "Skipped"
ws.cell(row=API_TABLE_STARTS_FROM_ROW, column=5).value = "Passed"
ws.cell(row=API_TABLE_STARTS_FROM_ROW, column=6).value = "Failed"
ws.column_dimensions['A'].width = 40
ws.column_dimensions['B'].width = 15
ws.column_dimensions['C'].width = 15
ws.column_dimensions['D'].width = 15
ws.column_dimensions['E'].width = 15
ws.column_dimensions['F'].width = 15
# Adding 1 beause API_TABLE_STARTS_FROM_ROW takes headings row,hence data will inserted from next row
rows = API_TABLE_STARTS_FROM_ROW + 1
for api_record in chart_records:
columns = 1
for val in api_record:
ws.cell(row=rows, column=columns).value = val
columns = columns + 1
rows = rows + 1
total_record = API_TABLE_STARTS_FROM_ROW + total_sheets
refs = 'A' + str(API_TABLE_STARTS_FROM_ROW) + ":F" + str(total_record)
tab = Table(displayName="Table1", ref=refs)
# Add a default style with striped rows and banded columns
style = TableStyleInfo(name="TableStyleMedium9",
showFirstColumn=False,
showLastColumn=False,
showRowStripes=True,
showColumnStripes=True)
tab.tableStyleInfo = style
ws.add_table(tab)
#Add First chart/graph
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.height = 8 # default is 7.5
chart1.width = 30 # default is 15
chart1.title = "Bar Chart"
chart1.y_axis.title = 'Total TestCases'
chart1.x_axis.title = 'API Names'
#add Category as API name
apis = Reference(ws,
min_col=1,
min_row=int(API_TABLE_STARTS_FROM_ROW + 1),
max_row=total_record)
data = Reference(ws,
min_col=3,
min_row=int(API_TABLE_STARTS_FROM_ROW),
max_row=total_record,
max_col=4)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(apis)
chart1.shape = 4
chart1.title = 'API TestCase Stats'
ws.add_chart(chart1, "A4")
#Second chart
chart2 = BarChart()
chart2.type = "col"
chart2.style = 13
chart2.height = 8 # default is 7.5
chart2.width = 30 # default is 15
chart2.grouping = "percentStacked"
chart2.title = "API Success Stats"
chart2.y_axis.title = 'Success Rate'
chart2.overlap = 100
#pick date from cell locations
v2 = Reference(ws,
min_col=5,
min_row=int(API_TABLE_STARTS_FROM_ROW),
max_col=6,
max_row=total_record)
chart2.add_data(v2, titles_from_data=True)
chart2.set_categories(apis)
# Style the lines
s1 = chart2.series[0]
s1.graphicalProperties.solidFill = "90EE90" # green
s2 = chart2.series[1]
s2.graphicalProperties.solidFill = "FF3300" # red
#Add Chart at locton
ws.add_chart(chart2, "A20")
# save formated file
read_wb.save(TEST_RESULTS)
def exportXL(currentGrades, grades, classes, XLName):
for i in range(len(grades)):
grades[i] = grades[i].tolist()
# tries to convert everything to floats
for i in range(len(grades)):
for j in range(len(grades[i])):
for k in range(len(grades[i][j])):
try:
grades[i][j][k] = float(grades[i][j][k])
except:
pass
# finds the number of actual classes
numClasses = 0
for i in range(len(classes)):
try:
if classes[i][0]:
numClasses += 1
except:
pass
# sorts by the category
if sortCategory:
for i in grades:
try:
i.sort(key=lambda x: x[catIndex])
except:
pass
aIndex = ord('A')
book = ''
try:
book = openpyxl.load_workbook(filename=XLName)
for i in range(numClasses):
sheet = book[classes[i][0]]
except:
print('Book not found')
book = openpyxl.Workbook()
for i in range(numClasses):
# makes a new sheet for each class
if i == 0:
sheet = book.active
sheet.title = classes[0][0]
else:
sheet = book.create_sheet(classes[i][0])
print('New book generated...')
ftBold = Font(name='Roboto', bold=True)
ftReg = Font(name='Roboto', bold=False)
alCenter = Alignment(horizontal='center', vertical='center')
alRight = Alignment(horizontal='right', vertical='center')
alLeft = Alignment(horizontal='left', vertical='center')
# for each class a different sheet
for i in range(numClasses):
sheet = book[classes[i][0]]
# creates column titles for assignments
colTitles = ['Date',
'Category',
'Assignment Name',
'Earned',
'Possible',
'Percentage',
'Grade',
'Comments']
for j in range(len(colTitles)):
cell = sheet[chr(aIndex + j) + str(1)]
cell.value = colTitles[j]
cell.font = ftBold
cell.alignment = alCenter
# makes a new row for each assignment
for assNum in range(len(grades[i])):
for col in range(len(grades[i][assNum])):
cell = sheet[chr(aIndex + col) + str(2 + assNum)]
cell.value = grades[i][assNum][col]
cell.font = ftReg
if col == catIndex + 1:
cell.alignment = alLeft
else:
cell.alignment = alCenter
# gets the category scores and gets them out
categories, num, den = classAnalysis(grades[i], classes[i][0])
rowTitles = ['Category Name',
'Points Earned',
'Possible Points',
'Category Percentage',
'Category Weight',
'1 Pt is worth']
maxNumCols = len(grades[i][assNum])
tableOffset = 2
# prints the row titles for category printing
for row in range(len(rowTitles)):
cell = sheet[chr(aIndex + maxNumCols + tableOffset - 1) + str(row + 1)]
cell.value = rowTitles[row]
cell.font = ftBold
cell.alignment = alRight
s = '=round('
for col in range(len(categories)):
colIndex = aIndex + col + maxNumCols + tableOffset
# print the category names
cell = sheet[chr(colIndex) + str(1)]
assignCell(cell, categories[col], ftBold, alCenter)
# print points earned
cell = sheet[chr(colIndex) + str(2)]
assignCell(cell, num[col], ftReg, alCenter)
# print points possible
cell = sheet[chr(colIndex) + str(3)]
assignCell(cell, den[col], ftReg, alCenter)
# print category percentage
cell = sheet[chr(colIndex) + str(4)]
assignCell(cell, '=round(' + chr(colIndex) + '2/' + chr(colIndex) + '3,3) * 100', ftReg,alCenter)
# print percentage per point
cell = sheet[chr(colIndex) + str(6)]
assignCell(cell, '=round( 1 / (' + chr(colIndex) + '3+5)*' + chr(colIndex) + '5,3)',ftReg,alCenter)
# adds the category percentage * category weight
s += chr(colIndex) + '4 * ' + chr(colIndex) + '5 + '
# prints label for grade
cell = sheet[chr(aIndex + len(categories) + maxNumCols + tableOffset - 2) + str(8)]
assignCell(cell, 'Grades:', ftBold, alCenter)
# prints grade
cell = sheet[chr(aIndex + len(categories) + maxNumCols + tableOffset - 1) + str(8)]
assignCell(cell, s[:-3] + ',3) / 100', ftBold, alCenter)
cell = sheet[chr(aIndex + len(categories) + maxNumCols + tableOffset - 2) + str(9)]
assignCell(cell, 'WebGrades:', ftBold, alCenter)
# prints grade
cell = sheet[chr(aIndex + len(categories) + maxNumCols + tableOffset - 1) + str(9)]
assignCell(cell, str(currentGrades[i]) + '%', ftBold, alCenter)
chart = BarChart()
chart.type = "col"
chart.style = 2
chart.title = "Category Percentage"
chart.x_axis.title = 'Categories'
chart.y_axis.title = 'Percent (%)'
chart.x_axis.delete = True
chart.y_axis.scaling.min = 50
chart.y_axis.scaling.max = 101
start = maxNumCols + tableOffset + 1
end = start + len(categories) - 1
cats = Reference(sheet, min_col=start, max_col=end, min_row=1, max_row=1)
for j in range(start, end + 1):
data = Reference(sheet, min_col=j, min_row=4, max_row=4)
series = Series(data, title=categories[j - start])
chart.series.append(series)
chart.grouping = 'clustered'
sheet.add_chart(chart, 'K15')
isOpen = True
while(isOpen):
try:
book.save(XLName)
isOpen = False
except:
# os.system('taskkill /F /IM excel.exe')
print('Close Excel Spreadsheet!')
time.sleep(0.8)
print(XLName + ' updated.')
input('Press [Enter] to open Excel File\n')
os.system(XLName)
import pandas as pd
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
wb = Workbook()
ws = wb.active
df = pd.read_csv('store_sales.csv')
ws.append(df.columns.tolist())
for row in df.values:
ws.append(list(row))
row_length = 1 + len(df.values)
data = Reference(ws, min_col=2, max_col=4, min_row=1, max_row=row_length)
categories = Reference(ws, min_col=1, min_row=2, max_row=row_length)
chart = BarChart()
chart.type = 'col'
chart.shape = 4
chart.grouping = 'percentStacked' #←100%積み上げ棒グラフを表すpercentStackedを指定
chart.overlap = 100
chart.title = '支店別売り上げ (100%積み上げ)'
chart.x_axis.title = '支店'
chart.y_axis.title = '売上'
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
ws.add_chart(chart, 'A9')
wb.save('store_sales_vertical_percent_stacked.xlsx')
import openpyxl
from openpyxl.chart import BarChart, Reference
wb = openpyxl.load_workbook("..\data\column_chart_stacked.xlsx")
sh = wb.active
data = Reference(sh, min_col=3, max_col=7, min_row=1, max_row=sh.max_row)
labels = Reference(sh, min_col=2, max_col=2, min_row=2, max_row=sh.max_row)
chart = BarChart()
chart.type = "col"
chart.grouping = "stacked"
#chart.grouping = "percentStacked"
chart.overlap = 100
chart.title = "分類別売上(サイズ積上げ)"
chart.x_axis.title = "分類"
chart.y_axis.title = "サイズ"
chart.add_data(data, titles_from_data=True)
chart.set_categories(labels)
sh.add_chart(chart, "I2")
wb.save("..\data\column_chart_stacked.xlsx")
