[轉]用Python讀寫Excel檔案

標籤：

轉自：http://www.gocalf.com/blog/python-read-write-excel.html#xlrd-xlwt

     雖然天天跟資料打交道，也頻繁地使用Excel進行一些簡單的資料處理和展示，但長期以來總是小心地避免用Python直接讀寫Excel檔案。通常我都是把資料儲存為以TAB分割的文字檔（TSV），再在Excel中進行匯入或者直接複製粘貼。

前段時間做一個項目，卻不得不使用Python直接產生Excel檔案，後來隨著需求的變化，還要對已有的Excel檔案進行讀取。在這個過程中，研究並嘗試了一些工具，也走了一些彎路。記錄下來，下次再有類似需求的時候就不用漫天遍野地搜尋了。

超級無敵大PK

我主要嘗試了四種工具，在此並不會給出他們的排名，因為在不同的應用情境下，做出的選擇會不同。

	XlsxWriter	xlrd&xlwt	OpenPyXL	Microsoft Excel API
介紹	可以建立Excel 2007或更高版本的XLSX檔案	即python-excel，含xlrd、xlwt和xlutils三大模組，分別提供讀、寫和其他功能	可以讀寫Excel 2007 XLSX和XLSM檔案	直接通過COM組件與Microsoft Excel進程通訊調用其各種功能實現對Excel檔案的操作
讀
寫
修改
.xls
.xlsx
大檔案
功能	強	弱	一般	超強
速度	快	快	快	超慢
系統	無限制	無限制	無限制	Windows + Excel
適用情境	要建立XLSX檔案 不需要讀取已有檔案 需要實現比較複雜的功能 資料量可能會很大 需要跨平台	要讀取XLS或XLSX檔案 要產生XLS檔案 需要的功能不太複雜 需要跨平台	要處理XLSX檔案 需要修改已有檔案，或者在寫入過程中需要不斷修改 需要的功能比較複雜 資料量可能會很大 需要跨平台	需要處理各種檔案格式 需要用到特別複雜的功能 在修改檔案時，不希望對原有資訊造成任何意外破壞 資料量很小，或者願意等待 僅在Windows中使用

XlsxWriter

XlsxWriter是我最終選擇的用於寫操作的工具。顧名思義，它只能用來寫檔案。

這應該是個比較新的項目，在GitHub上看它最早的提交是在2013年1月份。其官方文檔中宣稱它支援：

• 100% compatible Excel XLSX files.
• Full formatting.
• Merged cells.
• Defined names.
• Charts.
• Autofilters.
• Data validation and drop down lists.
• Conditional formatting.
• Worksheet PNG/JPEG images.
• Rich multi-format strings.
• Cell comments.
• Memory optimisation mode for writing large files.

優點

一、功能比較強

相對而言，這是除Excel自身之外功能最強的工具了。比如我就用到了它提供的：字型設定、前景色彩背景色、border設定、視圖縮放（zoom）、儲存格合并、autofilter、freeze panes、公式、data validation、儲存格注釋、行高和列寬設定等等。

最讓我驚奇的是，用它產生的帶有儲存格注釋的Excel檔案，不論是Excel 2007還是Excel 2013都可正常開啟（下面會提到，這個任務用Excel自身都無法完成）。

二、支援大檔案寫入

如果資料量非常大，可以啟用constant memory模式，這是一種順序寫入模式，得到一行資料就立刻寫入一行，而不會把所有的資料都保持在記憶體中。

缺點

一、不支援讀取和修改

作者並沒有打算做一個XlsxReader來提供讀取操作。不能讀取，也就無從修改了。它只能用來建立新的檔案。我是利用xlrd把需要的資訊讀入後，用XlsxWriter建立全新的檔案。

另外，即使是建立到一半Excel檔案，也是無法讀取已經建立出來的內容的（資訊應該在，但是並沒有相應的介面）。因為它的主要方法是write而不是set。當你在某個儲存格寫入資料後，除非你自己儲存了相關的內容，否則還是沒有辦法讀出已經寫入的資訊。從這個角度看，你無法做到讀出->修改->寫回，只能是寫入->寫入->寫入。

二、不支援XLS檔案

XLS是Office 2013或更早版本所使用的格式，是一種二進位格式的檔案。XLSX則是用一系列XML檔案組成的（最後的X代表了XML）一個壓縮包。如果非要建立低版本的XLS檔案，就請移步xlwt吧。

三、暫時不支援透視表（Pivot Table）

透視表是非常麻煩的東西，除了自身複雜的結構外，還需要一套資料緩衝。我向作者提出了這個需求，不過這是個很難完全實現的功能，我們慢慢期待吧。

xlrd&xlwt

我的程式在第一版的時候，使用xlwt建立XLS檔案，然後通過Microsoft Excel API將其轉換為XLSX檔案，並寫入進階的Data Validation（Excel 2007的Data Validation比Excel 2003要強大不少）和儲存格注釋。

我的程式最終的版本也依然用xlrd從已有的檔案中讀出所需的資訊。

xlrd&xlwt主要是針對Office 2013或更早版本的XLS檔案格式。

優點

一、支援XLS格式

XlsxWriter和OpenPyXL都不支援XLS格式，從這個角度看，xlrd&xlwt仍然有一定的不可替代性。

缺點

一、對XLSX支援比較差

目前xlrd已經可以讀取XLSX檔案了，有限地支援。至於xlwt我沒有實驗過，估計是夠嗆。

二、對修改的支援比較差

xlrd和xlwt是兩個相對獨立的模組，雖然xlutils提供方法協助你把xlrd.Book對象複製到xlwt.Workbook對象，但跟XlsxWriter類似，後者只是提供write方法，使得你無法很容易地擷取當前已經寫入的資料並進行有針對性的修改。如果非要這樣做，你要不斷地儲存，然後再用新的xlrd.Book對象讀取你要的資訊，還是比較麻煩的。

三、功能很弱

除了最基本的寫入資料和公式，xlwt所提供的功能非常少（Excel 2013本身支援的功能也就很少）。對於讀取也是一樣的，很多資訊在讀入時就丟失掉了。

OpenPyXL

OpenPyXL是比較綜合的一個工具，能讀能寫能修改，功能還算可以但也有很大的缺陷。我在中間版本的時候是打算完全依賴它的，但後來發現一個嚴重的問題就放棄了。

優點

一、能讀能寫能修改

OpenPyXL的工作模式跟XlsxWriter和xlwt有很大的區別，它用的是getter/setter模式。你可以隨時讀取某個儲存格的內容，並根據其內容進行相應的修改，OpenPyXL會幫你記住每個儲存格的狀態。

特別需要注意的一點：雖然它支援修改已有檔案，但由於其所支援的功能有限，讀入檔案時會忽略掉它所不支援的內容，再寫入時，這些內容就丟失了。因此使用時一定要謹慎。比如下面的缺點中提到它無法讀入公式，那如果你修改一個帶有公式的檔案，儲存之後，所有的公式就都沒有了。

二、功能還算可以

整體來講，它所支援的功能介於XlsxWriter和xlwt之間。

缺點

一、不支援XLS

這件事情只能讓xlrd和xlwt去做。

二、不支援讀取公式

這其實是個不太簡單的事情，雖然我沒嘗試過，但相信xlrd也做不好這件事。

Excel的儲存格如果是一個公式，它內部會同時儲存公式本身和運算結果的緩衝。用OpenPyXL讀取儲存格內容，它不會告訴你這個儲存格的公式是什麼，甚至不會告訴你這個儲存格存的是公式，它只會拿到這個緩衝的運算結果。我本來想利用它判別儲存格是不是用了公式，然後做出不同的處理。結果遇到了這個問題，最後只好採取了其他變通的方式去做。

Microsoft Excel API

大部分Windows環境的開發人員都會選擇Microsoft Excel API。實際上不僅僅是Python，幾乎各種語言都有相應的方法使用它，因為核心的邏輯完全是由Microsft Excel自身提供的。語言相關的部分只是負責跟Windows的COM組件進行通訊。

在Python中首先需要安裝Python for Windows extensions（pywin32），具體的文檔可以查閱Win32 Modules和Python COM。

當然你還必須要安裝某一個版本的Microsoft Office Excel，它內部的DLL負責實際的操作。

優點

一、最大的優點：強大無極限

因為直接與Excel進程通訊，你可以做任何在Excel裡可以做的事情。

二、文檔豐富

MSDN上的文檔絕對是世界上最優秀的文檔。沒有之一。

三、調試方便

你完全可以直接在Excel裡面用宏先調試你想要的效果。甚至如果你不清楚怎麼用程式實現某個操作，你可以通過宏錄製的方法得到該操作的處理代碼。

缺點

一、致命的缺點：慢到死

因為需要與Excel進程通訊，其效率是非常低的。

如果讓Excel視窗可見，隨著程式的運行，你可以看到每一句程式所帶來的變化，儲存格的內容一個一個地改變。如果要寫入的資料很多，那速度是無法忍受的。

二、平台限制

目前還沒有發現可以在非Windows系統使用它的方法。

另外，基於它的程式能做什麼事情，很大程度上依賴於當前系統所安裝的Excel版本。不同的版本在功能上有很大的差異，API也會有差異。用起來會比較麻煩。

三、Excel自身bug導致的問題

我剛好發現了其中一個，這和Python沒有任何關係，可以完全在Excel中手動複現。在Excel 2007中隨便建立一個檔案，給某個儲存格添加註釋，儲存。換台電腦，用Excel 2013開啟，就會報錯，然後注釋就消失了。

同樣如果你的程式在一台裝有Excel 2007的機器上建立一個帶有注釋的Excel檔案，把這個檔案拿到Excel 2013中開啟也會報錯，也看不到注釋。反過來也一樣。

關於初始化

Excel的com介面的具體細節我就不介紹了，需要的話直接查閱相關的MSDN文檔即可。這裡只提幾個特殊的小問題。

要想得到一個可以操作的excel對象，一般可以有兩種方式：

import win32com.clientexcel = win32com.client.Dispatch(‘Excel.Application‘)

import win32com.clientexcel = win32com.client.DispatchEx(‘Excel.Application‘)

二者的區別在於，Dispatch方法會試圖尋找並複用一個已有的Excel進程（比如你已經在運行著的Excel程式），而DispatchEx則一定會建立一個新的Excel進程。一般情況使用前者就可以了，還能節省一些資源的開銷。但也會帶來一些麻煩，有一些狀態是在一個Excel進程內共用的，你在同進程的其他視窗內操作有可能會影響到Python程式所要進行的處理，導致各種錯誤。比如當你手動開啟的Excel視窗中，某個儲存格正處於編輯狀態，那Python程式控制的大部分操作都有可能失敗（即使它操作的是另一個檔案），因為一個Excel進程中無法讓兩個儲存格同時被編輯。

為了避免麻煩，我一般都使用DispatchEx方法。

關於視窗可見

可以讓新啟動的Excel進程視窗可見，就像你通過雙擊案頭上的表徵圖啟動一樣，程式所控制的每一步操作，在這個視窗中都可以觀察得到。你也可以同時進行手動的操作，但一旦這樣做，很有可能使你的Python程式崩潰。

視窗不可見也會帶來一些麻煩，前面說了，通過Python啟動的Excel進程跟你直接從案頭開啟的Excel進程沒有什麼區別，在使用Excel的過程中，我們經常會遇到各種彈出的錯誤、警告或者提示框，這些在用Python處理時也有可能遇到。尤其當你的程式還沒完全調試好時。

我一般都會讓程式控制的Excel進程在調試過程中可見，正式使用時不可見，通過類似這樣的命令（假設你有一個叫做is_debug的變數記錄當前是否在調試狀態）：

excel = win32com.client.DispatchEx(‘Excel.Application‘)if is_debug:    excel.Visible = True

關於儲存並覆蓋已有檔案

開啟和儲存檔案的細節不在這裡多說了，可以查看MSDN中相關的API介紹，非常詳細。這裡只說一下在另存新檔時，如果目標檔案已經存在怎麼辦。Excel的API另存新檔方法似乎並沒有提供參數決定是否直接覆蓋同名的目標檔案，在視窗操作中，這種情況會彈出一個確認框來讓使用者決定。我們的程式當然不想這麼做，實際上如果你按照上面所說的讓視窗不可見，你也就看不到彈出的視窗。

可以把DisplayAlert屬性關閉，這樣Excel就不會彈出確認窗，而是直接覆蓋同名檔案。

orig_display_alerts = excel.DisplayAlertsexcel.DisplayAlerts = Falsetry:    book.SaveAs(save_as_file_path)finally:    excel.DisplayAlerts = orig_display_alerts

關於結束Excel進程

進程是一種資源，我們申請了資源，在用完之後就必須要釋放掉。尤其如果你隱藏了Excel視窗，使用者只有查看系統進程，否則無法關閉你所開啟的進程。

但是一個Excel進程是可以同時開啟多個檔案的，這些檔案可能是你程式的其他部分開啟的，也可能是使用者自己開啟的。這樣你就不能隨意地結束Excel進程，否則會影響到其他人或程式的操作。

我一般會在我的處理完成後（關閉了我自己開啟或者建立的Excel檔案），判斷一下當前Excel進程是否還開啟著其他的文檔，如果沒有了才會結束該進程。

number_of_workbooks = excel.Workbooks.Countif number_of_workbooks > 0:    logging.debug(        ‘there are still %d workbooks opened in excel process, not quit excel application‘,        number_of_workbooks    )else:    logging.debug(        ‘no workbook opened in excel process, quiting excel application instance ...‘    )    excel.Quit()del excel

關於枚舉常量

Excel API中有各種各樣的枚舉常量，我還沒有找到在Python中直接引用這些常量的方法，目前的辦法是找到所需的常數的值，自己定義這些常數。比如我用到了如下這些枚舉常量：

1 2 3 4 5 6 7 8 9101112131415

class ExcelConstants(object): # XlFileFormat Enumeration xlOpenXMLWorkbook = 51 # Open XML Workbook. # XlDVType Enumeration xlValidateList = 3 # Value must be present in a specified list. # XlDVAlertStyle Enumeration xlValidAlertStop = 1 # Stop icon. # Constants Enumeration xlCenter = -4108 # XlLineStyle enumeration xlContinuous = 1

要想知道某一個枚舉常量的數值，可以查閱MSDN中Excel Enumerations相關的資料。

【2014年7月31日更新】感謝@依雲提醒，在Python也能夠直接引用相關的常量，即通過win32com.client.constants擷取常量的值。不過這裡還有一點比較tricky的地方，如果直接用Dispatch或者DispatchEx得到Excel對象，是無法從constants中取出常量值的，需要手動運行makepy，或者通過win32com.client.gencache.EnsureDispatch獲得Excel對象：

12345

import win32comfrom win32com.client import constantsexcel = win32com.client.gencache.EnsureDispatch(‘Excel.Application‘)print constants.xlOpenXMLWorkbook # will be 51print constants.xlCenter # will be -4108

[轉]用Python讀寫Excel檔案