excelperfect
虽然我们都能感知到“时间的流逝”,但却始终无法确定“时间”究竟是什么,唯一能确定的是,“时间”确实在一点点地失去,并且不可逆转。人的一生“时间”并不多,请珍惜你的时间!
2022年的第3天,将《Python for Excel》的这篇连载免费送给你,一起学起来。
引言:本文为《Python for Excel》第8章Chapter 8:Excel File Manipulation with Reader and Writer Packages的内容,主要讲解操作Excel文件的一些Python软件包,包括OpenPyXL、XlsxWriter、pyxlsb、xlrd和xlwt和xlutils,以及如何处理大型Excel文件、如何将pandas与reader和writer软件包结合以改进数据框架的样式等内容。
有兴趣的朋友,可以到知识星球完美Excel社群第一时间获取《Python for Excel》完整内容及其它丰富的资源。
本章介绍OpenPyXL、XlsxWriter、pyxlsb、xlrd和xlwt:这些软件包可以读取和写入Excel文件,当调用read_Excel或to_Excel函数时,pandas会在后台使用这些软件包。直接使用读(reader)和写(writer)软件包可以创建更复杂的Excel报告,此外,如果从事的项目只需要读取和写入Excel文件,而不需要其他pandas功能,那么安装完整的NumPy/pandas堆栈可能会有点小题大做。在学习一些高级主题之前,将首先学习何时使用哪个软件包以及它们的语法工作原理,包括如何使用处理大型Excel文件以及如何将pandas与reader和writer软件包结合以改进数据框架的样式。最后,我们将再次从上一章开始的案例研究,并通过格式化表格和添加图表来提升Excel报告。与上一章一样,本章不需要安装Excel,这意味着所有代码示例都可以在Windows、macOS和Linux上运行。
读写器包
读(reader)和写(writer)的情况可能有点令人难以接受:在本节中,我们将看到不少于六个包,因为几乎每种Excel文件类型都需要不同的包。每个包使用不同的语法(通常与原始的Excel对象模型有很大的差异)这一事实并没有使它变得更容易——在下一章中详细介绍Excel对象模型。这意味着可能需要查找大量命令,即使是经验丰富的VBA开发人员。本节首先概述何时需要哪个包,然后再介绍辅助模块,该模块让使用这些包变得更容易。之后,将以cookbook样式显示每个包,可以在其中查看最常用的命令是如何工作的。
何时使用哪个包
本节介绍以下六个用于读取、写入和编辑Excel文件的软件包:
xlwings在哪里?
如果想知道xlwings在表8-1中的位置,那么答案是不在任何地方,取决于你的情况:与本章中的任何软件包不同,xlwings依赖于Excel应用程序,而Excel应用程序通常不可用,例如,如果需要在Linux上运行脚本。另一方面,如果可以在Windows或macOS上运行脚本,并且可以在Windows或macOS上安装Excel,那么xlwings确实可以作为本章中所有软件包的替代品。由于对Excel的依赖性是xlwings与所有其他Excel软件包之间的根本区别,因此将在下一章介绍xlwings,这也是本书的第四部分。
pandas使用它可以找到的writer包,如果同时安装了OpenPyXL和XlsxWriter,则默认为XlsxWriter。如果要选择pandas应使用的软件包,分别在read_excel或to_excel函数或ExcelFile和ExcelWriter类中指定engine参数。engine是小写的包名,因此要使用OpenPyXL而不是XlsxWriter写入文件,运行以下命令:
df.to_excel(“filename.xlsx”, engine=”openpyxl”)
一旦知道需要哪个包,第二个挑战就在等待着你:这些包中的大多数都需要编写大量代码来读取或写入单元格区域,并且每个包使用不同的语法。为了让你更轻松使用,创建了一个辅助模块,下面将介绍它。
excel.py模块
已经创建了excel.py模块,使你在使用reader和writer软件包时更加轻松,因为它解决了以下问题:
包切换
必须切换读写器包是一种比较常见的情况。例如,Excel文件的大小往往会随着时间的推移而增大,许多用户通过将文件格式从xlsx切换到xlsb来解决这一问题,因为这可以大大减小文件大小。在这种情况下,将不得不从OpenPyXL切换到pyxlsb,这迫使你重写OpenPyXL代码以表达pyxlsb的语法。
数据类型转换
这与前一点有关:在切换包时,不仅需要调整代码的语法,还需要注意这些包为相同单元格内容返回的不同数据类型。例如,对于空单元格,OpenPyXL返回None,而xlrd返回空字符串。
单元格循环
读写器软件包是低级软件包:这意味着它们缺少方便的功能,使得能够轻松地处理常见任务。例如,大多数软件包都需要遍历要读或写的每个单元格。
在本书配套库中可找到excel.py模块,我们将在接下来的章节中使用它,下面是读取和写入值的语法:
import excel
values = excel.read(sheet_object,first_cell=”A1”, last_cell=None)
excel.write(sheet_object, values,first_cell=”A1”)
read函数接受来自下列任一包的工作表对象:xlrd、OpenPyXL或pyxlsb,还接受可选参数first_cell和last_cell。它们可以用A1表示法提供,也可以用Excel基于1的索引(1,1)作为行-列元组提供。first_cell的默认值为A1,而last_cell的默认值为所使用区域的右下角。因此,如果只提供sheet对象,它将读取整个工作表。write函数的工作原理类似:它接受一个来自xlwt、OpenPyXL或XlsxWriter的sheet对象,以及嵌套列表和可选的first_cell,该单元格标记嵌套列表将写入的位置的左上角。excel.py模块还使数据类型转换一致,如表8-2所示。
表8-2.数据类型转换
配备了excel.py模块,现在准备深入研究这些包:接下来的四个部分是关于OpenPyXL、XlsxWriter、pyxlsb和xlrd/xlwt/xlutils的。它们遵循一种食谱风格,允许快速开始使用每个包。我建议根据表8-1选择所需的包,然后直接跳到相应的部分,而不是按顺序阅读。
OpenPyXL
OpenPyXL是本节中唯一一个既可以读取又可以写入Excel文件的包,甚至可以使用它编辑Excel文件,尽管只是简单的文件。
使用OpenPyXL读取
下面的示例代码显示了在使用OpenPyXL读取Excel文件时如何执行常见任务。要获取单元格值,需要打开工作簿,其中data_only=True,其默认值为False,这将返回单元格的公式:
使用OpenPyXL写入
OpenPyXL在内存中构建Excel文件,并在调用save方法后写出该文件。下面的代码生成如图8-1所示的文件:
如果要写入Excel模板文件,则需要在保存之前将template属性设置为True:
正如在代码中看到的,OpenPyXL通过提供类似FF0000的字符串来设置颜色。该值由三个十六进制值(FF、00和00)组成,对应于所需颜色的红色/绿色/蓝色值。Hex代表十六进制,表示以十六为基数的数字,而不是我们标准的十进制系统使用的以十为基数的数字。
查找颜色的十六进制值
要在Excel中找到所需的颜色的十六进制值,单击用于更改单元格填充颜色的“填充”下拉列表,然后选择“更多颜色”,选择颜色并在“自定义”选项卡中读取其十六进制值。
图8-1.通过OpenPyXL写入的文件(openpyxl.xlsx)
使用OpenPyXL编辑
没有一个读/写程序包可以真正编辑Excel文件:实际上,OpenPyXL使用它所能理解的所有东西读取文件,然后从头开始重新写入文件,包括其间所做的任何更改。对于主要包含数据和公式的格式化单元格的简单Excel文件来说,这是非常强大的,但是当电子表格中有图表和其他更高级的内容时,这又是有限的,因为OpenPyXL将更改它们或完全删除它们。例如,从v3.0.5版本起,OpenPyXL将重命名图表并删除其标题。下面是一个简单的编辑示例:
如果要编写xlsm文件,OpenPyXL必须处理一个需要加载的现有文件,并将keep_vba参数设置为True:
示例文件中的按钮正在调用显示消息框的宏。OpenPyXL涵盖的功能比在本节中介绍的要多得多,因此,建议看看官方文档。当再次选取上一章中案例研究时,还将看到更多功能。
XlsxWriter
顾名思义,XlsxWriter只能写Excel文件。下面的代码生成的工作簿与之前使用OpenPyXL生成的工作簿相同,如图8-1所示。注意,XlsxWriter使用基于零的单元索引,而OpenPyXL使用基于一的单元索引。如果在包之间切换,确保考虑到了这一点。
与OpenPyXL相比,XlsxWriter必须采用更复杂的方法来编写xlsm文件,因为它是一个纯编写器包。首先,需要从Anaconda提示符上的现有Excel文件中提取宏代码(示例使用macro.xlsm文件,可以在配套文件的xl文件夹中找到该文件):
对于Windows,首先切换到xl目录,然后找到vba_extract.py(与XlsxWriter一起的脚本)的路径:
(我是按照我自己存储文件位置来输入的命令,因此与书中稍有不同。你可以根据你的实际情况作相应的调整)
这将保存文件vbaProject.bin到运行命令的目录中,也包括了在配套文件的xl文件夹提取的文件。在下面的示例中使用它编写带有宏按钮的工作簿:
pyxlsb
与其他读取器库相比,pyxlsb提供的功能较少,但在读取二进制xlsb格式的Excel文件时,它是唯一的选择。pyxlsb不是Anaconda的一部分,因此如果尚未安装,则需要安装它。但它目前也无法通过Conda获得,因此使用pip进行安装:
pip install pyxlsb
读取工作表和单元格值如下:
pyxlsb目前无法识别带有日期的单元格,因此必须手动将日期格式单元格中的值转换为datetime对象,如下所示:
记住,使用pandas 1.3以下的版本读取xlsb文件格式时,需要显示指定引擎:
df = pd.read_excel(r“D:\完美Excel\stores.xlsb”, engine=”pyxlsb”)
xlrd,xlwt和xlutils
xlrd、xlwt和xlutils的组合为传统xls格式提供了与OpenPyXL为xlsx格式提供的功能大致相同的功能:xlrd读取、xlwt写入和xlutils编辑xls文件。虽然这些软件包已不再被积极开发,但只要还有xls文件,它们就可能是相关的。xlutils不是Anaconda的一部分,需要安装:
conda install xlutils
使用xlrd读取
下面的示例代码显示如何使用xlrd从Excel工作簿读取值:
已使用区域
与OpenPyXL和pyxlsb不同,xlrd使用值返回单元格区域的尺寸,而不是使用sheet.nrows和sheet.ncols的使用区域。Excel返回的已用区域通常在该区域的底部和右边框处包含空行和空列。例如,当删除行的内容(通过单击delete键)而不是删除行本身(通过右键单击并选择delete)时,可能会发生这种情况。
使用xlwt写入
下面的代码再现了我们之前使用OpenPyXL和XlsxWriter所做的工作,如图8-1所示。但是,xlwt无法生成图表,仅支持图片的bmp格式:
使用xlutils编辑
xlutils充当xlrd和xlwt之间的桥梁,这表明不是真正的编辑操作:通过xlrd(通过设置formatting_info=true)读取电子表格,包括格式,然后通过xlwt再次写入,包括它们之间所做的更改:
至此,你已经知道了如何以特定格式读写Excel工作簿。下一节将继续介绍一些高级主题,包括处理大型Excel文件以及同时使用pandas和reader与writer软件包。
联系客服