Python+Excel 报表自动化详解

今天给大家分享一篇Python自动化办公干货，全文3W+字，内容很干，可以码住细细品味。

0. Python Excel库对比

我们先来看一下python中能操作Excel的库对比（一共九个库）：

1. Python xlrd 读取 操作Excel

1.1 xlrd模块介绍

（1）什么是xlrd模块？

• python操作excel主要用到xlrd和xlwt这两个库，即xlrd是读excel，xlwt是写excel的库。

（2）为什么使用xlrd模块？

• 在UI自动化或者接口自动化中数据维护是一个核心，所以此模块非常实用。

xlrd模块可以用于读取Excel的数据，速度非常快，推荐使用！

官方文档：https://xlrd.readthedocs.io/en/latest/

1.2 安装xlrd模块

• 到python官网下载http://pypi.python.org/pypi/xlrd模块安装，前提是已经安装了python 环境。

• 或者在cmd窗口 pip install xlrd

我这里是anaconda自带有xlrd，所以提示已经安装：

1.3 使用介绍

1. 常用单元格的数据类型

• empty（空的）

• string（text）

• number

• date

• boolean

• error

• blank（空白表格）

• 2.导入模块

3.打开Excel文件读取数据

4.常用的函数

• excel中最重要的方法就是book和sheet的操作

（1）获取book（excel文件）中一个工作表

（2） 行的操作

（3）列（colnum）的操作

（4）单元格的操作

1.4 实战训练

我们先在表格放入以下数据，点击保存：

使用xlrd模块进行读取：

打印结果：

列表生成式介绍：

2. Python xlwt 写入 操作Excel（仅限xls格式！）

xlwt可以用于写入新的Excel表格或者在原表格基础上进行修改，速度也很快，推荐使用！

官方文档：https://xlwt.readthedocs.io/en/latest/

2.1 pip安装xlwt

我这里是anaconda自带有xlwt，所以提示已经安装：

2.2 使用xlwt创建新表格并写入

一开始目录下只有这两个文件：

编写xlwt新表格写入程序:

生成的表格内容如下：

2.3 xlwt 设置字体格式

程序示例：

效果如下：

2.4 xlwt 设置列宽

xlwt中列宽的值表示方法：默认字体0的1/256为衡量单位。

xlwt创建时使用的默认宽度为2960，既11个字符0的宽度

所以我们在设置列宽时可以用如下方法：

width = 256 * 20 256为衡量单位，20表示20个字符宽度

程序示例：

效果如下：

2.5 xlwt 设置行高

在xlwt中没有特定的函数来设置默认的列宽及行高

行高是在单元格的样式中设置的，你可以通过自动换行通过输入文字的多少来确定行高

程序示例：

效果如下：

2.6 xlwt 合并列和行

程序示例：

效果如下：

2.7 xlwt 添加边框

程序示例：

效果如下：

2.8 xlwt为单元格设置背景色

程序示例：

效果如下：

2.9 xlwt设置单元格对齐

使用xlwt中的Alignment来设置单元格的对齐方式，其中horz代表水平对齐方式，vert代表垂直对齐方式。

VERT_TOP = 0x00 上端对齐
VERT_CENTER = 0x01 居中对齐（垂直方向上）
VERT_BOTTOM = 0x02 低端对齐
HORZ_LEFT = 0x01 左端对齐
HORZ_CENTER = 0x02 居中对齐（水平方向上）
HORZ_RIGHT = 0x03 右端对齐

程序示例：

效果如下：

3. Python xlutils 修改 操作Excel

xlutils可用于拷贝原excel或者在原excel基础上进行修改，并保存；

官方文档：https://xlutils.readthedocs.io/en/latest/

3.1 pip安装xlutils

安装过程：

3.2 xlutils拷贝源文件（需配合xlrd使用）

表格内容如下：

程序示例：

效果如下：

内容为：

不过表格的样式全部消失了。

3.3 xlutils 读取 写入 （也就是修改）Excel 表格信息

程序示例：

效果如下：

复制了源文件信息，并进行了追加：

4. Python xlwings 读取 写入 修改 操作Excel

xlwings比起xlrd、xlwt和xlutils，xlwings可豪华多了，它具备以下特点：

• xlwings能够非常方便的读写Excel文件中的数据，并且能够进行单元格格式的修改

• 可以和matplotlib以及pandas无缝连接，支持读写numpy、pandas数据类型，将matplotlib可视化图表导入到excel中。

• 可以调用Excel文件中VBA写好的程序，也可以让VBA调用用Python写的程序。

• 开源免费，一直在更新

官网地址：https://www.xlwings.org/

官方文档：https://docs.xlwings.org/en/stable/api.html

4.1 pip安装xlwings

4.2 基本操作

引入库

打开Excel程序，默认设置：程序可见，只打开不新建工作薄

打开已有工作簿（支持绝对路径和相对路径）

保存工作簿

退出工作簿（可省略）

退出Excel

三个例子：

（1）打开已存在的Excel文档

（2）新建Excel文档，命名为test.xlsx，并保存在D盘

（3）在单元格输入值

新建test.xlsx，在sheet1的第一个单元格输入 “人生” ，然后保存关闭，退出Excel程序。

打开已保存的test.xlsx，在sheet2的第二个单元格输入“苦短”，然后保存关闭，退出Excel程序

掌握以上代码，已经完全可以把Excel当作一个txt文本进行数据储存了，也可以读取Excel文件的数据，进行计算后，并将结果保存在Excel中。

4.3 引用工作薄、工作表和单元格

（1）按名字引用工作簿，注意工作簿应该首先被打开

（2）引用活动的工作薄

（3）引用工作簿中的sheet

（4）引用活动sheet

（5）引用A1单元格

（6）引用活动sheet上的单元格

引用单元格：

引用区域：

4.4 写入&读取数据

1.写入数据

（1）选择起始单元格A1,写入字符串‘Hello’

（2）写入列表

• 默认按行插入：A1:D1分别写入1,2,3,4

等同于

• 按列插入：A2:A5分别写入5,6,7,8

你可能会想：

但是你会发现xlwings还是会按行处理的，上面一行等同于：

正确语法:

既然默认的是按行写入，我们就把它倒过来嘛（transpose），单词要打对，如果你打错单词，它不会报错，而会按默认的行来写入（别问我怎么知道的）

• 多行输入就要用二维列表了：

2.读取数据

（1）读取单个值

（2）将值读取到列表中

• 选取一列的数据

先计算单元格的行数(前提是连续的单元格)

接着就可以按准确范围读取了

• 选取一行的数据

4.5 常用函数和方法

1.Book工作薄常用的api

• wb.activate() 激活为当前工作簿

• wb.fullname 返回工作簿的绝对路径

• wb.name 返回工作簿的名称

• wb.save(path=None) 保存工作簿，默认路径为工作簿原路径，若未保存则为脚本所在的路径

• wb. close() 关闭工作簿

代码示例：

2.sheet常用的api

3.range常用的api

4.books 工作簿集合的api

4.sheets 工作表的集合

4.6 数据结构

1.一维数据

python的列表，可以和Excel中的行列进行数据交换，python中的一维列表，在Excel中默认为一行数据。

2.二维数据

python的二维列表，可以转换为Excel中的行列。二维列表，即列表中的元素还是列表。在Excel中，二维列表中的列表元素，代表Excel表格中的一列。例如：

3.Excel中区域的选取表格

4.7 xlwings生成图表

示例代码：

效果如下：

4.8 实战训练

1. xlwings 新建 Excel 文档

程序示例：

执行程序后文件夹增加了“example.xlsx”:

此时表格是空的：

2. xlwings 打开已存在的 Excel 文档

现有表格长这样：

运行程序：

生成新的表格：

内容如下：

3. xlwings 读写 Excel

程序示例：

执行效果：

5. Python openpyxl 读取 写入 修改 操作Excel

在openpyxl中，主要用到三个概念：Workbooks，Sheets，Cells。

• Workbook就是一个excel工作表；

• Sheet是工作表中的一张表页；

• Cell就是简单的一个格。

openpyxl就是围绕着这三个概念进行的，不管读写都是“三板斧”：打开Workbook，定位Sheet，操作Cell。

官方文档：https://openpyxl.readthedocs.io/en/stable/

官方示例：

5.1 openpyxl 基本操作

1.安装

因为我已经安装，所以提示如下信息：

2.打开文件

（1）新建

（2）打开已有

3.写入数据

4.创建表（sheet）

5.选择表（sheet）

6.查看表名（sheet）

7.访问单元格（cell）

（1）单个单元格访问

（2）多个单元格访问

8.保存数据

9.其它

（1）改变sheet标签按钮颜色

（2）获取最大行，最大列

（3）获取每一行每一列

• sheet.rows为生成器, 里面是每一行的数据，每一行又由一个tuple包裹。

• sheet.columns类似，不过里面是每个tuple是每一列的单元格。

（4）根据数字得到字母，根据字母得到数字

（5）删除工作表

（6）矩阵置换

10.设置单元格风格

（1）需要导入的类

（2）字体

• 下面的代码指定了等线24号，加粗斜体，字体颜色红色。直接使用cell的font属性，将Font对象赋值给它。

（3）对齐方式

• 也是直接使用cell的属性aligment，这里指定垂直居中和水平居中。除了center，还可以使用right、left等等参数

（4）设置行高和列宽

（5）合并和拆分单元格

• 所谓合并单元格，即以合并区域的左上角的那个单元格为基准，覆盖其他单元格使之称为一个大的单元格。

• 相反，拆分单元格后将这个大单元格的值返回到原来的左上角位置。

• 合并后只可以往左上角写入数据，也就是区间中:左边的坐标。

• 如果这些要合并的单元格都有数据，只会保留左上角的数据，其他则丢弃。换句话说若合并前不是在左上角写入数据，合并后单元格中不会有数据。

• 以下是拆分单元格的代码。拆分后，值回到A1位置

11.示例代码

5.2 openpyxl生成2D图表

示例代码：

效果如下：

5.3 openpyxl生成3D图表

示例代码：

效果如下：

5.4 实战训练

1.openpyxl 新建Excel

程序示例：

执行效果：

并对sheet设置了标题和背景颜色：

2.openpyxl 打开已存在Excel

程序示例：

效果如下：

3.openpyxl 读写Excel

程序示例：

执行结果：

6. Python xlswriter 写入 操作Excel

• XlsxWriter是一个用来写Excel2007和xlsx文件格式的python模块。它可以用来写文本、数字、公式并支持单元格格式化、图片、图表、文档配置、自动过滤等特性

• 优点：功能更多、文档高保真、扩展格式类型、更快并可配置 缺点：不能用来读取和修改excel文件

官方文档：https://xlsxwriter.readthedocs.io/

6.1 xlswriter基本操作

1.安装 xlswriter 模块

由于我已经安装过了，所以提示已经安装：

2.创建excel文件

3.创建sheet

4.写入数据

（1）写入文本

（2）写入数字

（3）写入函数

（4）写入图片

（5）写入日期

（6）设置行、列属性

5.自定义格式

常用格式：

• 字体颜色：color

• 字体加粗：bold

• 字体大小：font_site

• 日期格式：num_format

• 超链接：url

• 下划线设置：underline

• 单元格颜色：bg_color

• 边框：border

• 对齐方式：align

6.批量往单元格写入数据

7.合并单元格写入

8.关闭文件

6.3 xlswriter 生成折线图

示例代码：

效果如下：

6.4 xlswriter 生成柱状图

示例代码：

效果如下：

6.5 xlswriter 生成饼图

示例代码：

效果如下：

6.6 实战训练

1.xlswriter新建并写入Excel

程序示例：

效果如下：

7. Python win32com 读取 写入 修改 操作Excel

python可以使用一个第三方库叫做win32com达到操作com的目的，win32com功能强大，可以操作word、调用宏等等等。

7.1 pip安装win32com

由于我已经安装过了，所以提示已经安装：

7.2 Python使用win32com读写Excel

程序示例：

效果如下：

内容为：

8. Python pandas 读取 写入 操作Excel

简介：

pandas 是基于NumPy 的一种工具，该工具是为了解决数据分析任务而创建的。Pandas 纳入了大量库和一些标准的数据模型，提供了高效地操作大型数据集所需的工具。pandas提供了大量能使我们快速便捷地处理数据的函数和方法。你很快就会发现，它是使Python成为强大而高效的数据分析环境的重要因素之一。

官方网站：https://pandas.pydata.org/

官方文档：https://pandas.pydata.org/pandas-docs/stable/

8.1 pip安装pandas

8.2 pandas 读写 Excel

表格内容如下：

程序示例：

效果如下：

生成的excel如下：

pandas功能非常强大，这里只是做了又给很简单的示例，还有很多其它操作，可参考官方文档或快速入门进行学习。

读者福利：对Python感兴趣的童鞋，为此小编专门给大家准备好了Python全套的学习资料《完整版的Python的全套学习资料》（安全链接，放心点击）