从电子表格中读取数据,填充数据结构

import openpyxl,pprint

print('opening workbook...')
wb = openpyxl.load_workbook('censuspopdata.xlsx')
sheet = wb.get_sheet_by_name('Population by Census Tract')
countryData = {}
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
    print(state,county,pop)

    countryData.setdefault(state,{})
    countryData[state].setdefault(county,{'tracts':0,'pop':0})
    countryData[state][county]['pop'] += int(pop)

    print('Writing results...')
    resultFile = open('census2010.py','w')
    resultFile.write('allData = '+ pprint.pformat(countryData))
    resultFile.close()
    print('Done.....')
print(countryData)
print(countryData['CA']['San Francisco']['pop'])

• 结果是：

opening workbook...
Reading rows...
CA San Francisco 2685
Writing results...
Done.....
CA San Francisco 3894
Writing results...
Done.....
CA San Francisco 5592
Writing results...
Done.....
CA San Francisco 4578
Writing results...
Done.....
CA San Francisco 4320
Writing results...
Done.....
CA San Francisco 4827
Writing results...
Done.....
CA San Francisco 5164
Writing results...
Done.....
{'CA': {'San Francisco': {'tracts': 0, 'pop': 31060}}}
31060