如何讀寫(xiě)帶有formulas的Excel文件淺析

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

簡(jiǎn)介

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

雖然Excel并不是一個(gè)軟件領(lǐng)域的專用語(yǔ)言，但一些軟件領(lǐng)域的專家常常需要用其處理問(wèn)題。這就導(dǎo)致會(huì)“說(shuō)”Excel的軟件更受用戶的青睞。即使從長(zhǎng)遠(yuǎn)來(lái)看，我們的目標(biāo)是把Excel變成一個(gè)應(yīng)用程序或者軟件領(lǐng)域的一種語(yǔ)言（DSL），但能自動(dòng)處理Excel中的數(shù)據(jù)也是一個(gè)重要方向。

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

例如在一個(gè)項(xiàng)目中，許多用戶需要使用某些相同的Excel文件，而應(yīng)用程序通過(guò)處理這些Excel文件，讓用戶使用起來(lái)更加方便。或者當(dāng)一些用戶想用Excel中的數(shù)據(jù)生成帶有附加圖表和formulas的報(bào)告時(shí)，應(yīng)用程序可以生成一個(gè)電子表格，用來(lái)輸出一些查詢或計(jì)算結(jié)果，方便用戶使用這些數(shù)據(jù)。

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

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

使用formulas;

輸出Excel文件。

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

項(xiàng)目地址：

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

安裝

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

mkdir python-excel

cd python-excel

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

git clone 《repo-url》 python-excel

cd python-excel

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

python -m venv .venv

source .venv/bin/activate

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

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

隨后我們寫(xiě)一個(gè)requirements.txt文件，列出我們的依賴包：

echo openpyxl 》 requirements.txt

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

python -m pip install -r requirements.txt

簡(jiǎn)單使用

1.加載一個(gè)Excel文件

在Openpyxl中，我們把Excel文件稱為“workbook”，用openpyxl.workbook.Workbook類的實(shí)例來(lái)表示，打開(kāi)一個(gè)Excel文件非常簡(jiǎn)單：

wb = load_workbook（path）

調(diào)用load_workbook的結(jié)果

Openpyxl打開(kāi)文件后，一般可以同時(shí)進(jìn)行讀取和寫(xiě)入工作，除非我們給load_workbook設(shè)置一個(gè)read_only=True參數(shù)，表示只讀取文件，當(dāng)我們使用完一個(gè)Excel文件后，必須關(guān)閉它：

wb.close（）

不幸的是，Workbook不是一個(gè)“文件管理者”，所以不能用Python中的語(yǔ)句來(lái)自動(dòng)關(guān)閉它。即使在一些exceptions的情況中，也必須得手動(dòng)關(guān)閉文件：

wb = load_workbook（path）

try：

# Use wb.。.

finally：

wb.close（）

2.處理一個(gè)Excel文件 - 通用案例

通常，Workbooks中可能有幾個(gè)表，我們需要選擇Excel文件中的一個(gè)表，訪問(wèn)其中的數(shù)據(jù)。隨后，我們?cè)賹W(xué)習(xí)如何處理多個(gè)表。現(xiàn)在，假設(shè)我們對(duì)active工作表中的數(shù)據(jù)比較感興趣——當(dāng)用戶在他們的應(yīng)用中打開(kāi)文件就會(huì)看到的工作表：

sheet = wb.active

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

（1）對(duì)行進(jìn)行遍歷：

for row in sheet.rows（）：

# Do something with the row

rows（）產(chǎn)生的行，本身就是一個(gè)生成器，我們可以遍歷它們：

for row in sheet.rows（）：

for cell in row：

# Do something with the cell

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

for row in sheet.rows（）：

header = row［2］

實(shí)際上，表格本身就是可以按行進(jìn)行迭代的，所以我們可以忽略所有行：

for row in sheet：

pass

（2）使用cols方法對(duì)列進(jìn)行遍歷。

for col in sheet.cols（）：

# Use the column

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

（3）通過(guò)地址訪問(wèn)單元格。

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

cell = sheet［‘C5’］

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

3.處理單元格

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

tax_percentage = sheet［‘H16’］.value

tax_amount = taxable_amount * tax_percentage

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

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

然而，在不那么幸運(yùn)的情況下，例如，當(dāng)taxable_amount是一個(gè)整數(shù)時(shí)——因?yàn)槲覀冊(cè)谑纠刑幚淼臄?shù)據(jù)是錢(qián)，所以應(yīng)該是整數(shù)——我們將得到一個(gè)重復(fù)taxable_amount次的“bug”。這是因?yàn)镻ython把*操作符當(dāng)成了字符串，整數(shù)就意味著“重復(fù)字符串n次”。這可能會(huì)導(dǎo)致進(jìn)一步的類型錯(cuò)誤，或者在Python無(wú)法放置如此大的字符串時(shí)出現(xiàn)內(nèi)存錯(cuò)誤。因此，我們應(yīng)該驗(yàn)證程序的輸入，包括Excel文件。在這個(gè)特殊的例子中，我們可以使用Python的isinstance函數(shù)來(lái)檢查單元格中值的類型：

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

# Use the value

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

if cell.data_type == TYPE_NUMERIC：

# Use the numeric value

4.單元格高級(jí)尋址

到目前為止，我們已經(jīng)使用了訪問(wèn)單元格最簡(jiǎn)單、最直接的方法。然而，這并不是所有方法;讓我們來(lái)看看更復(fù)雜的方案。

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

我們可以通過(guò)在workbook中通過(guò)名稱來(lái)訪問(wèn)它們：

sheet = wb［’2020 Report‘］

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

（2）單元格范圍

我們不一定要一個(gè)一個(gè)的尋址單元格-還可以設(shè)定范圍來(lái)訪問(wèn)單元格：

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

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

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

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

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

以上任何一種情況，結(jié)果都是一個(gè)按行迭代所有單元格（除非迭代的范圍以列為標(biāo)準(zhǔn)，在這種情況下，單元格按列順序進(jìn)行迭代）：

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.單元格迭代器

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

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

min_col - 起始列的編號(hào)

max_row - 最后一行的編號(hào)

max_col - 最后一列的編號(hào)

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

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

# Use the cell

6.編寫(xiě)一個(gè)Excel文件

要寫(xiě)一個(gè)Excel文件，我們只需在workbook上調(diào)用save方法：

wb.save（’someFile.xlsx‘）

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

7.修改單個(gè)單元格

我們可以用指定的方式來(lái)改變一個(gè)單元格中的值：

cell.value = 42

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

Openpyxl的文檔中有許多關(guān)于調(diào)整樣式的詳細(xì)信息，我們可以在這里查詢：

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

8.添加或移除表格

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

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

new_sheet = wb.create_sheet（）

這樣就可以在workbook中的其他表格之后添加一個(gè)新表，我們可以給這個(gè)新表一個(gè)標(biāo)題：

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

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

# The new sheet will be inserted as the third sheet

new_sheet = wb.create_sheet（index = 2）

要?jiǎng)h除一個(gè)表格的話有兩種方法?？梢愿鶕?jù)名字進(jìn)行刪除：

del wb［’My sheet‘］

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

name = ’My sheet‘

if name in workbook：

del workbook［name］

或者還能調(diào)用remove方法來(lái)刪除表格：

wb.remove（sheet）

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

看看下面這些例子。首先通過(guò)訪問(wèn)一個(gè)單元格，可以為創(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方法在表格中添加行或列。當(dāng)新創(chuàng)建一行或一列時(shí)，單元格會(huì)自動(dòng)調(diào)整：

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）

與此相對(duì)應(yīng)，還可以使用delete_rows和delete_cols來(lái)刪除行或列：

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

sheet.delete_columns（1， 2）

10.使用formulas

電子表格非常強(qiáng)大，因?yàn)樗€支持用formulas來(lái)計(jì)算單元格中的值。當(dāng)其他單元格發(fā)生變化時(shí)，通過(guò)計(jì)算取得值的單元格也會(huì)自動(dòng)更新。讓我們看看如何使用在Openpyxl中使用formulas吧。首先，如果我們只想讀取一個(gè)Excel文件，我們可以完全忽略formulas。

此時(shí)，以“data only”模式打開(kāi)它，這種模式將會(huì)隱藏formulas，所有單元格中的值都是固定的-也就是上次Excel文件計(jì)算后的結(jié)果：

wb = load_workbook（filename， data_only=True）

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

$ cat requirements.txt

openpyxl

formulas

pip install -r requirements.txt

我們有兩種方法來(lái)使用formulas庫(kù)：

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

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

11.計(jì)算所有formulas中的值：

第一個(gè)示例比較枯燥，因?yàn)楣δ芘c前面的data_only方法作用相似。事實(shí)上，在那種模式下，我們加載不了workbook，修改不了它，也不能重新使用其中的formulas。我們必須：

把修改后的workbook保存到一個(gè)文件中；

用formulas重新加載文件；

調(diào)用API使用formulas；

把計(jì)算后的值保存到文件中；

在data_only模式下用openpyxl打開(kāi)文件，查看計(jì)算后的結(jié)果。

這簡(jiǎn)直是在浪費(fèi)時(shí)間！

當(dāng)然，formulas庫(kù)的這個(gè)特性也有價(jià)值，因?yàn)樗С忠恍└呒?jí)的用法：

同時(shí)在多個(gè)workbook中使用formulas

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

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

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

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

讓我們將重點(diǎn)放在單個(gè)formulas上，以便更好的使用openpyxl。操作如下：

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

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

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

func（1， 2） == 3 # True

13.處理formulas的依賴項(xiàng)

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

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

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編譯成一個(gè)Python函數(shù)，然后調(diào)用它。因?yàn)檩斎胧菍?duì)單元格的值，所以我們遞歸地調(diào)用compute_cell_value來(lái)獲取它們的值：

sheet = cell.parent

for key in func.inputs.keys（）：

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

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

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

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

sheet［key］

當(dāng)key是單個(gè)單元格的地址時(shí)，我們將得到一個(gè)單元格對(duì)象;但是當(dāng)key是許多單元格時(shí)，我們將得到一個(gè)元組。compute_cell_value不知道如何處理這樣的輸入，所以我們必須修改它，來(lái)應(yīng)對(duì)這種情況：

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支持許多內(nèi)置的Excel函數(shù)，但不包括所有函數(shù)。當(dāng)然，它也不支持VBA中的自定義函數(shù)。但是，我們可以添加一些新的Python函數(shù)，這樣就可以在formulas中調(diào)用這些函數(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，它表示計(jì)算中的錯(cuò)誤，例如#DIV/0！或#REF！ （當(dāng)我們?cè)谳斎雈ormulas中犯了一些錯(cuò)誤時(shí)，通常會(huì)在Excel中看到這些）：

def is_number（number）：

if isinstance（number， XlError）：

return False

。..

結(jié)論

通過(guò)使用openpyxl和formulas這兩個(gè)成熟的開(kāi)源庫(kù)，我們可以更高效地用Python處理Excel。對(duì)于那些經(jīng)常使用Excel的用戶來(lái)說(shuō)，能夠處理復(fù)雜的Excel文件是一個(gè)非常有用的功能。

在本文中，我們學(xué)習(xí)了如何讀寫(xiě)帶有formulas的Excel文件。你還可以在樣式，圖表，合并單元格中學(xué)到其他相關(guān)的知識(shí)。
編輯：lyn