excel数据自动录入网页_自动化办公筛查、统计Excel数据

现老板提供一张2010年美国人口普查Excel数据表，该表有72864条数据。要求你查找OH(俄亥俄州)Preble普查区的人口数或者统计出各普查区的数目和人口数。在Excel中，你可能会进行下图所示的操作。

上述操作对一个普查区的人口进行统计还行，但若对所有州所有普查区的人口进行统计，该方法就费时费力。又得冲杯咖啡继续熬夜加班了！若采用自动化的方式，那此事就变得很简单。

程序需要完成如下工作：

• 从Excel表格中读取数据

• 计算每个州中普查区的数目

• 计算普查区的人口数

• 输出查询结果或保存计算数据

. 读取Excel表格

Excel表格只有一张表，名为“Population by Census Tract”。每一行都保存了一个普查区的数据，表列分别是普查区的编号(A列)、州的简称(B列)、普查区名称(C列)、普查区人口(D列)。

import openpyxlwb = openpyxl.load_workbook("./censuspopdata.xlsx")ws = wb.get_sheet_by_name("Population by Census Tract")countryData = {}for row in range(2, ws.max_row + 1):    state = ws["B" + str(row)].value    country = ws["C" + str(row)].value    pop = ws["D" + str(row)].value

ws.max_row用于获取表单中的数据行数。openpyxl.load_workbook(filename)方法用于加载Excel表格，返回一个workbook对象，该对象就代表这个Excel文件。workbook对象有如下方法：

方法	说明
get_sheet_names()	返回Excel工作簿中所有表单名称的列表
get_sheet_by_name()	传入表单名，返回worksheet对象
get_active_sheet()	取得工作簿中的活动表，返回worksheet对象

. 填充数据结构

countryData是一个字典，以州的简称为键，每个州将映射到另一个字典，其键是该州的普查区名称，每个普查区又映射到另一个字典，该字典拥有两个键，分别是tracts和pop，这些键映射到普查区数量和普查区人数。

countryData.setdefault(state, {})countryData[state].setdefault(country, {"pop":0, "tracts":0})countryData[state][country]["tracts"] += 1countryData[state][country]["pop"] += int(pop)

countryData.setdefault(state, {})返回以state为键的字典。当state键不存在于字典中时，将添加state为键并将值设置为{}。countryData[state][country]["tracts"] += 1用于统计各州普查区的数量，而countryData[state][country]["pop"] += int(pop)用于统计各州普查区的人口数。

. 终端查询

可以在终端查询出每个州的普查区数量和人口总数。利用pprint.pformat()方法将字典写入一个.py文件。

import pprintresultFile = open("census2010.py", "w")resultFile.write("allData = " + pprint.pformat(countryData))resultFile.close()

再创建一个readCensus2010.py文件用于查询：

import census2010import sysresult = census2010.allData[sys.argv[1]][sys.argv[2]]print(result)

sys.argv[1]和sys.argv[2]分别获取命令行第一个参数(州名称)和第二个参数(普查区名称)。

运行结果显示，2010年俄亥俄州的Preble普查区12个，人口数42270。

. 数据保存

将统计的数据写入到另一个Excel表格中，便于保存查询。

workbook = openpyxl.Workbook()sheet1 = workbook.activesheet1.title = "统计表"sheet1.append(["州名", "普查区名称", "普查区数目", "人口数"])for k1,v1 in countryData.items():    for k2,v2 in v1.items():        sheet1.append([k1,k2,v2["tracts"],v2["pop"]])workbook.save("filename.xlsx")

统计出来的结果如下图所示：

---