Python 自動化辦公，處理Excel表格影片講解

https://www.bilibili.com/video/BV1Lh411R7Kd/

前言

這篇文章要做的事情，如標題所述，就是提取多張excel表上的資料或資訊，合併彙總到一張新表上，這是我們工作中經常會遇到的事情。

比如將每月銷售情況彙總到一張表上進行銷售情況分析，比如將各月發票資訊彙總到一張表上進行統計分析，還比如將每月工資表上的某些資訊彙總到一張表上進行工資成本分析等等。

具體看錶即為：

各期科目餘額表截圖

最後得到的新表為：

要實現上述目標，可以分如下四步進行。

1.獲取各科目餘額表文件路徑

將2017年1-12月、2018年1-12月、2019年1-12月及2020年1-6月各期科目餘額表放在同一資料夾下，要讀取多少個檔案，就把多少個檔案全部放在同一個資料夾下，如下圖。

然後讀取所有檔案的路徑，程式碼如下。

1dir_xls = []2def get_file(folder_path):  #獲取同一資料夾下所有科目餘額表各自的檔案路徑3    dir_file = os.listdir(folder_path)4    #print(dir_file)  5    for path in dir_file:6        if path[-4:] == 'xlsx' or path[-3:] == 'xls':7            whole_path = r'd:/F：學習/python/賬齡分析/科目餘額表/{}'.format(path)8            dir_xls.append(whole_path)9    return dir_xls

2.獲取各科目餘額表中應收賬款一級科目編碼所在的行列

比如在2017年科目餘額表中，應收賬款一級科目編碼為“1122”，其所在的單元格為C12，也即為第12行第3列，這裡的行號12、列號3，即為其定位。

其餘科目餘額表同理，均為獲取一級科目編碼“1122”的行號和列號，獲取程式碼如下。

1dict_row_col = {} 2def get_row_col(dir_xls):  #獲取每一張表中應收賬款一級科目編碼所在的行號和列號 3    for i in dir_xls: 4        #print(i) 5        account_balance_sheet_data = pd.DataFrame(pd.read_excel(i)) 6        for a in account_balance_sheet_data.index: 7            for b in range(len(account_balance_sheet_data.loc[a].values)): 8                if account_balance_sheet_data.loc[a].values[b] == '1122': 9                    row = a+110                    col = b+111                    dict_row_col[i] = [row,col]12    return dict_row_col

3.獲取各科目餘額表中應收賬款所有二級科目編碼

根據獲取到的應收賬款一級科目編碼行號和列號，即根據其定位，再獲取每一張表中應收賬款所有二級科目編碼，並將其不重複且升序排列新增到一張新表中，程式碼如下。

1def get_ar_code(dict_row_col): 2    i = 0 3    ar_list1 = [] 4    ar_list2 = [] 5    for key in dict_row_col.keys(): 6        workbook = xlrd.open_workbook(key) 7        balance_sheet = workbook.sheet_by_index(0) 8        row = dict_row_col[key][0] 9        col = dict_row_col[key][1]10        while True:11            if '1122' in balance_sheet.cell_value(row+1,col-1):12                ar_code = balance_sheet.cell_value(row+1,col-1)13                if ar_code not in ar_list1:14                    ar_list1.append(ar_code)15                else:16                    pass                17                row = row+1                18            else:19                break20    ar_list1.append('科目編碼')    21    ar_list1.sort(reverse=False) #科目編碼列表升序排列22    #將“科目編碼”從最後一個元素整體移動到第一個元素23    ar_list2.append(ar_list1[len(ar_list1)-1]) 24    for i in range(1,len(ar_list1)):25        ar_list2.append(ar_list1[i-1])26    #將所有元素寫入到excel表中27    for i in range(len(ar_list2)):28        ar_sheet.write(i,0,ar_list2[i])

得到的新表內容如下。

由上可看出，2017年至2020年1-6月，四張科目餘額表，應收賬款共有617個二級科目，對應著617個不同的客戶。

4.根據二級科目索引獲取全部所需資訊

此步的操作過程，即上一篇《如何用python實現excel中的vlookup功能？》所分享的過程，這裡就不再詳述了，程式碼如下。

1def get_ar_info(dict_row_col): 2    #讀取匯入目標表 3    file_target = r'd:\F：學習\python\賬齡分析\AR.xls' 4    list_ar_code = [] 5    workbook = xlrd.open_workbook(file_target) 6    balance_sheet = workbook.sheet_by_index(0) 7    rows = balance_sheet.nrows 8    for i in range(1,rows): 9        list_ar_code.append(balance_sheet.cell_value(i,0))10    #print(list_ar_code)11    data = {'科目編碼':list_ar_code}12    df_target = pd.DataFrame(data)1314    for key in dict_row_col.keys():15        #讀取原始資料來源表16        file_source = key17        df_source = pd.read_excel(file_source)18        #將原始資料來源表及匯入目標表資訊合併到同一表上19        dfneed = df_source[['科目編碼','科目名稱','期初借方','期初貸方','本期發生借方','本期發生貸方','期末借方','期末貸方']]20        df_target = pd.merge(df_target,dfneed,how='left',on='科目編碼')21    df_target.to_excel(file_target,index=False)

5.最終目標實現

前四步即為封裝的四個函式，每個函式為其中一個步驟，最終彙總可以實現此文總體目標，呼叫程式碼及執行程式碼如下。

1import os 2import pandas as pd 3import xlrd,xlwt 4 5folder_path = r'd:\F：學習\python\賬齡分析\科目餘額表' 6f = xlwt.Workbook() 7ar_sheet = f.add_sheet(u'ar_sheet',cell_overwrite_ok=True) 8dir_xls = get_file(folder_path) 9dict_row_col = get_row_col(dir_xls)10get_ar_code(dict_row_col)11f.save(r'd:\F：學習\python\賬齡分析\AR.xls')12get_ar_info(dict_row_col)

執行後生成的表格如下。

再經過簡單整理後，便可得出上文最終表格，至此實現了從多張excel表中提取所需資料或資訊並彙總到同一張新表上的目的。