报表的自动化探索（自动报表包括）
“ 引言 ”
报表是日常工作中主要的一项，报表自动化是提升工作效率的一种重要方式。随着共享文档的出现与普及，大大简化了报表的收集汇总这一繁琐的过程。但是在一些对保密要求较高的场景，往往不方便使用共享文档，因此大多还停留在手动汇总的阶段。本文旨在通过使用 Python ，为这些场景提供一些报表自动化的尝试与可能性。
难度：
小工具系列文章：
一、excel 表格的自动化汇总
在日常报表中，excel 的表格汇总是较常出现且繁琐的场景。如图所示，我们这边尝试实现多个文件的汇总与分 Sheet 保存。
1、思路
① 读取每个文件的数据
② 每个文件写入到汇总表的分页，将分页修改成各个文件名
③ 将所有文件的数据合并，存放至汇总页
2、相关依赖库安装与导入
1） openpyxl
① 安装：
pip install -i
https://pypi.tuna.tsinghua.edu.cn/simple openpyxl
② 导入：
from openpyxl import load_workbook
2） pandas
① 安装
pip install -i https://pypi.tuna.tsinghua.edu.cn/simple pandas
② 导入：
import pandas as pd
3、准备报表模板
定好报表模板，汇总 Sheet 命名为汇总。
4、实现方案
1、使用 openpyxl 实现
1）读取所有 excel 文件名
# 获取excel文件夹内所有文件名
def find_excel(excel_dir_path):
excel_names = [] # 存放excel名字
filenames = os.listdir(excel_dir_path)
for filename in filenames:
if filename.find('xls') != -1:
excel_names.append(filename)
print(f'excel文件名：{excel_names}')
return excel_names运行结果：
excel文件名：['东莞.xlsx', '广州.xlsx', '深圳.xlsx']
2）提取所有excel文件内容，暂存到list并返回
def get_excel_data(excel_names, excel_dir_path):
print('正在提取，请稍后')
file_count = 0
all_excel_data = [] # 存放所有文件的数据
for excel_name in excel_names:
# 判断文件是否存在
excel_path = excel_dir_path + '\\' + excel_name
if os.path.exists(excel_path) is False:
print(excel_path + ' 文件不存在，请重试')
continue
file_count += 1
data = load_workbook(excel_path) # 打开excel文件
sheet = data['Sheet1'] # 打开sheet
start_row = 1 # 从第二行开始（第一行为表头）
excel_data = [] # 存放单个文件的数据
for i in range(1, sheet.max_row + 1)[start_row:]:
row_data = [] # 存放单行文件的数据
for j in range(1, sheet.max_column + 1):
row_data.append(sheet.cell(i, j).value)
excel_data.append(row_data)
all_excel_data.append(excel_data)
print('提取完成')
print(f'总文件数：{file_count}')
print(f'总数据量：{len(all_excel_data)}条')
return all_excel_data运行结果：
正在提取，请稍后
提取完成
总文件数：3
总数据量：3条
3）追加到汇总表,同时分 Sheet
def summary_data(summary_file_path, excel_names, all_excel_data):
if os.path.exists(summary_file_path) is False:
print(f'{summary_file_path} 文件不存在，请重试')
exit()
summary_file = load_workbook(summary_file_path)
summary_sheet = summary_file['汇总']
table_name = [] # 存放表头
# 获取汇总页表头
for t in range(1, summary_sheet.max_column + 1):
table_name.append(summary_sheet.cell(1, t).value)
# 将数据同时保存至汇总Sheet和各分Sheet
for num, e in enumerate(all_excel_data):
# 新建分Sheet，文件名去掉后缀
sep_sheet = summary_file.create_sheet(excel_names[num].split('.')[0], 0)
sep_sheet.append(table_name) # 填充表头
for row in e:
summary_sheet.append(row)
sep_sheet.append(row)
summary_file.save(summary_file_path)2、使用 Pandas 实现
在制作报表时如需对数据进行二次加工，使用 Pandas 会更方便，下面提供 Pandas 实现方案。
excel_dir_path = './excel_file'
summary_file_path = '汇总表.xlsx'
# 读取所有文件名
excel_names = find_excel(excel_dir_path)
# pd.ExcelWriter 可使用 engine="xlsxwriter"或"openpyxl" 实现excel读写功能
summary_file = pd.ExcelWriter(summary_file_path)
df_summary = pd.DataFrame() # 存放所有数据
for excel_name in excel_names:
# 判断文件是否存在
excel_path = excel_dir_path + '\\' + excel_name
if os.path.exists(excel_path) is False:
print(excel_path + ' 文件不存在，请重试')
continue
df_sep = pd.read_excel(excel_path) # 读取每个excel数据
# 保存分页
df_sep.to_excel(summary_file,
sheet_name=excel_name.split('.')[0],
index=False,
encoding="gbk")
# 将分页内容合并到汇总的 Dataframe
df_summary = pd.concat([df_summary, df_sep], axis=0)
# 汇总页重建索引
df_summary.reset_index(drop=True, inplace=True)
# 保存汇总页
df_summary.to_excel(summary_file,
sheet_name="汇总",
index=False,
encoding="gbk")
summary_file.save() # 汇总表保存五、实现效果
二、PPT 与 Excel 的自动化整理
在日常报表中，也经常会遇到将 excel 的表格数据整理 PPT 报表中的场景。如图所示，我们这边尝试 PPT 报表数据的更新和周环比的计算。
1、python-pptx 库的安装与导入
① 安装：
pip install -i https://pypi.tuna.tsinghua.edu.cn/simple python-pptx
② 导入：
from pptx import Presentation
from pptx.enum.shapes import MSO_SHAPE_TYPE
from pptx.util import Pt
from pptx.enum.text import PP_ALIGN
from openpyxl import load_workbook2、代码
1）获取 excel 数据
def get_excel_data(excel_path):
excel_data = [] # 存放所有文件的数据
data = load_workbook(excel_path) # 打开excel文件
sheet = data['Sheet1'] # 打开sheet
start_row = 1 # 从第二行开始（第一行为表头）
for i in range(1, sheet.max_row + 1)[start_row:]:
row_data = [] # 存放单行文件的数据
for j in range(1, sheet.max_column + 1):
row_data.append(sheet.cell(i, j).value)
excel_data.append(row_data)
return excel_data2）主程序
ppt_path = '报表.pptx'
# 打开ppt文件
prs = Presentation(ppt_path)
# 获取excel数据
excel_data = get_excel_data('报表数据.xlsx')
# slides_num为第几页ppt，slice为该页ppt的内容
for slides_num, slice in enumerate(prs.slides):
for shape in slice.shapes:
if shape.shape_type == MSO_SHAPE_TYPE.TABLE: # 判断元素是否为表格
_table = shape.table
rows = len(_table.rows) # 获取行数
cols = len(_table.columns) # 获取列数
for row_num in range(1, rows):
# 计算周环比，并追加到excel_data
t0_value = int(_table.cell(row_num, 2).text_frame.text)
data = int(excel_data[row_num - 1][2]) - t0_value
excel_data[row_num - 1].append(data)
for col_num in range(0, cols):
# 计算周环比
_table.cell(row_num, col_num).text = str(excel_data[row_num - 1][col_num]) # 填充内容
_table.cell(row_num, col_num).text_frame.paragraphs[0].font.size = Pt(11) # 设置字体大小
_table.cell(row_num, col_num).text_frame.paragraphs[0].font.name = '微软雅黑'
_table.cell(row_num, col_num).text_frame.paragraphs[0].alignment = PP_ALIGN.CENTER # 居中
elif shape.shape_type == MSO_SHAPE_TYPE.TEXT_BOX: # 判断元素是否为文本框
text_ud = lambda a: "上升" if a >= 0 else "下降"
# 填充文本框内容
texts = ''
for i, data in enumerate(excel_data):
text = f'{i+1}、{data[1]}指标1：{data[2]}，' \
f'较上周{text_ud(float(data[3]))}' \
f'{str(abs(data[3]))}\n\n'
texts = texts + text
shape.text = texts
# 设置文本框格式
for i in range(6):
shape.text_frame.paragraphs[i].font.size = Pt(14) # 设置字体大小
shape.text_frame.paragraphs[i].font.name = '微软雅黑'
prs.save(ppt_path)3、运行结果：
三、小结
通过 Python 进行报表的自动化整理，从便利性而言远不及在线文档，只在一些特定场景有利于提升效率，需视场景选择。
大家平时被烦人报表折磨吗？欢迎在评论区 留言 偷偷吐槽 ~
（你不说我不说，老板就不会发现）