Python openpyxl、pandas操作Excel方法簡介與具體實例本篇重點講解windows系統(tǒng)下 Python3.5中第三方excel操作庫-openpyxl； 其實Python第三方庫有很多可以操作Excel，如：xlrd,xlwt，xlwings甚至注明的數(shù)據(jù)分析模塊Pandas也提供pandas.read_excel、pandas.DataFrame.to_excel功能。 那么openpyxl的庫有哪些優(yōu)缺點呢： 優(yōu)勢： 1、openpyxl提供對pandas的dataframe對象完美支持； 2、openpyxl支持后臺靜默打開excel文件； 3、它支持excel的一些sort、filter篩選、排序功能，支持豐富的單元格style（樣式）設(shè)計； 4、它同時支持讀取現(xiàn)成的excel文件&創(chuàng)建全新的excel文件； 5、它支持最新的xlsx格式文件，且更新頻繁，操作較為簡練。 缺點： 1、運算效率相對不高，當表格行項目過多時，運算相對pandas等速度較慢； 2、部分針對行或列的樣式設(shè)計有一定bug，語法可能失效； 3、對sort和filter的功能雖然支持，但是需要用戶手工打開excel后刷新數(shù)據(jù)方能使搜索條件生效，屬于半自動； 4、不支持excel原生的自動列寬功能，實現(xiàn)同樣效果略復(fù)雜。 簡單的屬性和方法如下： 新建工作簿對象： >>> from openpyxl import Workbook 定位當前活躍工作表： >>> ws = wb.active 創(chuàng)建新工作表： ws1 = wb.create_sheet("Sheet2") 工作表改名： ws.title = "New Sheet" 如果要選擇某張非活躍工作表，可以指定工作表名稱，如： ws=wb["New Sheet"] 復(fù)制工作表為副本： >>> ws1 = wb.active 訪問單元格有兩種寫法，如訪問單元格A3，可以寫作： >>> ws['A3'] = ”hello" 或： >>> ws.cell(row=3,column=1).value = ”hello" 也可簡化為： >>> ws.cell(3，1).value = ”hello" 訪問多個單元格區(qū)域Range： >>> cell_range = ws['A1':'B5'] 同理，訪問某一行（如第三行）可以寫作： >>> row3 = ws[3] 訪問某一列（如C列）可以寫作： >>> colC = ws['C'] 如果要訪問多行多列，則用“：”分隔，如： >>> col_range = ws['C:D'] >>> row_range = ws[5:10] 對工作表操作完成后的保存操作： >>> wb = Workbook() PS：如果要保存的文件名已存在，則此操作將覆蓋現(xiàn)有的文件沒有警告。 導(dǎo)入 >>> from openpyxl import load_workbook 通過append方法逐行寫入excel，如從頭寫入10行數(shù)據(jù)可以這些遍歷： >>> for row in range(1, 11): ps：workbook對象的-data_only 屬性 控制細胞是否有公式的 公式(默認)或Excel讀最后一次存儲的值表。 對單元格寫入公式，方法如下： >>> ws["C1"] = "=average(A1, B1)" 常見的單元格合并以及取消合并，方法如下： >>> ws.merge_cells('A1:B5') 主要示例：1、利用openpyxl模塊基于多字段拆分工作表為多張工作簿， 2、同時滿足樣式要求，只導(dǎo)入部分標紅色字段數(shù)據(jù)，同時對特定行項目進行顏色標注， 3、利用pandas模塊對excel文件進行排序、篩選，再寫回excel。原始數(shù)據(jù)表頭header如下：
主要代碼如下：
"""主要功能： 1、將需求的字段生成對應(yīng)表格；2、將金額為正的（S）的行項目標記為黃色； 3、只導(dǎo)入特定物料；4、對數(shù)據(jù)源取名進行約束； 5、增加程序運行完畢的消息提醒；6、增加padas的排序、篩選功能"""from openpyxl import load_workbookfrom openpyxl import Workbookfrom openpyxl.styles import PatternFill,Border,Side,Alignment,Protection,Font,GradientFill,Color,Colorsimport easygui as eg,pandas as pdimport os,time pwd = os.getcwd() writer = pd.ExcelWriter('暫估表_按公司&供應(yīng)商排序.xlsx') df1 = pd.DataFrame(pd.read_excel(os.path.join(pwd,'暫估表.xlsx'),dtype={'供應(yīng)商':str,'物料':str,'采購訂單':str})) #將excel讀入pandas的DataFrame對象，同時約定“供應(yīng)商、物料、采購訂單”等字段為字符串類型，避免被pandas轉(zhuǎn)化為數(shù)字df1=df1.sort_values(by=["公司","供應(yīng)商"],ascending=True) #基于公司、供應(yīng)商兩個字段做升序排列，字段排序有主次之分df1.to_excel(writer,'Sheet1',index=False) #index=False，表明導(dǎo)入excel時不寫入DataFrame對象的索引列writer.save() thin = Side(border_style="thin", color="000000") #邊框樣式，定義為對象if not os.path.exists(pwd+"\\暫估分類表"): os.mkdir(pwd+"\\暫估分類表") arr=[] wb = load_workbook(filename=pwd+u"\\暫估表_按公司&供應(yīng)商排序.xlsx") ws=wb["Sheet1"] navigation=['公司','供應(yīng)商','名稱描述','物料','物料描述','數(shù)量','單位','過賬日期','金額','采購訂單'] ubound=ws.max_rowwhile True: k=2 wb1=Workbook() ws1=wb1.active ws1.append(navigation) #所需表頭為固定內(nèi)容，用append方法通過列表寫入第一行 if ubound>1: for i in range(ubound,1,-1): #循環(huán)范圍為變量，范圍逐漸減小，每次循環(huán)次數(shù)遞減，同時用break語句完成循環(huán)的中斷，步長為-1，表明數(shù)據(jù)從表格尾行至下而上讀取 col=1 for j in range(1,21): #遍歷所有列 if ws.cell(1,j).value in navigation and ws.cell(i,18).value !=None and ws.cell(i,18).value[0:7] =="原材料-備配件": #歸納為同一個excel的條件：上下兩行的供應(yīng)商、公司相同 ws1.cell(k,col).value=ws.cell(i,j).value #新表第K行取自源表第i行數(shù)據(jù) if ws.cell(i,12).value=="S": #金額為正時顏色做特殊標記 ws1.cell(k,col).fill=GradientFill(stop=['FFFF00', 'F5DEB3']) #漸變黃色底紋背景 col=col+1 ws1.cell(k,8).number_format="yyyy-mm-dd" #設(shè)置單元格為日期格式 k=k+1 #新表從第一行順序往下寫，源表從最后一行往上讀 if ws.cell(i-1,1).value !=ws.cell(i,1).value or ws.cell(i-1,3).value != ws.cell(i,3).value: #判斷條件：公司不相同或供應(yīng)商不相同 break ubound=i-1 companyCode=ws1.cell(2,1).value #存儲每張子表對應(yīng)的公司代碼 vendorCode=ws1.cell(2,2).value #存儲每張子表對應(yīng)的供應(yīng)商編號 if companyCode !=None: for col in ["A","B","C","D","E","F","G","H","J"]: #ws1.column_dimensions[col] .border=Border(top=thin, left=thin, right=thin, bottom=thin) ws1.column_dimensions[col].width=25 #設(shè)置固定列寬 for col in range(1,11): ws1.cell(1,col).font=Font(name='Microsoft YaHei',size=13,bold=True,color=colors.RED) #設(shè)置標題行的字體樣式 ws1.cell(1,col).border=Border(top=thin, left=thin, right=thin, bottom=thin) #設(shè)置標題行的邊框樣式 wb1.save(pwd+"\\暫估分類表\\%s_%s.xlsx"%(companyCode,vendorCode)) #拆分后的表格按公司代碼、供應(yīng)商編號排序 else: breakeg.msgbox(msg='(暫估表拆分完成)', title='Information', ok_button='確定', image=None, root=None) |
|
|
