在日常生活和生产活动中，大量的数据处理工作是通过Excel表格进行的。借助于openpyxl模块，我们也可以使用Python对Excel表格数据进行计算和读写。

版权声明

本文可以在互联网上自由转载，但必须：注明出处(作者：海洋饼干叔叔)并包含指向本页面的链接。

本文不可以以纸质出版为目的进行改编、摘抄。

  如图8-6所示，在随书代码的CH8目录下，Excel表格文件“居民消费支出.xlsx”存储了我国1998 ~ 2002年的居民人均消费支出（列B）和居民人均食品烟酒支出（列C）金额，列D居民恩格尔系数为空。

image127

图8-6 居民消费支出

  作为一个经济学概念，恩格尔系数反应了居民食品支出在消费总支出中的占比。通常而言，恩格尔系数越低，说明居民越富裕，生活水平越高。较低的恩格尔系数意味着居民仅使用消费总支出的较少部分即可满足基本的食物需求，从而将多数收入用于改善生活品质，包括汽车、服装及各种服务消费。

image-20230919164215084

  接下来，我们使用openpyxl库读取该Excel文件，并依次计算各年度的居民恩格尔系数，然后填写至列D。

1
2
3
4
5
6
7
8
9
10
11
#engel.py
from openpyxl import load_workbook

workBook = load_workbook("居民消费支出.xlsx")
workSheet = workBook.worksheets[0]
for idx, row in enumerate(workSheet.rows):
    if idx==0:
        continue 
    row[3].value = row[2].value / row[1].value

workBook.save("恩格尔系数.xlsx")

上述程序未在控制台上输出任何内容。该程序执行完成后，在当前目录得到新的Excel文件“恩格尔系统.xlsx”，使用Excel或者WPS软件打开，文件内容如图8-7所示。

image129

图8-7 恩格尔系数

🚩第2行：openpyxl是用于读取、解析、修改Excel文件的包。请读者参考本书1.6.3节中介绍的方法使用pip工具自行安装。

🚩第4行：load_workbook()函数读取并解析一个Excel文档。本例中，解析后的表格内容赋值给了workBook。

🚩第5行：一个Excel文件可包含多个子表（sheet），第0个子表为workBook.worksheets[0]。

🚩第6 ~ 9行：使用for循环逐行遍历workSheet.rows中的全部行。请读者留意此处enumerate()函数的使用，借助于enumerate()函数，我们在遍历过程中，不光获得了每行的内容row，还同时获得了行号idx。

🚩第7 ~ 8行：当行号idx等于0时，说明该行为表格的标题行，无需进行任何处理，使用continue语句略过。

🚩第9行：对于行对象row，row[i]代表该行的第i列（从0开始计数），row[i].value则代表该单元格内的值。本行代码第2列的值（人均食品烟酒支出金额）除以第1列的值（人均消费支出总额），得当年度的恩格尔系数，将其赋值给第3列。

🚩第11行：将修改后的表格数据workBook保存为Excel文件“恩格尔系数.xlsx”。由于文件名中未包含路径信息，该文件将被创建在当前工作目录下。

通常情况下，图比数据更加直观，更容易看出变化。进一步地，我们使用下述程序读取Excel文件“恩格尔系数.xlsx”中的内容，并将1998 ~ 2022年度的消费支出、恩格尔系数变化绘制成曲线。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#engelPlot.py
from openpyxl import load_workbook

sheet = load_workbook("恩格尔系数.xlsx").worksheets[0]
years = [r[0].value for r in sheet.rows]
payouts = [r[1].value for r in sheet.rows]
engels = [r[3].value for r in sheet.rows]

from matplotlib import pyplot as plt, ticker as ticker
plt.rcParams["font.family"] = "Microsoft YaHei"   
plt.figure(figsize=(12,5))

ax = plt.subplot(121)
ax.plot(years[1:][::-1],payouts[1:][::-1],"-d")
ax.xaxis.set_major_locator(ticker.MultipleLocator(4))
ax.set_title("居民人均消费支出（元，1998-2022）")

ax = plt.subplot(122)
ax.plot(years[1:][::-1],engels[1:][::-1],"-o")
ax.set_title("居民恩格尔系数（1998-2022)")
ax.xaxis.set_major_locator(ticker.MultipleLocator(4))

plt.subplots_adjust(0.05,0.05,0.95,0.95,wspace=0.1,hspace=0.25)
plt.show()

上述代码的执行后，得到如图8-8所示的绘图结果。在1998至2022年期间，在居民人均消费支出稳步上升的同时，我国居民恩格尔系数总体下降。随着收入水平的提高，人民群众将更多的消费支出投向了家装、汽车、服装、旅行等领域，生活质量有了显著提升。

image130

图8-8 消费支出与恩格尔系数的变化趋势

🚩第4行：使用load_workbook()函数读取并解析Excel文件的内容，并直接取得第0个子表（sheet）。

🚩第5行：使用列表推导语法从子表sheet中获取第0列所有单元格的值，即年份信息。关于列表推导语法的细节，请回顾5.2节。本例中，sheet.rows包含了子表中的全部行。对于当前迭代行r而言，r[0].value对应该行第0列的单元格的值，根据图8-7，表格第0列存储的是年份信息。

🚩第6 ~ 7行：使用与第5行相同的语法获取消费支出列表和恩格尔系数列表。

🚩第9行：导入matplotlib绘图库中的pyplot和ticker。其中，pyplot用于绘图，ticker用于控制x坐标的显示密度。

🚩第10行：将绘图库的字体设置为“微软雅黑”，以便正确显示中文。

🚩第11行：在图（Figure）窗口的尺寸设为12英寸宽，5英寸高。

🚩第13行：plt.subplot(xyz)将当前图窗口分为x行y列，然后在其中第z个（从1开始计数）单元格创建子图（axes）并返回。本例中，xyz为121，故图被分成1行2列，新的子图被创建在第1个单元格，即图8-9中的子图1的位置。

image131

图8-9 图与子图

🚩第14行：使用子图ax的成员函数plot()绘制折线图。

years[1:][::-1]以列表形式提供了折线图中所有离散点的x坐标，即年份信息。请读者留意此处我们对years列表进行了两轮切片，[1:]去除了原始表格中的标题行，[::-1]则对列表元素倒序处理，因为原始表格中的数据是从2022年开始然后递减至1998年的。

payouts[1:][::-1]则以列表形式提供了折线图中所有离散点的y坐标，即人均消费支出信息。基于同样的理由，这里也进行了两轮切片处理。

“-d”指定了折线图使用的线型以及离散点形状。其中，-表示使用实线，d表示使用菱形来表示离散数据点。

🚩第15行：从1998年到2022年，共有24个年份信息。这么多的年份信息在x轴上挤不下。为了解决这个问题，本行代码给x轴自定义了坐标显示规则，每4个坐标显示1个。要想详细解释本行代码的工作原理，需要很多的篇幅，但对于多数读者而言，直接照葫芦画瓢就足够了，因此略去不提。

🚩第16行：设置子图ax的标题。

🚩第18 ~ 21行：使用相同过程和方法绘制“居民恩格尔系数”折线图。其中，第18行的122表示将图窗口分为1行2列，在其中第2个单元络创建子图。这个新建的子图对应图8-9中的子图2。

🚩第23行：调整图的边距以及子图间距。在plt.subplots_adjust()函数里，图窗口被“假设”为宽度为“1”，高度为“1”的矩形，“0.05,0.05,0.95,0.95”部分给出了绘图范围的左下角和右上角坐标，这相当于在图窗口的上下左右各留白0.05，请见图8-9中的虚线框。wspace和hspace则分别给出子图间的横向和纵向间距。

🚩第24行：将后台绘制完成的图显示出来。

Excel文件 - 居民消费支出.xlsx： 点击下载

Excel文件 - 恩格尔系数.xlsx： 点击下载


  Python