如何让Excel里的数据自动跑到PPT上去？
2022-04-17 本文已影响0人
Wi先森
引言
最近遇到这样的需求，一份通过Excel手动整理的数据需要可视化到PPT上，PPT可给到销售和市场用作对外材料。
一般而言，Excel数据应该在Excel本身实现可视化，再把可视化结果复制或截图到其他使用场景。但我在实际测试这一做法时却发现，如果把Excel里面做好的可视化结果直接复制到PPT或者Word上都是会变形的，如果是截图的话又不利于二次编辑。无奈之下，只能硬着头皮用python实现。
好，废话不多说，上代码
库引入
这次主要用到的是xlwings和python-pptx，前者用于从Excel中读取数据，是python与Excel数据互通的利器，我之前还用这个表实现过文本打标签功能；后者用于对PPT中的内容进行创建或修改，针对这一次需求我主要使用的是修改部分的功能。感兴趣的读者可参考文章底部有官方链接详细了解。
from pptx import Presentation
from pptx.chart.data import CategoryChartData as ChartData
import xlwings as xw
import pandas as pd
读取Excel
建议将Excel读取封装为一个函数，因为PPT中有以下几种不同类型的对象：
- 文本框：一般是标题，正文等描述内容，比如XXX报告 2022年4月，那么XXX和后面的日期就是变量
- 表格：读取出来的DataFrame可以比较好写入，入参对应了不同页的表格
- 图表：图形中的数据对应为一个或多个Series，跟echarts有点类似，数据是list类型
函数如下：
def read_from_excel(data_select):
wb = xw.Book.caller()
sheet = wb.sheets['Sheet1'] # 指定sheet
if data_select == 'page4_table':
shape = sheet.range('Q4:T18') # 获取数据范围，返回行、列
collist = ['city','rank','store','rate']
table_data = shape.value # 按行列读取数据
df_table = pd.DataFrame.from_records(table_data) # 写入df进行处理
df_table.columns = collist
for index, row in df_table.iterrows():
df_table.loc[index:index,'rate'] = str(round(row['rate'] * 100,1)) + '%' # 百分比格式处理
df_table.loc[index:index,'store'] = '{:,}'.format(round(row['store'])) # Number类型取整，加上千位符
if data_select == 'page5_table':
shape = sheet.range('V4:Y18') # 获取数据范围，返回行、列
collist = ['city','rank','store','rate']
table_data = shape.value # 按行列读取数据
df_table = pd.DataFrame.from_records(table_data) # 写入df进行处理
df_table.columns = collist
for index, row in df_table.iterrows():
df_table.loc[index:index,'rate'] = str(round(row['rate'] * 100,1)) + '%' # 百分比格式处理
df_table.loc[index:index,'store'] = '{:,}'.format(round(row['store'])) # Number类型取整，加上千位符
if data_select == 'page7_table':
shape = sheet.range('AA4:AB18') # 获取数据范围，返回行、列
collist = ['city','rank']
table_data = shape.value # 按行列读取数据
df_table = pd.DataFrame.from_records(table_data) # 写入df
df_table.columns = collist
return df_table
# 函数调用前，要先把文件读进来，xlwings在工作时，Excel会同时打开
xw.Book("3月榜单-20220407.xlsx").set_mock_caller()
wb = xw.Book.caller()
sheet = wb.sheets['Sheet1'] # 指定sheet
report_date = sheet.range('B1').value # 读取单个单元格：报告日期
report_theme = sheet.range('C1').value # 读取单个单元格：报告主题
report_data_date = sheet.range('D1').value # 读取单个单元格：数据日期
top15_brand = sheet.range('C7:C18').value # 读取范围值，这里是一列
top15_brand_store = sheet.range('D4:D18').value
top15_brand_store = sorted(top15_brand_store)
store_open_count1 = sheet.range('I4').value
store_open_count2 = sheet.range('I5').value
store_open_count3 = sheet.range('I6').value
store_open_count4 = sheet.range('I11').value
top15_brand_open = sheet.range('G7:G18').value # 需确定前三品牌是否要使用图片，现在写的是4--15品牌名称
store_close_count1 = sheet.range('N4').value
store_close_count2 = sheet.range('N5').value
store_close_count3 = sheet.range('N6').value
store_close_count4 = sheet.range('N7').value
top15_brand_close = sheet.range('M7:M18').value # 需确定前三品牌是否要使用图片，现在写的是4--15品牌名称
page4_table = read_from_excel('page4_table') # 读取PPT第4页要用到的数据
page5_table = read_from_excel('page5_table') # 读取PPT第5页要用到的数据
提前处理变量值（代码略）
在这里需要注意的是，从Excel中提取的信息对应的是PPT上需要变化的内容，但一个变化的内容，比如报告时间常常会在PPT中的多种页使用。
因此这一步骤的重点在于把Excel输出的变量，转换为PPT上每一页要修改的内容，以下代码只保留结构，相关报告实际内容已隐去。
# 1 榜单名称
page1_ranklist_name = '{}榜单'.format(report_theme)
# 1 榜单时间
page1_ranklist_date = report_date
# 2 标题(含时间)
page2_title1 = '产品名称 · 报告名称 · {}'.format(report_date)
# 2 标题(含业态)
page2_title2 = '{}·门店数量TOP15品牌'.format(report_theme)
# 2 数据说明(含时间)
page2_datasource_spec = """1、数据来源：互联网公开平台、品牌官方网站 2、门店数量统计截止时间：{}""".format(report_data_date)
# 2 品牌名称(4--15名)
page2_brand_list = top15_brand
# 2 数据
page2_chart_data = top15_brand_store
# 3 标题
page3_title1 = '产品名称 · 报告名称 · {}'.format(report_date)
page3_title2 = '{}·开门店数量'.format(report_theme)
# 3 开店量级
page3_new1 = store_open_count1
page3_new2 = store_open_count2
page3_new3 = store_open_count3
page3_new4 = store_open_count4
# 3 品牌名称
page3_brand_list = top15_brand_open
page3_datasource_spec = """1、数据来源：互联网公开平台、品牌官方网站 2、门店数量统计截止时间：{}""".format(report_data_date)
# 4 标题
page4_title1 = '产品名称 · 报告名称 · {}'.format(report_date)
page4_title2 = '{}·门店数量TOP15城市'.format(report_theme)
page4_title3 = '{}门店数'.format(str(int(report_date[5:6])-1) + '月') # 这个写法不严谨，后续优化
page4_datasource_spec = """1、数据来源：互联网公开平台、品牌官方网站 2、门店数量统计截止时间：{}""".format(report_data_date)
# 4 表格
page4_table = page4_table
# 5 标题
page5_title1 = '产品名称 · 报告名称 · {}'.format(report_date)
page5_title2 = '{}·门店数量增长率'.format(report_theme)
page5_title3 = '{}门店数'.format(str(int(report_date[5:6])-1) + '月') # 这个写法不严谨，后续优化
page5_datasource_spec = """1、数据来源：互联网公开平台、品牌官方网站 2、门店数量统计截止时间：{}""".format(report_data_date)
# 5 表格
page5_table = page5_table
了解python-pptx解析结构
要把数据写入到ppt文件中，首先要知道python-pptx是如何对内容进行解析的，通过文档了解不够直观，因此可以先写一个循环把现有ppt内容读取出来：
prs = Presentation(r"/文件路径/文件名.pptx")
# 查看pptx解析情况
for i, slide in enumerate(prs.slides): # 遍历每一页
print('\n=====第{}页====='.format(i+1))
for shape in slide.shapes: # 每一页里面的元素都是一个shape
print('shape: {}'.format(shape.name))
if shape.has_text_frame: # 解析文本框
for paragraph in shape.text_frame.paragraphs:
print('paragraph: {}'.format(paragraph.text))
for run in paragraph.runs:
print('run: ',run.text)
if shape.has_chart: # 解析图表
print('chart_type: ',shape.chart.chart_type)
print('values: ',shape.chart.plots[0].series[0].values)
if shape.has_table: # 解析表格
for row in shape.table.rows:
for cell in row.cells:
print('cell text: ',cell.text)
print('cell margin_left: ',cell.margin_left)
=====第1页=====
shape: 图形 4
shape: 文本框 5
paragraph: 产品名称 I 报告名称
run: 产品名称
run: I
run: 报告名称
shape: 文本框 6
paragraph: XXX榜单
run: XXX榜单
paragraph:
shape: 文本框 8
paragraph: 2022年N月
run: 2022
run: 年
run: N
run: 月
shape: 矩形 12
paragraph:
=====第2页=====
shape: 文本框 15
paragraph: 产品名称 · 报告名称 · 2022年2月
run: 产品名称
run: ·
run: 报告名称
run: · 2022
run: 年
run: 2
run: 月
从略，大家根据自己的实际解析结果进行分析。
通过分析可以发现一个奇怪的地方，原本一个文本框里面的文字会按照中文、数字、标点拆成多个text，比如：
shape: 文本框 15
paragraph: 产品名称 · 报告名称 · 2022年3月
run: 产品名称
run: ·
run: 报告名称
run: · 2022
run: 年
run: 3
run: 月
这意味着你不能对整个paragraph的text进行赋值，会把文本框附带的格式清空；也不能只对其中一个run的text进行修改，因为后续的text会保留导致内容写得不对。
同理一个表格内的单元格也是文本框，因此可以对这两种情况的处理封装成函数：
prs = Presentation(r"/文件路径/文件名.pptx")
# 修改文本框函数
def modify_text_frame(shape, pnum, rtext):
pi = 0
for paragraph in shape.text_frame.paragraphs: # 遍历paragraph
if pi == pnum: # 有的文本框中存在多个paragraph，只对要修改的进行操作，默认为 0
r = 0
for run in paragraph.runs: # 遍历paragraph下的run，只修改第一个，后面置空
if r == 0:
run.text = rtext
else:
run.text = ''
r = r + 1
pi = pi + 1
# 修改单元格文本函数(与上述函数同理)
def modify_cell_text(cell, pnum, rtext):
pi = 0
for paragraph in cell.text_frame.paragraphs:
if pi == pnum:
r = 0
for run in paragraph.runs:
if r == 0:
run.text = rtext
else:
run.text = ''
r = r + 1
pi = pi + 1
搞定变量和部分写入函数之后，可以开始编写修改ppt内容的代码了，我的做法是一页一页地去改，这样代码结构看起来会更为清晰。
因为ppt本身并不能对各种对象命名，所以只能代码中hard code很多“文本框 XX”，所以这里需要注意的是，只有当ppt的整体内容相对固定时才能做此自动化，而后续每次ppt的内容修改后，以下代码也都要相应修改才能使用。
最后只要把修改好的pptx保存到相应位置就可以了。
# 修改内容
for i, slide in enumerate(prs.slides):
if i == 0: # page 1
for shape in slide.shapes:
if shape.has_text_frame:
if shape.name == '文本框 6':
modify_text_frame(shape, 0, page1_ranklist_name)
if shape.name == '文本框 8':
modify_text_frame(shape, 0, page1_ranklist_date)
if i == 1: # page 2
for shape in slide.shapes:
if shape.has_text_frame:
if shape.name == '文本框 15':
modify_text_frame(shape, 0, page2_title1)
if shape.name == '文本框 16':
modify_text_frame(shape, 0, page2_title2)
if shape.name == '文本框 2':
modify_text_frame(shape, 3, page2_datasource_spec)
if shape.name == '文本框 46':
brand_num = 0
for brand in page2_brand_list:
modify_text_frame(shape, brand_num, brand)
brand_num = brand_num + 1
if shape.has_chart:
chart_data = ChartData()
chart_data.categories = shape.chart.plots[0].categories
chart_data .add_series('Series 1',page2_chart_data)
shape.chart.replace_data(chart_data)
# 部分代码从略
pptxFilepath = r"/Users/januswing/Dropbox/DataStory/数据榜单/表单_output.pptx"
prs.save(pptxFilepath)
后记
老实说，作为一个大半桶水研发 + 半桶水产品的人来说，我在接到这个需求的第一反应是，为什么不在数据库里面把所有数据都处理好，然后再通过前端把各个看板和表格固定下来呢？又或者直接在Excel中实现可视化效果，然后通过一个插件把Excel中的可视区域导出为PDF。从可复用、可自动化、不限制Excel和ppt内容修改的角度来看，现在的xlwings+pptx都不能满足（而且还不能迁移到完全不懂技术的同事电脑上去跑）。
但在实际工作的情况中，数据并不能一次性全部准备好，链路还没跑通，更也无法形成接口。而所谓的ppt固化模板也只是第一版，为了节省后续人工，你不得不做一个简单的自动化工具以解燃眉之急。这时只能想方设法在python体系里挖宝，把原来要人工做一个小时的事情简化为一分钟，那也可以为你节省未来的N个小时。这就是python办公自动化的优势。
