【導語】：openpyxl 和 formulas 是兩個成熟的開源庫，在Python中借助這兩個庫，處理Excel電子表格，可以實現(xiàn)自動訪問、處理表格中數(shù)據(jù)的功能，省時高效，不易出錯，是處理Excel表格的一種好辦法。

簡介

Excel在工作中很常見，許多公司的軟件項目都會用到它。對于應用程序開發(fā)者而言，我們經常需要將Excel文件轉換為應用程序。大多數(shù)情況下我們都把Excel作為數(shù)據(jù)的導出格式，有時也將其作為數(shù)據(jù)的輸入格式。

雖然Excel并不是一個軟件領域的專用語言，但一些軟件領域的專家常常需要用其處理問題。這就導致會“說”Excel的軟件更受用戶的青睞。即使從長遠來看，我們的目標是把Excel變成一個應用程序或者軟件領域的一種語言（DSL），但能自動處理Excel中的數(shù)據(jù)也是一個重要方向。

在以下情景中，自動處理Excel數(shù)據(jù)將非常有用：

例如在一個項目中，許多用戶需要使用某些相同的Excel文件，而應用程序通過處理這些Excel文件，讓用戶使用起來更加方便。或者當一些用戶想用Excel中的數(shù)據(jù)生成帶有附加圖表和formulas的報告時，應用程序可以生成一個電子表格，用來輸出一些查詢或計算結果，方便用戶使用這些數(shù)據(jù)。

在本文中，我們將探索如何使用openpyxl庫和其他工具，結合Python處理Excel電子表格。主要分為以下幾點：

處理Excel文件，用不同的方法訪問其中的數(shù)據(jù);

使用formulas;

輸出Excel文件。

注意：本文中我們處理的只是“現(xiàn)代”（2010）基于xml的Excel文件，文件擴展名為.xlsx。Openpyxl不支持之前的二進制Excel文件（.xls），針對這類文件有其他的解決方案，在本文中不做討論。本文中的所有示例代碼均可在GitHub倉庫中找到。

項目地址：

https://github.com/Strumenta/article-python-excel

安裝

下面，我們設定一個類似unix的環(huán)境-Linux、OSX或者WSL。第一步，創(chuàng)建一個目錄來存放我們的項目：

mkdir python-excel

cd python-excel

如果想要把代碼放到Github上，我們可以在Github上創(chuàng)建一個倉庫，然后把它克隆到本地：

git clone 《repo-url》 python-excel

cd python-excel

這將使我們能在GitHub為Python生成一個.gitignore文件，為我們生成一個README文件和一個license文件。在創(chuàng)建了項目根目錄之后，我們可以使用pip安裝來openpyxl。我們需要設置一個虛擬環(huán)境，這樣就不會影響到系統(tǒng)的Python安裝：

python -m venv .venv

source .venv/bin/activate

激活環(huán)境后，我們應該可以在shell prompt中看到：

（.venv） python-excel （main*） ?

隨后我們寫一個requirements.txt文件，列出我們的依賴包：

echo openpyxl 》 requirements.txt

即使當前只有一個依賴包，也要將它列在一個可以被機器解析的文件中，這樣做會使其他開發(fā)人員更容易地使用我們的項目-即使在之后的版本中我們已經忘記了這個依賴包。之后用pip安裝所需的依賴包：

python -m pip install -r requirements.txt

簡單使用

1.加載一個Excel文件

在Openpyxl中，我們把Excel文件稱為“workbook”，用openpyxl.workbook.Workbook類的實例來表示，打開一個Excel文件非常簡單：

wb = load_workbook（path）

調用load_workbook的結果

Openpyxl打開文件后，一般可以同時進行讀取和寫入工作，除非我們給load_workbook設置一個read_only=True參數(shù)，表示只讀取文件，當我們使用完一個Excel文件后，必須關閉它：

wb.close（）

不幸的是，Workbook不是一個“文件管理者”，所以不能用Python中的語句來自動關閉它。即使在一些exceptions的情況中，也必須得手動關閉文件：

wb = load_workbook（path）

try：

# Use wb.。.

finally：

wb.close（）

2.處理一個Excel文件 - 通用案例

通常，Workbooks中可能有幾個表，我們需要選擇Excel文件中的一個表，訪問其中的數(shù)據(jù)。隨后，我們再學習如何處理多個表。現(xiàn)在，假設我們對active工作表中的數(shù)據(jù)比較感興趣——當用戶在他們的應用中打開文件就會看到的工作表：

sheet = wb.active

這實際上是文檔中最常用的表。我們可以用不同的方法訪問表格中的數(shù)據(jù)。我們可以使用Pythonic生成器每次處理一行數(shù)據(jù)

（1）對行進行遍歷：

for row in sheet.rows（）：

# Do something with the row

rows（）產生的行，本身就是一個生成器，我們可以遍歷它們：

for row in sheet.rows（）：

for cell in row：

# Do something with the cell

還可以根據(jù)索引訪問數(shù)據(jù)：

for row in sheet.rows（）：

header = row［2］

實際上，表格本身就是可以按行進行迭代的，所以我們可以忽略所有行：

for row in sheet：

pass

（2）使用cols方法對列進行遍歷。

for col in sheet.cols（）：

# Use the column

遍歷列與遍歷行的操作基本相同：它們本身都是可迭代的，并且可以通過索引尋址。

（3）通過地址訪問單元格。

如果我們需要某個單元格中的數(shù)據(jù)，那么并不需要遍歷整個表格去找;可以使用excel樣式的坐標來訪問這個單元格：

cell = sheet［‘C5’］

在隨后的章節(jié)中，將展示如何從一行，一列，或者一些單元格中獲取一個生成器。

3.處理單元格

在任何情況下，想要處理電子表格中的數(shù)據(jù)，就必須訪問每個單元格。在Openpyxl中，單元格有一個值和許多僅用于編寫的其他信息，比如樣式信息。更方便地是，我們可以把單元格中的值作為Python對象（數(shù)字、日期、字符串等），用Openpyxl將它們轉換為Excel類型。因此，單元格內容就不一定要是字符串。例如，我們以數(shù)字的形式讀取單元格的內容：

tax_percentage = sheet［‘H16’］.value

tax_amount = taxable_amount * tax_percentage

然而，我們并不能保證用戶一定在單元格中輸入了數(shù)字;如果它包含字符串“bug”，如果比較幸運的話，在運行上面的代碼后，我們會得到一個運行錯誤：

TypeError： can‘t multiply sequence by non-int of type ’float‘

然而，在不那么幸運的情況下，例如，當taxable_amount是一個整數(shù)時——因為我們在示例中處理的數(shù)據(jù)是錢，所以應該是整數(shù)——我們將得到一個重復taxable_amount次的“bug”。這是因為Python把*操作符當成了字符串，整數(shù)就意味著“重復字符串n次”。這可能會導致進一步的類型錯誤，或者在Python無法放置如此大的字符串時出現(xiàn)內存錯誤。因此，我們應該驗證程序的輸入，包括Excel文件。在這個特殊的例子中，我們可以使用Python的isinstance函數(shù)來檢查單元格中值的類型：

if isinstance（cell.value， numbers.Number）：

# Use the value

我們還可以詢問單元格它存儲的數(shù)據(jù)類型是什么：

if cell.data_type == TYPE_NUMERIC：

# Use the numeric value

4.單元格高級尋址

到目前為止，我們已經使用了訪問單元格最簡單、最直接的方法。然而，這并不是所有方法;讓我們來看看更復雜的方案。

（1）除了active之外的其他表。

我們可以通過在workbook中通過名稱來訪問它們：

sheet = wb［’2020 Report‘］

然后我們就可以像之前看到的那樣訪問單元格了。

（2）單元格范圍

我們不一定要一個一個的尋址單元格-還可以設定范圍來訪問單元格：

sheet［’D‘］是指一整行（本例中是D這一行）

sheet［7］是指一整列（本例中是第7列）

sheet［’B:F‘］代表許多行

sheet［’4:10‘］ 代表許多列

sheet［’C3:H5‘］是最通用的選擇，代表任意范圍的單元格

以上任何一種情況，結果都是一個按行迭代所有單元格（除非迭代的范圍以列為標準，在這種情況下，單元格按列順序進行迭代）：

for cell in sheet［’B2:F10‘］：

# B2， B3， 。..， F1， F2， 。..， F10

for cell in sheet［’4:10‘］：

# A4， B4， 。..， A10， B10， 。..

sheet［’B2:F10‘］中的單元格

sheet［’4:10‘］中的單元格

5.單元格迭代器

如果上述尋址方案解決不了問題，那我們可以考慮一些簡單的方法iter_rows和iter_columns，它們分別按行和列返回單元格生成器。需要指出，這些方法都需要5個參數(shù)：

min_row - 起始行的編號（1就是A，2就是B，以此類推）

min_col - 起始列的編號

max_row - 最后一行的編號

max_col - 最后一列的編號

values_only - 生成器將只顯示每個單元格的值，而不是整個單元格對象。所以，我們不需要用cell.value，而只要value。另一方面，我們不能訪問單元格的其他屬性，比如data_type。例如，如果我們想按列在B2:F10的范圍上進行迭代，可以這樣寫：

for cell in sheet.iter_columns（min_row=2， min_col=2， max_row=6， max_col=10）：

# Use the cell

6.編寫一個Excel文件

要寫一個Excel文件，我們只需在workbook上調用save方法：

wb.save（’someFile.xlsx‘）

知道如何保存一個workbook后，讓我們看看如何修改它，這將會很有趣。我們可以修改文件中的workbook，也可以修改在Python中創(chuàng)建的workbook。

7.修改單個單元格

我們可以用指定的方式來改變一個單元格中的值：

cell.value = 42

這會自動更新單元格的數(shù)據(jù)類型以存儲新的值。除了基本類型（整數(shù)、浮點數(shù)、字符串）之外，還包括datetime模塊中的各種類，如果你安裝了NumPy，那么NumPy數(shù)字類型也可以使用。不僅可以設置值和類型，我們還可以設置單元格的其他屬性，特別是樣式信息（字體、顏色等），這對做一個好看的報告很有用。

Openpyxl的文檔中有許多關于調整樣式的詳細信息，我們可以在這里查詢：

https://openpyxl.readthedocs.io/en/stable/styles.html

8.添加或移除表格

到目前為止，我們已經看到了如何處理一些對象，特別是workbooks和worksheets——就像處理字典一樣，訪問其中的細節(jié)：工作表、行、列、單個單元格、單元格范圍。現(xiàn)在，我們將學習如何添加新信息，以及如何更改現(xiàn)有信息。我們先從表格開始。

使用 create_sheet方法來創(chuàng)建worksheet：

new_sheet = wb.create_sheet（）

這樣就可以在workbook中的其他表格之后添加一個新表，我們可以給這個新表一個標題：

new_sheet = wb.create_sheet（title = ’My new sheet‘）

如果我們想把這個表格放在其他位置，我們可以指定它的索引（從0開始）：

# The new sheet will be inserted as the third sheet

new_sheet = wb.create_sheet（index = 2）

要刪除一個表格的話有兩種方法。可以根據(jù)名字進行刪除：

del wb［’My sheet‘］

我們可以使用in操作符來查看給出的表格名稱是否在workbook中：

name = ’My sheet‘

if name in workbook：

del workbook［name］

或者還能調用remove方法來刪除表格：

wb.remove（sheet）

9.增加或移除行、列、單元格

看看下面這些例子。首先通過訪問一個單元格，可以為創(chuàng)建行和列騰出空間：

wb = Workbook（）

# Initially， an empty worksheet has a single row and column， A and 1

self.assertEqual（wb.active.max_row， 1）

self.assertEqual（wb.active.max_column， 1）

# We set the value of the cell at C3;

# openpyxl creates rows B， C and columns 2， 3 automatically

wb.active［’C3‘］.value = 12

# Now the sheet has 3 rows and columns

self.assertEqual（wb.active.max_row， 3）

self.assertEqual（wb.active.max_column， 3）

wb.close（）

此外，我們還可以用insert_rows和insert_cols方法在表格中添加行或列。當新創(chuàng)建一行或一列時，單元格會自動調整：

wb = Workbook（）

self.assertEqual（wb.active.max_row， 1）

wb.active［’A1‘］.value = 11

# Insert 3 rows， starting at index 0 （i.e. row 1）

wb.active.insert_rows（0， 3）

self.assertEqual（wb.active.max_row， 4）

# Note how the cell， A1， has automatically moved by 3 rows to A4

self.assertEqual（wb.active［’A4‘］.value， 11）

與此相對應，還可以使用delete_rows和delete_cols來刪除行或列：

# Delete 2 columns， starting from index 1， i.e. column B

sheet.delete_columns（1， 2）

10.使用formulas

電子表格非常強大，因為它還支持用formulas來計算單元格中的值。當其他單元格發(fā)生變化時，通過計算取得值的單元格也會自動更新。讓我們看看如何使用在Openpyxl中使用formulas吧。首先，如果我們只想讀取一個Excel文件，我們可以完全忽略formulas。

此時，以“data only”模式打開它，這種模式將會隱藏formulas，所有單元格中的值都是固定的-也就是上次Excel文件計算后的結果：

wb = load_workbook（filename， data_only=True）

只有在修改Excel文件時，才需要重新使用formulas。雖然openpyxl有一些對解析formulas的支持（例如，檢查是否只調用了已知函數(shù)），但openpyxl自身不能產生formulas。因此，如果我們想要使用formulas，就必須求助于第三方庫。進入一個叫做“formulas”的庫。讓我們把它添加到requirements.txt文件中并安裝它：

$ cat requirements.txt

openpyxl

formulas

pip install -r requirements.txt

我們有兩種方法來使用formulas庫：

（1）像Excel那樣，使用所有formulas，計算workbook中的值;

（2）將單獨的formulas寫入Python函數(shù)，這樣就可以放入不同的參數(shù)，來使用這些formulas。

11.計算所有formulas中的值：

第一個示例比較枯燥，因為功能與前面的data_only方法作用相似。事實上，在那種模式下，我們加載不了workbook，修改不了它，也不能重新使用其中的formulas。我們必須：

把修改后的workbook保存到一個文件中；

用formulas重新加載文件；

調用API使用formulas；

把計算后的值保存到文件中；

在data_only模式下用openpyxl打開文件，查看計算后的結果。

這簡直是在浪費時間！

當然，formulas庫的這個特性也有價值，因為它支持一些高級的用法：

同時在多個workbook中使用formulas

Excel中，可以在另一個文件中引用其他文件中的formulas。formulas可以在同一個集合中加載多個workbook，以解決這種跨文件引用問題，這是Excel中很少使用的特性。

將整個Excel工作簿編譯為Python函數(shù)

我們可以將某些單元格定義為輸入單元格，把剩下的定義為輸出單元格，得到一個函數(shù)，在給定輸入后使用formulas，并在計算后把值返回給輸出單元格。然而為了保持本文的簡潔性，就不做詳述。

12.把單獨的formulas編譯為Python函數(shù)：

讓我們將重點放在單個formulas上，以便更好的使用openpyxl。操作如下：

func = formulas.Parser（）.ast（value）［1］.compile（）

由于某些原因，ast方法返回一個由2個對象組成的元組，其中第二個對象builder是最有用的。盡管這是內部APl，但也應該被包裝在一個更友好的用戶界面中。無論如何，當我們對上面的代碼求值時，得到的func將是一個帶有許多參數(shù)的函數(shù)，這些參數(shù)與formulas的輸入一樣：

func = formulas.Parser（）.ast（’=A1+B1‘）［1］.compile（）

func（1， 2） == 3 # True

13.處理formulas的依賴項

因此，我們可以將單個單元格的formulas編譯成一個函數(shù)。但是，當formulas依賴于其他單元格本身的formulas時，會發(fā)生什么呢？formulas庫這時候就失去了作用;我們必須計算所有的輸入。讓我們來看看怎么做。

首先，如何區(qū)分含formulas的單元格和含常規(guī)值的單元格？Openpyxl沒有提供這樣的區(qū)分方法，所以我們必須檢查單元格的值是否以等號字符開頭：

def has_formula（cell： Cell）

return isinstance（cell.value， str） and cell.value.startswith（’=‘）

這樣我們就知道了如何處理不包含formulas的單元格了：

def compute_cell_value（cell： Cell）：

if not has_formula（cell）：

return cell.value

現(xiàn)在需要處理的是含有formulas的單元格：

func = formulas.Parser（）.ast（cell.value）［1］.compile（）

args = ［］

# TODO： compute function arguments

return func（*args）

我們將formulas編譯成一個Python函數(shù)，然后調用它。因為輸入是對單元格的值，所以我們遞歸地調用compute_cell_value來獲取它們的值：

sheet = cell.parent

for key in func.inputs.keys（）：

args.append（compute_cell_value（sheet［key］））

我們利用這樣一個關系：每個單元格都與包含它的工作表有關。我們還使用formulas保留的信息，這樣就可以檢查函數(shù)的輸入——包含單元格的字典。注意，它不支持跨表或跨文件的使用。

14.基于單元格范圍使用formulas

到目前為止，compute_cell_value函數(shù)使用基于其他單元格的formulas，成功地計算了單元格的值。然而，對于那些不依賴于單個單元格，而是依賴于許多單元格的formulas，又該如何計算呢？在這種情況下，函數(shù)的輸入是一個范圍表達式，例如=SUM（A1:21）中的A1:Z1。我們給compute_cell_ value傳入以下信息：

sheet［key］

當key是單個單元格的地址時，我們將得到一個單元格對象;但是當key是許多單元格時，我們將得到一個元組。compute_cell_value不知道如何處理這樣的輸入，所以我們必須修改它，來應對這種情況：

if isinstance（input， Tuple）：

return tuple（map（compute_cell_value， input））

函數(shù)的完整版本如下：

def compute_cell_value（input： Union［Cell， Tuple］）：

if isinstance（input， Tuple）：

return tuple（map（compute_cell_value， input））

if not has_formula（input）：

return input.value

func = formulas.Parser（）.ast（input.value）［1］.compile（）

args = ［］

sheet = input.parent

for key in func.inputs.keys（）：

args.append（compute_cell_value（sheet［key］））

return func（*args）

15.添加新的formula函數(shù)

formulas支持許多內置的Excel函數(shù)，但不包括所有函數(shù)。當然，它也不支持VBA中的自定義函數(shù)。但是，我們可以添加一些新的Python函數(shù)，這樣就可以在formulas中調用這些函數(shù)：

def is_number（number）：

。.. # This is actually defined in formulas， but strangely not exposed as the Excel function

FUNCTIONS = formulas.get_functions（）

FUNCTIONS［’ISNUMBER‘］ = is_number

函數(shù)的輸入值就是Python中的值，比如字符串、數(shù)字、日期等，而不是cell類中的值。此外，與普通Python函數(shù)相比，我們需要防止XIError，它表示計算中的錯誤，例如#DIV/0！或#REF！ （當我們在輸入formulas中犯了一些錯誤時，通常會在Excel中看到這些）：

def is_number（number）：

if isinstance（number， XlError）：

return False

。..

結論

通過使用openpyxl和formulas這兩個成熟的開源庫，我們可以更高效地用Python處理Excel。對于那些經常使用Excel的用戶來說，能夠處理復雜的Excel文件是一個非常有用的功能。

在本文中，我們學習了如何讀寫帶有formulas的Excel文件。你還可以在樣式，圖表，合并單元格中學到其他相關的知識。
編輯：lyn