用python從符合一定格式的txt文檔中逐行讀取資料並按一定規則寫入excel（openpyxl支援Excel 2007 .xlsx格式）

標籤：

 

前幾天接到一個任務，從gerrit上通過ssh命令擷取一些commit相關的資料到文字文件中，隨後將這些資料存入Excel中。資料格式如所示

觀察可知，存在文字文件中的資料符合一定的格式，通過python讀取、Regex處理並寫入Excel文檔將大大減少人工處理的工作量。

　　1. 從gerrit擷取原始資訊，存入文字文件： 

　　$ssh –p 29418 <your-account>@192.168.1.16 gerrit query status:merged since:<date/7/days/ago> 2>&1 | tee merged_patch_this_week.txt

　　2. 從txt文檔中讀取資料。

　　Python的標準庫中，檔案對象提供了三個“讀”方法： .read()、.readline() 和 .readlines()。每種方法可以接受一個變數以限制每次讀取的資料量，但它們通常不使用變數。 .read() 每次讀取整個檔案，它通常用於將檔案內容放到一個字串變數中。然而 .read() 組建檔案內容最直接的字串表示，但對於連續的面向行的處理，它卻是不必要的，並且如果檔案大於可用記憶體，則不可能實現這種處理。

　　readline() 和 readlines()之間的差異是後者一次讀取整個檔案，象 .read()一樣。.readlines()自動將檔案內容分析成一個行的列表，該列表可以由 Python 的 for... in ... 結構進行處理。另一方面，.readline()每次唯讀取一行，通常比 .readlines()慢得多。僅當沒有足夠記憶體可以一次讀取整個檔案時，才應該使用.readline()。   

patch_file_name="merged_patch_this_week.txt"      patch_file=open(patch_file_name,‘r‘)        #開啟文檔，逐行讀取資料for line in open(patch_file_name):    line=patch_file.readline()    print line

　　3. 寫入到Excel文檔中

 　　　　python處理Excel的函數庫中，xlrd、xlwt、xlutils比較常用，網上關於它們的資料也有很多。但由於它們都不支援Excel 2007以後的版本(.xlsx)，所以只能忍痛放棄。

經過一番搜尋，找到了openpyxl這個函數庫，它不僅支援Excel 2007，並且一直有人維護（當前最新版本為2.2.1，2015年3月31日發布）。官方的描述為:

A Python library to read/write Excel 2007 xlsx/xlsm files，它的文檔清晰易讀，相關網站：http://openpyxl.readthedocs.org/en/latest/index.html

　　openpyxl ：https://bitbucket.org/openpyxl/openpyxl/get/2.2.1.tar.bz2

　　它依賴於jdcal 模組，： https://pypi.python.org/packages/source/j/jdcal/jdcal-1.0.tar.gz

　　安裝方法(windows 7)：首先安裝jdcal模組--解壓縮到某目錄，cd到該目錄，運行"python setup.py install"。 然後安裝openpyxl,方法相同。

　　寫入步驟如下：

　　1. 開啟活頁簿:

wb=load_workbook(‘Android_Patch_Review-Y2015.xlsx‘)

  　 2. 獲得工作表

sheetnames = wb.get_sheet_names()                   ws = wb.get_sheet_by_name(sheetnames[2])  

　　3. 將txt文檔中的資料寫入並設定儲存格格式

patch_file_name="merged_patch_this_week.txt"      patch_file=open(patch_file_name,‘r‘)        #開啟文檔，逐行讀取資料ft=Font(name=‘Neo Sans Intel‘,size=11)for line in open(patch_file_name):    line=patch_file.readline()    ws.cell(row=1,column=6).value=re.sub(‘project:‘,‘‘,line)#匹配project行，若匹配成功，則將字串“project:”刪除，剩餘部分寫入Excel第1行第6列    ws.cell(row=rows+1,column=1).font=ft

 

　　4. 儲存活頁簿

wb.save(‘Android_Patch_Review-Y2015.xlsx‘)

 

 

完整代碼如下：

 

from openpyxl.workbook import Workbookfrom openpyxl.reader.excel import load_workbookfrom openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Fontimport re#from openpyxl.writer.excel import ExcelWriter#import xlrdft=Font(name=‘Neo Sans Intel‘,size=11)             #define font stylebd=Border(left=Side(border_style=‘thin‘,color=‘00000000‘),          right=Side(border_style=‘thin‘,color=‘00000000‘),          top=Side(border_style=‘thin‘,color=‘00000000‘),          bottom=Side(border_style=‘thin‘,color=‘00000000‘))            #define border stylealg_cc=Alignment(horizontal=‘center‘,                        vertical=‘center‘,                        text_rotation=0,                        wrap_text=True,                        shrink_to_fit=True,                        indent=0)                           #define alignment stylesalg_cb=Alignment(horizontal=‘center‘,                        vertical=‘bottom‘,                        text_rotation=0,                        wrap_text=True,                        shrink_to_fit=True,                        indent=0)alg_lc=Alignment(horizontal=‘left‘,                        vertical=‘center‘,                        text_rotation=0,                        wrap_text=True,                        shrink_to_fit=True,                        indent=0)                                                   patch_file_name="merged_patch_this_week.txt"      patch_file=open(patch_file_name,‘r‘)        #get data patch textwb=load_workbook(‘Android_Patch_Review-Y2015.xlsx‘)          #open excel to writesheetnames = wb.get_sheet_names()                   ws = wb.get_sheet_by_name(sheetnames[2])                    #get sheetrows=len(ws.rows)assert ws.cell(row=rows,column=1).value!=None, ‘New Document or empty row at the end of the document? Please input at least one row!‘print "The original Excel document has %d rows totally." %(rows)end_tag=‘type: stats‘for line in open(patch_file_name):    line=patch_file.readline()    if re.match(end_tag,line) is not None:    #end string        break    if  len(line)==1:                                       #go to next patch        rows=rows+1        continue    line = line.strip()    #    print line    ws.cell(row=rows+1,column=1).value=ws.cell(row=rows,column=1).value+1           #Write No.    ws.cell(row=rows+1,column=1).font=ft    ws.cell(row=rows+1,column=1).border=bd    ws.cell(row=rows+1,column=1).alignment=alg_cb    ws.cell(row=rows+1,column=5).border=bd    ws.cell(row=rows+1,column=9).border=bd        if re.match(‘change‘,line) is not None:        ws.cell(row=rows+1,column=2).value=re.sub(‘change‘,‘‘,line)           #Write Gerrit ID        ws.cell(row=rows+1,column=2).font=ft        ws.cell(row=rows+1,column=2).border=bd        ws.cell(row=rows+1,column=2).alignment=alg_cb    if re.match(‘url:‘,line) is not None:        ws.cell(row=rows+1,column=3).value=re.sub(‘url:‘,‘‘,line)             #Write Gerrit url        ws.cell(row=rows+1,column=3).font=ft        ws.cell(row=rows+1,column=3).border=bd        ws.cell(row=rows+1,column=3).alignment=alg_cb    if re.match(‘project:‘,line) is not None:        ws.cell(row=rows+1,column=6).value=re.sub(‘project:‘,‘‘,line)             #Write project        ws.cell(row=rows+1,column=6).font=ft        ws.cell(row=rows+1,column=6).border=bd        ws.cell(row=rows+1,column=6).alignment=alg_lc            if re.match(‘branch:‘,line) is not None:        ws.cell(row=rows+1,column=7).value=re.sub(‘branch:‘,‘‘,line)              #Write branch        ws.cell(row=rows+1,column=7).font=ft        ws.cell(row=rows+1,column=7).border=bd        ws.cell(row=rows+1,column=7).alignment=alg_cc            if re.match(‘lastUpdated:‘,line) is not None:        ws.cell(row=rows+1,column=8).value=re.sub(‘lastUpdated:|CST‘,‘‘,line)             #Write update time        ws.cell(row=rows+1,column=8).font=ft        ws.cell(row=rows+1,column=8).border=bd        ws.cell(row=rows+1,column=8).alignment=alg_cc            if re.match(‘commitMessage:‘,line) is not None:        description_str=re.sub(‘commitMessage:‘,‘‘,line)     if re.match(‘Product:|BugID:|Description:|Unit Test:|Change-Id:‘,line) is not None:        description_str=description_str+‘\n‘+line                       #    if re.match(‘Signed-off-by:‘,line) is not None:        description_str=description_str+‘\n‘+line        ws.cell(row=rows+1,column=4).value=description_str                #Write patch description        ws.cell(row=rows+1,column=4).font=ft        ws.cell(row=rows+1,column=4).border=bd        ws.cell(row=rows+1,column=4).alignment=alg_lcwb.save(‘Android_Patch_Review-Y2015.xlsx‘)print ‘Android_Patch_Review-Y2015.xlsx saved!\nPatch Collection Done!‘#patch_file.close()

 

目前為止，準系統已經實現，但是還有兩個問題沒有搞明白：

第一個是完整代碼中的最後一句注釋行，我搜到的幾篇介紹openpyxl的部落格中，開啟檔案後都沒有close，所以我在代碼中也沒有close。理論上感覺還是需要的。等對檔案對象的理解更加深入一些時會繼續考慮這個問題。

第二是運行該指令碼時有一個warning," UserWarning: Discarded range with reserved name，warnings.warn("Discarded range with reserved name")“，目前還在搜尋原因，如有明白的，也請不吝告知。

 

　　（參考：http://blog.csdn.net/werm520/article/details/6898473）

用python從符合一定格式的txt文檔中逐行讀取資料並按一定規則寫入excel（openpyxl支援Excel 2007 .xlsx格式）