openpyxl操作excel：项目练习--从电子表格中读取数据

从电子表格中读取数据 :

假定你有一张电子表格的数据，来自于 2010 年美国人口普查。你有一个无聊的任 务，要遍历表中的几千行，计算总的人口，以及每个县的普查区的数目（普查区就是一 个地理区域，是为人口普查而定义的）。每行表示一个人口普查区。我们将这个电子表格 文件命名为 censuspopdata.xlsx，可以从 http://nostarch.com/automatestuff/下载它。它的 内容如图 12-2 所示。 

尽管 Excel 是要能够计算多个选中单元格的和，你仍然需要选中 3000 个以上县 的单元格。即使手工计算一个县的人口只需要几秒钟，整张电子表格也需要几个小 时时间。 

在这个项目中，你要编写一个脚本，从人口普查电子表格文件中读取数据，并 在几秒钟内计算出每个县的统计值。 下面是程序要做的事：

• 从 Excel 电子表格中读取数据。
• 计算每个县中普查区的数目。
• 计算每个县的总人口。
• 打印结果。

这意味着代码需要完成下列任务：

• 用 openpyxl 模块打开 Excel 文档并读取单元格。
• 计算所有普查区和人口数据，将它保存到一个数据结构中。
• 利用 pprint 模块，将该数据结构写入一个扩展名为.py的文本文件。 

 

censuspopdata.xlsx 电子表格中只有一张表，名为'Population by Census Tract'。每 一行都保存了一个普查区的数据。列分别是普查区的编号（A），州的简称（B）， 县 的名称（C），普查区的人口（D）。 打开一个新的文件编辑器窗口，输入以下代码。将文件保存为readCensusExcel.py。 

import openpyxl
import pprint
print('Opening workbook...')
wb = openpyxl.load_workbook(r'C:\Users\Cherry\Desktop\censuspopdata.xlsx')
sheet = wb.get_sheet_by_name("Population by Census Tract")
countyData = {}
'''
保存在 countyData 中的数据结构将是一个字典，以州的简称作为键。每个州的简称将映射到另一个字典，其中的键是该州的县的名称。
每个县的名称又映射到一 个字典，该字典只有两个键，'tracts'和'pop'。这些键映射到普查区数目和该县的人口。
state:州、county:县、tracts:普查区数目、pop:该县的人口；
'''
print('Reading rows...')
# 读取电子表格数据
for row in range(2,sheet.max_row + 1): # 第一行是表头行，数据从第二行开始
    state = sheet['B' + str(row)].value
    county = sheet['C' + str(row)].value
    pop = sheet['D' + str(row)].value

    # 填充数据结构
    countyData.setdefault(state, {})
    countyData[state].setdefault(county, {'tracts': 0, 'pop': 0})
    countyData[state][county]['tracts'] += 1
    countyData[state][county]['pop'] += int(pop)

# 将结果写入文件
print('Writing results...')
resultFile = open('census2010.py', 'w')
resultFile.write('allData = ' + pprint.pformat(countyData))
resultFile.close()
print('Done.')

填充数据结构：

为了确保州简称的键存在，你需要调用 setdefault()方法，在 state 还不存在时设置一个默认值。正如 countyData 字典需要一个字典作为每个州缩写的值，这样的字典又需要一 个字典，作为每个县的键的值。这样的每个字典又需要键'tracts'和'pop'，它们的初 始值为整数 0。如果键已经存在，setdefault()不会做任何事情，因此在 for 循环的每次迭代中调 用它不会有问题。 

将结果写入文件：

for 循环结束后，countyData 字典将包含所有的人口和普查区信息，以县和州为 键。这时，你可以编写更多代码，将数据写入文本文件或另一个 Excel 电子表格。 目前，我们只是使用 pprint.pformat()函数，将变量字典的值作为一个巨大的字符串， 写入文件 census2010.py。

pprint.pformat()函数产生一个字符串，它本身就是格式化好的、有效的 Python 代码。将它输出到文本文件 census2010.py，你就通过 Python 程序生成了一个 Python 程序！这可能看起来有点复杂，但好处是你现在可以导入 census2010.py，就像任何其他 Python 模块一样。在交互式环境中，将当前工作目录变更到新创建的文件所在的 文件夹，然后导入他，如下：

Python 3.6.5 (v3.6.5:f59c0932b4, Mar 28 2018, 17:00:18) [MSC v.1900 64 bit (AMD64)] on win32
Type "copyright", "credits" or "license()" for more information.
>>> import os
>>> os.chdir(r'D:\Dev\PyCharm\workplace\redmine')
>>> import census2010
>>> print(census2010.allData['AK']['Aleutians East'])
{'pop': 3141, 'tracts': 1}
>>> anchoragePop = census2010.allData['AK']['Anchorage']['pop']
>>> print('The 2010 population of Anchorage was ' + str(anchoragePop))
The 2010 population of Anchorage was 291826
>>> 

readCensusExcel.py程序是可以扔掉的代码：当你把它的结果保存为census2010.py 之后，就不需要再次运行该程序了。任何时候，只要需要县的数据，就可以执行 import census2010。 手工计算这些数据可能需要数小时，这个程序只要几秒钟。利用 OpenPyXL，可 以毫无困难地提取保存在 Excel 电子表格中的信息，并对它进行计算。从 http://nostarch. com/automatestuff/可以下载这个完整的程序。

类似程序的思想：

许多公司和组织机构使用 Excel 来保存各种类型的数据，电子表格会变得庞大，这 并不少见。解析 Excel 电子表格的程序都有类似的结构：它加载电子表格文件，准备一 些变量或数据结构，然后循环遍历电子表格中的每一行。这样的程序可以做下列事情： 

• 比较一个电子表格中多行的数据。 
• 打开多个 Excel 文件，跨电子表格比较数据。 
• 检查电子表格是否有空行或无效的数据，如果有就警告。 
• 从电子表格中读取数据，将它作为 Python 程序的输入。