Python辦公⾃動化是利⽤Python編程語⾔來創建腳本和程序，以簡化、加速和⾃動化⽇常辦公任務和⼯作流程的過程。它基于Python的強⼤功能和豐富的第三⽅庫，使得能夠處理各種辦公任務，如⽂檔處理、數據分析、電⼦郵件管理、⽹絡通信等等。

下面就用一個真實的案例感受python自動化的高效，一分鍾搞定原來一個部門五六個人一周的表格彙總工作。

在國企的季度績效考核中，領導對員工進行評分是一項重要而繁瑣的任務。由于公司規模較大，員工衆多，每個領導都需要對自己所管轄的員工進行評分，同時員工之間還需要進行互評，以體現公平民主，而這些評分數據通常以Excel表格的形式提交。人事就肩負著將這些評分數據進行彙總並計算平均值的重任。

員工互評表

領導評分表

傳統的彙總方式需要人工手動打開每個Excel文件，提取出每位領導和員工對每個員工的評分數據，然後再按照員工姓名進行彙總，並計算平均值。這個過程不僅耗時耗力，而且容易出錯。一旦數據出現偏差，就需要重新核對，進一步增加了工作量。

彙總結果是這樣的，每個人每個月的考核分數及結果表

行政人事收上來的文件是這樣的：

每個文件夾就是每個部門領導或者員工給所有人打分

爲了解決這個問題，我嘗試利用Python的自動化處理能力來優化整個個工作流程。經過一段時間的努力，我成功開發出了一個自動化彙總軟件。

核心代碼

import jsonimport loggingimport osimport reimport openpyxlimport pandas as pdLOG_FORMAT = "%(asctime)s - %(levelname)s - %(message)s"logging.basicConfig(filename='./datacount.log', level=logging.WARN, format=LOG_FORMAT)dx_dir = "定性打分表"dl_dir = "定量打分表"def all_files(path, file_type): # 生成path路徑下全部file_type類型文件絕對路徑列表 f_list = [] def files_list(father_path): sub_path = os.listdir(father_path) # 讀取父路徑下全部文件或文件夾名稱 for sp in sub_path: full_sub_path = os.path.join(father_path, sp) # 生成完整子路徑 if os.path.isfile(full_sub_path): # 判斷是否爲文件 file_name, post_name = os.path.splitext( full_sub_path) # 獲取文件後綴名 if file_type in post_name: f_list.append(file_name + post_name) else: # 如果是文件夾，遞歸調用 files_list(full_sub_path) files_list(path) return f_listdef read_all_dl_exl(fs_list,mons): all_data_list = [] for excel_file in fs_list: logging.warning(excel_file) excel_name = os.path.split(excel_file)[1] logging.warning(excel_name) # rated_name = excel_file.split("\\")[-1].split(".")[0] # rated_name_mon = re.split(r"[- _ --]",excel_name)[-2] rater_name = excel_file.split("\\")[-2] logging.warning(rater_name) rated_name = re.split(r"[- _ --]",excel_name)[1] logging.warning(rated_name) if str(mons[0])+"月" in excel_name: mon = mons[0] elif str(mons[1])+"月" in excel_name: mon = mons[1] elif str(mons[2])+"月" in excel_name: mon = mons[2] else: mon = -99 rate_df = pd.DataFrame() rate_df["rated_name"] = [rated_name] rate_df["rater_name"] = [rater_name] rate_df["month"] = [mon] if ".xlsx" in excel_file: data_s = pd.read_excel(excel_file,usecols="I",header=None,engine="openpyxl") if len(data_s)<=0: data_s = pd.read_excel(excel_file,usecols="I",header=None,engine="openpyxl",sheet_name=1) if len(data_s)<=0: data_s = pd.read_excel(excel_file,usecols="I",header=None,engine="openpyxl",sheet_name=2) else: data_s = pd.read_excel(excel_file,usecols="I",header=None) if len(data_s)<=0: data_s = pd.read_excel(excel_file,usecols="I",header=None,sheet_name=1) if len(data_s)<=0: data_s = pd.read_excel(excel_file,usecols="I",header=None,sheet_name=2) rate_df["score"] = [data_s[data_s.iloc[:,0].notna()].iloc[:,0].iloc[-1]] all_data_list.append(rate_df) return all_data_listdef read_all_dx_exl(fs_list, mons): all_data_list = [] for excel_file in fs_list: logging.warning(excel_file) # rated_name = excel_file.split("\\")[-1].split(".")[0] rated_name_mon = excel_file.split("\\")[-1] rater_name = excel_file.split("\\")[-2] logging.warning(rater_name) if "-" in rated_name_mon: rated_name = rated_name_mon.split("-")[0] else: rated_name = re.split("\d", rated_name_mon)[0] logging.warning(rated_name) if str(mons[0]) in rated_name_mon: mon = mons[0] elif str(mons[1]) in rated_name_mon: mon = mons[1] elif str(mons[2]) in rated_name_mon: mon = mons[2] else: mon = -99 rate_df = pd.DataFrame() rate_df["rated_name"] = [rated_name] rate_df["rater_name"] = [rater_name] rate_df["month"] = [mon] if ".xlsx" in excel_file: data_s = pd.read_excel( excel_file, usecols="G", header=None, engine="openpyxl") if len(data_s) <= 0: data_s = pd.read_excel( excel_file, header=None, engine="openpyxl") data_s = data_s[data_s.columns[[-1]]] else: data_s = pd.read_excel(excel_file, usecols="G", header=None) if len(data_s) <= 0: data_s = pd.read_excel(excel_file, header=None) data_s = data_s[data_s.columns[[-1]]] rate_df["score"] = [ data_s[data_s.iloc[:, 0].notna()].iloc[:, 0].iloc[-1]] all_data_list.append(rate_df) return all_data_listdef save_dx_result(rated_group, mons): for rated_per, df in rated_group: wb1 = openpyxl.load_workbook("./模板表.xlsx") ws1 = wb1["Sheet1"] logging.warning(rated_per) logging.warning(df) ws1.cell(row=2, column=3).value = rated_per ws1.cell(row=11, column=3).value = rated_per ws1.cell(row=20, column=3).value = rated_per df_7 = df[df["month"] == mons[0]].reset_index(drop=True) if len(df_7) > 0: for i, row in df_7.iterrows(): ws1.cell(row=3, column=3+i).value = row["rater_name"] ws1.cell(row=4, column=3+i).value = row["score"] ws1.cell(row=5, column=3).value = df_7.score.mean() df_8 = df[df["month"] == mons[1]].reset_index(drop=True) if len(df_8) > 0: for i, row in df_8.iterrows(): ws1.cell(row=12, column=3+i).value = row["rater_name"] ws1.cell(row=13, column=3+i).value = row["score"] ws1.cell(row=14, column=3).value = df_8.score.mean() df_9 = df[df["month"] == mons[2]].reset_index(drop=True) if len(df_9) > 0: for i, row in df_9.iterrows(): ws1.cell(row=21, column=3+i).value = row["rater_name"] ws1.cell(row=22, column=3+i).value = row["score"] ws1.cell(row=23, column=3).value = df_9.score.mean() wb1.save("./result/"+rated_per+".xlsx")def save_dl_result(rated_group, mons): for rated_per,df in rated_group: try: wb1 = openpyxl.load_workbook("./result/"+rated_per+".xlsx") ws1 = wb1["Sheet1"] except Exception as e: logging.warning(rated_per) logging.warning(e) continue logging.warning(rated_per) logging.warning(df) df_7 = df[df["month"]==mons[0]].reset_index(drop=True) if len(df_7)>0: for i,row in df_7.iterrows(): ws1.cell(row=6,column=3).value = row["rater_name"] ws1.cell(row=7,column=3).value = row["score"] df_8 = df[df["month"]==mons[1]].reset_index(drop=True) if len(df_8)>0: for i,row in df_8.iterrows(): ws1.cell(row=15,column=3).value = row["rater_name"] ws1.cell(row=16,column=3).value = row["score"] df_9 = df[df["month"]==mons[2]].reset_index(drop=True) if len(df_9)>0: for i,row in df_9.iterrows(): ws1.cell(row=24,column=3).value = row["rater_name"] ws1.cell(row=25,column=3).value = row["score"] wb1.save("./result/"+rated_per+".xlsx")def clean_data(data): data.rated_name = data.rated_name.str.replace(r"《", "") data.rated_name = data.rated_name.str.replace(r"\（.*?\）", "") data.rated_name = data.rated_name.str.replace(r" ", "") data.rated_name = data.rated_name.str.replace(r" ", "") data.rated_name = data.rated_name.str.replace("\s*", "") data.rated_name = data.rated_name.str.replace(r" ", "") data.rated_name = data.rated_name.str.replace(r" ", "") data.rated_name = data.rated_name.str.replace(r"《定性指標考核表（員工層）》", "") data.rated_name = data.rated_name.str.replace(r"1月", "") data.rated_name = data.rated_name.str.replace(r"2月", "") data.rated_name = data.rated_name.str.replace(r"3月", "") data.rated_name = data.rated_name.str.replace(r"《定性指標考核表》", "") data.rated_name = data.rated_name.str.replace(r"《", "") return dataif __name__ == "__main__": save_dir = "./result/" if not os.path.exists(save_dir): os.makedirs(save_dir) with open("config.json") as json_file: config = json.load(json_file) mons = config["mons"] fs_list = all_files(dx_dir, "xls") all_dx_list = read_all_dx_exl(fs_list, mons) data_dx = pd.concat(all_dx_list, ignore_index=True) wrong_score_dx = data_dx[data_dx.score == 0] wrong_mon_dx = data_dx[data_dx["month"] == -99] wrong_score_dx.to_csv("wrong_data_dx.csv", mode="a", index=False) wrong_mon_dx.to_csv("wrong_data_dx.csv", mode="a", index=False) data_dx = data_dx[data_dx.score > 0] data_dx = data_dx[data_dx["month"] != -99] data_dx["score"] = pd.to_numeric(data_dx.score, errors='coerce') data_dx = data_dx[data_dx.score.notnull()] data = clean_data(data_dx) rated_group = data.groupby("rated_name") save_dx_result(rated_group, mons) dl_fs_list = all_files(dl_dir,"xls") all_dl_list = read_all_dl_exl(dl_fs_list, mons) data_dl = pd.concat(all_dl_list,ignore_index=True) wrong_score_dl = data_dl[data_dl.score < 0] wrong_mon_dl = data_dl[data_dl["month"] == -99] wrong_score_dl.to_csv("wrong_data_dl.csv", mode="a", index=False) wrong_mon_dl.to_csv("wrong_data_dl.csv", mode="a", index=False) data_dl = data_dl[data_dl["month"] != -99] data2 = clean_data(data_dl) dl_rated_group = data2.groupby("rated_name") save_dl_result(dl_rated_group, mons)

爲了進一步讓國企的朋友都能使用這段程序，我用pyinstaller對程序打包成了EXE軟件，朋友就像使用微信QQ一樣使用這段代碼，不在需要搭建python環境

這個軟件的操作非常簡單。只需要將本季度的所有評分表格導入到軟件中，它就能自動識別表格中的數據，並按照員工姓名進行彙總形成彙總表。軟件會自動計算每個員工的總分，並計算出平均值。一分鍾的時間，軟件就能生成一個包含每個員工平均分的彙總表。

自從使用了這個自動化彙總軟件後，朋友的工作效率得到了極大的提升。原本需要整個部門一周時間才能完成的工作，現在只需要幾分鍾就能輕松搞定。這不僅大大減輕了我們的工作負擔，還提高了數據的准確性和可靠性。