|
Python办公⾃动化是利⽤Python编程语⾔来创建脚本和程序，以简化、加速和⾃动化⽇常办公任务和⼯作流程的过程。它基于Python的强⼤功能和丰富的第三⽅库，使得能够处理各种办公任务，如⽂档处理、数据分析、电⼦邮件管理、⽹络通信等等。 下面就用一个真实的案例感受python自动化的高效，一分钟搞定原来一个部门五六个人一周的表格汇总工作。    
行政人事收上来的文件是这样的：   为了解决这个问题，我尝试利用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环境
这个软件的操作非常简单。只需要将本季度的所有评分表格导入到软件中，它就能自动识别表格中的数据，并按照员工姓名进行汇总形成汇总表。软件会自动计算每个员工的总分，并计算出平均值。一分钟的时间，软件就能生成一个包含每个员工平均分的汇总表。
|
|
|
