用Python操作Excel,實現班級成績的統計
本次是在原來有一定格式的Excel文件中補充成績。
- 安裝的模組:xlwt 、 xlrd 、xlutils
xlrd的模組是隻用讀取xls檔案,不能寫檔案,同理xlwt,只(新建寫)不讀已有的xls,
xlrd的用法:
data =xlrd.open_workbook(fime_path+'011.xls') table = data.sheet_by_index(0)
nrows = table.nrows ncols = table.ncols
- 讀取單元格:
table.cell(i,j).value
xlwt的用法
wbk = xlwt.Workbook() sheet = wbk.add_sheet('sheet 1') sheet.write(0,1,'test text') wbk.save('test.xls')
- 詳情:【 ofollow,noindex" target="_blank">https://my.oschina.net/dddttttt/blog/466103 】
到了講怎麼在原有的xls文件追加。這就使用到xlutils,xlutils(依賴於xlrd和xlwt)提供複製excel檔案內容和修改檔案的功能。其實際也只是在xlrd.Book和xlwt.Workbook之間建立了一個管道而已,如下圖:
from xlutils.copy import copy old_xls = xlrd.open_workbook(file_path2,formatting_info=True) table_xlwt_b = copy(old_xls) table_xlwt = table_xlwt_b.get_sheet(0) table_xlwt.write(id_p,j,list[k]) #iid_p是行,j是列,list[k]是填充的值 table_xlwt_b.save(fime_path+"033.xls")
最後需要注意,開啟原有xls檔案,需要保留文件單元格的格式,需要在xlrd開啟檔案引數新增formatting_info=True,(預設是FALSE),同時,這引數只支援舊版的xls字尾的檔案,不支援新版的xlsx字尾的文件,,如果開啟xlsx會丟擲異常,因此需要另存為xls文件
最後附上程式碼
#!coding:utf-8 import xlrd import xlwt import copy from xlutils.copy import copy fime_path="F:\\program_new\\PyCharm Community Edition 2018.2.3\\code_example\\xlwt_xlrd\\code\\" old_xls = xlrd.open_workbook(fime_path+"022.xls", formatting_info=True) def read_book(): data =xlrd.open_workbook(fime_path+'011.xls') #匯入表 table = data.sheet_by_index(0) nrows = table.nrows ncols = table.ncols i=0 j=0 list_score = [] score = [] for i in range(1,nrows): for j in range(6,ncols): # print("%d%d"%(i,j)) score.append(table.cell(i,j).value) list_score.append(score) score=[] return list_score def id_position(student_id): tabel_xlwt_ot = old_xls.sheet_by_index(0) nrows = tabel_xlwt_ot.nrows ncols = tabel_xlwt_ot.ncols for i in range(3,nrows): now_student_id = int(tabel_xlwt_ot.cell(i,0).value) now_student_id=str(now_student_id) if now_student_id==student_id: return i def write_book(): table_xlwt_b = copy(old_xls) table_xlwt = table_xlwt_b.get_sheet(0) list2=read_book() print(len(list2),len(list2[1])) for list in list2: s_id=list[0] print(list) id_p = id_position(s_id) if id_pis not None: for (j,k) in zip(range(2,27,3),range(2,11)): print(k,j) table_xlwt.write(id_p,j,list[k]) else: print(u"找不到該學號%s"%s_id) table_xlwt_b.save(fime_path+"033.xls") if __name__=="__main__": write_book()
不用關閉檔案,官方沒有提供具體的方法關閉開啟的xls檔案。可以使用 book.release_resources()
釋放記憶體