Python辦公自動化實踐1：從多個excel表中提取數(shù)據(jù)并匯總到一個工作表頁中,表格,抽取,sheet

發(fā)表時間：2020-04-26

問題：從當前目錄或子目錄中查詢符合條件的excel表格，并從這些excel表格中抽取符合條件的行匯總到1個excel的sheet頁中。
所有excel表格名字為：交付清單1、交付清單2，交付清單3……，格式也一樣，樣式如下：
 將類似多個excle表中，抽取序號中的N行（上圖是1~4行）匯總到指定excel的sheet頁中。
該表格在DataFrame中的顯示方式如下：

一、希望得到的匯總表格為：
1、列頭是：“系統(tǒng)名稱、用途 、CPU 內(nèi)存、操作系統(tǒng)、內(nèi)網(wǎng)IP、 互聯(lián)網(wǎng)IP 、交付時間 、到期時間”
注意：列頭（columns header)是合并格。
2、刪除掉A列，刪除最后的空行
3、將交付時間，到期時間從datetime類型轉(zhuǎn)變?yōu)樽址愋停?#第三方模塊datetime
4、將匯總表格中所有的單元格 字體，大小，字體是否據(jù)中等 全部調(diào)為一致；#第三方模塊xlsxwriter

二、具體實現(xiàn)步驟如下：
1、查詢符合條件的excel表； #第三方模塊os
2、用pandas讀取符合條件的表；
3、對讀入后的excel表（dataframe）進行裁剪；
4、將多個excel表格匯總到單一excel的sheet頁中；
5、將最終的sheet頁再進行單元格格式調(diào)整，再重新輸出新的sheet頁；
該腳本涉及第三方模塊有：pandas,os,datetime,xlsxwriter。

三、代碼展現(xiàn)
1、查詢符合條件的excel表
a、通過os.walk查詢當前目錄及子目錄中的文件
b、在這些文件中篩選匹配“交付清單”的文件
c、將匹配文件和絕對路徑“連接后”存放到列表中；

該段重點：
a、path_name 存放文件所在的絕對路徑，filename 存放文件名；
b、files.find(substr) 匹配substr字符串的文件，若不匹配返回-1，
若匹配，返回該字符串在文件名中第一次匹配成功的位置。
c、os.path.join(path_name,files)將絕對路徑與匹配的文件結合起來,再存放到file_list列表中。

file_list列表中的數(shù)據(jù)：

['D:\\cloud_files\\~$交付清單01.xlsx', 'D:\\cloud_files\\交付清單01.xlsx', 'D:\\cloud_files\\交付清單02.xlsx', 'D:\\cloud_files\\交付清單03.xlsx']

在交付清單01.xlsx 是打開的情況下，提交該段程序，將會出現(xiàn)~$交付清單01.xlsx。
表示該表被進程占用。

2、用pandas讀取符合條件的表
a、通過pandas讀取在file_list中的excel表格；
b、并將結果寫入到vm_tmplist 臨時列表中。

該段重點：
a、跳過excel中的前兩行，并定義B到J列區(qū)域；

3、對讀入后的excel表（dataframe）進行裁剪
a、選擇合適的列頭；
b、刪除掉A列，刪除最后的空行
c、將交付時間，到期時間從datetime類型轉(zhuǎn)變?yōu)樽址愋停?br> 轉(zhuǎn)換的原因是xlsxwriter不支持對時間類型，index，columns類型的單元格進行格式化。
官網(wǎng)原文：It isn’t possible to format any cells that already have a format
such as the index or headers or any cells that contain dates or datetimes .

def trim_frame(df):
    df.columns=np.concatenate([df.columns[:3],df.iloc[0,3:5],df.columns[5:]])
    df=df.dropna(subset=['系統(tǒng)名稱'])
    df=df.loc[(df['序號'].isin(range(1,10,1)))]
    df['交付時間']=pd.to_datetime(df['交付時間'],errors='coerce').dt.strftime('%Y-%m-%d')
    df['到期時間']=pd.to_datetime(df['到期時間'],errors='coerce').dt.strftime('%Y-%m-%d')
    return df
for value in vm_tmplist:
    df=trim_frame(value)
    vm_trimlist.append(df)

本段重點：
a、將前三列（0，1，2）的columns（列名）與第0行的第3、4列及columns第5列至最后一列合并；
通過numpy.concatenate函數(shù)合并
b、將“系統(tǒng)名稱”列中空格（NaN）所在的行去掉；
c、選取“序號”列中數(shù)字為1~10所在的行；
d、將“交付時間”，“到期時間”兩列datetime類型數(shù)據(jù)轉(zhuǎn)換為string。
e、將“修剪好的”表存入vm_trimlist列表中；

4、將多個excel表格匯總到單一excel的sheet頁中
a、調(diào)用xlsxwriter引擎；
b、從vm_list列表中選取“修剪好”的數(shù)據(jù)，按順序?qū)懭氲健疤摍C清單”sheet頁中；
c、保存寫好后的數(shù)據(jù).writer.save()

本段重點：
a、從vm_trimlist中讀取數(shù)據(jù)（注：df_list是形參）
b、定義row=0，開始寫的行startrow=row。
第2張表寫入時，啟始位置是第1張表的長度len(dataframe.index) 加1（第一行是從0開始的，所以加1，避免第2張表第一行沖掉第一張表最后一行）
c、to_excel（index=False）是防止將索引（index）寫入excel表內(nèi)。

5、將最終的sheet頁再進行單元格格式調(diào)整，重新輸出新的sheet頁；
a、重新讀取輸出的表；
b、讀取后，將該表刪掉；因為xlsxwriter不能對原表進行修改；
c、對單元格及列頭（columns header）進行格式化；
d、重新輸出新表

def formatExcel(df,path):
    df=df.loc[df['序號'].isin(range(1,10,1))]
    os.remove(path)
    df.reset_index(drop=True,inplace=True)
    for i in df.index:
        df['序號'].at[i]=i+1
    writer = pd.ExcelWriter(path,engine='xlsxwriter')
    df.to_excel(writer,sheet_name='虛機清單',index=False)
    workbook=writer.book
    worksheet=writer.sheets['虛機清單']
    fmt_cell={'bold':False,'font_name':'微軟雅黑','font_size':9,'align':'center','valign':'vcenter','border':0,'num_format':'#,##0'}
    fmt_header={'bold':True,'font_name':'微軟雅黑','font_size':10,'align':'center','valign':'vcenter','border':0}
    cell_format=workbook.add_format(fmt_cell)
    header_format=workbook.add_format(fmt_header)
    worksheet.set_column('A:I',15,cell_format)
    for colx,value in enumerate(df.columns.values):
        worksheet.write(0,colx,value,header_format)
    writer.save()
formatExcel(pd.read_excel(path_out),path_out)
print ('Done!')

該段重點：
a、對序號列重新賦值新的數(shù)字，df['序號’].at[i]=i+1；
b、定義單元格（cell）格式；
c、定義列頭的格式，通過worksheet.write的方式；

生成的新表：