Python openpyxl、pandas操作Excel方法簡介與具體執行個體

　　本篇重點講解windows系統下 Python3.5中第三方excel操作庫-openpyxl；

其實Python第三方庫有很多可以操作Excel，如：xlrd,xlwt，xlwings甚至注明的資料分析子產品Pandas也提供pandas.read_excel、pandas.DataFrame.to_excel功能。

那麼openpyxl的庫有哪些優缺點呢：

優勢：

1、openpyxl提供對pandas的dataframe對象完美支援；

2、openpyxl支援背景靜默打開excel檔案；

3、它支援excel的一些sort、filter篩選、排序功能，支援豐富的單元格style（樣式）設計；

4、它同時支援讀取現成的excel檔案&建立全新的excel檔案；

5、它支援最新的xlsx格式檔案，且更新頻繁，操作較為簡練。

缺點：

1、運算效率相對不高，當表格行項目過多時，運算相對pandas等速度較慢；

2、部分針對行或列的樣式設計有一定bug，文法可能失效；

3、對sort和filter的功能雖然支援，但是需要使用者手工打開excel後重新整理資料方能使搜尋條件生效，屬于半自動；

4、不支援excel原生的自動列寬功能，實作同樣效果略複雜。

簡單的屬性和方法如下：

建立工作簿對象：

>>> from openpyxl import Workbook

>>> wb = Workbook()

定位目前活躍工作表：

>>> ws = wb.active

建立新工作表：

ws1 = wb.create_sheet("Sheet2")

工作表改名：

ws.title = "New Sheet"

如果要選擇某張非活躍工作表，可以指定工作表名稱，如：

ws=wb["New Sheet"]

複制工作表為副本：

>>> ws1 = wb.active

>>> ws2 = wb.copy_worksheet(ws1)

通路單元格有兩種寫法，如通路單元格A3，可以寫作：

>>> ws['A3'] = ”hello"

或：

>>> ws.cell(row=3,column=1).value = ”hello"

也可簡化為：

>>> ws.cell(3，1).value = ”hello"

通路多個單元格區域Range：

>>> cell_range = ws['A1':'B5']

同理，通路某一行（如第三行）可以寫作：

>>> row3 = ws[3]

通路某一列（如C列）可以寫作：

>>> colC = ws['C']

如果要通路多行多列，則用“：”分隔，如：

>>> col_range = ws['C:D']

>>> row_range = ws[5:10]

對工作表操作完成後的儲存操作：

>>> wb.save('test.xlsx')  #可以指定fullname，如果隻包含名字本身，則檔案會儲存在py腳本所在的同級目錄下。

PS：如果要儲存的檔案名已存在，則此操作将覆寫現有的檔案沒有警告。

導入

openpyxl.load_workbook()

來 打開一個已存在的工作簿:

>>> from openpyxl import load_workbook

>>> wb = load_workbook('test.xlsx')

通過append方法逐行寫入excel，如從頭寫入10行資料可以這些周遊：

>>> for row in range(1, 11):

...     ws1.append(range(10))

ps：workbook對象的-data_only 屬性 控制細胞是否有公式的 公式(預設)或Excel讀最後一次存儲的值表。

對單元格寫入公式，方法如下：

>>> ws["C1"] = "=average(A1, B1)"

常見的單元格合并以及取消合并，方法如下：

>>> ws.merge_cells('A1:B5')

>>> ws.unmerge_cells('A1:B5')

主要示例：1、利用openpyxl子產品基于多字段拆分工作表為多張工作簿，

2、同時滿足樣式要求，隻導入部分标紅色字段資料，同時對特定行項目進行顔色标注，

3、利用pandas子產品對excel檔案進行排序、篩選，再寫回excel。原始資料表頭header如下：

主要代碼如下：

"""
主要功能：
1、将需求的字段生成對應表格；2、将金額為正的（S）的行項目标記為黃色；
3、隻導入特定物料；4、對資料源取名進行限制；
5、增加程式運作完畢的消息提醒；6、增加padas的排序、篩選功能
"""
from openpyxl import load_workbook
from openpyxl import Workbook
from openpyxl.styles import PatternFill,Border,Side,Alignment,Protection,Font,GradientFill,Color,Colors
import easygui as eg,pandas as pd
import os,time

pwd = os.getcwd()
writer = pd.ExcelWriter('暫估表_按公司&供應商排序.xlsx')
df1 = pd.DataFrame(pd.read_excel(os.path.join(pwd,'暫估表.xlsx'),dtype={'供應商':str,'物料':str,'采購訂單':str}))    #将excel讀入pandas的DataFrame對象，同時約定“供應商、物料、采購訂單”等字段為字元串類型，避免被pandas轉化為數字
df1=df1.sort_values(by=["公司","供應商"],ascending=True)   #基于公司、供應商兩個字段做升序排列，字段排序有主次之分
df1.to_excel(writer,'Sheet1',index=False)    #index=False，表明導入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"\\暫估表_按公司&供應商排序.xlsx")
ws=wb["Sheet1"]
navigation=['公司','供應商','名稱描述','物料','物料描述','數量','機關','過賬日期','金額','采購訂單']
ubound=ws.max_row

while True:
    
    k=2
    wb1=Workbook()
    ws1=wb1.active
    ws1.append(navigation) #所需表頭為固定内容，用append方法通過清單寫入第一行
    if ubound>1:
        for i in range(ubound,1,-1): #循環範圍為變量，範圍逐漸減小，每次循環次數遞減，同時用break語句完成循環的中斷，步長為-1，表明資料從表格尾行至下而上讀取
            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的條件：上下兩行的供應商、公司相同
                    ws1.cell(k,col).value=ws.cell(i,j).value  #新表第K行取自源表第i行資料
                    
                    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"  #設定單元格為日期格式
            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: #判斷條件：公司不相同或供應商不相同
                break

        ubound=i-1
        companyCode=ws1.cell(2,1).value   #存儲每張子表對應的公司代碼
        vendorCode=ws1.cell(2,2).value    #存儲每張子表對應的供應商編号
        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   #設定固定列寬
            for col in range(1,11):
                ws1.cell(1,col).font=Font(name='Microsoft YaHei',size=13,bold=True,color=colors.RED)    #設定标題行的字型樣式
                ws1.cell(1,col).border=Border(top=thin, left=thin, right=thin, bottom=thin)   #設定标題行的邊框樣式

            wb1.save(pwd+"\\暫估分類表\\%s_%s.xlsx"%(companyCode,vendorCode))  #拆分後的表格按公司代碼、供應商編号排序

    else:
        break

eg.msgbox(msg='(暫估表拆分完成)', title='Information', ok_button='确定', image=None, root=None)      

最終生成表格樣式如下：

 快來掃碼關注我的公衆号 擷取更多爬蟲、資料分析的知識！