本文共3000余字，預(yù)計(jì)閱讀時(shí)間14分鐘，本文同步發(fā)布于知乎（賬號(hào)silaoA）和微信公眾號(hào)平臺(tái)（賬號(hào)偽碼人）。
關(guān)注學(xué)習(xí)了解更多的Cygwin、Linux、Python等技術(shù)。

xlrd、xlwt、xlutils是Simplistix公司開(kāi)發(fā)，原網(wǎng)站內(nèi)容基本都清空了，項(xiàng)目遷移到http://www.，并在GitHub開(kāi)源，見(jiàn)https://github.com/python-excel。三兄弟在操作Excel方面表現(xiàn)中規(guī)中矩，能夠覆蓋大部分需求，也是本人最先熟悉的庫(kù)。三兄弟一起配合才能比較方便地實(shí)現(xiàn)Excel文件的讀寫(xiě)，xlutils不是必需，但額外提供了一些簡(jiǎn)化操作的工具函數(shù)。

0x00 讀文件

讀文件功能由xlrd包提供。xlrd包實(shí)現(xiàn)了xlrd.book.Book（以下簡(jiǎn)稱(chēng)Book）、xlrd.sheet.Sheet（以下簡(jiǎn)稱(chēng)Sheet）和xlrd.sheet.Cell（以下簡(jiǎn)稱(chēng)Cell）類(lèi)型，與Excel中的工作簿、表單、單元格概念相對(duì)應(yīng)，單元格是最小操作粒度。

API

xlrd沒(méi)有pyexcel那么花哨，加載表格文件就1個(gè)函數(shù)——open_workbook，常用參數(shù)就2個(gè)：

1. filename，指定要打開(kāi)的Excel文件路徑；
2. on_demand，如果是True則按需加載工作簿中的表單，如果是False則直接加載所有表單，默認(rèn)為False，為節(jié)省資源一般設(shè)為T(mén)rue，這在大文件時(shí)表現(xiàn)更明顯。

示例

假使當(dāng)前路徑下，樣例文件名稱(chēng)是data.xls，有3個(gè)表單，僅Sheet1有數(shù)據(jù)，內(nèi)容如下圖，其中C列2行是公式，sum(A2,B2)，C列3行是日期，A列4行是TRUE。
data.xls Sheet1

可按下述示例代碼加載文件。

1
2
3
4
5

import xlrd # 導(dǎo)入包
In [4]: rbk = xlrd.open_workbook('./data.xls',on_demand=True)
#變量rbk，r代表讀，bk代表book
In [5]: type(rbk)
Out[5]: xlrd.book.Book

0x01 數(shù)據(jù)訪問(wèn)

工作簿和表單

讀入Excel文件拿到Workbook后，下一步就是定位到Sheet。Book類(lèi)對(duì)象有幾個(gè)重要的屬性和方法，用于索引Sheet。

• nsheets屬性，指示包含的Sheet對(duì)象個(gè)數(shù);
• sheet_names方法，返回所有表單名稱(chēng)；
• sheet_by_index、sheet_by_name方法，分別使用序號(hào)、名稱(chēng)索引表單；
• sheets方法，返回一個(gè)包含所有Sheet對(duì)象的列表，。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

In [7]: rbk.nsheets
Out[7]: 3

In [8]: rbk.sheet_names()
Out[8]: ['Sheet1', 'Sheet2', 'Sheet3']

In [9]: rsh1 = rbk.sheet_by_index(0) # 通過(guò)序號(hào)索引

In [10]: rsh2 = rbk.sheet_by_name('Sheet2') # 通過(guò)名稱(chēng)索引

In [11]: type(rsh1)
Out[11]: xlrd.sheet.Sheet

In [12]: for sh in rbk.sheets(): # 循環(huán)遍歷每個(gè)Sheet
    ...:     print(sh.name, sh)
    ...:
Sheet1 <xlrd.sheet.Sheet object at 0x00000155511F7F28>
Sheet2 <xlrd.sheet.Sheet object at 0x0000015550DF4860>
Sheet3 <xlrd.sheet.Sheet object at 0x00000155511E4E80>

拿到Sheet對(duì)象后，下一步就是要索引行/列/單元格，獲取到行/列/單元格的數(shù)據(jù)。Sheet類(lèi)對(duì)象有幾個(gè)重要的屬性和方法，用于支持后續(xù)操作。

• name屬性，即表單名稱(chēng)；
• nrows、ncols屬性，指示讀入表單的最大行數(shù)、列數(shù)，由于單元格僅支持行列序號(hào)索引，因此這兩個(gè)屬性是檢查越界的必備內(nèi)容；
• cell方法，接受2個(gè)參數(shù)，即行、列序號(hào)，返回Cell對(duì)象，注意xlrd僅支持通過(guò)行列序號(hào)索引單元格，行列序號(hào)從0起始；
• cell_value方法，與cell方法類(lèi)似，只不過(guò)返回的是單元格中的值，不是Cell對(duì)象；
• cell_type方法，返回單元格的類(lèi)型，見(jiàn)下圖；
• row、col方法，返回某1整行（列）的Cell對(duì)象組成的列表；
• row_types、col_types，返回指定行（列）內(nèi)若干列（行）的單元格的類(lèi)型；
• row_values、col_values，返回指定行（列）內(nèi)若干列（行）的單元格的值；
• row_slice、col_slice，返回指定行（列）內(nèi)若干列（行）的單元格，是types和values的綜合。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

In [14]: rsh1.nrows, rsh1.ncols # 查看行列上限
Out[14]: (5, 3)

In [15]: rsh2.nrows, rsh2.ncols # Sheet2為空
Out[15]: (0, 0)

In [16]: cC2 = rsh1.cell(1,2) # 索引到第2行C列

In [17]: type(cC2)
Out[17]: xlrd.sheet.Cell

In [18]: rsh1.cell_value(1,2) # 讀公式返回公式值
Out[18]: 4.0

In [19]: rsh1.row_values(4)
Out[19]: ['A5', '', '']

# 注意日期在Excel中只是格式化形式，2019/1/1的值為43466（1900/1/1以來(lái)的天數(shù)）
In [20]: rsh1.col_values(2)
Out[20]: ['列C', 4.0, 43466.0, 0.0, '']

In [21]: rsh1.cell_type(2,2) # xlrd.XL_CELL_DATE
Out[21]: 3

In [22]: rsh1.cell_type(4,0) # xlrd.XL_CELL_TEXT
Out[22]: 1

In [23]: rsh1.cell_type(3,0) # xlrd.XL_CELL_BOOLEAN
Out[23]: 4

In [24]: rsh1.cell_type(3,1) # xlrd.XL_CELL_NUMBER
Out[24]: 2

In [25]: xlrd.XL_CELL_TEXT,xlrd.XL_CELL_NUMBER,xlrd.XL_CELL_DATE
Out[25]: (1, 2, 3)

In [26]: xlrd.XL_CELL_BOOLEAN,xlrd.XL_CELL_ERROR,xlrd.XL_CELL_BLANK,xlrd.XL_CELL_EMPTY
Out[26]: (4, 5, 6, 0)

In [30]: rsh1.col_types(2,1) # 第2列，第1行起始
Out[30]: [2, 3, 2, 0]

In [31]: rsh1.row_slice(3,0,3) # 第3行，第0列起始
Out[31]: [bool:1, number:-0.2, number:0.0]

xlrd單元格類(lèi)型
總結(jié)以上，xlrd根據(jù)單元格的類(lèi)型返回恰當(dāng)?shù)闹?，Number（整數(shù)或小數(shù)）類(lèi)型返回的是浮點(diǎn)數(shù)，Text類(lèi)型返回的是Unicode字符串，Boolean（TRUE或False）類(lèi)型返回的是1或0，Date類(lèi)型返回浮點(diǎn)數(shù)，公式則求值后根據(jù)公式值的類(lèi)型而定。

行、列、單元格

單元格的訪問(wèn)是核心，xlrd包中行、列本質(zhì)就是Cell對(duì)象組成的列表。Cell對(duì)象有幾個(gè)重要屬性、方法用于支持獲取數(shù)據(jù)。

• value屬性，和Sheet對(duì)象的cell_value方法作用相同；
• ctype屬性，和Sheet對(duì)象的cell_type方法作用相同；
• dump方法，打印單元格信息。

為便于索引，xlrd包的cellname、cellnameabs、colname函數(shù)，將行列序號(hào)轉(zhuǎn)換為Excel風(fēng)格的單元格地址；xlwt.Utils模塊的rowcol_to_cell、rowcol_pair_to_cellrange函數(shù)，也可以將行列序號(hào)轉(zhuǎn)換為Excel風(fēng)格的單元格地址；而col_by_name、cell_to_rowcol、cell_to_rowcol2、cellrange_to_rowcol_pair函數(shù)，則將Excel風(fēng)格的單元格地址轉(zhuǎn)換為行列序號(hào)。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

In [35]: xlrd.cellname(2,10)
Out[35]: 'K3'

In [36]: xlrd.cellnameabs(2,10) # 結(jié)果為絕對(duì)引用地址
Out[36]: '$K$3'

# 注意列名稱(chēng)必須大寫(xiě)，小寫(xiě)是錯(cuò)誤的！
In [44]: xlwt.Utils.col_by_name('K'),xlwt.Utils.col_by_name('k')
Out[44]: (10, 42)

In [45]: rsh1.col_values(xlwt.Utils.col_by_name('C'))
Out[45]: ['列C', 4.0, 43466.0, 0.0, '']

In [46]: xlwt.Utils.cell_to_rowcol('K3') # 行列均無(wú)絕對(duì)引用
Out[46]: (2, 10, False, False)

In [47]: xlwt.Utils.cell_to_rowcol('K$3') # 行絕對(duì)引用
Out[47]: (2, 10, True, False)

# 與上一個(gè)函數(shù)的區(qū)別是忽略絕對(duì)引用符號(hào)
In [48]: xlwt.Utils.cell_to_rowcol2('K$3')
Out[48]: (2, 10)

# cC2是C2單元格，在前一小節(jié)In [16]創(chuàng)建
In [50]: cC2.ctype, cC2.value
Out[50]: (2, 4.0)

行列序號(hào)與單元格地址轉(zhuǎn)換總結(jié)如下圖。
行列序號(hào)與單元格地址轉(zhuǎn)換

要遍歷1個(gè)sheet內(nèi)所有單元格，通常按行、列順序逐個(gè)得到單元格，再讀出單元格值存起來(lái)，以便后續(xù)處理。也可以直接得到一整行（列），整行（列）地處理數(shù)據(jù)。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

# 1、逐單元格處理
for rx in range(rsh.nrows):
	for cx in range(rsh.ncols):
		c = rsh.cell(rx, cx)
		# 對(duì)單元格的進(jìn)一步處理
		print(c.ctype, c.value)

# 2、整行處理
for rx in range(rsh.nrows):
	row = rsh.row(rx)
	# 對(duì)行的進(jìn)一步處理
	print(len(row))

# 3、整列處理
for cx in range(rsh.ncols):
	col = rsh.col(cx)
	# 對(duì)列的進(jìn)一步處理
	print(len(col))

0x02 改寫(xiě)文件

xlrd包只能將表單內(nèi)的數(shù)據(jù)讀出來(lái)，對(duì)改寫(xiě)數(shù)據(jù)無(wú)能為力，改寫(xiě)數(shù)據(jù)和保存至文件，由xlwt包完成。xlwt實(shí)現(xiàn)了一套xlwt.Workbook.Workbook（以下簡(jiǎn)稱(chēng)Workbook）、xlwt.Worksheet.Worksheet（以下簡(jiǎn)稱(chēng)Worksheet）類(lèi)型，但很不幸與xlrd包的不存在繼承關(guān)系，這導(dǎo)致用xlrd包讀出來(lái)的Book、Sheet對(duì)象并不能直接用于創(chuàng)建Workbook和Worksheet對(duì)象，只能把數(shù)據(jù)暫存著以備后續(xù)再寫(xiě)回去，使得過(guò)程十分繁瑣。

API

xlwt包對(duì)外暴露的類(lèi)型、方法、函數(shù)及參數(shù)也十分簡(jiǎn)潔，緊密契合改寫(xiě)數(shù)據(jù)、保存至文件的流程，屬于“人狠話不多”的類(lèi)型。

1. 調(diào)用Workbook模塊的Workbook函數(shù)，創(chuàng)建Workbook對(duì)象，第1個(gè)參數(shù)是encoding，直接默認(rèn)’ascii’即可；
2. 調(diào)用Workbook對(duì)象的add_sheet方法，往Workbook中添加Worksheet對(duì)象，第1個(gè)參數(shù)sheetname指定表單名稱(chēng)，第2個(gè)參數(shù)cell_overwrite_ok確定是否允許單元格覆寫(xiě)，建議設(shè)置為T(mén)rue，避免對(duì)程序可能對(duì)單元格多次寫(xiě)數(shù)據(jù)而拋出錯(cuò)誤；
3. 調(diào)用Worksheet對(duì)象的write方法，往Worksheet的行/列/單元格內(nèi)寫(xiě)入數(shù)據(jù)，此處用到的數(shù)據(jù)多數(shù)情況來(lái)自xlrd包從Excel文件讀出來(lái)的結(jié)果，前2個(gè)參數(shù)為行列序號(hào)，第3個(gè)參數(shù)是要寫(xiě)入的值，第4個(gè)參數(shù)是單元格風(fēng)格，如無(wú)特殊需求默認(rèn)即可；
4. 調(diào)用Workbook對(duì)象的save方法，將Workbook對(duì)象保存至文件，參數(shù)為文件名稱(chēng)或文件流對(duì)象。

其他的屬性、方法、函數(shù)一般用的較少。

改寫(xiě)與保存示例

假使將前一節(jié)data.xls中讀出的表格做簡(jiǎn)單處理，再保存至新的Excel文件。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

import xlwt  # 導(dǎo)入庫(kù)
In [51]: wbk = xlwt.Workbook()

In [52]: type(wbk)
Out[52]: xlwt.Workbook.Workbook

In [53]: wsh1 = wbk.add_sheet("Sheet1", cell_overwrite_ok=True)

In [54]: type(wsh1)
Out[54]: xlwt.Worksheet.Worksheet

# 將rsh1中的數(shù)據(jù)復(fù)制到wsh1，刻意多空1行、1列
In [55]: for rx in range(rsh1.nrows):
    ...:     for cx in range(rsh1.ncols):
    ...:         wsh1.write(rx+1,cx+1,rsh1.cell_value(rx,cx))

In [56]: wsh1.write(0,0, '新數(shù)據(jù)A1')

In [59]: wsh1.write(0,1, 3.14159)

In [60]: wsh1.write(0,6, False)

In [61]: wsh1.write(4+1,0+1,False) # 覆寫(xiě)原表第4行A列

# 覆寫(xiě)原表第4行D列
In [62]: wsh1.write(3+1, xlwt.Utils.col_by_name('D'),'列D')

經(jīng)過(guò)一番改寫(xiě)，目前所有的改動(dòng)仍在內(nèi)存中，調(diào)用Workbook對(duì)象的save方法，可將數(shù)據(jù)寫(xiě)入文件中。

1
2
3
4
5

In [65]: wbk.save('./data2.xls')

In [66]: wbk.save('./data-second.xlsx') # 可以多次保存

In [67]: wbk.save('./data-second.xlsx') # 本質(zhì)還是xls格式

對(duì)于保存，有兩點(diǎn)需要提醒：

1. Python所有涉及Excel操作的庫(kù)都不支持“原地編輯與保存”，xlwt也不例外，“保存”實(shí)際上是“另存為”，只是指定保存到原文件的話，原文件被覆蓋。
2. xlwt支持寫(xiě)入到xls格式文件，不支持xlsx格式，即使指定擴(kuò)展名.xlsx，文件格式本身仍是xls格式。

保存后文件內(nèi)容如下圖。
datasecond.xls Sheet1

注意從data.xls中讀出來(lái)的日期，本質(zhì)是數(shù)值，復(fù)制后寫(xiě)入還是數(shù)值，需要在Excel中將單元格設(shè)定為日期格式，才能顯示為日期形式。上述寫(xiě)入過(guò)程忽略了單元格風(fēng)格，原data.xls中包含的風(fēng)格信息被全部丟失。關(guān)于單元格風(fēng)格支持，詳見(jiàn)xlwt.Style模塊API，風(fēng)格設(shè)置過(guò)程繁瑣，不如在Excel軟件中操作方便，本文不做評(píng)述。

xlwt還支持寫(xiě)入公式，但較為有限，比如支持countif卻不支持countifs，寫(xiě)入不支持的公式會(huì)報(bào)錯(cuò)。

1
2
3
4

# 第3行E列寫(xiě)入公式
In [69]: wsh1.write(2,4, xlwt.Formula('sum(A3:D3)'))

In [70]: wbk.save('./data-含公式.xls')

表格保存后文件內(nèi)容如下。
data-含公式.xls Sheet1

其他輔助

從上述步驟看，如果僅是生成全新的Excel文件，使用xlwt包即可。如果是“編輯”Excel文件中的某些數(shù)據(jù)，則必須使用xlrd加載原文件并將原表格復(fù)制一份，再使用xlwt去處理需要編輯的單元格，流程繁瑣。xlutils包的copy正是為簡(jiǎn)化此流程而生，可以將xlrd的Book對(duì)象復(fù)制轉(zhuǎn)換為xlwt的Workbook對(duì)象。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

import xlutils.copy # 導(dǎo)入模塊
# rbk是xlrd載入data.xls文件對(duì)應(yīng)的工作簿，見(jiàn)In [4]
In [78]: wbk2 = xlutils.copy.copy(rbk)

In [79]: wbk2
Out[79]: <xlwt.Workbook.Workbook at 0x15552d5aba8>

In [84]: sh = wbk2.get_sheet(0) # 索引到Sheet1

In [85]: sh.write(0,6,'COPIED')

In [86]: wbk2.add_sheet('表單2') #新增表單
Out[86]: <xlwt.Worksheet.Worksheet at 0x15552cda048>

In [87]: wbk2.save('./data-copy.xls')

xlutils.copy.copy是以工作簿整體復(fù)制和轉(zhuǎn)換，原data.xls文件的3個(gè)表單均被復(fù)制過(guò)來(lái)，上述代碼對(duì)Sheet1做了修改，后又增加了新表單，保存后文件內(nèi)容如下。
data-copy.xls

除了copy模塊，xlutils包中還有display、filter等模塊輔助操作Excel文件。

0x03 總結(jié)

從上述讀寫(xiě)的示例來(lái)看，xlrd負(fù)責(zé)讀、xlwt負(fù)責(zé)寫(xiě)、xlutils負(fù)責(zé)提供輔助和銜接，三兄弟各自相對(duì)獨(dú)立而分工明確、配合緊密。xlrd、xlwt、xlutils是以十分原始的方式進(jìn)行數(shù)據(jù)訪問(wèn)，流程相對(duì)繁瑣，好在功能支持方面可以滿(mǎn)足大部分需求。

參考

• Working with Excel files in Python，2009. www.