Python利用openpyxl處理Excel文件(公式實例)

前面我們學習了Python使用openpyxl模塊處理Excel文件的大部分內容，今天，我們通過一個例子來學習Python使用Excel公式的方法，引出今天的主題利用openpyxl處理Excel公式。

一、使用openpyxl模塊處理Excel公式的方法

先構造一個應用Excel文件

# -*- coding: utf-8 -*-

from openpyxl import Workbook

from openpyxl import load_workbook

wb = load_workbook('example.xlsx')

ws=wb.active

ws["A1"]=1000

ws["A2"]=2000

ws["A3"]=3000

直接將公式內容寫入響應的Cell實例（單元格）中即可,為單元格設置公式就像設置其他文本值一樣。

ws["A4"] = "=SUM(1, 1)"

ws["A5"] = "=SUM(A1:A4)"

print(ws["A4"].value) # 默認只列印公式內容，不列印結果

print(ws["A5"].value) # 同上

# 保存文件

wb.save("example.xlsx")

Excel單元格中插入公式

插入公式後列印單元格內容

打開Excel表格後顯示效果（默認不設置任何參數的時候顯示公式計算後的數值），如下圖所示。

默認情況下Excel顯示效果

如果你希望看到該公式的計算結果，而不是原來的公式，就必須將load_workbook()的data_only關鍵字參數設置為True。這意味著Workbook對象要麼顯示公式，要麼顯示公式的結果，不能兼得（但是針對一個電子表格文件，可以加載多個Workbook對象）。

在交互式環境中輸入以下代碼，看看有無data_only關鍵字參數時，加載工作簿的區別。

（一）使用data_only參數

# -*- coding: utf-8 -*-

from openpyxl import Workbook

from openpyxl import load_workbook

wb = load_workbook('example.xlsx', data_only=True)

ws=wb.active

print(ws[『A4』].value)

print(ws[『A5』].value)

data_only為True時列印效果

在這裡必須說明的是，當使用data_only=True時，雖然你能看到公式及顯示的結果，除非你再次將Excel打開並保存一下，否則，公式是不會應用到Excel表格中，雖然你執行了wb.save(『example.xlsx』)操作。這是和不設置參數是不一樣的。

（二）使用data_only參數

# -*- coding: utf-8 -*-

from openpyxl import Workbook

from openpyxl import load_workbook

wb = load_workbook('example.xlsx')

ws=wb.active

print(ws[『A4』].value)

print(ws[『A5』].value)

默認不設置data_only時的效果

上面演示了data_only參數設置效果，當data_only=True請牢記上面的「坑」。

二、實例演示

我們以前面的「population.xlsx」為原始表格，統計「20XX年X國人口統計」表格的人口總數。將統計結果放在「人口數量」列的最後一行，並將該行前三個單元格進行合併（樣式自己定義哦）。

不贅述直接上代碼

import openpyxl

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

ws = wb.get_sheet_by_name(wb.get_sheet_names()[0])

row = ws.max_row

ws.merge_cells("A{0}:C{0}".format(row + 1))

ws['A{0}'.format(row+1)].value = "總計"

ws.cell(row = row + 1, column = 4).value = "=SUM(D3:D{0})".format(row)

# 保存

wb.save("population.xlsx")

程序運行效果圖

三、實例分析

Excel公式賦予電子表格一定程度的編程能力，但對於複雜的任務，很快就會失去控制。例如，即使你非常熟悉Excel的公式，要想弄清楚=IFERROR(TRIM(IF(LEN(VLOOKUP(F7,Sheet3!$C$1:$D$10000,2,FALSE))>0,SUBSTITUTE(VLOOKUP(F8, Sheet2!$B$3:$A$30000, 1, FALSE), " ", ""),"")), "")實際上做了什麼，也是一件非常頭痛的事。Python代碼的可讀性要好得多。因此，利用Python對打開的數據對象進行複雜的條件判斷，這個過程中使用的是Python語法，而非公式，這樣可以大大提高代碼的可讀性和處理具體問題的效率。上面的例子只是簡單的對求和公式進行了例舉，這個例子只起了個拋磚引玉的效果，如果涉及更多複雜的條件篩選單元格，使用Python進行處理是不錯的選擇，我們將在以後的梳理過程中逐漸增加這部分內容。對於Excel函數不怎麼感冒的同學，可以試試使用Python進行處理。

可愛的python

未完待續！