来自小z
欢迎关注 ，专注Python、数据分析、数据挖掘、好玩工具！

这篇文章将带你了解报表自动化的流程，并教你用 Python 实现工作中的一个报表自动化实战，篇幅较长，建议先收藏，喜欢点赞支持，文末提供技术交流群，文章具体的目录为：

1.Excel的基本组成
2.一份报表自动化的流程
3.报表自动化实战
- 当日各项指标同环比情况

• 当日各省份创建订单量情况
• 最近一段时间创建订单量趋势
  4.将不同的结果进行合并
• 将不同结果合并到同一个Sheet中
• 将不同结果合并到同一个工作簿的不同Sheet中

Excel的基本组成

我们一般在最开始做报表的时候，基本都是从Excel开始的，都是利用Excel在做报表，所以我们先了解下Excel的基本组成。

下图是Excel的中各个部分的组成关系，我们工作中每天会处理很多Excel文件，一个Excel文件其实就是一个工作簿。你在每次新建一个Excel文件时，文件名都会默认是工作簿x，其中x就是你新建的文件个数。而一个工作簿里面又可以有多个Sheet，不同Sheet之间是一个独立的表。每一个Sheet里面又由若干个单元格组成。每一个单元格又有若干的元素或属性，我们一般针对Excel文件进行设置最多的其实就是针对单元格的元素进行设置。

而针对单元格元素进行设置的主要内容其实就是如下图菜单栏中显示，比如字体、对齐方式、条件格式等内容。本书也是按照Excel菜单栏中的各个模块进行编写。

一份自动化报表的流程

下图是我整理的做一份自动化报表需要经历的流程，主要分为5个步骤：

第一步是对要做的报表进行步骤拆解，这个步骤拆解和用不用工具或者是用什么工具没有直接关系，比如做报表的第一步一般都是收集数据，这个数据可能是线下人员记录在纸质笔记本上的，也可能是存储在Excel表里面的，还有可能是存储在数据库里面的。会因为数据源的类型或者是存储方式不同，对应的收集数据方式会不一样，但是收集数据这个步骤本身是不会变的，这个步骤的目的就是把数据收集过来。

第二步是去想第一步里面涉及到的每一个具体步骤对应的代码实现方式，一般都是去找对应每一步的代码，比如导入数据的代码是什么样的，再比如重复值删除的代码是什么样的。

第三步是将第二步中各个步骤对应的代码进行组合，组合成一个完整的代码。

第四步是对第三步完整代码得出来的报表结果进行验证，看结果是否正确。

第五步就是等待调用，看什么时候需要制作报表了，然后就将写好的代码执行一遍就行。

其实报表自动化本质上就是让机器代替人工做事情的过程，我们只需要把我们人工需要做的每一个步骤转化成机器可以理解的语言，也就是代码，然后让机器自动去执行，这其实就是实现了自动化。

报表自动化实战

这一节给大家演示下在实际工作中如何结合Pandas和openpyxl来自动化生成报表。

假设我们现在有如下一份数据集：

现在我们需要根据这份数据集来制作每天的日报情况，会主要包含三方面：

• 当日各项指标的同环比情况；

• 当日各省份创建订单量情况；

• 最近一段时间创建订单量趋势

接下来分别来实现这三部分。

当日各项指标的同环比情况：

我们先用Pandas对数据进行计算处理，得到各指标的同环比情况，具体实现代码如下：

#导入文件
import pandas as pd
df = pd.read_excel(r'D:\Data-Science\share\excel-python报表自动化\sale_data.xlsx')

#构造同时获取不同指标的函数
def get_data(date):   
    create_cnt = df[df['创建日期'] == date]['order_id'].count()
    pay_cnt = df[df['付款日期'] == date]['order_id'].count()
    receive_cnt = df[df['收货日期'] == date]['order_id'].count()
    return_cnt = df[df['退款日期'] == date]['order_id'].count()
    return create_cnt,pay_cnt,receive_cnt,return_cnt
    
#假设当日是2021-04-11
#获取不同时间段的各指标值
df_view = pd.DataFrame([get_data('2021-04-11')
                     ,get_data('2021-04-10')
                     ,get_data('2021-04-04')]
                     ,columns = ['创建订单量','付款订单量','收货订单量','退款订单量']
                     ,index = ['当日','昨日','上周同期']).T

df_view['环比'] = df_view['当日'] / df_view['昨日'] - 1
df_view['同比'] = df_view['当日'] / df_view['上周同期'] - 1
df_view

运行上面代码会得到如下结果：

上面只是得到了各指标的同环比绝对数值，但是我们一般的日报在发出去之前都要做一些格式调整的，比如调整字体之类的。而格式调整就需要用到openpyxl库，我们需要将Pandas库中DataFrame格式的数据转化为适用openpyxl库的数据格式，具体实现代码如下：

from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows

#创建空工作簿
wb = Workbook()
ws = wb.active

#将DataFrame格式数据转化为openpyxl格式
for r in dataframe_to_rows(df_view,index = True,header = True):
    ws.append(r)

wb.save(r'D:\Data-Science\share\excel-python报表自动化\核心指标_原始.xlsx')

运行上面代码会得到如下结果，可以看到原始的数据文件看起来是很混乱的：

接下来我们针对上面原始数据文件进行格式调整，具体调整代码如下：

from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.styles import colors
from openpyxl.styles import Font
from openpyxl.styles import PatternFill
from openpyxl.styles import Border, Side
from openpyxl.styles import Alignment

wb = Workbook()
ws = wb.active

for r in dataframe_to_rows(df_view,index = True,header = True):
    ws.append(r)
    
#第二行是空的，删除第二行
ws.delete_rows(2)

#给A1单元格进行赋值
ws['A1'] = '指标'

#插入一行作为标题行
ws.insert_rows(1)
ws['A1'] = '电商业务方向 2021/4/11 日报'

#将标题行的单元格进行合并
ws.merge_cells('A1:F1') #合并单元格

#对第1行至第6行的单元格进行格式设置
for row in ws[1:6]:
    for c in row:
        #字体设置
        c.font = Font(name = '微软雅黑',size = 12)
        #对齐方式设置
        c.alignment = Alignment(horizontal = "center")
        #边框线设置
        c.border = Border(left = Side(border_style = "thin",color = "FF000000"),
                   right = Side(border_style = "thin",color = "FF000000"),
                   top = Side(border_style = "thin",color = "FF000000"),
                   bottom = Side(border_style = "thin",color = "FF000000"))

#对标题行和表头行进行特殊设置
for row in ws[1:2]:
    for c in row:
        c.font = Font(name = '微软雅黑',size = 12,bold = True,color = "FFFFFFFF")
        c.fill = PatternFill(fill_type = 'solid',start_color='FFFF6100')

#将环比和同比设置成百分比格式        
for col in ws["E":"F"]:
    for r in col:
        r.number_format = '0.00%'

#调整列宽
ws.column_dimensions['A'].width = 13
ws.column_dimensions['E'].width = 10

#保存调整后的文件        
wb.save(r'D:\Data-Science\share\excel-python报表自动化\核心指标.xlsx')

运行上面代码会得到如下结果：

可以看到各项均已设置成功。

当日各省份创建订单量情况：

我们同样先利用Pandas库处理得到当日各省份创建订单量情况，具体实现代码如下：

df_province = pd.DataFrame(df[df['创建日期'] == '2021-04-11'].groupby('省份')['order_id'].count())
df_province = df_province.reset_index()
df_province = df_province.sort_values(by = 'order_id',ascending = False)
df_province = df_province.rename(columns = {'order_id':'创建订单量'})
df_province

运行上面代码会得到如下结果：

在得到各省份当日创建订单量的绝对数值之后，同样对其进行格式设置，具体设置代码如下：

from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.styles import colors
from openpyxl.styles import Font
from openpyxl.styles import PatternFill
from openpyxl.styles import Border, Side
from openpyxl.styles import Alignment
from openpyxl.formatting.rule import DataBarRule

wb = Workbook()
ws = wb.active

for r in dataframe_to_rows(df_province,index = False,header = True):
    ws.append(r)

#对第1行至第11行的单元格进行设置
for row in ws[1:11]:
    for c in row:
        #字体设置
        c.font = Font(name = '微软雅黑',size = 12)
        #对齐方式设置
        c.alignment = Alignment(horizontal = "center")
        #边框线设置
        c.border = Border(left = Side(border_style = "thin",color = "FF000000"),
                   right = Side(border_style = "thin",color = "FF000000"),
                   top = Side(border_style = "thin",color = "FF000000"),
                   bottom = Side(border_style = "thin",color = "FF000000"))

#设置进度条条件格式
rule = DataBarRule(start_type = 'min',end_type = 'max',
                    color="FF638EC6", showValue=True, minLength=None, maxLength=None)
ws.conditional_formatting.add('B1:B11',rule)

#对第1行标题行进行设置
for c in ws[1]:
    c.font = Font(name = '微软雅黑',size = 12,bold = True,color = "FFFFFFFF")
    c.fill = PatternFill(fill_type = 'solid',start_color='FFFF6100')
        
#调整列宽
ws.column_dimensions['A'].width = 17
ws.column_dimensions['B'].width = 13

#保存调整后的文件     
wb.save(r'D:\Data-Science\share\excel-python报表自动化\各省份销量情况.xlsx')

运行上面代码会得到如下结果：

最近一段时间创建订单量趋势：

一般用折线图的形式反映某个指标的趋势情况，我们前面也讲过，在实际工作中我们一般用matplotlib或者其他可视化的库进行图表绘制，并将其进行保存，然后再利用openpyxl库将图表插入到Excel中。

先利用matplotlib库进行绘图，具体实现代码如下：

%matplotlib inline
import matplotlib.pyplot as plt
plt.rcParams["font.sans-serif"]='SimHei'#解决中文乱码

#设置图表大小
plt.figure(figsize = (10,6))
df.groupby('创建日期')['order_id'].count().plot()
plt.title('4.2 - 4.11 创建订单量分日趋势')
plt.xlabel('日期')
plt.ylabel('订单量')

#将图表保存到本地
plt.savefig(r'D:\Data-Science\share\excel-python报表自动化\4.2 - 4.11 创建订单量分日趋势.png')

将保存到本地的图表插入到Excel中，具体实现代码如下：

from openpyxl import Workbook
from openpyxl.drawing.image import Image

wb = Workbook()
ws = wb.active

img = Image(r'D:\Data-Science\share\excel-python报表自动化\4.2 - 4.11 创建订单量分日趋势.png')

ws.add_image(img, 'A1')

wb.save(r'D:\Data-Science\share\excel-python报表自动化\4.2 - 4.11 创建订单量分日趋势.xlsx')

运行上面代码会得到如下结果，可以看到图表已经被成功插入到Excel中：

将不同的结果进行合并

上面我们是把每一部分都单独拆开来实现，最后存储在了不同的Excel文件中。当然了，有的时候放在不同文件中会比较麻烦，我们就需要把这些结果合并在同一个Excel的相同Sheet或者不同Sheet中。

将不同的结果合并到同一个Sheet中：

将不同的结果合并到同一个Sheet中的难点在于不同表结果的结构不一样，而且需要在不同结果之间进行留白。

首先插入核心指标表df_review，插入方式与单独的插入是一样的，具体代码如下：

for r in dataframe_to_rows(df_view,index = True,header = True):
    ws.append(r)

接下来就该插入各省份情况表df_province，因为append默认是从第一行开始插入的，而我们前面几行已经有df_view表的数据了，所以就不能用appen的方式进行插入，而只能通过遍历每一个单元格的方式进行插入。

那我们怎么知道要遍历哪些单元格呢？核心需要知道遍历开始的行列和遍历结束的行列。

遍历开始的行 = df_view表占据的行 + 留白的行(一般表与表之间留2行) + 1
遍历结束的行 = 遍历开始的行 + df_province表占据的行

遍历开始的列 = 1
遍历结束的列 = df_province表占据的列

而又因为DataFrame中获取列名的方式和获取具体值的方式不太一样，所以我们需要分别插入，先插入列名，具体代码如下：

for j in range(df_province.shape[1]):
    ws.cell(row = df_view.shape[0] + 5,column = 1 + j).value = df_province.columns[r]

df_province.shape[1]是获取df_province表有多少列，df_view.shape[0]是获取df_view表有多少行。

前面说过，遍历开始的行是表占据的行加上留白的行再加1，一般留白的行是2，可是这里面为啥是df_view.shape[0] + 5呢？这是因为df_view.shape[0]是不包列名行的，同时在插入Excel中的时候会默认增加1行空行，所以就需要在留白行的基础上再增加2行，即2 + 2 + 1 = 5。

因为range()函数是默认是从0开始的，而Excel中的列是从1开始的，所以column需要加1。

上面的代码只是把df_province表的列名插入进来了，接下来插入具体的值，方式与插入列名的方式一致，只不过需要在列名的下一行开始插入，具体代码如下：

#再把具体的值插入
for i in range(df_province.shape[0]):
    for j in range(df_province.shape[1]):
        ws.cell(row = df_view.shape[0] + 6 + i,column = 1 + j).value = df_province.iloc[i,j]

接下来就该插入图片了，插入图片的方式与前面单独的插入是一致的，具体代码如下：

#插入图片
img = Image(r'D:\Data-Science\share\excel-python报表自动化\4.2 - 4.11 创建订单量分日趋势.png')
ws.add_image(img, 'G1')

将所有的数据插入以后就该对这些数据进行格式设置了，因为不同表的结构不一样，所以我们没法直接批量针对所有的单元格进行格式设置，只能分范围分别进行设置，而不同范围的格式可能是一样的，所以我们先预设一些格式变量，这样后面用到的时候直接调取这些变量即可，减少代码冗余，具体代码如下：

#格式预设

#表头字体设置
title_Font_style = Font(name = '微软雅黑',size = 12,bold = True,color = "FFFFFFFF")
#普通内容字体设置
plain_Font_style = Font(name = '微软雅黑',size = 12)
Alignment_style = Alignment(horizontal = "center")
Border_style = Border(left = Side(border_style = "thin",color = "FF000000"),
                   right = Side(border_style = "thin",color = "FF000000"),
                   top = Side(border_style = "thin",color = "FF000000"),
                   bottom = Side(border_style = "thin",color = "FF000000"))
PatternFill_style = PatternFill(fill_type = 'solid',start_color='FFFF6100')

格式预设完之后就可以对各个范围分别进行格式设置了，具体代码如下：

#对A1至F6范围内的单元格进行设置
for row in ws['A1':'F6']:
    for c in row:
        c.font = plain_Font_style
        c.alignment = Alignment_style
        c.border = Border_style

#对第1行和第2行的单元格进行设置
for row in ws[1:2]:
    for c in row:
        c.font = title_Font_style
        c.fill = PatternFill_style

#对E列和F列的单元格进行设置
for col in ws["E":"F"]:
    for r in col:
        r.number_format = '0.00%'

#对A9至B19范围内的单元格进行设置
for row in ws['A9':'B19']:
    for c in row:
        c.font = plain_Font_style
        c.alignment = Alignment_style
        c.border = Border_style

#对A9至B9范围内的单元格进行设置
for row in ws['A9':'B9']:
    for c in row:
        c.font = title_Font_style
        c.fill = PatternFill_style
        
#设置进度条
rule = DataBarRule(start_type = 'min',end_type = 'max',
                    color="FF638EC6", showValue=True, minLength=None, maxLength=None)
ws.conditional_formatting.add('B10:B19',rule)

#调整列宽
ws.column_dimensions['A'].width = 17
ws.column_dimensions['B'].width = 13
ws.column_dimensions['E'].width = 10

最后将上面所有代码片段合并在一起，就是将不同的结果文件合并到同一个Sheet中的完整代码，具体结果如下，可以看到不同结果文件合并在了一起，并且各自的格式设置完好。

将不同的结果合并到同一工作簿的不同Sheet中：

将不同的结果合并到同一工作簿的不同Sheet中比较好实现，只需要新建几个Sheet，然后针对不同的Sheet插入数据即可，具体实现代码如下：

from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows

wb = Workbook()
ws = wb.active

ws1 = wb.create_sheet()
ws2 = wb.create_sheet()

#更改sheet的名称
ws.title = "核心指标" 
ws1.title = "各省份销情况" 
ws2.title = "分日趋势" 

for r1 in dataframe_to_rows(df_view,index = True,header = True):
    ws.append(r1)

for r2 in dataframe_to_rows(df_province,index = False,header = True):
    ws1.append(r2)

img = Image(r'D:\Data-Science\share\excel-python报表自动化\4.2 - 4.11 创建订单量分日趋势.png')

ws2.add_image(img, 'A1')

wb.save(r'D:\Data-Science\share\excel-python报表自动化\多结果合并_多Sheet.xlsx')

运行上面代码，会得到如下结果，可以看到创建了3个Sheet，且不同的内容保存到了不同Sheet中：

到这里我们的一份自动化报表的代码就完成了，以后每次需要用到这份报表的时候，把上面代码执行一遍，结果马上就可以出来，当然了也可以设置定时执行，到时间结果就自动发送到你邮箱里面啦。

本文版权归原作者所有，如有内容版权等问题请联系我，本文仅供交流学习使用

---

技术交流

欢迎转载、收藏、有所收获点赞支持一下！

目前开通了技术交流群，群友已超过2000人，添加时最好的备注方式为：来源+兴趣方向，方便找到志同道合的朋友

• 方式①、发送如下图片至微信，长按识别，后台回复：加群；
• 方式②、添加微信号：dkl88191，备注：来自CSDN
• 方式③、微信搜索公众号：Python学习与数据挖掘，后台回复：加群

 相关文章和数据源：

Python自动化办公--Pandas玩转Excel【一】

Python自动化办公--Pandas玩转Excel数据分析【二】

Python自动化办公--Pandas玩转Excel数据分析【三】

---

python处理Excel实现自动化办公教学（含实战）【一】

python处理Excel实现自动化办公教学（含实战）【二】

python处理Excel实现自动化办公教学（数据筛选、公式操作、单元格拆分合并、冻结窗口、图表绘制等）【三】

---

python入门之后须掌握的知识点（模块化编程、时间模块）【一】

python入门之后须掌握的知识点（excel文件处理+邮件发送+实战：批量化发工资条）【二】

---

spandas玩转excel码源.zip-数据挖掘文档类资源-CSDN下载

Python自动化办公（2021最新版！有源代码，）.zip-

Python自动化办公（可能是B站内容最全的！有源代码，）.zip-

上面是对应码源，图方便的话可以直接下载都分类分好了，当然也可以看文章。

---

1.知识巩固excel

这个系列文章主要以实例为主，学会每个demo，活学活用，办公效率提升飞快！

1.1 xlrd+xlwt读写excel

#安装命令：
pip install xlrd

pip install xlwt

我们在读取文件的时候，excel的列是字母我们不容易直观看出来是第几列，下面对excel进行设置。

 操作如下

import xlrd
# 打开excel
xlsx = xlrd.open_workbook('7月新.xls')

sheet = xlsx.sheet_by_index(0)
data = sheet.cell_value(5, 1)
print(data)

# for i in xlsx.sheet_names():
#     print(i)
    # table = xlsx.sheet_by_name(i)
    # print(table.cell_value(3, 3))
# 通过sheet名查找：xlsx.sheet_by_name("7月下旬入库表")
# 通过索引查找：xlsx.sheet_by_index(3)
# print(table.cell_value(0, 0))

#单元格读取方式
print(sheet.cell_value(1, 2))
print(sheet.cell(0, 0).value)
print(sheet.row(0)[0].value)


# for i in range(0, xlsx.nsheets):
#     sheet = xlsx.sheet_by_index(i)
#     print(sheet.name)
    # print(sheet.cell_value(0, 0))
#
# # 获取所有sheet名字：xlsx.sheet_names()
# # 获取sheet数量：xlsx.nsheets
#
# for i in xlsx.sheet_names():
#     print(i)
    # table = xlsx.sheet_by_name(i)
    # print(table.cell_value(3, 3))

import xlwt

# 新建工作簿
new_workbook = xlwt.Workbook()
# 新建sheet
worksheet = new_workbook.add_sheet('new_test')
# 新建单元格，并写入内容-行列
worksheet.write(1, 2, 'test')
# 保存
new_workbook.save('test.xls')

 

 1.2  xlutils设置格式

xlutils整体没有pandas设置来的方便

from xlutils.copy import copy
import xlrd
import xlwt

# 安装：pip install xlutils

tem_excel = xlrd.open_workbook('日统计.xls', formatting_info=True) #格式信息打开
tem_sheet = tem_excel.sheet_by_index(0)

new_excel = copy(tem_excel)
new_sheet = new_excel.get_sheet(0)

style = xlwt.XFStyle()

# 字体
font = xlwt.Font()
font.name = '微软雅黑'
font.bold = True
# 18*20
font.height = 360
style.font = font

# 边框：细线==THIN
borders = xlwt.Borders()
borders.top = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
borders.left = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
style.borders = borders

# 对齐
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER
alignment.vert = xlwt.Alignment.VERT_CENTER
style.alignment = alignment


new_sheet.write(2, 1, 12)
new_sheet.write(3, 1, 18)
new_sheet.write(4, 1, 19)
new_sheet.write(5, 1, 15)


# new_sheet.write(2, 1, 12, style)
# new_sheet.write(3, 1, 18, style)
# new_sheet.write(4, 1, 19, style)
# new_sheet.write(5, 1, 15, style)

new_excel.save('填写.xls')

 

 1.3 自动生成统计报表【案例一：分数统计】

import xlrd

import xlwt

# 读取excel文件
xlsx = xlrd.open_workbook('三年二班（各科成绩单）.xls')
# 选择指定sheet
sheet = xlsx.sheet_by_index(0)

all_data = []
# 统计共有多少学生，并去重
num_set = set()
for row_i in range(1, sheet.nrows):
    num = sheet.cell_value(row_i, 0)
    name = sheet.cell_value(row_i, 1)
    grade = sheet.cell_value(row_i, 3)

    student = {
        'num': num,
        'name': name,
        'grade': grade,
    }
    all_data.append(student)
    num_set.add(num)
# print(all_data)
# print(len(all_data))
# print(len(num_set))

# 计算总分
sum_list = []
for num in num_set:
    name = ''
    sum = 0
    for student in all_data:
        # print(student['num'])
        # print(num)
        if num == student['num']:
            sum += student['grade']
            name = student['name']
    sum_stu = {
        'num': num,
        'name': name,
        'sum': sum
    }
    sum_list.append(sum_stu)
print(sum_list)

# 写入新的excel

# 新建工作簿
new_workbook = xlwt.Workbook()
# 新建sheet
worksheet = new_workbook.add_sheet('2班')
# 新建单元格，并写入内容
# 写入第一列的内容
worksheet.write(0, 0, '学号')
worksheet.write(0, 1, '姓名')
worksheet.write(0, 2, '总分')
# 自动写入后面的内容
for row in range(0,len(sum_list)):
    worksheet.write(row+1,0,sum_list[row]['num'])
    worksheet.write(row+1,1,sum_list[row]['name'])
    worksheet.write(row+1,2,sum_list[row]['sum'])
# 保存
new_workbook.save('2班学生总分.xls')

 1.4 xlsxwriter 和openpyxl

xlwt会遇到不支持超过列256的表格

# import xlwt
#
# workbook = xlwt.Workbook()
# sheet0 = workbook.add_sheet('sheet0')
# for i in range(0,300):
#     sheet0.write(0,i,i)
# workbook.save('num.xls')

# 不带格式
import xlsxwriter as xw
workbook = xw.Workbook('number.xlsx')
sheet0 = workbook.add_worksheet('sheet0')
for i in range(0,300):
    sheet0.write(0,i,i)
workbook.close()


# 性能不稳定
import openpyxl
workbook = openpyxl.load_workbook('number.xlsx')
sheet0 = workbook['sheet0']
sheet0['B3']= '2'
sheet0['C2']= '4'
sheet0['D7']= '3'
workbook.save('num_open.xlsx')

1.5 xlwings和pandas

pandas在第一期已经详细讲解过，参考相关文章

Python用来处理Excel的全部可用库，以及它们的优缺点

xlwings具有以下优点：

- xlwings能够非常方便的读写Excel文件中的数据，并且能够进行单元格格式的修改
- 可以和matplotlib以及pandas无缝连接
- 可以调用Excel文件中VBA写好的程序，也可以让VBA调用用Python写的程序。
- 开源免费，一直在更新

#### 1、打开/新建Excel文档

```python
import xlwings as xw
wb = xw.Book()  # 新建一个文档
wb = xw.Book('test.xlsx')  # 打开一个已有的文档
```

#### 2、读取/写入数据

```python
sht = wb.sheets['Sheet1'] # 找到指定sheet
print(sht.range('A1').value) # 读取指定单元格的数据，这里读的是A1
sht.range('B1').value = 10 # 给指定的空白单元格赋值，这里赋值的是B1
```

#### 3、保存文件、退出程序

```python
wb.save(r'test.xlsx') #保存Excel文档，命名为test.xlsx，并保存在D盘
wb.close() # 退出程序，该步骤不可省略
```

#### 4、连接pandas处理复杂数据

```python
import pandas as pd
df = pd.DataFrame([[1,2], [3,4]], columns=['a', 'b'])
sht.range('A1').value = df
sht.range('A1').options(pd.DataFrame, expand='table').value
```

#### 5、连接**Matplotlib** 画图

```python
import matplotlib.pyplot as plt
fig = plt.figure()
plt.plot([1, 2, 3, 4, 5])
sht.pictures.add(fig, name='MyPlot', update=True)

1.6 案例实战---把文件名整理到excel中

import os
import xlwt

# 目标文件夹
file_path = 'd:/'
# 取出目标文件夹下的文件名
os.listdir(file_path)

new_workbook = xlwt.Workbook()
sheet = new_workbook.add_sheet('new_dir')

n = 0
for i in os.listdir(file_path):
    sheet.write(n,0,i)
    n+=1
new_workbook.save('dir.xls')

1.7  案例实战---excel填充画

把一幅画导入到excel中用每个单元格背景色生成原画

# coding: utf-8

from PIL import Image
from xlsxwriter.workbook import Workbook


class ExcelPicture(object):
    FORMAT_CONSTRAINT = 65536

    def __init__(self, pic_file, ratio=0.5):
        self.__pic_file = pic_file

        self.__ratio = ratio
        self.__zoomed_out = False

        self.__formats = dict()

    # 缩小图片
    def zoom_out(self, _img):
        _size = _img.size
        _img.thumbnail((int(_img.size[0] * self.__ratio), int(_img.size[1] * self.__ratio)))

        self.__zoomed_out = True

    # 对颜色进行圆整
    def round_rgb(self, rgb, model):
        return tuple([int(round(x / model) * model) for x in rgb])

    # 查找颜色样式，去重
    def get_format(self, color):
        _format = self.__formats.get(color, None)

        if _format is None:
            _format = self.__wb.add_format({'bg_color': color})
            self.__formats[color] = _format

        return _format

    # 操作流程
    def process(self, output_file='_pic.xlsx', color_rounding=False, color_rounding_model=5.0):
        # 创建xlsx文件，并调整行列属性
        self.__wb = Workbook(output_file)
        self.__sht = self.__wb.add_worksheet()
        self.__sht.set_default_row(height=9)
        self.__sht.set_column(0, 5000, width=1)

        # 打开需要进行转换的图片
        _img = Image.open(self.__pic_file)
        print('Picture filename:', self.__pic_file)

        # 判断是否需要缩小图片尺寸
        if self.__ratio < 1:
            self.zoom_out(_img)

        # 遍历每一个像素点，并填充对应的颜色到对应的Excel单元格
        _size = _img.size
        print('Picture size:', _size)
        for (x, y) in [(x, y) for x in range(_size[0]) for y in range(_size[1])]:
            _clr = _img.getpixel((x, y))

            # 如果颜色种类过多，则需要将颜色圆整到近似的颜色上，以减少颜色种类
            if color_rounding: _clr = self.round_rgb(_clr, color_rounding_model)

            _color = '#%02X%02X%02X' % _clr
            self.__sht.write(y, x, '', self.get_format(_color))

        self.__wb.close()

        # 检查颜色样式种类是否超出限制，Excel2007对样式数量有最大限制
        format_size = len(self.__formats.keys())
        if format_size >= ExcelPicture.FORMAT_CONSTRAINT:
            print('Failed! Color size overflow: %s.' % format_size)
        else:
            print('Success!')
            print('Color: %s' % format_size)
            print('Color_rounding:', color_rounding)
            if color_rounding:
                print('Color_rounding_model:', color_rounding_model)


if __name__ == '__main__':
    r = ExcelPicture('test.jpg', ratio=0.5)
    r.process('0407.xlsx', color_rounding=True, color_rounding_model=5.0)
# 同级目录

效果如下：

原图

下面可以看到有一点糊是因为填充单元格可以当作像素点来考虑

 

 2.自动化处理word

安装库

>pip install python-docx

- python-docx
    - 说明文档：https://python-docx.readthedocs.io/en/latest/#api-documentation

2.1 自动生成word【批量化写模板文档】

from docx import Document
from docx.enum.text import WD_ALIGN_PARAGRAPH
from docx.shared import Pt
from docx.oxml.ns import qn

import time

price = input('请输入工资调整金额：')
# 全体员工姓名
company_list = ['员工1', '员工1', '员工2', '员工3', '员工4',
                '员工5', '员工6', '员工7', '员工8', '员工9', '员工10']
# word和excel可以结合

# excel学习：for
# 当天的日期！
today = time.strftime("%Y{y}%m{m}%d{d}", time.localtime()
                      ).format(y='年', m='月', d='日')

for i in company_list:
    document = Document()
    # 设置文档的基础字体
    document.styles['Normal'].font.name = u'宋体'
    # 识别中文
    document.styles['Normal'].element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')

    # 建立一个自然段
    p1 = document.add_paragraph()
    # 对齐方式为居中，没有这句的话默认左对齐
    p1.alignment = WD_ALIGN_PARAGRAPH.CENTER
    run1 = p1.add_run('关于%s工资调整的通知' % (today))
    run1.font.name = '微软雅黑'
    run1.element.rPr.rFonts.set(qn('w:eastAsia'), u'微软雅黑')
    run1.font.size = Pt(21)
    run1.font.bold = True
    p1.space_after = Pt(5)
    p1.space_before = Pt(5)

    p2 = document.add_paragraph()
    run2 = p2.add_run(i + '：')
    run2.font.name = '宋体'
    run2.element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')
    run2.font.size = Pt(16)
    run2.font.bold = True

    p3 = document.add_paragraph()
    run3 = p3.add_run('因为疫情影响，我们很抱歉的通知您，您的工资调整为每月%s元，特此通知' % price)
    run3.font.name = '宋体'
    run3.element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')
    run3.font.size = Pt(14)

    p4 = document.add_paragraph()
    p4.alignment = WD_ALIGN_PARAGRAPH.RIGHT
    run4 = p4.add_run('人事：王小姐 电话：686868')
    run4.font.name = '宋体'
    run4.element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')
    run4.font.size = Pt(14)
    run4.font.bold = True

    document.save('%s-工资调整通知.docx' % i)

其中在读取员工信息这块可以和excel结合。

 

2.2 批量化写模板文档【并添加图片和表格】

新的要求，要求小杨在通知函上方加上图片红头,价格数据以表格形式展示。并在第二页加
上广告【插入分页符】。

效果如下：

from docx import Document
from docx.enum.text import WD_ALIGN_PARAGRAPH
from docx.shared import Pt
from docx.oxml.ns import qn
from docx.shared import Inches

import time

price = input('请输入工资调整金额：')
company_list = ['员工1', '员工1', '员工2', '员工3', '员工4',
                '员工5', '员工6', '员工7', '员工8', '员工9', '员工10', ]
today = time.strftime("%Y{y}%m{m}%d{d}", time.localtime()
                      ).format(y='年', m='月', d='日')
for i in company_list:
    document = Document()
    # 设置文档的基础字体
    document.styles['Normal'].font.name = u'宋体'
    document.styles['Normal'].element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')

    # 红头文件加入文件
    document.add_picture('title002.jpg', width=Inches(6))

    # 建立一个自然段
    p1 = document.add_paragraph()
    # 对齐方式为居中，没有这句的话默认左对齐
    p1.alignment = WD_ALIGN_PARAGRAPH.CENTER
    run1 = p1.add_run('关于%s工资调整的通知' % (today))
    run1.font.name = '微软雅黑'
    run1.element.rPr.rFonts.set(qn('w:eastAsia'), u'微软雅黑')
    run1.font.size = Pt(21)
    run1.font.bold = True
    p1.space_after = Pt(5)
    p1.space_before = Pt(5)

    p2 = document.add_paragraph()
    run2 = p2.add_run(i + '：')
    run2.font.name = '宋体'
    run2.element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')
    run2.font.size = Pt(16)
    run2.font.bold = True

    p3 = document.add_paragraph()
    run3 = p3.add_run('因为疫情影响，我们很抱歉的通知您，您的工资调整为每月%s元，特此通知。' % price)
    run3.font.name = '宋体'
    run3.element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')
    run3.font.size = Pt(14)
    
    #添加表格
    table = document.add_table(rows=2, cols=2, style='Table Grid')#默认格式
    # 合并单元格
    table.cell(0, 0).merge(table.cell(0, 1)) #坐上合并到右下
    table_run1 = table.cell(0, 0).paragraphs[0].add_run('签名栏')
    table_run1.font.name = '黑体'
    table_run1.element.rPr.rFonts.set(qn('w:eastAsia'), u'黑体')
    table.cell(0, 0).paragraphs[0].alignment = WD_ALIGN_PARAGRAPH.CENTER
    table.cell(1, 0).text = i
    table.cell(1, 0).paragraphs[0].alignment = WD_ALIGN_PARAGRAPH.CENTER
    
   
    p4 = document.add_paragraph()
    p4.alignment = WD_ALIGN_PARAGRAPH.RIGHT

    run4 = p4.add_run('人事：王小姐 电话：686868')
    run4.font.name = '宋体'
    run4.element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')
    run4.font.size = Pt(14)
    run4.font.bold = True
    
        #插入分页符
    document.add_page_break()
    p5= document.add_paragraph()
    run4=p5.add_run("此处是广告")

    document.save('%s-工资调整通知.docx' % i)

2.3 读取word文档【 文字+表格混合文档：】

from docx import Document

document = Document('pure.docx')
all_paragraphs = document.paragraphs
for p in all_paragraphs:
    print(p.text)


# excel写入

 如果word里是表格呈现读取如下：

 文字+表格形式：

word基本格式问题：把word重名后缀为zip的文件，打开看到里面有xml的格式文件

 导入zipfile库解压文件，设置格式只读取我们需要的文字：进行组合。

import zipfile

word_book = zipfile.ZipFile('word_table.docx')
xml = word_book.read("word/document.xml").decode('utf-8')
# print(xml)
xml_list = xml.split('<w:t>')
# print(xml_list)
text_list = []
for i in xml_list:
    if i.find('</w:t>') + 1:
        text_list.append(i[:i.find('</w:t>')])
    else:
        pass
text = "".join(text_list)
print(text)

2.4 word转pdf（批量化）

安装库：

pip install pywin32

from win32com.client import Dispatch,constants,gencache

doc_path = 'test.docx'
pdf_path = 'test.pdf'

gencache.EnsureModule('{00020905-0000-0000-C000-000000000046}',0,8,4)
wd = Dispatch("Word.Application")
doc = wd.Documents.Open(doc_path,ReadOnly=1)
doc.ExportAsFixedFormat(pdf_path,constants.wdExportFormatPDF,
                        Item=constants.wdExportDocumentWithMarkup,
                        CreateBookmarks=constants.wdExportCreateHeadingBookmarks)
wd.Quit(constants.wdDoNotSaveChanges)

在执行时报错了，下面来说解决方法。
参考网站：

win32api pywin32 安装后出现 ImportError: DLL load failed_mengfanteng的博客-CSDN博客

• 找到我们安装python的文件夹，在Lib文件中找到site-packages\pywin32_system32
• D:\Program Files (x86)\Python\Python36\Lib\site-packages\pywin32_system32
• 把里面的所有的文件复制到：C:\Windows\System32

import os
from win32com.client import Dispatch,constants,gencache



from docx import Document
from docx.enum.text import WD_ALIGN_PARAGRAPH
from docx.shared import Pt
from docx.oxml.ns import qn

import time

price = input('请输入工资调整金额：')
# 全体员工姓名
company_list = ['员工1', '员工1', '员工2', '员工3', '员工4', '员工5', '员工6', '员工7', '员工8', '员工9', '员工10' ]
# 当天的日期
today = time.strftime("%Y{y}%m{m}%d{d}", time.localtime()).format(y='年', m='月', d='日')

for i in company_list:
    document = Document()
    # 设置文档的基础字体
    document.styles['Normal'].font.name = u'宋体'
    # 识别中文
    document.styles['Normal'].element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')

    # 建立一个自然段
    p1 = document.add_paragraph()
    # 对齐方式为居中，没有这句的话默认左对齐
    p1.alignment = WD_ALIGN_PARAGRAPH.CENTER
    run1 = p1.add_run('关于%s工资调整的通知' % (today))
    run1.font.name = '微软雅黑'
    run1.element.rPr.rFonts.set(qn('w:eastAsia'), u'微软雅黑')
    run1.font.size = Pt(21)
    run1.font.bold = True
    p1.space_after = Pt(5)
    p1.space_before = Pt(5)

    p2 = document.add_paragraph()
    run2 = p2.add_run(i + '：')
    run2.font.name = '宋体'
    run2.element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')
    run2.font.size = Pt(16)
    run2.font.bold = True

    p3 = document.add_paragraph()
    run3 = p3.add_run('因为疫情影响，我们很抱歉的通知您，您的工资调整为每月%s元，特此通知' % price)
    run3.font.name = '宋体'
    run3.element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')
    run3.font.size = Pt(14)

    p4 = document.add_paragraph()
    p4.alignment = WD_ALIGN_PARAGRAPH.RIGHT
    run4 = p4.add_run('人事：王小姐 电话：686868')
    run4.font.name = '宋体'
    run4.element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')
    run4.font.size = Pt(14)
    run4.font.bold = True


    document.save('%s-工资调整通知.docx' % i)

    doc_path = '%s-工资调整通知.docx' % i
    pdf_path = '%s-工资调整通知.pdf' % i

    gencache.EnsureModule('{00020905-0000-0000-C000-000000000046}',0,8,4)
    wd = Dispatch("Word.Application")
    doc = wd.Documents.Open(doc_path,ReadOnly=1)
    doc.ExportAsFixedFormat(pdf_path,constants.wdExportFormatPDF,Item=constants.wdExportDocumentWithMarkup,CreateBookmarks=constants.wdExportCreateHeadingBookmarks)
    wd.Quit(constants.wdDoNotSaveChanges)
    time.sleep(10)

3.PDF识别以及读取PDF中文字【pdf合并】

3.1 使用 pdfplumber和PyPDF2

安装库：

pip install pdfplumber

pip install PyPDF2

参考文章：

PDFPlumber使用入门_顺其自然~的博客-CSDN博客_pdfplumber

import PyPDF2
import pdfplumber

def extract_content(pdf_path):
    # 内容提取，使用 pdfplumber 打开 PDF，用于提取文本
    with pdfplumber.open(pdf_path) as pdf_file:
        # 使用 PyPDF2 打开 PDF 用于提取图片
        pdf_image_reader = PyPDF2.PdfFileReader(open(pdf_path, "rb"))
        print(pdf_image_reader.getNumPages())

        content = ''
        # len(pdf.pages)为PDF文档页数，一页页解析
        for i in range(len(pdf_file.pages)):
            print("当前第 %s 页" % i)
            # pdf.pages[i] 是读取PDF文档第i+1页
            page_text = pdf_file.pages[i]
            # page.extract_text()函数即读取文本内容
            page_content = page_text.extract_text()
            if page_content:
                content = content + page_content + "\n"
                print(page_content)

extract_content('静夜思.pdf')

合并pdf

from PyPDF2 import PdfFileReader, PdfFileWriter

def merge_pdfs(paths, output):
    pdf_writer = PdfFileWriter()

    for path in paths:
        pdf_reader = PdfFileReader(path)
        for page in range(pdf_reader.getNumPages()):
            # 把每张PDF页面加入到这个可读取对象中
            pdf_writer.addPage(pdf_reader.getPage(page))

    # 把这个已合并了的PDF文档存储起来
    with open(output, 'wb') as out:
        pdf_writer.write(out)

if __name__ == '__main__':
    paths = ['静夜思.pdf', '静夜思.pdf']
    merge_pdfs(paths, output='pandas官方文档中文版.pdf')

from PyPDF2 import PdfFileReader, PdfFileWriter

def merge_pdfs(paths, output):
    pdf_writer = PdfFileWriter()

    for path in paths:
        pdf_reader = PdfFileReader(path)
        for page in range(pdf_reader.getNumPages()):
            # 把每张PDF页面加入到这个可读取对象中
            pdf_writer.addPage(pdf_reader.getPage(page))

    # 把这个已合并了的PDF文档存储起来
    with open(output, 'wb') as out:
        pdf_writer.write(out)

if __name__ == '__main__':
    paths = ['study.pdf', 'labuladong的算法小抄官方完整版.pdf']
    merge_pdfs(paths, output='pandas官方文档中文版.pdf')

3.2 pdfminer（推荐）

读取：

 4.ppt自动化操作

 python-pptx说明文档
    - https://pypi.org/project/python-pptx/

# pip install python-pptx

4..1在ppt中写入文字

from pptx import Presentation
from pptx.util import Inches,Pt

ppt = Presentation()
slide = ppt.slides.add_slide(ppt.slide_layouts[1])# 在PPT中插入一个幻灯片

body_shape = slide.shapes.placeholders
# body_shape[0].text = '这是占位符0'
# body_shape[1].text = '这是占位符1'
#
title_shape = slide.shapes.title
title_shape.text = '这是标题'
# subtitle = slide.shapes.placeholders[1] #取出本页第二个文本框
# subtitle.text = '这是文本框'
#
# new_paragraph = body_shape[1].text_frame.add_paragraph()
# new_paragraph.text = '新段落'
# new_paragraph.font.bold = True
# new_paragraph.font.italic = True
# new_paragraph.font.size = Pt(15)
# new_paragraph.font.underline = True
#
left = Inches(2)
top = Inches(2)
width = Inches(3)
height = Inches(3)
#
#
#
textbox = slide.shapes.add_textbox(left,top,width,height)
textbox.text = 'new textbox'
# 如何在文本框里添加第二段文字？
# new_para = textbox.text_frame.add_paragraph()
# new_para.text = '第二段文字'
#


ppt.save('test.pptx')

4.2 在ppt插入图片表格

# pip install python-pptx
from pptx import Presentation
from pptx.util import Inches,Pt

ppt = Presentation()
slide = ppt.slides.add_slide(ppt.slide_layouts[1])# 在PPT中插入一个幻灯片

left = Inches(1)
top = Inches(1)
width = Inches(2)
height = Inches(2)

img = slide.shapes.add_picture('img.jpg',left,top,width,height)

rows = 2
cols = 2
left = Inches(1)
top = Inches(1)
width = Inches(4)
height = Inches(4)

table = slide.shapes.add_table(rows,cols,left,top,width,height).table
table.columns[0].width = Inches(1)
table.columns[1].width = Inches(3)
table.cell(0,0).text = '00'
table.cell(0,1).text = '01'
table.cell(1,0).text = '10'
table.cell(1,1).text = '11' #二进制的11，代表十进制的多少？

ppt.save('text.pptx')