def draw_bar_chart(writer, max_row):
sheet = writer.sheets['死亡率']
date = sheet['A2'].value
# 初始化我们的bar chart
chart = BarChart()
# 指定bar chart 的数据范围
chart_data = Reference(sheet, min_col=6, max_col=6, min_row=2, max_row=max_row-1)
chart_series = Series(chart_data, title='截止{}死亡率'.format(date))
chart_series.graphicalProperties.solidFill = '5DD092' # Silver Tree
# 指定x轴
x_axis_data = Reference(sheet, min_col=3, max_col=3, min_row=2, max_row=max_row-1)
# chart 添加到 sheet里
chart.append(chart_series)
chart.set_categories(x_axis_data)
# 设置chart的样式
chart.height, chart.width = 14, 21
chart.title, chart.y_axis.title = '主要疫情地区死亡率', '死亡率'
chart.y_axis.number_format = '0.0%'
chart.legend.position = 't'
# 画平均死亡率横线line chart
line_chart = LineChart()
line_data = Reference(sheet, min_col=7, max_col=7, min_row=2, max_row=max_row - 1)
line_series = Series(line_data, title='除湖北外死亡率')
line_chart.append(line_series)
# 合并图表
chart += line_chart
# 添加图表并保存
sheet.add_chart(chart, 'I1')
writer.save()
def produce(filename, name, date):
wb = xl.load_workbook(filename)
rez = False
for sheet in wb:
if sheet.title == name + date:
ws = wb[name + date]
rez = True
break
if not rez:
ws = wb.create_sheet(title=name + date)
for i in range(1, 13):
ws.cell(row=1, column=i).value = i
ws.cell(row=2, column=i).value = 0
for prod in IterRowDict(wb, "Products"):
for sale in IterRowDict(wb, "Sales"):
if prod["Name"] == name and sale["Date"][6:] == date and prod[
"id"] == prod["id"]:
amount = int(prod["Price"]) * int(sale["Quantity"])
ws.cell(row=2, column=int(sale["Date"][3:5])).value = amount
ws = wb[name + date]
chart = BarChart()
x = Reference(ws, min_col=1, max_col=12, min_row=1)
y = Reference(ws, min_col=1, max_col=12, min_row=2)
chart.append(Series(y))
chart.set_categories(x)
ws.add_chart(chart, "A10")
wb.save(filename)
def numbers(wb):
ws = wb.create_sheet(1, "Numbers")
for i in range(10):
ws.append([i])
chart = BarChart()
values = Reference(ws, (0, 0), (9, 0))
series = Series(values)
chart.append(series)
ws.add_chart(chart)
def negative(wb):
ws = wb.create_sheet(1, "Negative")
for i in range(-5, 5):
ws.append([i])
chart = BarChart()
values = Reference(ws, (0, 0), (9, 0))
series = Series(values)
chart.append(series)
ws.add_chart(chart)
def dates(wb):
ws = wb.create_sheet(3, "Dates")
for i in range(1, 10):
ws.append([date(2013, i, 1), i])
chart = BarChart()
values = Reference(ws, (0, 1), (8, 1))
labels = Reference(ws, (0, 0), (8, 0))
labels.number_format = 'd-mmm'
series = Series(values, labels=labels)
chart.append(series)
ws.add_chart(chart)
def bar_chart():
wb = Workbook()
sheet = wb.active
for i in range(1, 11): # create some data in column A
sheet['A' + str(i)] = i
refObj = Reference(sheet, min_col=1, min_row=1, max_col=1, max_row=10)
seriesObj = Series(refObj, title='First series')
chartObj = BarChart()
chartObj.title = 'My Chart'
chartObj.append(seriesObj)
sheet.add_chart(chartObj, 'C5')
wb.save('chart.xlsx')
def letters(wb):
ws = wb.create_sheet(2, "Letters")
for idx, l in enumerate("ABCDEFGHIJ"):
ws.append([l, idx, idx])
chart = BarChart()
labels = Reference(ws, (0, 0), (9, 0))
values = Reference(ws, (0, 1), (9, 1))
series = Series(values, labels=labels)
chart.append(series)
# add second series
values = Reference(ws, (0, 2), (9, 2))
series = Series(values, labels=labels)
chart.append(series)
ws.add_chart(chart)
def plot_bar_chart():
wb = xl.load_workbook('C:\\Resources\\Program_Xl_Sheets\\Groceriesptfinal.xlsx') # load xl workbook
sheet = wb['dATA'] # get the worksheet
for i in range(2, 10):
cell = sheet['f'+str(i)]
sheet['a'+str(i)] = cell.value
values = Reference(
sheet,
min_row=2,
max_row=10,
min_col=1,
max_col=1
)
series_object = xl.chart.Series(values, title="Yearly Revenue")
chart = BarChart()
chart.append(series_object)
sheet.add_chart(chart, 'j2')
wb.save('C:\\Resources\\Program_Xl_Sheets\\Groceriesptfinal.xlsx')
cell_sum.fill = fill_orange
wb.save('./xlsx/merged_102_04.xlsx')
# 범위에 스타일 지정
sheet['B2:c14'] # 범위(range )
for row in sheet['B2:c14']:
for cell in row:
cell.border = border_thin
cell.number_format = '0.00'
for row in sheet['B14:C14']:
for cell in row:
cell.alignment = align_vcenter
cell.fill = fill_orange
wb.save('./xlsx/merged_102_05.xlsx')
# 차트 추가 하기
chart = BarChart()
chart.title = '2017년 월별 광고비 (억원)'
values = Reference(sheet, range_string='Sheet1!B1:B13')
series = Series(values, title='삼성전자')
chart.append(series)
values = Reference(sheet, range_string='Sheet1!C1:C13')
series = Series(values, title='LG전자')
chart.append(series)
sheet.add_chart(chart,'E1')
wb.save('./xlsx/merged_102_06.xlsx')
nums.append(i)
for i, j in list(enumerate(nums, start=1)):
sheet[f'B{i}'] = j
# First rectangular selection for a first series.
ref_obj_1 = Reference(sheet, min_col=1, min_row=1, max_col=1, max_row=10)
ref_obj_2 = Reference(sheet, min_col=2, min_row=1, max_col=2, max_row=10)
# First series of email_data from the selection and its name 'First series' (it will be displayed in the chart legend).
series_obj_1 = Series(ref_obj_1, title='First series')
series_obj_2 = Series(ref_obj_2, title='Second series')
# Create a chart
chart_obj = BarChart()
chart_obj.title = 'My Chart' # Top of the chart.
chart_obj.append(series_obj_1)
chart_obj.append(series_obj_2)
sheet.add_chart(
chart_obj,
'D5') # 'C5' is the anchor of the chart (the from_top-from_left corner).
wb.save('sample_chart.xlsx')
"""
OpenPyXL supports creating bar, line, scatter, and pie charts using the email_data in a sheet’s cells.
To make a chart, you need to do the following:
Create a Reference object from a rectangular selection of cells.
Create a Series object by passing in the Reference object.
Create a Chart object.
Append the Series object to the Chart object.
Add the Chart object to the Worksheet object,
optionally specifying which cell the from_top from_left corner of the chart should be positioned.
from openpyxl import Workbook
wb = Workbook()
sheet = wb.active
for i in range(1, 10):
sheet["A" + str(i)] = i
from openpyxl.chart import BarChart, Reference, Series
refObj = Reference(sheet, min_col=1, min_row=1, max_col=1, max_row=10)
seriesObj = Series(refObj, title='First series')
chartObj = BarChart()
chartObj.append(seriesObj)
sheet.add_chart(chartObj)
wb.save("SampleChart2.xlsx")
def to_excel_error_barchart(df,
filename="untitled.xlsx",
xlabel='samples',
ylabel='value',
title=''):
"""
Assumes that DataFrame `df` is organized as:
| group 1 measure | group 1 measure error | group 2 measure | group 2 measure error | ...
sample 1| | | | | ...
sample 2| | | | | ...
sample 3| | | | | ...
.
.
.
Then all groups are plotted in a single cluster, with a cluster for every sample provided
"""
cols = df.columns.tolist()
ngroups = len(cols[0::2])
rearrange_cols = [*cols[0::2], *cols[1::2]]
rearranged_df = df[rearrange_cols]
wb = Workbook()
ws = wb.active
for r in dataframe_to_rows(rearranged_df, index=True, header=True):
ws.append(r)
name_ref = Reference(ws,
min_col=1,
min_row=3,
max_col=1,
max_row=len(df) + 2)
col_idx = 2
mean_refs = [
Reference(ws, min_col=i, min_row=3, max_col=i, max_row=len(df) + 2)
for i in range(col_idx, col_idx + ngroups)
]
col_idx += ngroups
std_refs = [
Reference(ws, min_col=i, min_row=3, max_col=i, max_row=len(df) + 2)
for i in range(col_idx, col_idx + ngroups)
]
series_names = list(df.columns)[0::2]
means_series = [
SeriesFactory(mean_ref, title=name)
for mean_ref, name in zip(mean_refs, series_names)
]
for i, std_ref in enumerate(std_refs):
eBarsNumDataSource = NumDataSource(NumRef(std_ref))
means_series[i].errBars = ErrorBars(errDir='y',
errValType='cust',
plus=eBarsNumDataSource,
minus=eBarsNumDataSource)
chartObj = BarChart()
[chartObj.append(means) for means in means_series]
chartObj.title = title
chartObj.set_categories(name_ref)
chartObj.y_axis.title = ylabel
chartObj.x_axis.title = xlabel
ws.add_chart(chartObj, 'B12')
wb.save(filename)
plt.title('Energy Price per Resource')
plt.ylabel('dollars per Energy [$/MWh]')
plt.xlabel('Resources')
plt.bar(names, values, color='c')
plt.show()
#--------Excel using (openpyxl)---From Existing Wind---------------------
from openpyxl.chart import BarChart, Reference, Series
# Figure 1 'Energy vs Resources'
W_energy = Reference(page, min_row=4, max_row=13, min_col=5, max_col=5)
W_resources = Reference(page, min_row=4, max_row=13, min_col=4, max_col=4)
Rplot = Series(W_energy, title='resources')
chartWR = BarChart()
chartWR.append(Rplot)
chartWR.set_categories(W_resources)
# labeling
chartWR.title = 'Energy by Resource'
chartWR.x_axis.title = 'Resources'
chartWR.y_axis.title = 'Energy [MWh]'
# chart dimentions
chartWR.height = 10 # default is 7.5
chartWR.width = 20 # default is 15
# place on excel
page.add_chart(chartWR, 'K15')
# Figure 2 'Dollars vs Resources'
W_dollars = Reference(page, min_row=4, max_row=13, min_col=6, max_col=6)
#resources = Reference (page, min_row=4, max_row=13, min_col=4,max_col=4 )
Dplot = Series(W_dollars, title='resources')
import openpyxl
from openpyxl.chart import BarChart,Reference,Series
wb=openpyxl.Workbook()
ws=wb.active
for i in range(10):
ws.append([i])
ref_obj=Reference(ws,min_col=1,min_row=1,max_col=1,max_row=10)
series_obj=Series(ref_obj,title='sample series')
chart=BarChart()
chart.title='sample chart'
chart.append(series_obj)
ws.add_chart(chart,'C1')
wb.save('sample_chart_109p.xlsx')
#Gitのための試験
#从将表格中涉及的要画图的数据使用：Reference 创建一个对象
#比如：我选取 data = Reference(ws, min_col=5, min_row=4, max_col=10, max_row=4)
#参数含义：ws 一个活跃的sheet，数据来源。可以使用ws = wb.active 获取
#其他的就是指定这个表中的行列数据了：起始行、起始列、终止行、终止列
#通过传入Reference对象，创建一个Series对象
#创建一个Chart对象
#可选择的设置Chart对象的长（drawing.height）、宽（drawing.width）、坐标位置（drawing.top、drawing.left）。
#将Chart对象添加到Worksheet对象。
#定义类型图
chart = BarChart()
#定义Y轴
chart.y_axis.title = 'order_num'
#定义X 皱
chart.x_axis.title = 'cust_id'
refObj = Reference(ws,min_col=1,min_row=1,max_col=1,max_row=5)
seriesObj = Series(refObj,title='orders')
#seriesObj = Series(data,title='压力')
chart.append(seriesObj)
ws.add_chart(chart,"C8")
wb.save("D://python/mysql-execl-linechart2.xlsx")
# Escalado de ejes
chart2 = ScatterChart()
chart2.title = "Clipped Axes"
chart2.x_axis.title = 'x'
chart2.y_axis.title = '1/x'
chart2.legend = None
chart2.x_axis.scaling.min = 0
chart2.y_axis.scaling.min = 0
chart2.x_axis.scaling.max = 11
chart2.y_axis.scaling.max = 1.5
x = Reference(ws17, min_col=1, min_row=2, max_row=22)
y = Reference(ws17, min_col=2, min_row=2, max_row=22)
s = Series(y, xvalues=x)
chart1.append(s)
chart2.append(s)
ws17.add_chart(chart1, "C1")
ws17.add_chart(chart2, "C15")
##########################
## Adding a second axis ##
##########################
from openpyxl import Workbook
from openpyxl.chart import (
LineChart,
BarChart,
Reference,
Series,
