import xlrd
from pptx import *
from pptx.util import Pt
from pptx.dml.color import RGBColor
from pptx.chart.data import ChartData
contr_col = 0
contr_row = 0
xl = xlrd.open_workbook('11月分报告数据.xlsx')
prs = Presentation('temp.pptx')
# 幻灯片1
slide = prs.slides[0]
table = xl.sheet_by_name("P1")
ppt_name = table.cell(contr_row + 1, contr_col).value
shape = slide.shapes[0]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = ppt_name
font = run.font
# font.name = 'Calibri'
font.size = Pt(62)
font.bold = True
font.italic = None # cause value to be inherited from theme
font.color.rgb = RGBColor(0, 0, 0)
# 幻灯片2
slide = prs.slides[1]
table = xl.sheet_by_name("P2")
# 处理文本
# 标题
shape = slide.shapes[0]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = ppt_name + '-得分情况'
font = run.font
font.bold = True
# 项目成绩标题
result = table.cell(contr_row + 1, contr_col + 2).value
text_frame = slide.shapes[1].text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
if result > table.cell(13, 2).value:
run.text = '神秘顾客暗访综合成绩为' + str(result) + '分，高于集团水平。'
elif result < table.cell(13, 2).value:
run.text = '神秘顾客暗访综合成绩为' + str(result) + '分，低于集团水平。'
else:
run.text = '神秘顾客暗访综合成绩为' + str(result) + '分，等于集团水平。'
font = run.font
font.size = Pt(12)
font.bold = False
text_name1 = table.cell(contr_row + 1, contr_col + 5).value
text_name2 = table.cell(contr_row + 1, contr_col + 6).value
text_frame = slide.shapes[3].text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = '电话接听人员姓名：' + text_name1.strip() + '\n\n现场接待人员姓名：' + text_name2.strip()
font = run.font
font.bold = True
font.size = Pt(10)
# 处理图表
shape = slide.shapes[2]
chart = shape.chart
chart_data = ChartData()
chart_data.categories = ['集团', ppt_name]
chart_data.add_series('项目总得分', (table.cell(13, 2).value, table.cell(contr_row + 1, contr_col + 2).value))
chart.replace_data(chart_data)
# 幻灯片3
slide = prs.slides[2]
table = xl.sheet_by_name("P3")
shape = slide.shapes[0]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = ppt_name + '与上期成绩比较'
font = run.font
font.bold = True
shape = slide.shapes[1]
chart = shape.chart
chart_data1 = ChartData()
cat = []
s1 = [] # 10月
s2 = [] # 11月
for i in range(2, 11):
s = table.cell(31, i).value
if i > 6:
cat.append(s[2:])
else:
cat.append(s[3:])
s1.append(table.cell(16 + contr_row, i).value)
s2.append(table.cell(33 + contr_row, i).value)
chart_data1.categories = cat
chart_data1.add_series('11月', s2)
chart_data1.add_series('10月', s1)
chart.replace_data(chart_data1)
dt = slide.shapes[2].table
for i in range(len(s1)):
p = dt.cell(0, i).text_frame.paragraphs[0]
p.clear()
run = p.add_run()
run.text = str(round(s2[i] - s1[i], 1))
font = run.font
font.size = Pt(10)
font.color.rgb = RGBColor(0x00, 0xcd, 0xcd)
total_result = 0
pass_result = 0
indiv_result = "从各模块看，"
for j in range(len(cat)):
total_result = total_result + s2[j]
pass_result = pass_result + s1[j]
if (s2[j] - s1[j]) > 0:
indiv_result = indiv_result + cat[j] + '提升' + str(round(s2[j] - s1[j], 1)) + '分；'
elif (s2[j] - s1[j]) < 0:
indiv_result = indiv_result + cat[j] + '降低' + str(round(s1[j] - s2[j], 1)) + '分；'
s = ""
if (total_result - pass_result) > 0:
s = '本期' + ppt_name + '神秘顾客暗访综合成绩为' + str(round(total_result, 1)) + '分，相比上期提升' \
+ str(round(total_result - pass_result, 1)) + '分；'
elif (total_result - pass_result) < 0:
s = '本期' + ppt_name + '神秘顾客暗访综合成绩为' + str(round(total_result, 1)) + '分，相比上期降低' \
+ str(round(pass_result - total_result, 1)) + '分；'
else:
s = '本期' + ppt_name + '神秘顾客暗访综合成绩为' + str(round(total_result, 1)) + '分，相比上期持平'
s = s + '\n' + indiv_result
text_frame = slide.shapes[6].text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = s
font = run.font
font.bold = False
font.size = Pt(12)
# 幻灯片4
slide = prs.slides[3]
shape = slide.shapes[0]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = ppt_name + '-得分情况'
font = run.font
font.bold = True
standard=[10.0,6.0,9.0,28.6,20.0,10.4,4.0,6.0,6.0]
lose_point=[]
for i in range(len(s2)):
lose_point.append(round(standard[i]-s2[i],1))
s='从各维度表现上看，'
flag=True
for k in range(len(lose_point)):
if int(lose_point[k]) != 0:
flag = False
s = s + cat[k]+'失去'+str(lose_point[k])+'分；'
if flag :
s = '从各维度表现上看，各环节均未失分。'
text_frame = slide.shapes[1].text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = s
font = run.font
font.bold = False
font.size = Pt(12)
chart=slide.shapes[2].chart
chart_data = ChartData()
chart_data.categories = cat
chart_data.add_series('项目得分', s2)
chart_data.add_series('满分', standard)
chart_data.add_series('失分点', lose_point)
chart.replace_data(chart_data)
# 幻灯片5
slide = prs.slides[4]
shape = slide.shapes[0]
text_frame = shape.text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = ppt_name + '-得分情况'
font = run.font
font.bold = True
group_result=[9.8,6.0,7.0,27.6,20.0,10.3,4.0,5.7,5.5]
chart=slide.shapes[3].chart
chart_data = ChartData()
chart_data.categories = cat
chart_data.add_series('项目得分', s2)
chart_data.add_series('集团均值', group_result)
chart.replace_data(chart_data)
lose_point=[]
for i in range(len(s2)):
lose_point.append(round(s2[i]-group_result[i],1))
s='对比集团成绩，'
m = 0
n = 0
p = 0
for k in range(len(lose_point)):
if lose_point[k] < 0:
m += 1
s = s + cat[k]+'低于集团'+str(lose_point[k])+'分；'
elif lose_point[k] > 0:
n += 1
s = s + cat[k] + '高于集团' + str(lose_point[k]) + '分；'
else: p += 1
if n== (len(lose_point)-1):
s = '对比集团成绩，各环节均高于集团水平。'
if m== (len(lose_point)-1):
s = '对比集团成绩，各环节均低于集团水平。'
if p== (len(lose_point)-1):
s = '对比集团成绩，各环节均和集团水平持平。'
text_frame = slide.shapes[2].text_frame
text_frame.clear()
p = text_frame.paragraphs[0]
run = p.add_run()
run.text = s
font = run.font
font.bold = False
font.size = Pt(12)
prs.save(str(ppt_name) + '.pptx')
