基于Python 第三方庫實現(xiàn) Excel讀寫

陳衍鵬

(廣東電網(wǎng)有限責(zé)任公司 佛山供電局， 佛山 528000)

基于Python 第三方庫實現(xiàn) Excel讀寫

陳衍鵬

(廣東電網(wǎng)有限責(zé)任公司 佛山供電局， 佛山 528000)

介紹了基于Python第三方庫pyExcelerator與xlrd實現(xiàn)Excel讀寫的方法，著重介紹了xlrd讀取Excel內(nèi)容和 pyExcelerator修改與生成帶格式 的 Excel 文件的實現(xiàn)細(xì)節(jié)。pyExcelerator與xlrd可以廣泛應(yīng)用于部署在非Windows操作系統(tǒng)下的應(yīng)用程序，方便程序調(diào)用 Excel 報表信息，并進(jìn)行二次編輯或者生成的操作，特別是一些系統(tǒng)自動導(dǎo)出 Excel 文件、網(wǎng)絡(luò)游戲 Excel 報表等場合，具有極大的應(yīng)用價值。

Python； pyExcelerator； xlrd； Excel

0 引言

Python 語言一種解釋型、交互式、純面向?qū)ο蟮哪_本程序設(shè)計語言，它結(jié)合了多種不同語言最好的設(shè)計原則和思想，在軟件開發(fā)的各個領(lǐng)域都得到了廣泛的應(yīng)用。Python 是一種跨平臺的程序設(shè)計語言，在 Windows、Unix、Linux 甚至各種嵌入式操作系統(tǒng)中都得到支持，并且其生成的字節(jié)碼具有平臺無關(guān)性，可以在各種平臺中不經(jīng)修改而直接運(yùn)行。Python語言的強(qiáng)大之處在于它有豐富和強(qiáng)大的類庫，可以方便地處理工作中各種需求。對于簡化程序的代碼，起到了很大的作用！庫很多，博大精深，這樣形容python最恰當(dāng)不過了。正因為其強(qiáng)大的庫，讓編程不再艱難。我們只需要調(diào)用庫中的函數(shù)，而對于函數(shù)的具體實現(xiàn)，沒有特殊需求，我們無需探究。這無疑是編碼者的福音。其次，與C/C++相比，所有語句末尾無需再添加繁瑣的“；”(分號)，因為每一行就是一個語句，并且使用簡單而美觀的縮進(jìn)，規(guī)范了變量和語句的執(zhí)行域，而摒棄了一貫使用的“{}”(花括號)作為界限，不僅使語句更加富有可讀性，而且還為代碼的編寫者提供了良好的代碼習(xí)慣。

Excel 是 Windows 平臺下主流的電子表格處理軟件，可以進(jìn)行各種數(shù)據(jù)的處理、統(tǒng)計分析和輔助決策操作，廣泛應(yīng)用于管理、統(tǒng)計財經(jīng)、金融等多個財務(wù)領(lǐng)域。實際應(yīng)用中有時需要用編程生成腳本的方式對 Excel 文件進(jìn)行自動化處理，比如生成 Excel 格式的報表、整合多個Excle文件的表格內(nèi)容、從多個 Excel 文件提取相關(guān)信息進(jìn)行重運(yùn)算等。這些操作在 Windows 操作系統(tǒng)環(huán)境下可以通過 VB編程實現(xiàn)，但在Linux、Unix等其他操作系統(tǒng)環(huán)境下由于沒有 VBA 運(yùn)行環(huán)境而不能輕易實現(xiàn)，這只能通過直接讀寫 Excel 文件的方式進(jìn)行操作。Python作為跨平臺的語言，很多類庫和組件是平臺無關(guān)的，其中 pyExcelerator 與xlrd就是可以用來跨平臺操作 Excel 文件的類庫之一。文中詳細(xì)介紹使用第三方庫pyExcelerator與xlrd 讀寫 Excel 的方法。

1 pyExcelerator與xlrd庫

pyExcelerator 是一個主要用于產(chǎn)生 Excel 文件的庫，pyExcelerator 完全支持 UNICODE，并且支持各種格式設(shè)置，也提pyExcelerator 主要通過兩個對象對 Excel 進(jìn)行操作 ：Workbook 和 Worksheet，分別對應(yīng) Excel 的 Book 和 Sheet，一個 Workbook 可以包含多個 Worksheet。pyExcelerator讀取數(shù)據(jù)不方便，因為它不提供表格的行數(shù)、列數(shù)、單元格數(shù)據(jù)類型等關(guān)鍵信息的訪問接口，也不支持空數(shù)據(jù)的存在，因此你沒法對它進(jìn)行循環(huán)操作。它讀取數(shù)據(jù)的機(jī)制是這樣的：sheets=parse_xls(filename) 這個sheets返回的是一個列表，每一項是一張sheet的數(shù)據(jù)，每一項本身是一個二元組的數(shù)據(jù)結(jié)構(gòu)(表名，單元格數(shù)據(jù))，單元格數(shù)據(jù)又是一個字典結(jié)構(gòu)，key是單元格的索引(i,j)，value是單元格的數(shù)據(jù)。所以，如果你想獲取此excel文件的第N張工作表的第i行第j列的單元格數(shù)據(jù)，則應(yīng)該是這樣：sheets[n][1][(i,j)]。

XLRD模塊是純Python編寫的Excel表格處理接口模塊，相比pyExcelerator的讀取操作更加便捷和簡單。在完成excel文件打開后，可以sheet_by_index()函數(shù)獲取對應(yīng)的sheet，再通過cell_value(x,y)函數(shù)獲取對應(yīng)單元格的值。當(dāng)然也可以通過col_values(i)獲取整列數(shù)據(jù)，返回的數(shù)據(jù)是列表型，使用sh.nrows或sh.ncols獲取行數(shù)和列數(shù)。因此，xlrd庫提供了更為快捷的excel讀取方式，單該庫不能實現(xiàn)excel的寫操作。

2 讀取 Excel 文件

2.1 使用pyExcelerator讀取excel文件

pyExcelerator 最主要的特色在于其能夠靈活產(chǎn)生各種帶格 式的 Excel 文件，但也能夠讀取已經(jīng)存在的Excel文件，只不過讀取起來不方便。

pyExcelerator讀取Excel 文件只需使用函數(shù)：parse_xls。該函數(shù)在 ImportXLS.py 文件中定義，從函數(shù)聲明中觀察：

parse_xls(filename, encoding = None)。

Filename指向需要打開的Excel 文件， encoding規(guī)定Excel 文件使用的編碼方法。返回值為一個列表，每條數(shù)據(jù)存放一個 Sheet 的數(shù)據(jù)，它是一個二元組 (sheets[n]，{(row,col)：value})，其中單元格數(shù)據(jù)又是一個字典，鍵值就是單元格的索引(row，col)。如果某個單元格無數(shù)據(jù)，那么就不存在這個值。假如book1.xls 文件一共有 3 個 sheet，名稱分別為SheetA、SheetB、SheetC；SheetA 數(shù)據(jù)為 A1=11，A2=12，A3=13；SheetB 數(shù)據(jù)為 A1=21，A2 為空，A3=23；SheetC 為空。用pyExcelerator 讀取方法如下：

from pyExcelerator import *

sheets = parse_xls ('book1.xls')

print sheets

print sheets提供了操作 Excel 打印數(shù)據(jù)的接口。最重要的是 pyExcelerator 是一個跨平臺的模塊，并不需要在Windows操作系統(tǒng)下和COM服務(wù)器環(huán)境也能正常執(zhí)行。這對于需要在如Linux、Unix 環(huán)境下操作Excel的程序來說是個很友好的操作方式，比如能夠很好地應(yīng)用于運(yùn)行在 Linux 下卻需要動態(tài)產(chǎn)生Excel文件，同時能夠把數(shù)據(jù)返回到瀏覽器端的Web程序。

得到的結(jié)果：

[

(u'SheetA',{(2, 0): 13.0,(1, 0): 12.0,(0, 0) : 11.0}) ,

(u'SheetB',{(2, 0): 23.0,(0, 0): 21.0}),

(u'SheetC',{})

]

可以看出，由于 SheetB 的A2不存在，所以得到SheetB數(shù)據(jù)中不存在(1，0) 單元格。所以，在使用返回的數(shù)據(jù)時，一定要先添加判斷條件，確定單元格是否存在再去讀取，否則會出現(xiàn) KeyError 的報錯。

3.2 使用xlrd讀取excel文件

Xlrd主要是提供快捷的讀取方式，可以通過讀取文件，得到表格，通過表格讀取單元格，不需要通過字典的鍵值對來回去單元格的value，并且可以通過nrows和ncols來進(jìn)行表格的遍歷，獲取這個表格的原數(shù)據(jù)。

如一個excel存在三張表，sheet1、sheet2、sheet3，sheet1中的單元格內(nèi)容為：A1=11，A2=12，A3=13，讀取excel的過程代碼如下：

import xlrd

wb = xlrd.open_workbook('book.xls') #打開文件

sh = wb.sheet_by_index(0) #獲得工作表的方法1

sh = wb.sheet_by_name('sheet1') #獲得工作表的方法2

row_count=sh.nrows #獲得行數(shù)

col_count=sh.ncols #獲得列數(shù)

for i in range(0,col_count):

for j range(0,row_count):

print cellA1Value = sh.cell_value(i, j) #獲得單元格數(shù)據(jù)

得到結(jié)果：

11

12

13

從上述結(jié)果來看，open_workbook()函數(shù)讀取了需要打開的excel文件，并且獲取了excel對象。sheet_by_index()和sheet_by_name()兩個函數(shù)是讀取工作表的兩個方式，可以通過位置和表名來進(jìn)行定位。通過nrows和ncols來獲取當(dāng)前表格的行數(shù)和列數(shù)，并且通過遍歷的方式來打印單元格的數(shù)據(jù)。由于遍歷單元格過程中，如果單元格不存在只時，會返回None，因此不存在error的情況，有效保護(hù)程序的運(yùn)行。

個人推薦使用xlrd讀取excel文件，由于不涉及過多的數(shù)據(jù)結(jié)構(gòu)和字典，無需進(jìn)行多余的判斷操作，并且讀取過程和代碼結(jié)構(gòu)清晰，便于代碼的閱讀，降低代碼維護(hù)難度。

3 寫入 Excel 文件

由于xlrd模塊不提供excel文件的修改和生成操作，因此這里使用pyExcelerator 來完成生成的操作。pyExcelerator中有多個類用于寫入 Excel 文件，其中常用的有 3 個：Workbook，Worksheet，XFStyle。Workbook 定義為一個 Excel 文件，Worksheet 定義為一個 Excel 文件中的一頁，XFStyle用于確定寫入單元格的格式。

3.1 生成Excel 文件

下面我們來演示生成 Excel 文件的過程，代碼如下：

coding=utf-8

from pyExcelerator import *

wb = Workbook () # 生成 Workbook 對象

ws = wb.add_sheet ('Sheet1') # 生成Worksheet對象

ws.write (0, 0, 'Hello world') # 向Worksheet對象的 A1 單元寫入數(shù)據(jù)‘Hello world’

w.save ('MyExcel.xls') # 將Workbook對象進(jìn)行保存

首先，我們讀取第一行代碼，pyExcelerator使用workbook生成工作簿對象，然后調(diào)用add_sheet函數(shù)添加sheet表格，接下來使用Worksheet對象的write方法寫入數(shù)據(jù)的，此函數(shù)定義：write (row, column, label="" , style=Style.XFStyle ())。row、column 是要寫入數(shù)據(jù)的單元格的行、列坐標(biāo)，從 0 開始，如果A1單元就是(0，0)，B2 單元就是(1，1)。Label 參數(shù)是要寫入的具體內(nèi)容。最后的style參數(shù)指定寫入時的格式，關(guān)于這個style的使用，我們將在接下來內(nèi)容進(jìn)行講解。

3.2 設(shè)置單元格字體

設(shè)置單元格的字體格式就要使用Font對象，代碼如下：

font = Font ()

font.name = 'Arial'# 明確字體的名稱

font.bold = True# 確定字體是否加粗

font.italic = True# 確定字體是否斜體

font.height = 200 # 設(shè)置字體的高度，200相當(dāng)于10點高

font.struck_out = True# 設(shè)置是否在字之間劃刪除線

font.outline = False# 設(shè)置是否采用 outline 字體

font.shadow = False# 設(shè)置是否加陰影

font.colour_index = 2# 設(shè)置字體顏色的索引

font.escapement = font0.ESCAPEMENT_SUBSCRIPT# 指定字體的上、下標(biāo)

font.underline = font0.UNDERLINE_SINGLE_ACC# 設(shè)置是否使用下劃線

font.family = font0.FAMILY_NONE# 指定字體集

font.charset = font0.CHARSET_ANSI_CYRILLIC# 指定字符集

style = XFStyle ()

style.font = font

wb = Workbook ()

ws = wb.add_sheet('Sheet1')

ws.write(1, 1, 'Test', style)

wb.save('MyExcel.xls')

可以看到通過 Font 對象可以設(shè)置各種字體格式，大部分使用的解釋都在注釋中給出，其中比較特殊的兩個用法是ESCAPEMENT用于設(shè)置字體的上下標(biāo)， ESCAPEMENT_NONE表示不使用上下標(biāo) ， ESCAPEMENT_SUPERSCRIP表示使用上標(biāo)，ESCAPEMENT_SUBSCRIPT表示使用下標(biāo)。underline 用于設(shè)置字體的下劃線，UNDERLINE_NONE表示不使用下劃線， UNDERLINE_SINGLE 表示使用單下劃線，UNDERLINE_SINGLE_ACC表示使用會計用單下劃線， UNDERLINE_DOUBLE 表示使用雙下劃線， UNDERLINE_DOUBLE_ACC 表示使用會計用雙下劃線。另外，通過設(shè)置當(dāng)前行上的某一個單元格的高度 (height)就可以間接地設(shè)置此行的行高。

3.3 設(shè)置列寬

wb = Workbook ()

ws = w.add_sheet ('Sheet1')

ws.write (1, 1, 'Hello World')

ws.col (1) .width = 8000# 8000:400點

w.save ('MyExcel.xls')

ws.col (colnum) 函數(shù)返回的是第colnum列的對象，我們可以對它的width屬性來設(shè)置和修改此列的寬度。

3.4 設(shè)置單元格邊框

使用pyExcelerator模塊中的Borders對象，可以對每個單元格的邊框進(jìn)行設(shè)置。設(shè)置屬性包括left(左邊框)、right(有邊框)、top(上邊框)、bottom(底邊框)、diag(對角線)，還有一些邊框的顏色，如left_colour(左邊框顏色)、right_colour(右邊框顏色)、top_colour(上邊框顏色)、bottom_colour(底邊框顏色)、diag_colour(對角線顏色)。代碼如下：

borders = Borders ()

borders.left = borders.DOUBLE # 設(shè)置左邊框

borders.right = borders.DOUBLE # 設(shè)置右邊框

borders.top = borders.NO_LINE # 設(shè)置上邊框

borders.bottom = borders.NO_LINE # 設(shè)置下邊框

borders.diag = borders.DOUBLE # 設(shè)置對角線

borders.left_colour = 0x80 # 設(shè)置左邊框顏色

borders.right_colour = 0x50 # 設(shè)置右邊框顏色

borders.top_colour = 0x60 # 設(shè)置上邊框顏色

borders.bottom_colour = 0x70 # 設(shè)置下邊框顏色

borders.diag_colour = 0x90 # 設(shè)置對角線顏色

borders.need_diag1 = borders.NEED_DIAG1#設(shè)置是否顯示左上->右下對角線

borders.need_diag2 = borders.NO_NEED_DIAG2#設(shè)置是否顯示左下->右上對角線

style = XFStyle ()

style.borders = borders

wb = Workbook ()

ws = wb.add_sheet ('Sheet1')

ws.write (1, 1, 'Test borders! ', style)

wb.save ('MyExcel.xls')

可以看出Borders是格式 (XFStyle) 的一種，它的 left、 right、top、bottom、diag 屬性分別設(shè)置左、右、上、下、對角五條線的類型，類型共有以下14 種：NO_LINE、THIN、MEDIUM、DASHED、DOTTED、THICK、DOUBLE、HAIR、MEDIUM_DASHED、THIN_DASH_DOTTED、MEDIDOTTED、THIN_DASH_DOT_DOTTED、MEDIUM_DASH_DOT_DOTTED、SLANTED_MEDIUM_DASH_DOTTED。其中 NO_LINE 表示不顯示相應(yīng)的邊框線，其他值顯示對應(yīng)線形樣式的邊框。大家可以注意到left_colour等以是_colour結(jié)尾的屬性是對應(yīng)的線段的顏色的索引。need_diag1、need_diag2是用于設(shè)置是否需要對角線：NEED_DIAG1 (或 2)需要、NO_NEED_DIAG1 (或2)則表示不需要。其實diag屬性只是設(shè)置對角線的線形，實際使用中并不多，只有設(shè)置 need_diag 屬性為NEED才真正顯示對角線，因此一般代碼編寫者都不會把該屬性設(shè)置出來。

3.5 設(shè)置單元格底紋

使用pyExcelerator模塊中的Pattern對象，可以設(shè)置單元格的底紋。設(shè)置的屬性包括patteren(圖案索引)、pattern_fore_colour(底紋的前景色)、pattern_back_colour(底紋的背景色)。

pattern = Pattern ()

pattern.pattern = 1 # 設(shè)置底紋的圖案索引

pattern.pattern_fore_colour = 25 # 設(shè)置底紋的前景色

pattern.pattern_back_colour = 15 # 設(shè)置底紋的背景色

style = XFStyle ()

style.pattern = pattern

wb = Workbook ()

ws0 = wb.add_sheet ('Sheet2')

ws0.write (1, 1, 'Hello world! ', style)

wb.save ('MyExcel.xls')

Pattern的pattern屬性表示底紋的圖案索引，0指實心，1指75%灰色，2指50%灰色，以此類推，具體的設(shè)置值可以參考Excel自帶的幫助文檔。pattern_fore_colour、pattern_back_colour 分別表示底紋的前景色和背景色的顏色索引。

3.6 生成合并的單元格

生成合并單元格要使用模塊中Worksheet 的write_merge、merge函數(shù)。我們來觀察一下write_merge 和merge的定義：

write_merge ( r1, r2, c1, c2, label="" , style)

merge (r1, r2, c1, c2, style)

觀察write_merge的定義，其中 r1，r2 指示需要進(jìn)行單元格合并的起始行和終止行 (起始行一般以0為首計數(shù))；c1、c2 指示要進(jìn)行單元格合并的起始列和終止列 (起始列一般以0為首計數(shù))；label 是寫入的數(shù)據(jù)內(nèi)容；style表示合并后單元格的格式，具體格式的設(shè)置可以參考之前所展示的例子。

觀察merge的定義，可以看出 merge 是 write_merge 的特殊情況，merge 適用于寫入內(nèi)容為空的單元格合并操作。參考代碼如下：

wb = Workbook ()

wb = wb.add_sheet ('Sheet3')

ws.write_merge (3, 3, 1, 5, 'Hello world!')#合并第4行，第2列到6列，并寫入Hello world!。

ws.write_merge (4, 10, 1, 5, 'Hello world2!')#合并第5行到11行，第2列到6列，并寫入Hello world2!。

ws.merge (12,15,1,5)#合并第13行到16行，第2列到 6 列，并寫入空(None)。

wb.save ('MyExcel.xls')

3.7 插入圖片

使用Workbook對象中的insert_bitmap函數(shù)進(jìn)行圖片的插入。代碼如下：

wb = Workbook ()

ws = wb.add_sheet ('Image')

插入 Mypitcure.bmp 圖片

ws.insert_bitmap (' Mypitcure.bmp ', 3, 3, 11, 11, 1, 1)

ws.insert_bitmap ( ' Mypitcure.bmp ', 11, 3) # 在單元格(11,3)插入Mypitcure.bmp圖片

w.save ('MyExcel.xls')

insert_bitmap的定義為 insert_bitmap(filename, row, col, x= 0, y = 0, scale_x = 1, scale_y = 1)，filename為要插入圖片的文件名或文件路徑，由于pyExcelerator的當(dāng)前版本較低，只支持插入位圖文件 (bmp)；row、col 表示需要要插入圖片的行列，x、y為插入的位置偏移量， 默認(rèn)值為 0，不偏移；scale_x、scale_y 為橫向、縱向的伸縮比例，默認(rèn)值為1。

4 結(jié)語

本文給出使用基于Python環(huán)境下 pyExcelerator 、xlrd模塊的讀寫 Excel 文件的方法，由于xlrd是專門的excel文件讀取模塊，在讀取excel文件時，強(qiáng)烈推薦使用xlrd模塊。然后本文著重介紹了修改和生成Excel文件的方法。由于pyExcelerator與xlrd可以跨平臺使用，為Unix、Linux 操作系統(tǒng)下操作 Excel 文件提供了極為友好的環(huán)境，可以廣泛應(yīng)用于部署在非Windows操作系統(tǒng)下的應(yīng)用程序，方便程序調(diào)用 Excel 報表信息，并進(jìn)行二次編輯或者生成的操作，特別是一些系統(tǒng)自動導(dǎo)出 Excel 文件、網(wǎng)絡(luò)游戲 Excel 報表等場合，具有極大的應(yīng)用價值。

[1] [美]Christian, Benvenut 著，夏宏 閆江毓 黃景昌 譯．深入理解Linux網(wǎng)絡(luò)技術(shù)內(nèi)幕 [M]．中國電力出版社，2009(1):978-7-5083-7964-7．

[2] [美] Paul Barry 著；林琪 郭靜 等 譯；Head First Python [M].中國電力出版社,2012(1).978-7-5123-2223-3.

[3] [美] Ryan Mitchell 著；陶俊杰 陳小莉 譯. Python 網(wǎng)絡(luò)數(shù)據(jù)采集 [M].人民郵電出版社,2016(01). 978-7-1154-1629-2.

[4] 安曉輝 著. QT Quick核心編程 [M].電子工業(yè)出版社,2015(01). 978-7-1212-4684-5.

[5] [美] Richard Blum, Christin Bresnahan 著；武海峰 譯. Linux命令行與shell腳本編程大全(第2版) [M].人民郵電出版社,2012(2). 978-7-1152-8889-9.

[6] 馬玉軍，陳連山 著. Red Hat Enterprise Linux 6.5系統(tǒng)管理 [M].清華大學(xué)出版社,2014(01).

[7] Excel Home 著. 別怕，Excel VBA其實很簡單 Excel VBA實戰(zhàn)技巧精粹 [M].人民郵電出版社,2013(1).

[8] 賽貝爾資訊 著. Excel函數(shù)與公式速查手冊 [M].清華大學(xué)出版社,2015(01). 978-7-30238471-7.

[9] [印] Shantanu Tushar, Sarath Lakshman 著；門佳 譯. Linux Shell腳本攻略(第2版) [M].人民郵電出版社,2014(1). 978-7-115-33921-8.

[10] Excel Home 著.Excel2013函數(shù)與公式大全 [M]. 北京大學(xué)出版社,2016(1). 978-7-30126191-0.

Implementation of Reading and Writing for Excel Based on the Third Party Library of Phthon

Chen Yanpeng

(Foshan Power Supply Bureau, Guangdong Power Grid Limited Corporation, Foshan 528000, China)

This paper introduces a method based on Python third library pyExcelerator and xlrd to realize Excel opctions, and emphatically introduces the implementation details of reading Excel contents and producing excel files with format. PyExcelerator and xlrd library widely used on the deployment of applications on non windows operating system, for the program can easily reading and writing excel information, and the operation of correcting or creating, especially some system automatic export Excel files, network game excel report form and so on occasions, and has great application value.

Python; pyExcelerator; xlrd; Excel

陳衍鵬(1990-)，男，研究方向：信息開發(fā)和管理，數(shù)據(jù)庫。

1007-757X(2017)08-0075-04

TG4

A

2017.04.30)