在這一小節(jié)，我們將會學(xué)習(xí)如何使用Python來操作Excel文檔以及如何利用Python語言的函數(shù)和表達式操縱Excel文檔中的數(shù)據(jù)。雖然微軟公司本身提供了一些函數(shù)，我們可以使用這些函數(shù)操作Excel文檔。但是，使用Excel自帶的函數(shù)受限于Excel軟件的功能限制。換句話說，只有微軟提供了某種功能，我們才能使用相應(yīng)的功能解決問題。如果微軟沒有提供相應(yīng)的函數(shù)應(yīng)對一個復(fù)雜的功能，那么，我們只能進行重復(fù)性操作。使用Python語言操作Excel則不然，我們可以靈活應(yīng)用Python語言的所有功能，讀取、計算和編輯Excel文檔中的數(shù)據(jù)。

除了使用Python語言操作Excel文檔以外，讀者還可以使用VBA操作Excel文檔。VBA（Visual Basic for Applications）是Visual Basic的一種宏語言，是微軟開發(fā)出來在其桌面應(yīng)用程序中執(zhí)行自動化任務(wù)的編程語言，主要用來擴展Windows應(yīng)用程序（特別是Microsoft Office軟件）的功能。靈活應(yīng)用VBA這門宏語言能夠在處理文檔時顯著提高工作效率，但是，VBA代碼可讀性差、應(yīng)用領(lǐng)域有限。而Python語言擁有文檔豐富、語法清晰、易于學(xué)習(xí)、跨平臺等諸多優(yōu)點。因此，筆者強烈建議使用Python語言來處理Excel文檔，不要浪費時間學(xué)習(xí)VBA。

7.1.1 openpyxl簡介與安裝

根據(jù)官方文檔的介紹，openpyxl是一個讀寫Excel 2010（xlsx/xlsm）文檔的Python庫，如果要處理更早格式的Excel文檔，需要用到另外的庫。openpyxl是一個比較綜合的工具，能夠同時讀取和修改Excel文檔。XlsxWriter也是一個與Excel處理相關(guān)的知名項目，僅支持創(chuàng)建和寫入Excel文檔，不支持讀取Excel文檔。

openpyxl是一個開源項目，因此，在使用之前需要先進行安裝：

pip install openpyxl

7.1.2 使用openpyxl讀取Excel文檔

在使用openpyxl操作Excel文檔之前，我們簡單回顧一下Excel文檔，幫助我們理解openpyxl對Excel文檔的抽象。一個Excel文檔稱為一個工作簿，在Office 2010下，典型工作簿的文件擴展名為xlsx。一個工作簿可以包含多個表格（在Excel又稱為sheet）。打開工作簿后會默認(rèn)顯示一個表格，這個表格一般稱為活躍表。表格中包含若干單元格，所有單元格都有一個唯一的坐標(biāo)。Excel通過行和列表示一個單元格，其中，行的坐標(biāo)使用數(shù)字表示，列的坐標(biāo)使用字母表示。例如，表格中左上角的單元格，其坐標(biāo)為“A1”，該單元格下方的單元格坐標(biāo)為“A2”，右邊的單元格坐標(biāo)為“B1”。

理解了Excel的構(gòu)成以后，再來看openpyxl對Excel的抽象就會覺得很好理解。openxpyxl中有三個不同層次的類，分別是Workbook、Worksheet和Cell。Workbook是對Excel工作簿的抽象，Worksheet是對表格的抽象，Cell是對單元格的抽象。每一個類都包含了若干屬性和方法，以便于我們通過這些屬性和方法獲取表格中的數(shù)據(jù)。

例如，我們要打開一個Excel表格或者創(chuàng)建一個Excel文檔，都需要創(chuàng)建一個Workbook對象。我們需要獲取Excel文檔中的某一張表，應(yīng)該先創(chuàng)建一個Workbook對象，然后使用該對象的方法來得到一個Worksheet對象。如果要讀取或者修改某個單元格，我們需要先獲得Worksheet對象，然后再獲取代表單元格的Cell對象。

在接下來的例子中，我們將使用下面的Excel文檔（見圖7-1）進行實驗。讀者可以在本書的附件中找到該文檔，文檔的名稱為example.xlsx。

openpyxl模塊使用到的Excel文檔

一個Workbook對象代表一個Excel文檔，因此，在操作一個Excel之前，應(yīng)該先創(chuàng)建一個Workbook對象。對于創(chuàng)建一個新的Excel文檔，直接進行Workbook類調(diào)用即可。對于讀取一個已有的Excel文檔，可以使用openpyxl模塊的load_workbook函數(shù)。該函數(shù)接受多個參數(shù)，但只有filename參數(shù)為必傳參數(shù)。filenmame可以是一個文件名，也可以是一個打開的文件對象。如下所示：

import openpyxl
wb = openpyxl.load_workbook('example.xlsx')

調(diào)用完load_workbook函數(shù)以后，我們就得到了一個Workbook對象。Workbook對象有很多的屬性和方法，其中，大部分方法都與sheet相關(guān)。Workbook對象的部分屬性如下：

?active：獲取活躍的Worksheet；
?read_only：是否以read_only模式打開Excel文檔；
?encoding：文檔的字符集編碼；
?properties：文檔的元數(shù)據(jù)，如標(biāo)題，創(chuàng)建者，創(chuàng)建日期等；
?worksheets：以列表的形式返回所有的Worksheet。

下面是我們附件中example.xlsx文件的屬性，包括活躍的Worksheet、只讀屬性和字符集編碼。如下所示：

>>>wb.active
<Worksheet "student">

>>>wb.read_only
False

>>>wb.encoding
'utf-8'

>>>wb.worksheets
[<Worksheet "student">, <Worksheet "teacher">]

Workbook對象的方法大都與Worksheet相關(guān)。常用的方法如下：

?get_sheet_names：獲取所有表格的名稱；
?get_sheet_by_name：通過表格名稱獲取Worksheet對象；
?get_active_sheet：獲取活躍的表格；
?remove_sheet：刪除一個表格；
?create_sheet：創(chuàng)建一個空的表格；
?copy_worksheet：在Workbook內(nèi)拷貝表格。

附件的example.xlsx文件中包含了兩個表格。其中，名為teacher的表格為活躍表格。如下所示：

>>>wb.get_sheet_names()
[u'student', u'teacher']
# 上面函數(shù)新版本以后不再使用，將使用如下wb.sheetnames 獲取sheetnames
>>>wb.sheetnames
[u'student', u'teacher']

>>>wb.get_active_sheet() # 建議使用新版wb.active
<Worksheet "teacher">

>>> wb.get_sheet_by_name(u'student') # 建議使用新版 wb['student']
<Worksheet "student">

有了Worksheet對象以后，我們可以通過這個Worksheet對象獲取表格的屬性，得到單元格中的數(shù)據(jù)，修改表格中的內(nèi)容。openpyxl提供了非常靈活的方式來訪問表格中的單元格和數(shù)據(jù)。常用的Worksheet屬性如下：

?title：表格的標(biāo)題；
?dimensions表格的大小，這里的大小是指有含有數(shù)據(jù)的表格大小。例如，對于example.xlsx文件，dimensions屬性的值為'A1:E11'；
?max_row表格的最大行；
?min_row表格的最小行；
?max_column表格的最大列；
?min_column表格的最小列；
?rows按行獲取單元格（Cell對象）；
?columns按列獲取單元格（Cell對象）；
?freeze_panes凍結(jié)窗格；
?values按行獲取表格的內(nèi)容（數(shù)據(jù)）。

對于附件中的example.xlsx文件，其擁有的屬性如下所示：

>>>ws = wb.get_sheet_by_name('student')
>>>ws.title
u'student'

>>>ws.dimensions
'A1:E11'

>>>ws.max_column
5

>>>ws.min_column
1

>>>ws.max_row
11

>>>ws.min_row
1

>>>ws.columns
<generator object _cells_by_col at 0x7fe5a4d89640>

>>>ws.rows
<generator object _cells_by_row at 0x7fe5a5d32550>

>>>ws.values
<generator object values at 0x7fe5ac289780>

在這段代碼中，我們首先通過Workbook的get_sheet_by_name方法獲取Worksheet對象。接著，通過不同的屬性名獲取student這張表的屬性。其中，columns、rows和values這幾個屬性都是通過生成器（生成器以后介紹）的方式返回數(shù)據(jù)。openpyxl并不知道我們的表格中有多少數(shù)據(jù)，在數(shù)據(jù)量大的情況下，如果一次獲取所有數(shù)據(jù)，勢必會占用較多的內(nèi)存。因此，openpyxl的設(shè)計中，需要返回數(shù)據(jù)時都是通過生成器的方式返回。對于附件中的student表，因為記錄較少，我們可以使用list函數(shù)或tuple函數(shù)獲取所有的數(shù)值。需要注意的是，columns與rows返回的是Cell對象，values返回的是數(shù)據(jù)。

freeze_panes這個參數(shù)比較特別，主要用于在表格較大時凍結(jié)頂部的行或左邊的列。對于凍結(jié)的行或列，就算用戶滾動電子表格，也是始終可見的。每個Worksheet對象都有一個freeze_panes屬性，可以設(shè)置為一個Cell對象或一個單元格坐標(biāo)的字符串，單元格上面的行和左邊的列將會凍結(jié)（注意單元格所在的行和列并不會凍結(jié)）。例如，我們需要凍結(jié)第一行，那么freeze_panes取值應(yīng)該為A2，如果要凍結(jié)第一列，freeze_panes取值為B1。如果要同時凍結(jié)第一行和第一列，則freeze_panes取值為B2。freeze_panes取值為None表示不凍結(jié)任何窗格。

下面是Worksheet常用的一些方法：

?iter_rows：按行獲取所有單元格（Cell對象）；
?iter_cols：按列獲取所有的單元格；
?append：在表格末尾添加數(shù)據(jù)；
?merged_cells：合并多個單元格；
?unmerge_cells：移除合并的單元格。

iter_rows方法和iter_cols方法在參數(shù)取默認(rèn)值時，與rows屬性和columns屬性的作用相同。區(qū)別在于，iter_rows方法和iter_cols方法可以通過函數(shù)參數(shù)限定訪問表格的范圍。如下所示：

>>>list(ws.iter_rows(min_row=2, max_row=4, min_col=1, max_col=3))
[(<Cell u'student'.A2>, <Cell u'student'.B2>, <Cell u'student'.C2>),
(<Cell u'student'.A3>, <Cell u'student'.B3>, <Cell u'student'.C3>),
(<Cell u'student'.A4>, <Cell u'student'.B4>, <Cell u'student'.C4>)]

從Worksheet的屬性和方法的使用中可以看到，很多屬性和方法返回的不是某一個具體的數(shù)值，而是一個Cell對象。一個Cell對象就代表一個單元格，我們可以直接使用Excel坐標(biāo)的方式獲取Cell對象，也可以使用Worksheet的cell方法獲取Cell對象。如下所示：

>>> ws['A1']
<Cell u'student'.A1>

>>>ws['A2']
<Cell u'student'.A2>

>>>ws.cell(row=1, column=2)
<Cell u'student'.B1>

>>>ws.cell(row=2, column=1)
<Cell u'student'.A2>

# 也可以只讀一列數(shù)據(jù):
>>>ws['B']
# 只讀一行數(shù)據(jù):
>>>ws[83]

Cell對象比較簡單，其常用的屬性如下：

?row：單元格所在的行；
?column：單元格所在的列；
?value：單元格的取值；
?cordinate：單元格的坐標(biāo)。

為了熟悉openpyxl提供的各種API，接下來我們使用4種不同的方法來打印student表中的內(nèi)容。為了對數(shù)據(jù)的格式進行控制，我們使用print函數(shù)而不是print語句進行打印。

下面是通過Worksheet的values方法打印表格中的數(shù)據(jù)，這也是打印數(shù)據(jù)最簡單的方法。values通過生成器訪問數(shù)據(jù)并按行返回，因此，我們使用for循環(huán)遍歷表格的內(nèi)容。

>>>from __future__ import print_function

>>>for row in ws.values:
...:      print(＊row)

image.png

我們也可以使用Worksheet的rows屬性來遍歷表格中的數(shù)據(jù)。rows屬性按行返回Cell對象，因此，我們使用列表推導(dǎo)來獲取每一個Cell對象的值。如下所示：

>>>for row in ws.rows:

...:      print(＊[cell.value for cell in row])

image.png

Worksheet的iter_rows方法在不加任何參數(shù)的情況下，與rows屬性效果相同，因此，這種方法與前一種方法看起來很像。

>>>for row in ws.iter_rows():

...:      print(＊[cell.value for cell in row])

image.png

最后這種方式是最麻煩的方式，也是大家最容易想到的方式。我們首先獲取表格的最小行數(shù)和最大行數(shù)，然后獲取最小列數(shù)與最大列數(shù)，通過行和列的索引確定一個唯一單元格。確定單元格以后，打印單元格的值。這種方式是每確定一個單元格打印一次，因此，我們在print函數(shù)中將end參數(shù)取值為空格來避免換行，并在內(nèi)層for循環(huán)結(jié)束以后，顯示地進行換行。如下所示：

>>>for i in range(ws.min_row, ws.max_row + 1):
...:      for j in range(ws.min_column, ws.max_column + 1):
...:           print(ws.cell(row=i, column=j).value, end=' ')
...:      print()

7.1.3 使用openpyxl修改Excel文檔

openpyxl不但可以讀取Excel文檔，而且還可以修改Excel文檔，包括修改單元格的數(shù)據(jù)、合并單元格、修改單元格的字體、在Excel文檔中畫圖等。我們接下來將介紹如何使用openpyxl創(chuàng)建工作簿，創(chuàng)建和刪除表格，修改單元格的數(shù)據(jù)。然后，我們通過計算example.xlsx文件中每位同學(xué)的平均分來演示如何修改一份Excel文檔。

一個Workbook對象就代表了一個工作簿，因此，新建一個工作簿就是創(chuàng)建一個Workbook對象。創(chuàng)建完Workbook對象以后，默認(rèn)會有一個名為“sheet1”的表格，我們可以通過表格的名稱或get_active_sheet方法來獲取這個表格。獲取表格以后，可以通過給表格的title屬性賦值的方式來修改表格的名稱。

In [1]: from openpyxl import Workbook
In [2]: wb = Workbook()
In [3]: wb.get_sheet_names()
Out[3]: [u'Sheet']
In [4]: ws = wb.get_active_sheet()
In [5]: ws.title
Out[5]: u'Sheet'
In [6]: ws.title = 'student'
In [7]: ws.title
Out[7]: u'student'

創(chuàng)建完Workbook以后，就可以使用create_sheet方法創(chuàng)建新的表格，也可以使用remove_sheet方法刪除表格。如下所示：

In [8]: wb.create_sheet(index=0, title="new sheet")
Out[8]: <Worksheet "new sheet">
In [9]: wb.get_sheet_names()
Out[9]: [u'new sheet', u'student']
In [10]: wb.remove_sheet(wb.get_sheet_by_name('student'))
In [11]: wb.get_sheet_names()
Out[11]: [u'new sheet']
In [12]: ws = wb.get_active_sheet()
In [13]: ws.title
Out[13]: u'new sheet'

如果要填充單元格的數(shù)據(jù)，可以直接對單元格賦值。openpyxl還能夠自動處理Python數(shù)據(jù)類型到Excel數(shù)據(jù)類型之間的轉(zhuǎn)換。如下所示：

In [14]: ws['A1'] = 'Hello, world'
In [15]: import datetime
In [16]: ws['A2'] = datetime.datetime.now()

我們已經(jīng)創(chuàng)建了工作簿，同時在工作簿中創(chuàng)建了表格，并為表格中的部分單元格進行了賦值。此時，磁盤上還沒有一個Excel文檔保存了這些信息，我們需要調(diào)用Workbook的save方法將數(shù)據(jù)保存到磁盤中。如下所示：

In [17]: wb.save('sample.xlsx')

下面來看一個案例，用來鞏固openpyxl讀取和修改Excel文檔的知識。附件里的example.xlsx文件保存了十位學(xué)生的信息，其中，最后三列分別是學(xué)生的語文、英語和數(shù)學(xué)成績?，F(xiàn)在，我們希望計算每一位學(xué)生的平均分和總分，并保存到表格的右側(cè)中。

為了計算學(xué)生的成績，我們首先需要打開Excel文檔，并創(chuàng)建一個Workbook對象。有了Workbook對象以后，通過表格的名稱獲取我們需要操作的表格。在我們下面的代碼中，將表格傳遞給process_worksheet函數(shù)。在process_worksheet函數(shù)中，我們首先獲取了表格的最大列，用來確定平均分與總分的列坐標(biāo)。隨后，我們需要使用Worksheet的ifer_rows方法遍歷每一位學(xué)生的成績，iter_rows方法支持指定遍歷的起點和終點。在我們的表格中，第一行保存的是表頭信息，不需要計算。因此，我們通過傳遞min_row為2表示從第二行開始遍歷。表格的第一列保存的是學(xué)生的學(xué)號，第二列保存的是學(xué)生的姓名，這也是我們在計算平均分和總分時不會使用的數(shù)據(jù)。因此，我們通過傳遞min_col為3表示從第三列開始遍歷。iter_rows函數(shù)按行返回單元格，因此，我們只需要循環(huán)遍歷iter_rows函數(shù)的結(jié)果，就實現(xiàn)了計算每一位學(xué)生的平均分和總分的功能。需要注意的是，iter_rows函數(shù)返回的是Cell對象，所以，我們在計算成績之前需要先通過一個列表推導(dǎo)表達式，得到每一個單元格的取值。隨后計算平均分和總分，并且通過每一行的第一個單元格獲取平均分和總分的行坐標(biāo)。有了行坐標(biāo)和列坐標(biāo)以后，直接通過Worksheet對象的cell方法為單元格復(fù)制。這就實現(xiàn)了計算平均分和總分，并且保存到表格右側(cè)的功能。

process_worksheet函數(shù)處理完畢以后，當(dāng)前所做的修改都還在內(nèi)存之中，我們需要調(diào)用Workbook的save方法將Workbook所表示的工作簿保存到磁盤文件中。如下所示：

#! /usr/bin/python
#-＊- coding: UTF-8 -＊-
import openpyxl

def process_worksheet(sheet):
    avg_column = sheet.max_column + 1
    sum_column = sheet.max_column + 2
    for row in sheet.iter_rows(min_row=2, min_col=3):
        scores = [cell.value for cell in row]
        sum_score = sum(scores)
        avg_score = sum_score / len(scores)
          # 計算平均分和總分，并且保存到最后兩列
        sheet.cell(row=row[0].row, column=avg_column).value = avg_score
        sheet.cell(row=row[0].row, column=sum_column).value = sum_score
    # 設(shè)置平均分和總分的標(biāo)題部分
    sheet.cell(row=1, column=avg_column).value = 'avg'
    sheet.cell(row=1, column=sum_column).value = 'sum'

def main():
    wb = openpyxl.load_workbook('example.xlsx')
    sheet = wb.get_sheet_by_name('student')
    process_worksheet(sheet)
    wb.save('example_copy.xlsx')
if __name__ == '__main__':
    main()

修改完成以后，當(dāng)前目錄下存在一個名為example_copy.xlsx的文件，該文件的內(nèi)容相對于附件中的example.xlsx文件增加了兩列，分別是學(xué)生的平均分和總分，如圖7-2所示。

image.png

圖7-2 計算平均分和總分以后的結(jié)果

7.1.4 案例：合并多個Excel文檔到一個Excel文檔

我們也可以使用Excel的函數(shù)實現(xiàn)計算學(xué)生平均分和總分的例子，而且更加簡單快捷。如果說計算平均分和總分的例子達到的是事倍功半的效果，那么，接下來這個例子將實現(xiàn)事半功倍！

現(xiàn)在，假設(shè)你是公司的人力資源管理部人才發(fā)展中心的員工，需要組織公司2019年的技術(shù)分享。因此，你需要將一個類似于圖7-3的報名表格發(fā)送給公司的各位同事，讓公司的同事自行填寫。填寫完成以后，你需要將每一位同事返回的Excel文檔進行合并。需要注意的是，部分同事可能填寫多行。同事們填寫完畢以后，會將報名表再發(fā)送給你。你收到了每位同事的報名表后，接下來要做的是，將這些報名表合并到一張匯總表中。

圖7-3 技術(shù)分享報名表

將多個Excel文檔合并成單個文件，表面上看是個簡單的需求。但是，因為我們的數(shù)據(jù)在不同的Excel文檔中，即便是熟練的Excel操作人員也沒有辦法快速進行處理。這個時候，只能依次打開各個文檔，并將內(nèi)容手動拷貝到匯總表中。對于有編程背景的技術(shù)人員，我們不可能允許這么低效率的重復(fù)工作。這個時候就顯現(xiàn)出使用Python語言處理Excel的優(yōu)勢。

對于這里的需求，使用Python語言處理將會非常簡單。如下所示：

#! /usr/bin/python
import os
import glob
import openpyxl

def merge_xlsx_files(xlsx_files):
    wb = openpyxl.load_workbook(xlsx_files[0])
    ws = wb.active
    ws.title = "merged result"
    for filename in xlsx_files[1:]:
        workbook = openpyxl.load_workbook(filename)
        sheet = workbook.active
        for row in sheet.iter_rows(min_row=2):
            values = [cell.value for cell in row]
            ws.append(values)
    return wb

def get_all_xlsx_files(path):
    xlsx_files = glob.glob(os.path.join(path, '＊.xlsx'))
    sorted(xlsx_files, key=str.lower)
    return xlsx_files

def main():
    xlsx_files = get_all_xlsx_files(os.path.expanduser('～lmx'))
    wb = merge_xlsx_files(xlsx_files)
    wb.save('merged_form.xlsx')

if __name__ == '__main__':
    main()

在這個例子中，我們首先通過glob獲取了指定目錄下所有的Excel文檔。然后，我們將這些文檔按照文件名稱進行了排序。排序以后，在merge_xlsx_files函數(shù)中嘗試合并多個Excel文檔。我們合并Excel的思路也很簡單：

1）獲取第一個文檔中的表格（我們的Excel文檔中只有一個表格）;
2）依次遍歷其他文件中的報名表，并通過iter_rows函數(shù)忽略報名表中的首行內(nèi)容；
3）通過列表推導(dǎo)獲取報名表中的數(shù)據(jù)，然后調(diào)用Worksheet的append函數(shù)將數(shù)據(jù)添加到匯總表的末尾。

上述操作完成以后，返回Workbook對象。在main函數(shù)中，我們調(diào)用Workbook的save方法將匯總表保存到merged_form.xlsx文件中。

我們只花費幾分鐘時間編寫了不到30行的Python代碼，就實現(xiàn)了將多個Excel文檔合并成單個文件的功能。因為是Python程序進行處理，所以無論有多少張表需要合并都能夠快速處理，并且不會出錯，也不會抱怨處理的表格太多。