快速处理表格python_xlwings

快速处理表格python_xlwings 教程：使⽤Python更快速地处理

Excel

Excel在当今商业中的使⽤⾮常普遍。在Dataquest，出于很多原因，我们通常推荐使⽤代码处理数据，并且我们的许多数据科学课程的⽬

标是教授数据分析和数据科学的⾼效编码。但是，⽆论您多么喜欢使⽤Python，在⼀天结束时，有时您还是需要使⽤Excel来展⽰您的发现

或共享您的数据。

但这并不意味着我们就不能继续享受Python的⼀些效率了!实际上，使⽤⼀个名为xlwings的库，您可以使⽤Python使Excel运⾏得更快。

在这个xlwings教程中，我们将介绍如何在Excel中使⽤Python来执⾏和使⽤⼀些常见的操作，⽐如根据特定的条件删除⾏、使⽤Excel函数

和公式、⾃动填充、创建表单、图表等。为了跟随这篇⽂章，你应该熟悉基本的Python概念(对象、⽅法、属性、函数)佛手瓜怎么种植 和Python的语法，

并对Excel和VBA有⼀定的了解。

我们将使⽤⼀个数据集，其中包含有关欧洲彩票抽奖的信息，称为EuroMillions。这个数据集是从这个链接下载的，它包含了截⽌到9⽉20

⽇的所有EuroMillions彩票信息。当你阅读这篇⽂章的时候，这个链接上的数据应该会被更新为最新的信息，但是以防它是不可⽤的，这⾥

是⼀个CSV儿童画的画 ⽂件，其中包含了来⾃该链接的9⽉20⽇的数据。

Euromillions是⼀种跨国彩票，在⼀些欧洲国家有售，特别是在安道尔、奥地利、⽐利时、法国(包括海外地区和集体)、爱尔兰、马恩岛、

列⽀敦⼠登、卢森堡、摩纳哥、葡萄⽛、西班⽛、瑞⼠和英国(来源)。

在写这篇⽂章的时候，抽奖由从50个数字(从1到50)池中抽出的5个数字和从12个数字池中抽出的两个称为幸运之星的数字组成。为了赢得

头奖，参加者必须正确选择所有抽奖号码和幸运之星。史上最⼤的头等奖是1.9亿欧元。(请注意，我们的数据集是以英镑⽽不是欧元来计算

奖⾦的)。

在本教程中，我们将使⽤Python和xlwings来与Excel⼀起清理⼀个数据集，然后⽣成⼀些图形来可视化哪些数字最常赢得EuroMillions。

第⼀列是抽奖号码，第N1-L2列是已经抽到的号码和幸运星(按抽奖顺序排列)，Jackpot列是欧元的头奖，Wins列告诉我们有多少下注中了

头奖。

遇见 xlwings

请注意，您需要在使⽤本xlwings教程的计算机上安装⼀个Microsoft Excel版本。

xlwings 对象

在xlwings中，有四种主要的对象类型，按递减的层次顺序排列:App(代表⼀个Excel实例)、Book、Sheet和Range。除了这些之外，我们

还将处理Chart和Shape对象。您可以在官⽅⽂档中找到关于这些对象和其他对象的有⽤信息，但是我们将⼀次查看⼀个对象。

我们⾸先创建⼀个Book实例并将其命名为wb(⼯作簿)。

当您运⾏该代码时，它应该是这样的。

注意，当代码单元在Jupyter Notebook中运⾏时，Excel将⾃动启动。

通过实例化⼀个Book对象，⼀个属于我们的Book对象的App对象会被⾃动创建。下⾯是检查所有打开的Excel实例的⽅法。

注意:我们不打算在本教程中包含每个步骤的gif图⽚，因为我们不希望这个页⾯对于互联⽹连接缓慢或受限的⼈来说是⼀个⿇烦的加载过

程。但是，后续的代码运⾏步骤应该与我们上⾯所看到的类似:当我们在Juypter中运⾏⼀个单元格时，Excel电⼦表格将根据我们所运⾏的

代码进⾏更新。

对象是⼀个可迭代对象。要检查哪些⼯作簿属于这个可迭代对象的唯⼀实例，我们可以像这样对它调⽤books⽅法。

正如预期的那样，这个可迭代对象的惟⼀实例是⼯作簿wb。我们在下⾯来检查这个事实。

同样，我们可以检查哪些⼯作表属于这个⼯作簿:

我们也可以⽤它们的名字来引⽤⼯作表:

Sheet 对象有⼀个按预期⼯作的name属性。让我们更改我们唯⼀的⼯作表名称。

我们可以将数据从某些Python对象(例如列表和元组)移⼊到Excel中。让我们将我们dataframe中的数据移⼊到EuroMillions⼯作表中。为

此，我们将使⽤range创建⼀个range对象，该对象将我们的DataFrame中的数据存储在Excel中的⼀排单元格内，在本例中从单元格A1开

始:

>>>今⽇签到⼝令：r7kt<<<

现在运⾏结果是这样⼦：

可以看到，df的索引列也被移动到了Excel中。让我们清除此⼯作表的内容并复制不带索引的数据。

能够知道我们的表在哪⾥结束是很有⽤的。更具体地说，我们需要包含了数据的最后⼀⾏。为此，我们可以使⽤end⽅法和Range对象的

row属性。

不出所料，row⽅法会返回Range对象的row。

⽅法end接受⼀个⽅向(“up”(或1)、“right”(或2)、“left”(或3)、“down”(或4))作为参数，并返回另⼀个range对象。它模仿

Excel中常见的CTRL+Shift+箭头动作。

看，它检查出来了！

API属性

并不是所有的Excel功能都可以作为⼀个本地xlwings特性来使⽤。有时我们必须找到变通办法来做我们想做的事。幸运的是，xlwings让这

⼀切变得⾮常简单。来⾃官⽅⽂档中“缺失的特性”⼀节:

解决⽅案:本质上，xlwings只是⼀个围绕Windows平台上的pywin32和Mac平台上的appscript的灵活的包装器。你可以通过调⽤其api属

性来访问底层对象。底层对象将使⽤pywin32语法(感觉很像VBA)和appscript语法(感觉不像VBA)为您提供⼏乎所有可以⽤VBA做的事

情。但是除了看起来很丑之外，请记住它使您的代码平台变成特定的(!)。Excel Visual Basic for Applications是对各种现有Excel对象的

⼀个详细解释。

排序就是xlwings中缺少的功能之⼀。您可能已经注意到，那些记录是从最近到最远的抽签顺序排列的。在接下来的⼏个步骤中，我们将反

转顺序。

对象(“A2:N{row}”.format(row=last_row))是⼀个Range对象。将api属性追加给它之后，会⽣成⼀个VBA Range对象，该对

象反过来⼜可以访问它的VBA特性。

我们将使⽤这个VBA对象的Sort属性。在它的最简单的应⽤程序中，Sort接受两个参数:对表排序所使⽤的列(作为⼀个VBA Range对象)和

排序类型(⽆论希望按升序还是降序排序)。第⼆个参数的参数⽂档可以在这⾥看到。我们将按升序进⾏排序。

把所有这感恩母亲的诗 些放在⼀起看起来就是这样的:

下⾯是它运⾏后在屏幕上显⽰的样⼦(注意：第⼀列已经改变，八四年属什么生肖 现在是按升序⽽不是降序进⾏排序。

分析数据

在尝试分析这个数据集时，我们将遇到⼀个问题，即⽇期分散在三个不同的列中。我们需要把它压缩到⼀列中。为此，我们将使⽤Python

将Excel中的列适当地连接起来。我们⾸先在空的相邻列中插⼊⼀个头部。

接下来，我们可以插⼊想要⽤作字符串的Excel公式。注意:您应该使⽤什么参数分隔符的具体细节取决于您机器的本地区域设置。在我的⽰

例中，参数分隔符是逗号，这也是我在本教程中使⽤的，但在您的⽰例中，它可能是分号。

在第⼀个单元格中插⼊公式后，在常规的Excel⼯作流中，第⼆个特性是通过表的末尾⾃动填充其余单元格。Autofill是VBA Range对象的

⼀个⽅法。它接受两个参数，即⼀个将⽬标单元格作为参数的VBA Range对象和填充类型。我们感兴趣的是枚举为0的默认值。

这是执⾏这⼀步后屏幕显⽰的⼤致样⼦;注意最右边的新“Date”列。

我们还可以使⽤所需填充类型的命名形式。为此，我们需要从模块nts中检索它，该模块包含⼤多数VBA属性的枚举参数的

命名版本。您可以经常通过打印dir(郑重其事造句 nts)来检查可⽤的属性。

(如果您不熟悉它，dir是⼀个本地Python函数，可以接受多种参数(模块、类和常规对象(如列表和字符串))。例如，如果您打印

dir(some_list)，它将为您输出您可以通过列表使⽤的所有⽅法和属性。

我们在上⾯所做的也可以通过下⾯的代码⽚段来实现。

由于我们将经常使⽤这个操作，我们将创建⼀个应⽤默认填充的函数，给定:

⼀个⼯作表

⼀个表⽰⼯作表中单元格的字符串

⼀个需要填充的末尾⾏。

为此，我们将引⼊⼀个名为get_address的新的Range⽅法。它接受四个布尔参数并返回⼀个字符串，该字符串标识不同详细级别的范围。

下⾯是这个⽅法的⼀个很有启发性的说明。

现在我们来定义函数。

为了避免Excel进⾏不必要的计算，我们将使⽤硬编码的值替换刚才插⼊到列O中的公式。在此之前，让我们花点时间思考⼀下当Range是

⼀个数组时，是什么类型的Python对象。

它是⼀个列表。我们来看它的前⼗个元素。

如果我们将这个列表插⼊到任何范围中，它将会⽔平放置值，这不是我们想要的。为了将它们垂直放置，我们需要使⽤Range对象的

options⽅法，并将transpo=True选项作为⼀个参数，如下所⽰:

现在我们可以删除列C到E。

EuroMillions格式多年来经历了⼀些温和的修改，最后⼀次修改是在2016年9⽉24⽇。

从2016年9⽉24⽇起，幸运星的数量从11个数字变为12个数字。为了进⾏有意义的分析，我们只考虑在最后修改之后进⾏的抽奖。下⼀

个代码⽚段将查找修改之前的最后⼀次抽奖，并将其命名为to_delete。

我们现在可以删除从第⼀次抽奖到to_delete的每⼀⾏。

这是我们⽬前的情况，此时:

在完成数据准备之后，我们现在将格式化这个表。我们⾸先将第⼀⾏的字体设置为粗体。

作为后续⼯作的辅助步骤，我们将找到与最后⼀列数据对应的字母。

现在，我们在header单元格的底部添加边框。与我们所做的类似，我们将使⽤api属性。此外，我们还将需要Range对象的Bord心脏穴位 er属性、

边框对齐枚举和边框样式。我们将在header单元格的底部设置⼀个双边边框(⾏样式-4119，对齐⽅式9)。

现在让我们对⾏和列进⾏⾃动调整。

那样看起来更好⼀点。这个⼯作表我们就处理完成了!

让我们add⼀个名为Frequencies的新空⽩⼯作表，并将其分配给Python变量frequencies。

我们将在这个⼯作表中填⼊我们刚刚在河龟 EuroMillions⼯作表中处理过的数据集中每个数字和每个幸运星的绝对频率。

下⾯，我们将为单元格B1中的频率插⼊⼀个header，在单元格B2中，我们将输⼊⼀个公式，计算A2中的值在C2:G201范围内出现的次

数。换句话说，我们将计算1在N1-N5列中出现的次数。在此之后，我们将⾃动填充B列上的其余单元格，以对它们各⾃的⾏执⾏相同的操

作。

我们对幸运星也执⾏同样的操作:

此时，我们的新⼯作表看起来应该像这样：

我们正在接近我们的⽬标。让我们创建⼀个名为Graphs的⼯作表。

现在我们将创建⼀个Chart对象。这只会产⽣⼀个空⽩的⽩框，但是不要担⼼!我们稍后会⽤这个⽅框来图表化我们的数据。

我们可以像name⼯作表⼀样来name图表。⽅法t_source_data允许我们通过传⼊⼀个范围对象来定义我们图表的数据源。

Excel将尝试猜测x轴应该是什么，但我们可以使⽤VBA Chart⽅法FullSeriesCollection来强制让它成为我们在Frequencies上创建的数

字。我们可以通过使⽤nr_索引1的对象来编辑图表:

Excel⾮常擅长猜测⽤户想要什么样的图表，但是为了防⽌它猜错，我们将强制它成为柱状图。这⾥列出了各种类型的图表。唉，将这些图

表与chart_type属性的可能值连接起来的唯⼀⽂档就是其源代码本⾝。

现在我们将定义图表的⾼度和宽度。度量单位为points。

此时，我们应该会看到:

我们来添加最后的修饰。我们使⽤HasLegend属性删除图例。

我们将xlCategory类别作为参数1传⼊给Axes⽅法，并将TickLabelSpacing属性设置为1，这确保了图表轴上的每个元素都被显⽰。

为了完成对这个图表的格式化，我们通过将Line对象的Visible属性设置为0来删除轮廓。

这⾥我们会看到:

下⾯我们对幸运星也做了⼏乎同样的事情。

最后，我们创建了⼀个显梦到结婚是什么预兆 ⽰jackpot演变的时间序列图。

我们通过将TickLabels的NumberForma属性设置为期望的外观来修复垂直轴“labels”的格式。

这样我们就完成了!现在我们保存该⽂件并退出我们的Excel实例。

希望本xlwings教程对您有所帮助!

学习xlwings的⼀些有⽤资源包括官⽅⽂档、这个格式化备忘录、⽤于Excel⽂档的VBA和由xlwings的开发者——Felix Zumstein本⼈设计

的《xlwings: 使⽤Python处理Excel》课程。

译者：好酒不上头