題圖：希臘德爾斐(Delphi)神廟遺址。傳說此地原為巨蟒Python盤踞，阿波羅來到此地射殺Python后建立起神廟，成為古希臘的精神圣地。

關鍵詞：Python, 郵件掃描， yaml配置文件，郵件附件編解碼，Excel自動處理?

一 、前言

? ? ? ?作為一只二級市場資管狗，平時少不了向各相關方匯報產品數據，不論領導、風控部門同事、市場人員或者客戶，經常會要求我們按照一定頻率披露相關數據(工作年頭一長，感覺需要匯報的人越來越多)。資管產品最準確的數據來源就是每個交易日收盤后，經過估值清算生成的估值表。但是這些估值服務供應商不同，表格格式差別較大，內容也比較繁雜，不論從可讀性和信息保密方面考慮，都不應該隨意將估值表提供給對方。比較合適的做法是將對方感興趣的且允許披露的內容提取出來，放到一個匯總報告中。

? ? ? ?靠手工完成這類簡單重復工作比較耗時，且容易讓人感覺枯燥導致出錯，這里我用Python實現了一個工具，能夠自動掃描郵件，下載Excel電子表格附件，抽取所需信息并匯總，再將報告自動發送到指定郵件帳戶。看似簡單的功能，里面還需要不少技巧，完工后順便寫篇帖子總結一下經驗。

二、系統設計

? ? ? 圖1. 所示是系統設計。因為是凈值(Net Asset Value)報告程序，整個程序的入口類叫做NavReporter。頂層模塊下面實現了6個功能模塊，每個功能模塊都以單例(Singleton)模式實現。分別完成配置信息解析(Config)，日期文件解析和簡單日期函數(Calendar)，郵箱掃描定位目標郵件并下載附件(EmailScanner)，估值表電子表格解析和信息提取(NavParser)，匯總報告表格自動填寫(ReportFiller)以及報告發送(ReportSender)等幾塊功能。分成多模塊實現目標是每個類盡量爭取做到單獨可用，這樣郵件附件下載后，可以根據不同需求解析不同信息，填寫不同格式報告，發送給不同接收人。

????????????????????圖1. 系統結構圖

三、功能實現

? ?1. Config模塊

? ? ? ?這個模塊實現比較簡單，主要使用了yaml庫解析yaml格式的配置文件。我原本打算使用json格式文件作為配置文件，但是發現手動編輯的json文件總是出現莫名的解析錯誤。看到一些經驗分享文章也不推薦使用json作為配置文件。json本身設計目的是作為一種數據交換格式，對于注釋信息支持較差，自身語法格式規范也很嚴格(大概是我手動編輯引發解析錯誤的原因)，不太適合作為配置文件使用。

? ? ? 這里推薦使用yaml庫，文件格式結構與json接近，層次關系省略了json大量使用的花括號，代之以縮進方式。對于yaml格式配置文件，yaml.load()一個方法就能把文件讀入，并解析成字典格式，非常方便。

? ? ? ?yaml庫解析出來的內容，會放到一個字典結構中，因為配置項存在嵌套關系，返回的字典也會是嵌套結構，訪問一個深層次配置項會有 config_info['TopLev']['MidLev']['BotLev']這種情況。針對此我引入屬性字典結構，把這種嵌套字典都映射成不同層次的屬性，提高了可讀性，至少也可以省略多個中括號和引號。映射成屬性字典后，訪問一個層次嵌套屬性會變成如下形式：config_info.TopLev.MidLev.BotLev。實現這種功能只需要一個深度優先遞歸遍歷字典結構并更新Python的類成員self.__dict__操作。圖2是這部分的關鍵代碼。

圖2.?遞歸更新self.__dict__成員實現屬性字典

? ?2. Calendar模塊

? ? ? ?日期模塊功能也很簡單。日期模塊功能依賴于初始化時候讀入事先設置好的日歷文件，包含了交易日信息，通常只有在交易日才會有新的估值信息。日期模塊提供了交易日判斷功能，判斷傳入的日期是否是交易日，如果不是，程序可以直接退出，無需做任何工作。另外，這個模塊還提供了方法獲得指定的交易日。假設當前是T日，多數情況下，T日只能獲得T-1日或者T-2日的估值信息，所以模塊提供了get_prev_trading_day()方法獲得前一個交易日信息。

? ? 3. EmailScanner模塊

? ? ? ?郵件掃描先要登錄郵箱，通過配置文件提供的POP3服務器地址、用戶名和密碼，調用Python的poplib庫就可以登錄郵件服務器，獲得郵箱內郵件信息。圖3所示登錄代碼片斷。

圖3. 登錄POP3服務器

? ? ? 登錄成功后，按照由新及舊的順序，逐個掃描郵件。先解析郵件頭信息，然后查看發件人，如果發件人不是估值表提供商，則循環到下一個郵件。匹配發件人成功的郵件，還要檢查是否存在附件，如果存在附件，則檢查附件名是否是所需要文件，只有以上規則都匹配，才將附件下載，否則就跳到下一個郵件。提取附件時候，郵件解碼是個容易出錯問題，具體內容在下一節避坑指南中介紹。圖4是郵件掃描流程圖。

圖4.?郵件掃描流程

? ?4.?NavParser解析Excel電子表格

????? ?Python提供了多個能處理Excel電子表格的庫，這里采用操作比較簡單xlrd庫對文件進行讀取解析。操作步驟是：

????????1).?打開工作表文件

????????2).?獲取表單(Sheet)列表名稱或者索引

????????3).?根據名稱或者索引取得當前表單

????????4). 逐行、逐列遍歷Excel表格單元，查找所需要的內容

???????圖5的代碼片斷就是解析文件的過程，掃描電子表格的每個單元，提取出單位凈值、累計收益率等信息，然后返回一個字典結構，字典鍵就是產品名，數據就是提取到的凈值、規模、收益率數據。提取數據的幾個方法這里我都實現成私有方法(函數名前有2個下劃線)。

圖5. 解析電子表格文件

? ?5.?ReportFiller模塊

??????讀完表后就開始填表。Python對Excel操作的庫實現得比較擰巴，xlrd和xlwt兩個模塊一個只負責讀，一個只負責寫，并且，負責寫的模塊只支持新建寫入文件，不能直接在原有文件上覆蓋寫入，以至于對報告進行每日增量更新比較麻煩。還好有人提供了一個xlutils庫，實際上是在xlrd和xlwt上加了一層管道，把二者結合起來，但使用起來依然不是那么順手。另外，這里面關于寫入文件如何保持原有文件格式也需要一個技巧，在下一部分避坑指南中會詳細介紹。最后，這些操作只能支持.xls格式的Excel文件，對于較新的.xlsx格式文件尚不支持。

?????? ReportFiller使用xlrd和xlutils兩個庫結合實現原Excel表格文件的增量更新。如果要保留原來文件格式，需要在打開工作簿時候，設置參數formatting_info=True。xlutils會復制一個工作簿進行修改工作，修改完畢后將工作簿寫回原來文件。圖6是填寫工作簿的代碼片段。

????? 從圖中代碼示例可見，使用xlrd.open_workbook()方法時候，參數formatting_info被設置為True。第54行是xlutils的拷貝操作，其內部本質還是調用了xlwt模塊方法，因為xlwt模塊不支持在原有Excel文件上直接修改，這個拷貝操作實際上是創建了一個新的工作簿。循環體中的操作是先從原工作簿中定位到需要填寫的Excel單元行、列坐標，然后將要寫入的內容填寫到新拷貝的工作簿中。注意最后文件保存操作wrt_wb.save()是在with語句之外。這是因為with語句結束后才會釋放資源(讀寫鎖)，關閉被打開的文件。這樣保存操作才會成功將原報告文件覆蓋，否則可能會引發無法寫入已打開文件的錯誤。

圖6. 填寫Excel電子表格代碼片段?

??6. ReportSender模塊

??????相比前面掃描郵件、讀寫電子表格，ReportSender模塊功能就簡單多了——創建一個電子郵件，把填寫好的報告作為附件發送給相關人員。?創建郵件體使用email.mime類中提供的各種方法，然后使用smtplib模塊，登錄smtp服務器，將郵件發送出去即可。圖7是整個郵件發送的核心代碼。? ?

圖7.?使用Python的smtplib發送電子郵件

四、技巧和避坑指南

??? ? 最開始編寫這個程序，我的初衷就是實現一個提高工作效率的小工具，預計差不多半天到一天就可以完成，結果沒想到中間還踩了幾個坑，花了大概三天左右時間才初步完成，中間也學習到一些技巧，有了一些收獲，這里把有意思的干貨總結一下。

? ?1. 如何優雅的跳出兩層循環

??????在遍歷掃描Excel表格時候，通常采用先按行再按列的雙層循環結構，找到目標單元后，需要結束循環，進入下一步操作。如果在最內層循環調用break，只能中斷最內層循環，外面一層還會繼續。如果設置一個標識變量，比如loop_flag，中斷內層循環后設置標識，外層再判斷這個標識決定是否中斷。感覺這樣實現起來實在不夠“優雅”或者“Pythonic”，有什么辦法能夠看起來更“舒適”一些呢？

??????我發現使用Python語言的一個特性可以實現，就是循環結束后的“else”語句。具體實現如圖8代碼所示。

圖8. Python終結兩層循環

? ? ? ?第73行的“else”語句是Python特有的語法，這個else語句是距離它最近的一個循環如果正常結束就會被執行，如果被break中斷掉，就不會執行。這樣邏輯就很清晰了，如果70~72行的循環體一直循環到結束沒有被中斷，則73~74行語句會被執行，continue語句會跳過76行的break；如果70~72行的循環被72的break語句中斷，則73~75行的語句不會被執行，76行的break會被執行，這樣結果就是內層循環被中斷，外層的同時也會被中斷。

??????我發現Python這個循環體自帶的“else”語句很有意思，第一次接觸時候不太習慣甚至有點排斥，后面遇到一些應用場景后，感覺挺好用的，當然也有人批評這個特性讓代碼可讀性變差。

? 2. 郵件編碼解碼

? ? ??我們知道Email內容在傳輸時是經過編碼的，最知名的就是base64編碼，但其實MIME還定義一種QuotePrintable(簡稱QP)編碼。由于只知道base64編碼，以至于我遇到QP編碼的郵件解碼總是失敗，開始還以為程序問題，后來找了一些經驗資料才知道是解碼方式不對。為此，郵件掃描程序實現了一個郵件解碼方法，根據郵件提供的編碼信息判斷是base64還是QP編碼，然后調用相應的解碼器解出郵件原文。郵件解碼方法如圖9所示。

圖9. 處理郵件編碼解碼問題

? 3. Excel保留格式問題

????? 使用xlwt或者xlutils填寫Excel表格，會有一個惱人的問題：如果你的當前工作表拷貝自一個原工作表，在寫入操作后，被寫入單元的格式信息會丟失。解決辦法是：在寫入前先保存格式信息，然后進行寫入單元格操作，再把保留的格式信息賦值給被寫入的單元格。具體操作見圖10。

圖10. 如何對Excel單元進行寫操作并保留原來格式

??4. Excel文件保存問題

????? 使用xlutils時候還遇到一個莫名其妙問題是，如果對Excel文件做了些許改動，比如打開文件手工修改一下格式等，修改完畢關閉文件，再用xlutils操作Excel寫入后，保存文件會報錯。報錯原因也很無厘頭，提示內容往往是：TypeError: save descriptor 'decode' requires a 'bytes' object but received a 'NoneType'。經過一番查詢，貌似這是一個庫文件的bug，應該是Unicode編碼處理問題，需要Hack一下庫文件，修改后即正常。可以參考網上內容https://github.com/python-excel/xlutils/issues/11 ??

? ?具體修正方式是:進入到Python安裝目錄下，找到Lib/site-packages/xlwt/UnicodeUtils.py文件，把里面的upack2()方法修改成圖11的形式。

圖11. 修正xlwt/UnicodeUtils.py中的Bug

五、如何獲得代碼

??????前文洋洋灑灑幾千字介紹了很多內容，碼農屆有句話叫：“Talk is cheap, show me the code”。光有文字沒有代碼怎么能說明問題，這里告訴你如何獲得參考代碼實現——在github網站下載，地址：https://github.com/swankong/NavReporter 。代碼中已經去掉了我工作相關的敏感信息，包括產品名，公司名，郵箱地址等。

????? 由于這個程序較多依賴于我的工作環境，我又急于趁熱打鐵寫成這篇文章，導致修改過后的代碼大概不能直接成功運行，后面我會抽空花些時間維護一下，力爭能做成一個例子，可以讓github上下載的程序直接成功運行，這樣就更有參考意義。還要強調一下，目前版本中的一些功能方法稍加修改就可以拿出來單獨使用，有類似需求的讀者可自由參考借鑒。

六、總結與展望

?????本文介紹了如何使用Python實現自動郵件掃描和Excel讀寫，以達到辦公自動化提高工作效率的目的。目前整個系統還只是完成了初步的功能實現，尚且存在一些缺陷。下一步的工作包括但不限于：

??????? 1). 完善程序中英文注釋信息，提高可讀性。

??????? 2). 完成一個去掉敏感信息的應用實例，實現github上傳的程序可以直接運行。

??????? 3). 考慮修改程序功能，增加把數據寫入關系數據庫的功能，從而實現估值表掃描下載、讀取解析、數據入庫一整個流程自動化。

???????最后感謝互聯網上眾多技術愛好者發布的經驗分享文章，讓我在程序踩坑時候及時找到了解決方案。

總結

以上是生活随笔為你收集整理的字典写入excel_使用Python扫描邮件/填写Excel表格实现办公自动化的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。