基于Python 第三方庫實現 Excel讀寫

陳衍鵬

(廣東電網有限責任公司 佛山供電局， 佛山 528000)

基于Python 第三方庫實現 Excel讀寫

陳衍鵬

(廣東電網有限責任公司 佛山供電局， 佛山 528000)

介紹了基于Python第三方庫pyExcelerator與xlrd實現Excel讀寫的方法，著重介紹了xlrd讀取Excel內容和 pyExcelerator修改與生成帶格式 的 Excel 文件的實現細節。pyExcelerator與xlrd可以廣泛應用于部署在非Windows操作系統下的應用程序，方便程序調用 Excel 報表信息，并進行二次編輯或者生成的操作，特別是一些系統自動導出 Excel 文件、網絡游戲 Excel 報表等場合，具有極大的應用價值。

Python； pyExcelerator； xlrd； Excel

0 引言

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

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

1 pyExcelerator與xlrd庫

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

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

2 讀取 Excel 文件

2.1 使用pyExcelerator讀取excel文件

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

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

parse_xls(filename, encoding = None)。

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

from pyExcelerator import *

sheets = parse_xls ('book1.xls')

print sheets

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

得到的結果：

[

(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數據中不存在(1，0) 單元格。所以，在使用返回的數據時，一定要先添加判斷條件，確定單元格是否存在再去讀取，否則會出現 KeyError 的報錯。

3.2 使用xlrd讀取excel文件

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

如一個excel存在三張表，sheet1、sheet2、sheet3，sheet1中的單元格內容為：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 #獲得行數

col_count=sh.ncols #獲得列數

for i in range(0,col_count):

for j range(0,row_count):

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

得到結果：

11

12

13

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

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

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 單元寫入數據‘Hello world’

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

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

3.2 設置單元格字體

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

font = Font ()

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

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

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

font.height = 200 # 設置字體的高度，200相當于10點高

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

font.outline = False# 設置是否采用 outline 字體

font.shadow = False# 設置是否加陰影

font.colour_index = 2# 設置字體顏色的索引

font.escapement = font0.ESCAPEMENT_SUBSCRIPT# 指定字體的上、下標

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

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

3.3 設置列寬

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) 函數返回的是第colnum列的對象，我們可以對它的width屬性來設置和修改此列的寬度。

3.4 設置單元格邊框

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

borders = Borders ()

borders.left = borders.DOUBLE # 設置左邊框

borders.right = borders.DOUBLE # 設置右邊框

borders.top = borders.NO_LINE # 設置上邊框

borders.bottom = borders.NO_LINE # 設置下邊框

borders.diag = borders.DOUBLE # 設置對角線

borders.left_colour = 0x80 # 設置左邊框顏色

borders.right_colour = 0x50 # 設置右邊框顏色

borders.top_colour = 0x60 # 設置上邊框顏色

borders.bottom_colour = 0x70 # 設置下邊框顏色

borders.diag_colour = 0x90 # 設置對角線顏色

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

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

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 屬性分別設置左、右、上、下、對角五條線的類型，類型共有以下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 表示不顯示相應的邊框線，其他值顯示對應線形樣式的邊框。大家可以注意到left_colour等以是_colour結尾的屬性是對應的線段的顏色的索引。need_diag1、need_diag2是用于設置是否需要對角線：NEED_DIAG1 (或 2)需要、NO_NEED_DIAG1 (或2)則表示不需要。其實diag屬性只是設置對角線的線形，實際使用中并不多，只有設置 need_diag 屬性為NEED才真正顯示對角線，因此一般代碼編寫者都不會把該屬性設置出來。

3.5 設置單元格底紋

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

pattern = Pattern ()

pattern.pattern = 1 # 設置底紋的圖案索引

pattern.pattern_fore_colour = 25 # 設置底紋的前景色

pattern.pattern_back_colour = 15 # 設置底紋的背景色

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%灰色，以此類推，具體的設置值可以參考Excel自帶的幫助文檔。pattern_fore_colour、pattern_back_colour 分別表示底紋的前景色和背景色的顏色索引。

3.6 生成合并的單元格

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

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

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

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

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

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函數進行圖片的插入。代碼如下：

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的當前版本較低，只支持插入位圖文件 (bmp)；row、col 表示需要要插入圖片的行列，x、y為插入的位置偏移量， 默認值為 0，不偏移；scale_x、scale_y 為橫向、縱向的伸縮比例，默認值為1。

4 結語

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

[1] [美]Christian, Benvenut 著，夏宏 閆江毓 黃景昌 譯．深入理解Linux網絡技術內幕 [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 網絡數據采集 [M].人民郵電出版社,2016(01). 978-7-1154-1629-2.

[4] 安曉輝 著. QT Quick核心編程 [M].電子工業出版社,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系統管理 [M].清華大學出版社,2014(01).

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

[8] 賽貝爾資訊 著. Excel函數與公式速查手冊 [M].清華大學出版社,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函數與公式大全 [M]. 北京大學出版社,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-)，男，研究方向：信息開發和管理，數據庫。

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

TG4

A

2017.04.30)