def create_impala_query_slide(prs,chart_series_title, categories, values):
# change title and properties
slide = prs.slides.add_slide(prs.slide_layouts[12])
title = slide.shapes.title
title.text = chart_series_title
title.text_frame.paragraphs[0].font.name = "calibri"
title.text_frame.paragraphs[0].font.size = Pt(28)
# define chart data ---------------------
chart_data = ChartData()
chart_data.categories = categories
chart_series_title = chart_series_title
chart_data.add_series(chart_series_title, values)
# add chart to slide --------------------
x, y, cx, cy = Inches(2.25), Inches(1.4), Inches(9), Inches(6.0)
chart = slide.shapes.add_chart(XL_CHART_TYPE.COLUMN_CLUSTERED, x, y, cx, cy, chart_data)
chart.chart_style = 10
# sets chart plot to all be the same color (default in PPT is all different colors by category)
plot = chart.chart.plots[0]
plot.vary_by_categories = True
# change axis properties
category_axis = chart.chart.category_axis
value_axis = chart.chart.value_axis
# change axis font properties
category_axis.tick_labels.font.name = "calibri"
category_axis.tick_labels.font.size = Pt(11)
value_axis.tick_labels.font.name = "calibri"
value_axis.tick_labels.font.size = Pt(11)
# change legend properties
chart.chart.has_legend = True
chart.chart.legend.include_in_layout = False
chart.chart.legend.font.name = "calibri"
chart.chart.legend.font.size = Pt(11)
return
def create_ppt(self, data):
# create presentation with 1 slide ------
prs = Presentation()
slide = prs.slides.add_slide(prs.slide_layouts[5])
zp = []
zp_value = []
pro_data = sorted(data, key=lambda result: result[0])
for item in pro_data:
zp.append(item[0])
zp_value.append(item[1])
chart_data = ChartData()
chart_data.categories = zp
chart_data.add_series('Series 1', zp_value)
# add chart to slide --------------------
x, y, cx, cy = Inches(2), Inches(2), Inches(6), Inches(4.5)
slide.shapes.add_chart(XL_CHART_TYPE.COLUMN_CLUSTERED, x, y, cx, cy,
chart_data)
file_name = 'chart-011.pptx'
file_path = os.path.join(sys.path[0], file_name)
prs.save(file_path)
with open(file_path, 'rb') as fp:
data = fp.read().encode('base64')
return data
def add_series_fixture(self, request, categories, _SeriesData_,
series_data_):
chart_data = ChartData()
chart_data.categories = categories
name = 'Series Foo'
values = (1.1, 2.2, 3.3)
return chart_data, name, values, _SeriesData_, series_data_
def xlsx_fixture(self, request, WorkbookWriter_, categories, series_lst_,
xlsx_blob_):
chart_data = ChartData()
chart_data.categories = categories
chart_data._series_lst = series_lst_
return (chart_data, WorkbookWriter_, categories, series_lst_,
xlsx_blob_)
def fill_slide_mean(tsg_ppt, org_all, a, a1, a2, a3, a4, a5, a6, a7, data, box=None):
common_slide=tsg_ppt.slides[a]
textbox=common_slide.shapes[0]
chart_data = ChartData()
chart_data.categories= [a1,a2,a3,a4,a5,a6,a7]
chart_data.add_series('01', (data[8], data[7], data[6], data[5], data[4], data[3], data[0]))
x,y,cx,cy = Inches(0.3), Inches(2.25), Inches(9.38), Inches(4.7)
graphic_frame = common_slide.shapes.add_chart(XL_CHART_TYPE.BAR_CLUSTERED, x, y, cx, cy, chart_data)
chart = graphic_frame.chart
value_axis = chart.value_axis
value_axis.maximum_scale = 3
value_axis.minimum_scale = 1
tick_labels = value_axis.tick_labels
tick_labels.number_format = '0.00'
tick_labels.font.size = Pt(12)
tick_labels.font.type = 'Tele-GroteskEENor'
plot = chart.plots[0]
plot.has_data_labels = True
data_labels = plot.data_labels
data_labels.font.size = Pt(12)
data_labels.number_format = '0.00'
data_labels.font.color.rgb = RGBColor(0, 0, 0)
data_labels.position = XL_LABEL_POSITION.OUTSIDE_END
category_axis = chart.category_axis
category_axis.minor_tick_mark = XL_TICK_MARK.OUTSIDE
category_axis.tick_labels.font.size = Pt(12)
bar_plot = chart.plots[0]
bar_plot.gap_width = 20
#bar_series = chart.series.bar_series
#bar_series.fill.solid()
#bar_series.fill.color.rgb = RGBColor(0, 0, 0)
common_slide=tsg_ppt.slides[a]
return tsg_ppt
def chart(df):
data = ChartData()
data.categories = list(df.index)
for col in df:
data.add_series(col, df[col].tolist())
type = XL_CHART_TYPE.COLUMN_CLUSTERED
return type, data
def add_account_type_chart(self):
"""
Gender breakdown pie chart
:return:
"""
chart_data = ChartData()
chart_data.categories = ['Organisation', 'Individual']
organisation_percent = self.data_row['twitter_audience_organisational']
individual_percent = self.data_row['twitter_audience_individuals']
total = organisation_percent+individual_percent
i_percent = individual_percent/total
o_percent = organisation_percent/total
chart_data.add_series('Series 1', (i_percent, o_percent))
x_coord, y_coord, cx_width, cy_height = Cm(18), Cm(0.44), Cm(7.9), Cm(6.9)
chart = self.slide.shapes.add_chart(
XL_CHART_TYPE.PIE, x_coord, y_coord, cx_width, cy_height, chart_data).chart
chart.has_legend = True
chart.legend.position = XL_LEGEND_POSITION.BOTTOM
chart.legend.include_in_layout = False
chart.plots[0].has_data_labels = True
data_labels = chart.plots[0].data_labels
data_labels.number_format = '0%'
top = Cm(0.01)
left = Cm(18)
width = Cm(3)
height = Cm(1)
t_box = self.shapes.add_textbox(left, top, width, height)
t_frame = t_box.text_frame
t_frame.text = "Account Type Distribution"
def add_audience_age_chart(self):
"""
Creates audience age bar chart
:return:
"""
chart_data = ChartData()
chart_data.categories = ['0-9', '10-17', '18-24',
'25-34', '35-44', '45-54',
'55-64', '65+']
group_1 = self.data_row['0 to 9']
group_2 = self.data_row['10 to 17']
group_3 = self.data_row['18-24']
group_4 = self.data_row['25-34']
group_5 = self.data_row['35-44']
group_6 = self.data_row['45-54']
group_7 = self.data_row['55-64']
group_8 = self.data_row['65+']
chart_data.add_series('Series 1', (group_1, group_2,
group_3, group_4,
group_5, group_6,
group_7, group_8))
x_coord, y_coord, cx_width, cy_height = Cm(10.46), Cm(8.44), Cm(7), Cm(6)
self.slide.shapes.add_chart(
XL_CHART_TYPE.COLUMN_CLUSTERED, x_coord, y_coord, cx_width, cy_height, chart_data)
tx_box = self.shapes.add_textbox(Cm(10.9), Cm(7.8), Cm(10.9), height=1)
t_frame = tx_box.text_frame
t_frame.text = "Age Distribution"
def add_gender_pie_chart(self):
"""
Gender breakdown pie chart
:return:
"""
chart_data = ChartData()
chart_data.categories = ['Male', 'Female']
male_percent = self.data_row['twitter_audience_male_percent']
female_percent = self.data_row['twitter_audience_female_percent']
total = male_percent+female_percent
m_percent = male_percent/total
f_percent = female_percent/total
chart_data.add_series('Series 1', (m_percent, f_percent))#sort out the % values
x_coord, y_coord, cx_width, cy_height = Cm(10.46), Cm(0.44), Cm(7), Cm(6)
chart = self.slide.shapes.add_chart(
XL_CHART_TYPE.PIE, x_coord, y_coord, cx_width, cy_height, chart_data).chart
chart.has_legend = True
chart.legend.position = XL_LEGEND_POSITION.BOTTOM
chart.legend.include_in_layout = False
chart.plots[0].has_data_labels = True
data_labels = chart.plots[0].data_labels
data_labels.number_format = '0%'
top = Cm(0.01)
left = Cm(10.46)
width = Cm(3)
height = Cm(1)
tx_box = self.shapes.add_textbox(left, top, width, height)
t_frame = tx_box.text_frame
t_frame.text = "Gender Breakdown"
def assign_chart_data(df, config):
if 'DataFrame' in str(type(df)):
chart_data = ChartData()
chart_data.categories = df.index
percent_format = '0'
if config['dec places'] > 0:
percent_format += '.'
for place in range((config['dec places'])):
percent_format += '0'
percent_format += '%'
config['number format'] = percent_format
# Move to Data Cleanup
if config['*FORCE FLOAT'] or config['*MEAN']:
config['number format'] = '0.0'
elif config['*FORCE INT']:
config['number format'] = '0'
elif config['*FORCE CURRENCY']:
config['number format'] = '$0.00'
for col in df.columns:
chart_data.add_series(col, df[col], config['number format'])
else:
chart_data = df
return chart_data
def CreatePptNum(self,s,num):
i = 0
z = 0
# 创建幻灯片 ------
prs = Presentation()
while i < s:
slide = prs.slides.add_slide(prs.slide_layouts[5])
# 定义图表数据 ---------------------
chart_data = ChartData()
chart_data.categories = ['East', 'West', 'Midwest']
chart_data.add_series('Series 1', (19.2, 21.4, 16.7))
# 将图表添加到幻灯片 --------------------
x, y, cx, cy = Inches(2), Inches(2), Inches(6), Inches(4.5)
slide.shapes.add_chart(
XL_CHART_TYPE.COLUMN_CLUSTERED, x, y, cx, cy, chart_data
)
i = i + 1
title_slide_layout1 = prs.slide_layouts[0]
slide1 = prs.slides.add_slide(title_slide_layout1)
title1 = slide1.shapes.title
subtitle1 = slide1.placeholders[1]
# 设置标题和副标题
title1.text = "我是关键字"
salt = ''.join(random.sample(string.ascii_letters + string.digits, 16))
subtitle1.text = "王宝强" + salt
prs.save('Result\\dlp%s.pptx' % num)
return True
def prt_slide_graph_p2_1():
global slide
slide = prs.slides.add_slide(prs.slide_layouts[6])
title_para('prt_slide_graph_p2_1', '년 월 시스템 ')
text_para_1st('월 평균(영업일기준) CPU/MEM 사용율%')
# 가로 그래프 1개 좌표
x1, y1, cx1, cy1 = Cm(1.0), Cm(2.5), Cm(11.5), Cm(7.8)
# x2, y2, cx2, cy2 = Cm(13.0), Cm(2.5), Cm(11.5), Cm(7.8)
# x3, y3, cx3, cy3 = Cm(1.0), Cm(10.5), Cm(11.5), Cm(7.8)
# x4, y4, cx4, cy4 = Cm(13.0), Cm(10.5), Cm(11.5), Cm(7.8)
# 그래프
on_label = 0;on_mark = 1
chart_data1 = ChartData()
# chart_data2 = ChartData()
# chart_data3 = ChartData()
# chart_data4 = ChartData()
# chart_data1.categories = deco_data(data1['data'])
chart_data1.categories = ['A', 'B', 'C', 'D']
# chart_data1.categories = chart_data2.categories = chart_data3.categories = chart_data4.categories = ['A', 'B', 'C', 'D']
chart_data1.add_series("CH01", (5,3,7), 2)
# chart_data2.add_series("CH02", (5,3,7), 2)
# chart_data3.add_series("CH03", (5,3,7), 2)
# chart_data4.add_series("CH04", (5,3,7), 2)
add_chart_in_ppt(x1, y1, cx1, cy1, chart_data1)
def add_series_fixture(
self, request, categories, _SeriesData_, series_data_):
chart_data = ChartData()
chart_data.categories = categories
name = 'Series Foo'
values = (1.1, 2.2, 3.3)
return chart_data, name, values, _SeriesData_, series_data_
def _generate_conceptnet_data(presentation_context, title_generator,
conceptnet_function):
seed = presentation_context["seed"]
title = title_generator(presentation_context)
presentation_context["chart_title"] = title
conceptnet_relations = conceptnet_function(seed)
if conceptnet_relations:
conceptnet_relations = conceptnet.remove_duplicates(
conceptnet_relations)
conceptnet_relations = conceptnet.remove_containing(
conceptnet_relations, seed)
random.shuffle(conceptnet_relations)
conceptnet_relations = conceptnet_relations[0:random.randint(2, 5)]
categories = [location[1] for location in conceptnet_relations]
values = [float(location[0])**2 for location in conceptnet_relations]
if len(categories) == 0:
return None
series_data = normalise_data(values)
chart_data = ChartData()
chart_data.categories = categories
chart_data.add_series("", series_data)
return title, chart_data
def when_I_add_a_chart_with_categories_and_series(context, kind, cats, sers):
chart_type = {
'Area': XL_CHART_TYPE.AREA,
'100% Stacked Area': XL_CHART_TYPE.AREA_STACKED_100,
'Stacked Area': XL_CHART_TYPE.AREA_STACKED,
'Clustered Bar': XL_CHART_TYPE.BAR_CLUSTERED,
'100% Stacked Bar': XL_CHART_TYPE.BAR_STACKED_100,
'Stacked Bar': XL_CHART_TYPE.BAR_STACKED,
'Clustered Column': XL_CHART_TYPE.COLUMN_CLUSTERED,
'100% Stacked Column': XL_CHART_TYPE.COLUMN_STACKED_100,
'Stacked Column': XL_CHART_TYPE.COLUMN_STACKED,
'Line': XL_CHART_TYPE.LINE,
'Pie': XL_CHART_TYPE.PIE,
}[kind]
category_count, series_count = int(cats), int(sers)
category_source = ('Foo', 'Bar', 'Baz', 'Boo', 'Far', 'Faz')
series_value_source = count(1.1, 1.1)
chart_data = ChartData()
chart_data.categories = category_source[:category_count]
for idx in range(series_count):
series_title = 'Series %d' % (idx+1)
series_values = tuple(islice(series_value_source, category_count))
chart_data.add_series(series_title, series_values)
context.chart = context.slide.shapes.add_chart(
chart_type, Inches(1), Inches(1), Inches(8), Inches(5), chart_data
).chart
def when_I_call_placeholder_insert_chart(context):
chart_data = ChartData()
chart_data.categories = ['Yes', 'No']
chart_data.add_series('Series 1', (42, 24))
placeholder = context.shape
context.placeholder = placeholder.insert_chart(XL_CHART_TYPE.PIE,
chart_data)
def when_I_call_placeholder_insert_chart(context):
chart_data = ChartData()
chart_data.categories = ['Yes', 'No']
chart_data.add_series('Series 1', (42, 24))
placeholder = context.shape
context.placeholder = placeholder.insert_chart(
XL_CHART_TYPE.PIE, chart_data
)
def xml_bytes_fixture(self, chart_type_, ChartXmlWriter_, series_lst_):
chart_data = ChartData()
chart_data._series_lst = series_lst_
expected_bytes = 'ƒøØßår'.encode('utf-8')
return (
chart_data, chart_type_, ChartXmlWriter_, expected_bytes,
series_lst_
)
def drawbar(shapes,
df,
title,
left=Inches(0.5),
top=Inches(1),
width=Inches(6),
height=Inches(6.5),
sorted=True,
fontsize=18):
# values = df.values.tolist()
columns = df.columns.values.tolist() # 列名
df = df.dropna()
if sorted: # 默认对数值排序
sort_item = columns[1]
df = df.sort_values(by=sort_item)
df_labels = df.iloc[:, 0] # 部门
df_values = df.iloc[:, 1:] # 数值
labels = df_labels.values.tolist()
chart_data = ChartData()
for i in range(df_values.shape[1]):
values = df_values.iloc[:, i].values.tolist()
column = columns[i + 1]
chart_data.add_series(column, values)
chart_data.categories = labels
# x, y, cx, cy = Inches(0.5), Inches(1), Inches(6), Inches(6.5)
graphic_frame = shapes.add_chart(XL_CHART_TYPE.BAR_CLUSTERED, left, top,
width, height, chart_data)
graphic_frame.chart.has_title = True
graphic_frame.chart.chart_title.text_frame.clear()
new_title = graphic_frame.chart.chart_title.text_frame.add_paragraph()
new_title.text = title
new_title.font.size = Pt(24)
new_title.font.bold = True
series = graphic_frame.chart.series[0]
series.invert_if_negative = False # 没什么卵用
for i in range(df_values.shape[0]):
point = series.points[i]
fill = point.format.fill
fill.patterned() # 此处不可用solid()，否则负值会被取反色
fill.fore_color.rgb = RGBColor(210, 71, 38) # orange
fill.back_color.rgb = RGBColor(210, 71, 38) # 背景色也被设置为橙色，背景色就是负值颜色
plot = graphic_frame.chart.plots[0] # 取图表中第一个plot
plot.has_data_labels = True # 是否显示数据标签
data_labels = plot.data_labels # 数据标签控制类
data_labels.position = XL_DATA_LABEL_POSITION.OUTSIDE_END # 字体位置
data_labels.font.bold = True
data_labels.number_format = '0.0%'
data_labels.font.size = Pt(fontsize)
category_axis = graphic_frame.chart.category_axis # 纵轴标签控制类
category_axis.tick_labels.font.bold = True
category_axis.tick_label_position = XL_TICK_LABEL_POSITION.LOW
category_axis.has_major_gridlines = False
value_axis = graphic_frame.chart.value_axis # 横轴值坐标标签控制类
value_axis.tick_labels.number_format = '0%'
value_axis.has_minor_gridlines = False
value_axis.has_major_gridlines = False
return graphic_frame
def fill_slide_common(tsg_ppt, org_all, a, c1, c2, c3, c4, c5, data, box=None):
common_slide = tsg_ppt.slides[a]
mittelwert = common_slide.placeholders[18]
text_frame = mittelwert.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = box
font = run.font
font.size = Pt(10.5)
font.color.rgb = RGBColor(226, 0, 116)
chart_data = ChartData()
#chart_data.categories= [c1,c2,c3,c4,c5]
chart_data.categories = ['asdf']
#chart_data.add_series('01', (data[0], data[1], data[2], data[3], data[4]))
a = 0.3
b = 0.7
#chart_data.add_series('1',(a,b))
chart_data.add_series('1', (a, b))
chart_data.add_series('2', (b, a))
x, y, cx, cy = Inches(0.3), Inches(2.25), Inches(9.38), Inches(4.7)
graphic_frame = common_slide.shapes.add_chart(
XL_CHART_TYPE.BAR_STACKED_100, x, y, cx, cy, chart_data)
chart = graphic_frame.chart
value_axis = chart.value_axis
chart.has_legend = True
chart.plots[0].has_data_labels = True
data_labels = chart.plots[0].data_labels
data_labels.number_format = '0%'
#data_labels.position = XL_LABEL_POSITION.OUTSIDE_END
chart.legend.position = XL_LEGEND_POSITION.CUSTOM
#value_axis.maximum_scale = 100.0
#tick_labels = value_axis.tick_labels
#tick_labels.number_format = '0"%"'
#tick_labels.font.size = Pt(12)
#tick_labels.font.type = 'Tele-GroteskEENor'
#plot = chart.plots[0]
#plot.has_data_labels = True
#data_labels = plot.data_labels
#data_labels.font.size = Pt(12)
#data_labels.number_format = '0"%"'
#data_labels.font.color.rgb = RGBColor(0, 0, 0)
#data_labels.position = XL_LABEL_POSITION.OUTSIDE_END
#category_axis = chart.category_axis
#category_axis.minor_tick_mark = XL_TICK_MARK.OUTSIDE
#category_axis.tick_labels.font.size = Pt(12)
#bar_plot = chart.plots[0]
#bar_plot.gap_width = 20
#bar_plot.overlap = -20
#print data
#if (box):
#print box
chart.replace_data(chart_data)
return tsg_ppt
def drawbar2(shapes,
df,
title,
left=Inches(0.5),
top=Inches(1),
width=Inches(7),
height=Inches(6.5),
sorted=True,
fontsize=18,
chart_type=XL_CHART_TYPE.COLUMN_CLUSTERED):
# values = df.values.tolist()
columns = df.columns.values.tolist() # 列名
df = df.dropna()
if sorted: # 默认对数值排序
sort_item = columns[1]
df = df.sort_values(by=sort_item)
df_labels = df.iloc[:, 0] # 部门
df_values = df.iloc[:, 1:] # 数值
labels = df_labels.values.tolist()
chart_data = ChartData()
for i in range(df_values.shape[1]):
values = df_values.iloc[:, i].values.tolist()
column = columns[i + 1]
chart_data.add_series(column, values)
chart_data.categories = labels
# x, y, cx, cy = Inches(0.5), Inches(1), Inches(6), Inches(6.5)
graphic_frame = shapes.add_chart(chart_type, left, top, width, height,
chart_data)
graphic_frame.chart.has_legend = True
graphic_frame.chart.legend.position = XL_LEGEND_POSITION.CORNER # 图例在右上角
graphic_frame.chart.has_title = True
graphic_frame.chart.chart_title.text_frame.clear()
# graphic_frame.chart.has_table = True
new_title = graphic_frame.chart.chart_title.text_frame.add_paragraph()
new_title.text = title
new_title.font.size = Pt(24)
new_title.font.bold = True
series = graphic_frame.chart.series[0]
series.invert_if_negative = False # 没什么卵用
plot = graphic_frame.chart.plots[0] # 取图表中第一个plot
plot.has_data_labels = True # 是否显示数据标签
data_labels = plot.data_labels # 数据标签控制类
data_labels.position = XL_DATA_LABEL_POSITION.OUTSIDE_END # 字体位置
data_labels.font.bold = True
if True in (df_values[df_values < 1].count() / df_values.count() >
0.9).tolist():
data_labels.number_format = '0.0%'
data_labels.font.size = Pt(fontsize)
category_axis = graphic_frame.chart.category_axis # 纵轴标签控制类
category_axis.tick_labels.font.bold = True
category_axis.tick_label_position = XL_TICK_LABEL_POSITION.LOW
category_axis.has_major_gridlines = False
value_axis = graphic_frame.chart.value_axis # 横轴值坐标标签控制类
# value_axis.tick_labels.number_format = '0%'
value_axis.has_minor_gridlines = False
value_axis.has_major_gridlines = False
return graphic_frame
def xlsx_fixture(
self, request, WorkbookWriter_, categories, series_lst_,
xlsx_blob_):
chart_data = ChartData()
chart_data.categories = categories
chart_data._series_lst = series_lst_
return (
chart_data, WorkbookWriter_, categories, series_lst_, xlsx_blob_
)
def create_chart(chart_name, slide_name, placeholder_index, categories, series,
name_of_attribute):
chart_data = ChartData()
my_tuple = list(zip(categories, series))
my_tuple.sort(key=lambda elem: elem[1])
new_cat = [i[0] for i in my_tuple]
new_series = [i[1] for i in my_tuple]
if name_of_attribute == "Food taste and flavor":
index = new_series.index(
kpis[slugify(Chain)]["attributes"]["Food taste and flavor"])
elif name_of_attribute == "Overall Rating":
index = new_series.index(
kpis[slugify(Chain)]["attributes"]["Overall Rating"])
elif name_of_attribute == "Food quality":
index = new_series.index(
kpis[slugify(Chain)]["attributes"]["Food quality"])
elif name_of_attribute == "Food quality takeout":
index = new_series.index(
kpis[slugify(Chain)]["attributes"]["Food quality takeout"])
elif name_of_attribute == "Interior cleanliness":
index = new_series.index(
kpis[slugify(Chain)]["attributes"]["Interior cleanliness"])
elif name_of_attribute == "Kitchen/food prep area cleanliness":
index = new_series.index(kpis[slugify(Chain)]["attributes"]
["Kitchen/food prep area cleanliness"])
elif name_of_attribute == "Order accuracy":
index = new_series.index(
kpis[slugify(Chain)]["attributes"]["Order accuracy"])
else:
index = new_series.index(kpis[slugify(Chain)]["attributes"]
["Dishware/glassware/silverware cleanliness"])
# index = [name_of_attribute for name_of_attribute in new_series.index(kpis[slugify(Chain)]["attributes"][name_of_attribute]) if name_of_attribute == name_of_attribute]
chart_data.categories = (new_cat)
chart_data.add_series('Series1', (new_series))
chart_name = slide_name.placeholders[placeholder_index].insert_chart(
XL_CHART_TYPE.BAR_CLUSTERED, chart_data)
chart_name.chart.plots[0].series[0].format.fill.solid()
chart_name.chart.plots[0].series[0].format.fill.fore_color.rgb = RGBColor(
192, 192, 192)
chart_name.chart.plots[0].has_data_labels = True
chart_name.chart.value_axis.has_major_gridlines = False
chart_name.chart.value_axis.visible = False
chart_name.chart.plots[0].data_labels.font.size = Pt(14)
chart_name.chart.plots[0].data_labels.number_format = '#,##0.0%'
chart_name.chart.plots[0].chart.value_axis.maximum_scale = .95
chart_name.chart_part.chart.category_axis.major_tick_mark = XL_TICK_MARK.NONE
point = chart_name.chart.plots[0].series[0].points[index]
fill = point.format.fill
fill.solid()
fill.fore_color.rgb = RGBColor(225, 34, 34)
chart_name.chart.plots[0].chart.category_axis.format.line.fill.solid()
chart_name.chart.plots[
0].chart.category_axis.format.line.fill.fore_color.rgb = RGBColor(
255, 255, 255)
def fill_slide_not_common_2(tsg_ppt,
org_all,
a,
c1,
c2,
c3,
c4,
c5,
data,
box=None):
fill_slide_title(tsg_ppt, a, org_all)
not_common_slide = tsg_ppt.slides[a]
mittelwert = not_common_slide.placeholders[18]
text_frame = mittelwert.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = box
font = run.font
font.size = Pt(10.5)
font.color.rgb = RGBColor(226, 0, 116)
chart_data = ChartData()
chart_data.categories = [c1, c2, c3, c4, c5]
chart_data.add_series('01', (data[0], data[1], data[2], data[3], data[4]))
x, y, cx, cy = Inches(0.3), Inches(2.25), Inches(9.38), Inches(4.7)
graphic_frame = not_common_slide.shapes.add_chart(
XL_CHART_TYPE.COLUMN_CLUSTERED, x, y, cx, cy, chart_data)
chart = graphic_frame.chart
value_axis = chart.value_axis
value_axis.maximum_scale = 100.0
tick_labels = value_axis.tick_labels
tick_labels.number_format = '0"%"'
tick_labels.font.size = Pt(12)
tick_labels.font.type = 'Tele-GroteskEENor'
plot = chart.plots[0]
plot.has_data_labels = True
data_labels = plot.data_labels
data_labels.font.size = Pt(12)
data_labels.number_format = '0"%"'
data_labels.font.color.rgb = RGBColor(0, 0, 0)
data_labels.position = XL_LABEL_POSITION.OUTSIDE_END
category_axis = chart.category_axis
category_axis.minor_tick_mark = XL_TICK_MARK.OUTSIDE
category_axis.tick_labels.font.size = Pt(12)
bar_plot = chart.plots[0]
bar_plot.gap_width = 20
bar_plot.overlap = -20
#print data
#if (box):
#print box
chart.replace_data(chart_data)
return tsg_ppt
def replace_chart_data(shape, df):
chart_data = ChartData()
chart_data.categories = df.index
for col_idx, col in enumerate(df.columns):
chart_data.add_series(col, (df.iloc[:, col_idx].values))
shape.chart.replace_data(chart_data)
return shape
def chart(self, df, placehold):
data = ChartData()
# 横轴名
data.categories = list(df.index)
# 系列名，数据
for col_name in df:
array = df[col_name].tolist()
data.add_series(col_name, array)
# 图表类型
type = XL_CHART_TYPE.COLUMN_CLUSTERED
# 指定placehold中插入图表
placehold.insert_chart(type, data)
def chart(self, df, chart_class='bar', left=2, top=25, width=95, height=60):
# 获取第一例的列名，作为categories
categories = df.iloc[:, 0].tolist()
# 获取第二列滞后的数据
series_name = df.iloc[:, 1:]
# 将第二列后的数据，按照列转换成系列
series = series_name.to_dict(orient='series')
# define chart data ---------------------
chart_data = ChartData()
chart_data.categories = categories
for key in series:
chart_data.add_series(key, series[key].tolist())
left = int(0.01 * left * self.prs.slide_width)
top = int(0.01 * top * self.prs.slide_height)
width = int(0.01 * width * self.prs.slide_width)
height = int(0.01 * height * self.prs.slide_height)
if chart_class == 'bar':
graphic_frame = self.slide.shapes.add_chart(
XL_CHART_TYPE.COLUMN_CLUSTERED, left, top, width, height, chart_data
)
elif chart_class == 'pie':
graphic_frame = self.slide.shapes.add_chart(
XL_CHART_TYPE.PIE, left, top, width, height, chart_data
)
elif chart_class == 'line':
graphic_frame = self.slide.shapes.add_chart(
XL_CHART_TYPE.LINE, left, top, width, height, chart_data
)
chart = graphic_frame.chart
if chart_class == 'line':
chart.series[0].smooth = False
else:
plot = chart.plots[0]
plot.has_data_labels = True
data_labels = plot.data_labels
data_labels.font.size = Pt(13)
data_labels.font.color.rgb = RGBColor(0x0A, 0x42, 0x80)
data_labels.position = XL_LABEL_POSITION.OUTSIDE_END
if chart_class == 'pie':
data_labels.number_format = '0%'
if chart_class in ['bar', 'line'] and df.shape[1] > 2:
chart.has_legend = True
chart.legend.position = XL_LEGEND_POSITION.RIGHT
chart.legend.include_in_layout = False
else:
chart.has_legend = True
chart.legend.position = XL_LEGEND_POSITION.BOTTOM
chart.legend.include_in_layout = False
def when_I_replace_its_data_with_categories_and_series(context, cats, sers):
category_count, series_count = int(cats), int(sers)
category_source = ('Foo', 'Bar', 'Baz', 'Boo', 'Far', 'Faz')
series_value_source = count(1.1, 1.1)
chart_data = ChartData()
chart_data.categories = category_source[:category_count]
for idx in range(series_count):
series_title = 'New Series %d' % (idx + 1)
series_values = tuple(islice(series_value_source, category_count))
chart_data.add_series(series_title, series_values)
context.chart.replace_data(chart_data)
def when_I_replace_its_data_with_categories_and_series(context, cats, sers):
category_count, series_count = int(cats), int(sers)
category_source = ("Foo", "Bar", "Baz", "Boo", "Far", "Faz")
series_value_source = count(1.1, 1.1)
chart_data = ChartData()
chart_data.categories = category_source[:category_count]
for idx in range(series_count):
series_title = "New Series %d" % (idx + 1)
series_values = tuple(islice(series_value_source, category_count))
chart_data.add_series(series_title, series_values)
context.chart.replace_data(chart_data)
def add_happiness_chart(slide):
chart_data = ChartData()
chart_data.categories = ['Q1', 'Q2', f'Hiring John', 'Q3', 'Q4']
chart_data.add_series('Happiness', (30.0, 23.0, 17.2, 69.69, 120.00))
x, y, cx, cy = Inches(2), Inches(2), Inches(6), Inches(4.5)
chart = slide.shapes.add_chart(XL_CHART_TYPE.LINE, x, y, cx, cy,
chart_data).chart
chart.has_legend = False
chart.series[0].smooth = True
chart.value_axis.visible = False
chart.category_axis.axis_title.text_frame.paragraphs[0].font.size = Pt(11)
chart.chart_title.text_frame.paragraphs[0].font.size = Pt(11)
chart.value_axis.axis_title.text_frame.paragraphs[0].font.size = Pt(11)
def _build_chart_data(csv):
chart_data = ChartData()
categories = [_nan_to_none(x) or '' for x in csv.iloc[:, 0].values]
log.debug(u" Setting categories with values:%s" % categories)
chart_data.categories = categories
for i in range(1, csv.columns.size):
col = csv.iloc[:, i]
values = [_nan_to_none(x) for x in col.values]
name = _to_unicode(col.name)
log.debug(u" Adding series:%s values:%s" % (name, values))
chart_data.add_series(name, values)
return chart_data
def add_bar_chat(self, categories):
chart_layout = self.presentation.slide_layouts[SLD_LAYOUT_CHART]
slide = self.presentation.slides.add_slide(chart_layout)
self.add_title(slide)
chart_data = ChartData()
chart_data.categories = categories
values = random_values(len(categories))
chart_data.add_series('Serie', values)
x, y, cx, cy = Inches(2), Inches(2), Inches(6), Inches(4.5)
slide.shapes.add_chart(
XL_CHART_TYPE.COLUMN_CLUSTERED, x, y, cx, cy, chart_data)
def make_series_data_seq(cat_count, ser_count):
"""
Return a sequence of |_SeriesData| objects populated with *cat_count*
category names and *ser_count* sequences of point values. Values are
auto-generated.
"""
category_names = ('Foo', 'Bar', 'Baz', 'Boo', 'Far', 'Faz')
point_values = count(1.1, 1.1)
chart_data = ChartData()
chart_data.categories = category_names[:cat_count]
for idx in range(ser_count):
series_title = 'Series %d' % (idx + 1)
series_values = tuple(islice(point_values, cat_count))
chart_data.add_series(series_title, series_values)
return chart_data.series
def make_series_data_seq(cat_count, ser_count):
"""
Return a sequence of |_SeriesData| objects populated with *cat_count*
category names and *ser_count* sequences of point values. Values are
auto-generated.
"""
category_names = ('Foo', 'Bar', 'Baz', 'Boo', 'Far', 'Faz')
point_values = count(1.1, 1.1)
chart_data = ChartData()
chart_data.categories = category_names[:cat_count]
for idx in range(ser_count):
series_title = 'Series %d' % (idx+1)
series_values = tuple(islice(point_values, cat_count))
series_values = [round(x*10)/10.0 for x in series_values]
chart_data.add_series(series_title, series_values)
return chart_data.series
def add_bar_chart(self,
slide,
x,
y,
cx,
cy,
categories,
*series,
unit='cm'):
'''
Add bar chart into given slide.
Args:
slide: slide Given slide
x: x aixs location of chart
y: float y aixs location of chart
cx: float Total width of chart
cy: float Total height of chart
categories: list List of bar names
series: list List of data series, one of each has 2 element: 'series name', 'data list'
eg: *series = *[('Series 1', [1.3, 3.6]),
('Series 2', [-0.5, 1.6])]
unit: str Must 'cm' or 'inches'
Return:
chart: added chart
'''
x, y, cx, cy = self._convert_nums(x, y, cx, cy, unit=unit)
chart_data = self._add_categories_series(ChartData(), categories,
*series)
graphic_frame = slide.shapes.add_chart(XL_CHART_TYPE.COLUMN_CLUSTERED,
x, y, cx, cy, chart_data)
return graphic_frame.chart
def generate_yes_no_large_funny_answer_chart_data(presentation_context):
title = yes_no_question_generator(presentation_context)
presentation_context["chart_title"] = title
categories = [
'Yes', 'No',
funny_yes_no_answer_generator(presentation_context)
]
series_data = normalise_data(
create_equal_data_with_outlier_end(len(categories), .7, 1, 2.5, 1, 20))
chart_data = ChartData()
chart_data.categories = categories
chart_data.add_series("", series_data)
return title, chart_data
def getDataFromColumn(self, fileRef):
book = xlrd.open_workbook(fileRef)
dataSheet = book.sheet_by_name(
AGGREGATE_SHEET_NAME) #throw error if nothing is returned?
self.chart_data = ChartData()
rawData = []
self.categories = []
self.categoryCount = []
self.numberOfDataPoints = dataSheet.nrows
colNum = self.colNum
for i in range(dataSheet.nrows - 1):
rawData.append(dataSheet.cell_value(rowx=i + 1, colx=colNum))
unicodeCategories = list(set(rawData))
self.categories = list(set(rawData))
for i in range(len(self.categories)):
self.categories[i] = str(self.categories[i]).upper()
for i in range(len(unicodeCategories)):
self.categoryCount.append(
sum(dataEntry == unicodeCategories[i]
for dataEntry in rawData))
print "categories : "
print self.categories
print "tuple(categorCount) : "
print tuple(self.categoryCount)
def create_pie_chart(details, base=None):
if len(details["categories"]) != len(details["percentages"]):
return False, "Couldn't create the pie chart as the number of categories and percentage values are not equal"
# prs = Presentation()
# slide = prs.slides.add_slide(prs.slide_layouts[5])
try:
prs = Presentation('./Final - Presentation.pptx')
except exc.PackageNotFoundError as err:
# print(f'No presentation file: {err}')
prs = Presentation(base)
slide = prs.slides.add_slide(prs.slide_layouts[10])
chart_data = CategoryChartData()
x, y, cx, cy = Inches(2), Inches(2), Inches(6), Inches(4.5)
chart_data = ChartData()
# chart_data.categories = ['West', 'East', 'North', 'South', 'Other']
# chart_data.add_series('Series 1', (0.135, 0.324, 0.180, 0.235, 0.126))
try:
percent_floats = [float(i) for i in details["percentages"]]
except ValueError as err:
print(f'Cannot extract number from percentages: {err}')
return False, "Couldn't create the pie chart due to invalid percentage values"
if sum(percent_floats) != 100.0 and sum(percent_floats) != 1.0:
return False, "Couldn't create the pie chart as percentages don't add up to 100"
if sum(percent_floats) == 100.0:
temp = percent_floats
percent_floats = [i / 100.0 for i in temp]
chart_data.add_series('Series 1', percent_floats)
chart_data.categories = details["categories"]
chart = slide.shapes.add_chart(XL_CHART_TYPE.PIE, x, y, cx, cy,
chart_data).chart
chart.has_legend = True
chart.legend.position = XL_LEGEND_POSITION.BOTTOM
chart.legend.include_in_layout = False
chart.plots[0].has_data_labels = True
data_labels = chart.plots[0].data_labels
data_labels.number_format = '0%'
data_labels.position = XL_DATA_LABEL_POSITION.OUTSIDE_END
if 'title' in details:
slide.placeholders[0].text = details['title'].title()
# chart.chart_title.text_frame.text = details['title'].title()
prs.save('Final - Presentation.pptx')
return True, "Created the Pie Chart"
def df_to_chartdata(df,datatype,number_format=None):
'''
根据给定的图表数据类型生成相应的数据
Chartdata:一般的数据
XyChartData: 散点图数据
BubbleChartData:气泡图数据
'''
if isinstance(df,pd.Series):
df=pd.DataFrame(df)
df.fillna(0,inplace=True)
datatype=datatype.lower()
if datatype == 'chartdata':
chart_data = ChartData()
chart_data.categories = ['%s'%(c) for c in list(df.index)]
for col_name in df.columns:
chart_data.add_series('%s'%(col_name),list(df[col_name]),number_format)
return chart_data
if datatype == 'xychartdata':
chart_data=XyChartData()
if not isinstance(df,list):
df=[df]
for d in df:
series_name='%s'%(d.columns[0])+' vs '+'%s'%(d.columns[1])
series_ = chart_data.add_series(series_name)
for i in range(len(d)):
series_.add_data_point(d.iloc[i,0], d.iloc[i,1])
return chart_data
if datatype == 'bubblechartdata':
chart_data=BubbleChartData()
if not isinstance(df,list):
df=[df]
for d in df:
series_name='%s'%(d.columns[0])+' vs '+'%s'%(d.columns[1])
series_ = chart_data.add_series(series_name)
for i in range(len(d)):
series_.add_data_point(d.iloc[i,0],d.iloc[i,1],d.iloc[i,2])
return chart_data
table.cell(1, 1).text = str(val_AI1[0])
table.cell(1, 2).text = str(val_AI1[1])
table.cell(1, 3).text = str(val_AI1[2])
table.cell(2, 0).text = name_AIs[1]
table.cell(2, 1).text = str(val_AI2[0])
table.cell(2, 2).text = str(val_AI2[1])
table.cell(2, 3).text = str(val_AI2[2])
table.cell(3, 0).text = name_AIs[2]
table.cell(3, 1).text = str(val_AI3[0])
table.cell(3, 2).text = str(val_AI3[1])
table.cell(3, 3).text = str(val_AI3[2])
# define chart data ---------------------
chart_data = ChartData()
chart_data.categories = name_objects
chart_data.add_series(name_AIs[0], val_AI1)
chart_data.add_series(name_AIs[1], val_AI2)
chart_data.add_series(name_AIs[2], val_AI3)
# add chart to slide --------------------
x, y, cx, cy = Cm(0.7), Cm(3.5), Cm(24), Cm(8)
graphic_frame = slide.shapes.add_chart(
XL_CHART_TYPE.COLUMN_CLUSTERED, x, y, cx, cy, chart_data
)
chart = graphic_frame.chart
chart.has_legend = True
def create_metric_slide(prs,metric):
#metric info
metric_name = metric['timeSeries'][0]['metadata']['metricName']
category = metric['timeSeries'][0]['metadata']['attributes']['category']
print metric_name
#determine if service, and send to IMPALA_QUERY function if an IMPALA_QUERY slide
if category == 'SERVICE':
service_name = metric['timeSeries'][0]['metadata']['attributes']['serviceDisplayName']
elif category == 'IMPALA_QUERY' and metric_name == 'query_duration':
create_impala_query_slides(prs,metric)
return
elif category == 'IMPALA_QUERY' and metric_name == 'hdfs_average_scan_range':
return
else:
service_name = 'Cluster'
#metric_name = metric['timeSeries'][0]['metadata']['metricName']
formatted_metric_name = ''
#format the metric name and capitalize first letters
metric_name_arr = metric_name.split("_")
for word in metric_name_arr:
formatted_metric_name += word.title() + " "
metric_name = formatted_metric_name
value_access_name = 'max'
unit_type = metric['timeSeries'][0]['metadata']['unitNumerators'][0]
numerator = metric['timeSeries'][0]['metadata']['unitNumerators'][0]
denominators = metric['timeSeries'][0]['metadata']['unitDenominators']
timestamps = deque()
values = deque()
points = ''
if not metric['timeSeries'][0]['data']:
points = metric['timeSeries'][1]['data']
else:
points = metric['timeSeries'][0]['data']
slide = prs.slides.add_slide(prs.slide_layouts[12])
for point in points:
formatted_timestamp = point['timestamp'].split("T")
timestamps.append(formatted_timestamp[0])
#max value doesn't exist for data points in SUM or INTEGRAL statistics, need to use value key
if point['type'] == 'CALCULATED':
values.append(point['value'])
elif unit_type == 'bytes':
#Read and write metrics in MB, otherwise in gigabytes
if 'read' in metric_name.lower() or 'writ' in metric_name.lower():
value_in_mb = float(point['aggregateStatistics'][value_access_name])/(1024*1024)
values.append(value_in_mb)
numerator = 'megabytes'
else:
value_in_gb = float(point['aggregateStatistics'][value_access_name])/(1024*1024*1024)
values.append(value_in_gb)
numerator = 'gigabytes'
else:
values.append(point['aggregateStatistics'][value_access_name])
#determine what our measurement (IE bytes/sec or just queries)
if len(denominators) > 0:
denominator = metric['timeSeries'][0]['metadata']['unitDenominators'][0]
measurement = numerator + '/' + denominator
else:
denominator = 'None'
measurement = numerator
# change title and properties
title = slide.shapes.title
title.text = service_name + " " + metric_name
title.text_frame.paragraphs[0].font.name = "calibri"
title.text_frame.paragraphs[0].font.size = Pt(28)
# define chart data ---------------------
chart_data = ChartData()
chart_data.categories = timestamps
chart_series_title = measurement
chart_data.add_series(chart_series_title, values)
# add chart to slide --------------------
x, y, cx, cy = Inches(2.25), Inches(1.4), Inches(9), Inches(6.0)
chart = slide.shapes.add_chart(XL_CHART_TYPE.LINE, x, y, cx, cy, chart_data)
# sets chart plot to all be the same color (default in PPT is all different colors by category)
plot = chart.chart.plots[0]
plot.vary_by_categories = False
# change axis properties
category_axis = chart.chart.category_axis
value_axis = chart.chart.value_axis
# change axis font properties
category_axis.tick_labels.font.name = "calibri"
category_axis.tick_labels.font.size = Pt(11)
value_axis.tick_labels.font.name = "calibri"
value_axis.tick_labels.font.size = Pt(11)
# change legend properties
chart.chart.has_legend = True
chart.chart.legend.include_in_layout = False
chart.chart.legend.font.name = "calibri"
chart.chart.legend.font.size = Pt(11)
return
def series_fixture(self, series_data_):
chart_data = ChartData()
chart_data._series_lst = [series_data_, series_data_]
expected_value = (series_data_, series_data_)
return chart_data, expected_value
def categories_get_fixture(self, categories):
chart_data = ChartData()
chart_data._categories = list(categories)
expected_value = categories
return chart_data, expected_value
# coding:utf-8
"""
python写pptx，demo
"""
from pptx import Presentation
from pptx.chart.data import ChartData
from pptx.enum.chart import XL_CHART_TYPE
from pptx.util import Inches, Pt
demo_pptx_path = r'C:\Users\stephen\Desktop\1.pptx'
prs = Presentation(demo_pptx_path)
slide = prs.slides.add_slide(prs.slide_layouts[6])
chart_data = ChartData()
chart_data.categories = ['east', 'west', 'midwest']
chart_data.add_series('series1', (19.2, 21.4, 16.7))
x, y, cx, cy = Inches(2), Inches(2), Inches(6), Inches(4.5)
graphic_frame = slide.shapes.add_chart(
XL_CHART_TYPE.COLUMN_CLUSTERED, x, y, cx, cy, chart_data
)
chart = graphic_frame.chart
# chart.font.size = Pt(13)
prs.save(r'C:\Users\stephen\Desktop\chart-01.pptx')
from pptx import Presentation
from pptx.chart.data import ChartData
from pptx.enum.chart import XL_CHART_TYPE
from pptx.util import Inches
# create presentation with 1 slide ------
prs = Presentation()
slide = prs.slides.add_slide(prs.slide_layouts[5])
# define chart data ---------------------
chart_data = ChartData()
chart_data.categories = ['East', 'West', 'Midwest']
chart_data.add_series('Series 1', (19.2, 21.4, 16.7))
# add chart to slide --------------------
x, y, cx, cy = Inches(2), Inches(2), Inches(6), Inches(4.5)
slide.shapes.add_chart(
XL_CHART_TYPE.COLUMN_CLUSTERED, x, y, cx, cy, chart_data
)
prs.save('chart-01.pptx')