Python+Excel报表自动化详解

见贤思齐

今天给大家分享一篇Python自动化办公干货，全文3W+字，内容很干，可以码住细细品味。0.PythonExcel库对比我们先来看一下python中能操作Excel的库对比（一共九个库）：1.Pythonxlrd读取操作Excel1.1xlrd模块介绍（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窗口pipinstallxlrd我这里是anaconda自带有xlrd，所以提示已经安装：1.3使用介绍常用单元格的数据类型empty（空的）string（text）numberdatebooleanerrorblank（空白表格）2.导入模块3.打开Excel文件读取数据4.常用的函数excel中最重要的方法就是book和sheet的操作（1）获取book（excel文件）中一个工作表（2）行的操作（3）列（colnum）的操作（4）单元格的操作1.4实战训练我们先在表格放入以下数据，点击保存：使用xlrd模块进行读取：打印结果：列表生成式介绍：2.Pythonxlwt写入操作Excel（仅限xls格式！）xlwt可以用于写入新的Excel表格或者在原表格基础上进行修改，速度也很快，推荐使用！官方文档：https://xlwt.readthedocs.io/en/latest/2.1pip安装xlwt我这里是anaconda自带有xlwt，所以提示已经安装：2.2使用xlwt创建新表格并写入一开始目录下只有这两个文件：编写xlwt新表格写入程序:生成的表格内容如下：2.3xlwt设置字体格式程序示例：效果如下：2.4xlwt设置列宽xlwt中列宽的值表示方法：默认字体0的1/256为衡量单位。xlwt创建时使用的默认宽度为2960，既11个字符0的宽度所以我们在设置列宽时可以用如下方法：width=256*20256为衡量单位，20表示20个字符宽度程序示例：效果如下：2.5xlwt设置行高在xlwt中没有特定的函数来设置默认的列宽及行高行高是在单元格的样式中设置的，你可以通过自动换行通过输入文字的多少来确定行高程序示例：效果如下：2.6xlwt合并列和行程序示例：效果如下：2.7xlwt添加边框程序示例：效果如下：2.8xlwt为单元格设置背景色程序示例：效果如下：2.9xlwt设置单元格对齐使用xlwt中的Alignment来设置单元格的对齐方式，其中horz代表水平对齐方式，vert代表垂直对齐方式。VERT_TOP=0x00上端对齐VERT_CENTER=0x01居中对齐（垂直方向上）VERT_BOTTOM=0x02低端对齐HORZ_LEFT=0x01左端对齐HORZ_CENTER=0x02居中对齐（水平方向上）HORZ_RIGHT=0x03右端对齐程序示例：效果如下：3.Pythonxlutils修改操作Excelxlutils可用于拷贝原excel或者在原excel基础上进行修改，并保存；官方文档：https://xlutils.readthedocs.io/en/latest/3.1pip安装xlutils安装过程：3.2xlutils拷贝源文件（需配合xlrd使用）表格内容如下：程序示例：效果如下：内容为：不过表格的样式全部消失了。3.3xlutils读取写入（也就是修改）Excel表格信息程序示例：效果如下：复制了源文件信息，并进行了追加：4.Pythonxlwings读取写入修改操作Excelxlwings比起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.html4.1pip安装xlwings4.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）写入列表默认按行插入：A11分别写入1,2,3,4等同于按列插入：A2:A5分别写入5,6,7,8你可能会想：但是你会发现xlwings还是会按行处理的，上面一行等同于：正确语法:既然默认的是按行写入，我们就把它倒过来嘛（transpose），单词要打对，如果你打错单词，它不会报错，而会按默认的行来写入（别问我怎么知道的）多行输入就要用二维列表了：2.读取数据（1）读取单个值（2）将值读取到列表中选取一列的数据先计算单元格的行数(前提是连续的单元格)接着就可以按准确范围读取了选取一行的数据4.5常用函数和方法1.Book工作薄常用的apiwb.activate()激活为当前工作簿wb.fullname返回工作簿的绝对路径wb.name返回工作簿的名称wb.save(path=None)保存工作簿，默认路径为工作簿原路径，若未保存则为脚本所在的路径wb.close()关闭工作簿代码示例：2.sheet常用的api3.range常用的api4.books工作簿集合的api4.sheets工作表的集合4.6数据结构1.一维数据python的列表，可以和Excel中的行列进行数据交换，python中的一维列表，在Excel中默认为一行数据。2.二维数据python的二维列表，可以转换为Excel中的行列。二维列表，即列表中的元素还是列表。在Excel中，二维列表中的列表元素，代表Excel表格中的一列。例如：3.Excel中区域的选取表格4.7xlwings生成图表示例代码：效果如下：4.8实战训练1.xlwings新建Excel文档程序示例：执行程序后文件夹增加了“example.xlsx”:此时表格是空的：2.xlwings打开已存在的Excel文档现有表格长这样：运行程序：生成新的表格：内容如下：3.xlwings读写Excel程序示例：执行效果：5.Pythonopenpyxl读取写入修改操作Excel在openpyxl中，主要用到三个概念：Workbooks，Sheets，Cells。Workbook就是一个excel工作表；Sheet是工作表中的一张表页；Cell就是简单的一个格。openpyxl就是围绕着这三个概念进行的，不管读写都是“三板斧”：打开Workbook，定位Sheet，操作Cell。官方文档：https://openpyxl.readthedocs.io/en/stable/官方示例：5.1openpyxl基本操作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.2openpyxl生成2D图表示例代码：效果如下：5.3openpyxl生成3D图表示例代码：效果如下：5.4实战训练1.openpyxl新建Excel程序示例：执行效果：并对sheet设置了和背景颜色：2.openpyxl打开已存在Excel程序示例：效果如下：3.openpyxl读写Excel程序示例：执行结果：6.Pythonxlswriter写入操作ExcelXlsxWriter是一个用来写Excel2007和xlsx文件格式的python模块。它可以用来写文本、数字、公式并支持单元格格式化、图片、图表、文档配置、自动过滤等特性优点：功能更多、文档高保真、扩展格式类型、更快并可配置缺点：不能用来读取和修改excel文件官方文档：https://xlsxwriter.readthedocs.io/6.1xlswriter基本操作1.安装xlswriter模块由于我已经安装过了，所以提示已经安装：2.创建excel文件3.创建sheet4.写入数据（1）写入文本（2）写入数字（3）写入函数（4）写入图片（5）写入日期（6）设置行、列属性5.自定义格式常用格式：字体颜色：color字体加粗：bold字体大小：font_site日期格式：num_format超链接：url下划线设置：underline单元格颜色：bg_color边框：border对齐方式：align6.批量往单元格写入数据7.合并单元格写入8.关闭文件6.3xlswriter生成折线图示例代码：效果如下：6.4xlswriter生成柱状图示例代码：效果如下：6.5xlswriter生成饼图示例代码：效果如下：6.6实战训练1.xlswriter新建并写入Excel程序示例：效果如下：7.Pythonwin32com读取写入修改操作Excelpython可以使用一个第三方库叫做win32com达到操作com的目的，win32com功能强大，可以操作word、调用宏等等等。7.1pip安装win32com由于我已经安装过了，所以提示已经安装：7.2Python使用win32com读写Excel程序示例：效果如下：内容为：8.Pythonpandas读取写入操作Excel简介：pandas是基于NumPy的一种工具，该工具是为了解决数据分析任务而创建的。Pandas纳入了大量库和一些标准的数据模型，提供了高效地操作大型数据集所需的工具。pandas提供了大量能使我们快速便捷地处理数据的函数和方法。你很快就会发现，它是使Python成为强大而高效的数据分析环境的重要因素之一。官方网站：https://pandas.pydata.org/官方文档：https://pandas.pydata.org/pandas-docs/stable/8.1pip安装pandas8.2pandas读写Excel表格内容如下：程序示例：效果如下：生成的excel如下：pandas功能非常强大，这里只是做了又给很简单的示例，还有很多其它操作，可参考官方文档或快速入门进行学习。读者福利：对Python感兴趣的童鞋，为此小编专门给大家准备好了Python全套的学习资料《完整版的Python的全套学习资料》（安全链接，放心点击）