|
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软件包。 |
|
|
来自： hercules028 > 《Python and AI》
