Python利用openpyxl處理Excel文件(實戰項目—人口統計)

之前，學習了兩章關於openpyxl的基礎知識，通過這些還不能了解openpyxl的強大作用。今天，帶領大家通過一個實戰項目認識openpyxl的強大之處，項目來自《Python編程快速上手—讓繁瑣工作自動化》一書，並對書中的例子進行了些許改動和擴充，本文旨在提供一種通過openpyxl處理大量Excel數據並進行數據統計的方法。項目數據來自網站（http://nostarch.com/automatestuff/），讀者可以自行下載。

一、實例

有一個Excel電子表格，表格內的數據統計了20XX年某國人口普查數據，表格有四列，分別是普查區（一個地理區域，為人口普查而定義）區域編碼、該區域所屬州的簡稱、該區域所屬縣的名稱、該區域的人口數量，表格中每一行表示一個人口普查區。現在要求統計該年度該國家的總人口、每個縣的人口以及每個縣裡面包含的普查區的個數，並把這些內容寫入工作簿的另一個工作表中。表格一共有7萬多行數據。數據表結構如下圖所示。

待處理的Excel表結構

二、實例分析

該實例通過Excel篩選功能或者公式解決，對於一個熟練操作Excel的苦逼文員，可能需要1到n個小時來解決該問題，通過Excel公式解決該問題也較為繁瑣。但是，如果我們利用openpyxl來統計該數據的話，只需要幾秒鐘就可以搞定了。下面我們分析解決問題的幾個重點環節……

（一）需要打開該Excel文件並定位到具體的工作表，使用openpyxl.load_Workbook()、ws.get_sheet_by_name()函數；

（二）確定返回統計信息的數據結構，這裡我們使用Python中的字典類型，popResult = {}。首先分析，每個縣裡面我們需要統計兩個參數：」普查區數量」和「縣的總人口」，於是，得到每個縣的數據結構為：「縣名稱」：｛「普查區數量」：n,」人口」:m｝，每個州裡面有n多個縣，我們最後統計的字典結構應該是這樣的：popResult = {「州名稱」:{「縣名稱」：｛「普查區數量」：n,」縣人口數」:m｝,……},……}。如果能理清這個數據結構，基本就算完成了一半任務。

（三）利用for循環遍歷每一行數據，將每一行數據統計到popResult字典中；

（四）按照相關要求將結果寫入本地Excel文件中。這一過程有個難點，就是如果直接遍歷字典的話，字典結構比較複雜，會嵌套多個for循環，使得程序可讀性變差，因此，我們將插入表格的每一行數據變成一個列表，然後循環遍歷，通過列表的形式比較方便插入。所以，這一步我們轉變了一次數據結構，使得問題得以方便解決。

三、編程解決問題

廢話不說，直接上代碼。

import openpyxl

popResult = {}

resultList = []

listItem = []

wb = openpyxl.load_workbook("population.xlsx")

# 只有一張表，用這種方式直接獲取

ws = wb.active

# range()計算邊界不含右邊界，所以加1

for row in range(2, ws.max_row + 1):

city = ws['B' + str(row)].value

country = ws["C" + str(row)].value

population = ws["D" + str(row)].value

# 此處避免出現異常，因此使用setdefault

popResult.setdefault(city, {})

popResult[city].setdefault(country, {"area":0,"population":0})

# 統計區域數量和人口數量

popResult[city][country]["area"] += 1

popResult[city][country]["population"] += population

# for循環將得到的字典popResult轉化為列表形式resultList，便於遍歷寫入countSheet表中

for city, country_info in popResult.items():

for country, info in country_info.items():

listItem.append(city)

listItem.append(country)

listItem.append(info['area'])

listItem.append(info["population"])

resultList.append(listItem)

# 置空便於下次循環

listItem = []

# 獲得寫入表countSheet的行數

numberRow = 0

for key, value in popResult.items():

numberRow += len(value)

print(numberRow)

# 然後將popResult寫入另一個表中

countSheet = wb.create_sheet(index=1, title="統計結果")

# 直接將表頭寫入工作表

countSheet['A1'].value = "市"

countSheet['B1'].value = "縣"

countSheet['C1'].value = "區域數量"

countSheet['D1'].value = "人口數量"

# 得到result_list

for row in range(2, numberRow + 1):

# 表格從第二行開始插入，而列表從index=0開始循環

countSheet['A' + str(row)] = resultList[row-2][0]

countSheet['B' + str(row)] = resultList[row-2][1]

countSheet['C' + str(row)] = resultList[row-2][2]

countSheet['D' + str(row)] = resultList[row-2][3]

# 保存修改，沒有這一步countSheet表沒有保存

wb.save("population.xlsx")

最終結果應該是這樣的。

最終結果展示

四、結果展望

一是不一定要將字典類型的數據結構轉化為列表類型，如果數據量過大，這種轉化既浪費時間，又可能得不到結果（死機啦），所以直接遍歷字典或者引入yield也是不錯的辦法，我們將在以後探討這個問題；

二是該程序提供了利用openpyxl進行Excel數據統計的一般方法。難點在於，如何構造一種或多種數據結構作為中介，來傳遞數據。

三是數據統計表中對於相同的市的單元格可以通過程序直接進行合併，這在以後的學習中再進行探討。

可愛的python

未完待續！