“ 引言 ”

报表是日常工作中主要的一项，报表自动化是提升工作效率的一种重要方式。随着共享文档的出现与普及，大大简化了报表的收集汇总这一繁琐的过程。但是在一些对保密要求较高的场景，往往不方便使用共享文档，因此大多还停留在手动汇总的阶段。本文旨在通过使用 Python ，为这些场景提供一些报表自动化的尝试与可能性。

难度：????

小工具系列文章：

1、如何使用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_workbook

2、代码

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_data

2）主程序

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 进行报表的自动化整理，从便利性而言远不及在线文档，只在一些特定场景有利于提升效率，需视场景选择。

大家平时被烦人报表折磨吗？欢迎在评论区 留言 偷偷吐槽 ~

（你不说我不说，老板就不会发现）