def main():
book = Workbook()
sheet = book.active
rows = [("USA", 46), ("UK", 27), ("China", 26), ("Russia", 19),
("Germany", 17), ("Japan", 12)]
for row in rows:
sheet.append(row)
data = Reference(sheet, min_row=1, min_col=2, max_row=6, max_col=2)
categs = Reference(sheet, min_row=1, min_col=1, max_row=6)
chart = BarChart()
chart.add_data(data=data)
chart.set_categories(categs)
chart.legend = None
chart.y_axis.majorGridlines = None
chart.varyColors = True
chart.title = 'Olympic Gold medals in Rio 2016'
sheet.add_chart(chart, 'A8')
book.save('bar_chart.xlsx')
def sample_output_excel_chart():
# Sample from http://zetcode.com/articles/openpyxl/
book = Workbook()
sheet = book.active
rows = [
("USA", 46),
("China", 38),
("UK", 29),
("Russia", 22),
("South Korea", 13),
("Germany", 11)
]
for row in rows:
sheet.append(row)
data = Reference(sheet, min_col=2, min_row=1, max_col=2, max_row=6)
categs = Reference(sheet, min_col=1, min_row=1, max_row=6)
chart = BarChart()
chart.add_data(data=data)
chart.set_categories(categs)
chart.legend = None
chart.y_axis.majorGridlines = None
chart.varyColors = True
chart.title = "Olympic Gold medals in London"
sheet.add_chart(chart, "A8")
book.save("bar_chart.xlsx")
sheet.title = "Chart"
sheet3 = book.worksheets[2]
rows = [
['김일수', 55],
['김이수', 11],
['김삼수', 33],
['김사수', 15],
['김오수', 11],
]
for row in rows:
sheet.append(row)
# y축
data = Reference(sheet, min_col=2, min_row=1, max_col=2, max_row=5)
# x축
categs = Reference(sheet, min_col=1, min_row=1, max_row=5)
chart = BarChart()
chart.add_data(data=data)
chart.set_categories(categs)
chart.legend = None # 범례
chart.varyColors = True
chart.title = "Sample Chart"
sheet.add_chart(chart, "A1")
book.save("./melon_top_xls.xlsx")
book.save("./data/meltop100.xlsx")
# Trythis 3-1번
book = openpyxl.load_workbook("./data/meltop100.xlsx")
sheet3 = book.create_sheet()
sheet3.title = "차트출력"
datax = Reference(sheet1, min_col=4, min_row=2, max_col=4, max_row=11)
categs = Reference(sheet1, min_col=2, min_row=2, max_row=11)
chart1 = BarChart()
chart1.add_data(datax)
chart1.set_categories(categs)
chart1.legend = None # 범례
chart1.varyColors = True
chart1.title = "상위 10위 좋아요수"
sheet3.add_chart(chart1, "A8")
# Trythis 3-2번
chart2 = ScatterChart()
chart2.style = 13
datax = Reference(sheet1, min_col=1, min_row=2, max_row=11)
value = Reference(sheet1, min_col=5, min_row=1, max_row=11)
series = Series(value, datax, title_from_data=True)
chart2.series.append(series)
categs = Reference(sheet1, min_col=2, min_row=2, max_row=11)
# chart1.add_data(datax)
chart2.set_categories(categs)
#!/usr/bin/env python3
from openpyxl import Workbook
from openpyxl.chart import Reference, Series, BarChart
book = Workbook()
sheet = book.active
rows = [("USA", 46), ("China", 38), ("UK", 29), ("Russia", 22),
("South Korea", 13), ("Germany", 11)]
for row in rows:
sheet.append(row)
data = Reference(sheet, min_col=2, min_row=1, max_col=2, max_row=6)
categs = Reference(sheet, min_col=1, min_row=1, max_row=6)
chart = BarChart()
chart.add_data(data=data)
chart.set_categories(categs)
chart.legend = None
chart.y_axis.majorGridlines = None
chart.varyColors = True # Setting varyColors to True, each bar has a different colour.
chart.title = "Olympic Gold medals in London"
sheet.add_chart(chart, "A8")
book.save('barChart.xlsx')
print('Done.')
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
elif head<1:
feuille.cell(row=ligne, column=head+1+colonne).style = styleEntree
else:
feuille.cell(row=ligne, column=head+1+colonne).style = styleVal
feuille.cell(row = ligne, column = head+1+colonne).number_format = '0.0'
ligne +=1
ligne += 2
sites = Reference(feuille, min_row = 11, min_col = 2+colonne, max_col = nb_sites+1+colonne)
graphique1 = BarChart()
graphique1.title = "Emissions totales des sites par poste"
graphique1.type = "bar";graphique1.style= 10;graphique1.grouping = "stacked";graphique1.overlap = 100
graphique1.height = 15;graphique1.width = 25
graphique1.x_axis.scaling.orientation = "maxMin"
cp = CharacterProperties(sz=1000) #Taille du label entre 100 et 400 000
graphique1.x_axis.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp), endParaRPr=cp)])
graphique1.varyColors=True
valeurs1 = Reference(feuille, min_row=12, min_col=1+colonne, max_col=nb_sites+1+colonne, max_row = nb_postes+11)
graphique1.add_data(valeurs1, titles_from_data=True, from_rows=True)
graphique1.set_categories(sites)
graphique1.y_axis.title = 'Emissions Carbones (tCO2e)'
feuille.add_chart(graphique1, "O7")
offsetg2 = nb_postes+4
graphique2 = BarChart()
graphique2.title = "Emissions totales des sites par poste"
graphique2.type = "bar";graphique2.style= 10;graphique2.grouping = "stacked";graphique2.overlap = 100
graphique2.height = 15;graphique2.width = 25
graphique2.x_axis.scaling.orientation = "maxMin"
cp = CharacterProperties(sz=1000) #Taille du label entre 100 et 400 000
col = ((i + 9) // 10) * 2 - 1
row = (7 * i - 6) % 70 + 1
tmpCell = ws2.cell(row=row - 1, column=col)
tmpCell.value = "Top{} Image".format(i)
ws2.add_image(img, '{}{}'.format(chr(col + 64), row))
print("OK === >> {}".format(i))
dat = Reference(ws, min_col=4, min_row=2, max_col=4, max_row=11)
cat = Reference(ws, min_col=2, min_row=2, max_col=2, max_row=11)
barChart = BarChart()
barChart.add_data(data=dat)
barChart.set_categories(cat)
barChart.legend = None # 범례
barChart.varyColors = True
barChart.title = "Top10 Likes"
ws3.add_chart(barChart, "D1")
scatChart = ScatterChart()
scatChart.style = 13
scatChart.x_axis.title = 'SongName'
scatChart.y_axis.title = 'DiffLikes'
xvalues = Reference(ws, min_col=1, min_row=2, max_row=11)
values = Reference(ws, min_col=5, min_row=1, max_row=11)
series = Series(values, xvalues, title_from_data=True)
scatChart.series.append(series)
ws3.add_chart(scatChart, "D20")
