excel内容合并脚本

README
脚本中环境变量改成自己实际得环境变量

path=os.chdir(r’D:\工作\pythonProject\合并excel\合并’) #需要合并得excel
savefile = “D:/工作/pythonProject/合并excel/test.csv” #合并后保存得位置文件名自定义
sheet_name = ‘20220527’ #要合并得sheet

合并前后对比图：
在这里插入图片描述

import pandas as pd
import os
import csvpath=os.chdir(r'D:\工作\pythonProject\合并excel\合并')
savefile = "D:/工作/pythonProject/合并excel/test.csv"
sheet_name = '20220527'result = []
yingyongval = []
chenhuival = []
rizhival = []
biangengval = []
shijianguanlival = []
shengchanwentival = []
rongliangval = []
zhaojihuiyival = []
gongzuo1val = []
gongzuo2val = []
gongzuo3val = []
zonghegongzuo1val = []
zonghegongzuo2val = []
zonghegongzuo3val = []
zonghegongzuo4val = []
zonghegongzuo5val = []
qita1val = []
qita2val = []
qita3val = []
zhiduval = []
yichengqingkuangval = []yingyongkey = "应用系统巡检、服务请求处理、重点系统运行保障等情况统计分析:"
chenhui = "晨会待办事项的推进处理："
rizhi = "日志中心查询使用情况："
biangeng = "变更管理："
shijianguanli = "事件管理和整改工作推进："
shengchanwenti = "生产问题分析和优化需求："
rongliang = "容量管理："
zhaojihuiyi = "召集或参加会议："
gongzuo1 = "重点工作1"
gongzuo2 = "重点工作2"
gongzuo3 = "重点工作3"
zonghegongzuo1 = "综合类或行政类工作1"
zonghegongzuo2 = "综合类或行政类工作2"
zonghegongzuo3 = "综合类或行政类工作3"
zonghegongzuo4 = "综合类或行政类工作4"
zonghegongzuo5 = "综合类或行政类工作5"
qita1 = "其他1"
qita2 = "其他2"
qita3 = "其他3"
zhidu = "制度和企业文化学习："
yichengqingkuang = "个人异常情况说明："def get_data():allxls=os.listdir(path) #获取当前路径下的文件for fl in allxls :data = pd.read_excel(fl,sheet_name=sheet_name,header=1,index_col=0)data_1 = data.iloc[:, 0]data_2 = data_1.to_dict()#print(data_2)for key in data_2.keys():aaa = data_2[key].split()result.append(aaa)return resultdef get_data_list():results = get_data()for x in range(0, len(results)):if results[x][0] == yingyongkey:for y in range(0, len(results[x])):if y != 0:yingyongval.append(results[x][y][2:])elif results[x][0] == chenhui:for y in range(0, len(results[x])):if y != 0:chenhuival.append(results[x][y][2:])elif results[x][0] == rizhi:for y in range(0, len(results[x])):if y != 0:rizhival.append(result[x][y][2:])elif results[x][0] == biangeng:for y in range(0, len(results[x])):if y != 0:biangengval.append(result[x][y][2:])elif results[x][0] == shijianguanli:for y in range(0, len(results[x])):if y != 0:shijianguanlival.append(result[x][y][2:])elif results[x][0] == shengchanwenti:for y in range(0, len(results[x])):if y != 0:shengchanwentival.append(result[x][y][2:])elif results[x][0] == rongliang:for y in range(0, len(results[x])):if y != 0:rongliangval.append(result[x][y][2:])elif results[x][0] == zhaojihuiyi:for y in range(0, len(results[x])):if y != 0:zhaojihuiyival.append(results[x][y][2:])elif results[x][0] == gongzuo1:for y in range(0, len(results[x])):if y != 0:gongzuo1val.append(result[x][y][2:])elif results[x][0] == gongzuo2:for y in range(0, len(results[x])):if y != 0:gongzuo2val.append(result[x][y][2:])elif results[x][0] == gongzuo3:for y in range(0, len(results[x])):if y != 0:gongzuo13val.append(result[x][y][2:])elif results[x][0] == zonghegongzuo1:for y in range(0, len(results[x])):if y != 0:zonghegongzuo1val.append(result[x][y][2:])elif results[x][0] == zonghegongzuo2:for y in range(0, len(results[x])):if y != 0:zonghegongzuo2val.append(result[x][y][2:])elif results[x][0] == zonghegongzuo3:for y in range(0, len(results[x])):if y != 0:zonghegongzuo3val.append(result[x][y][2:])elif results[x][0] == zonghegongzuo4:for y in range(0, len(results[x])):if y != 0:zonghegongzuo4val.append(result[x][y][2:])elif results[x][0] == zonghegongzuo5:for y in range(0, len(results[x])):if y != 0:zonghegongzuo5val.append(result[x][y][2:])elif results[x][0] == qita1:for y in range(0, len(results[x])):if y != 0:qita1val.append(result[x][y][2:])elif results[x][0] == qita2:for y in range(0, len(results[x])):if y != 0:qita2val.append(result[x][y][2:])elif results[x][0] == qita3:for y in range(0, len(results[x])):if y != 0:qita3val.append(result[x][y][2:])elif results[x][0] == zhidu:for y in range(0, len(results[x])):if y != 0:zhiduval.append(result[x][y][2:])elif results[x][0] == yichengqingkuang:for y in range(0, len(results[x])):if y != 0:yichengqingkuangval.append(result[x][y][2:])else:print("结束")return yingyongval,chenhuival,rizhival,biangengval,shijianguanlival,shengchanwentival,rongliangval,zhaojihuiyival,gongzuo1val,gongzuo2val,gongzuo3val,zonghegongzuo1val,zonghegongzuo2val,zonghegongzuo3val,zonghegongzuo4val,zonghegongzuo5val,qita1val,qita2val,qita3val,zhiduval,yichengqingkuangvalif __name__ == "__main__":with open(savefile, "w", encoding="utf-8-sig", newline='') as f:writer = csv.writer(f)writer.writerow(["序号", "工作事项", "个人或团队及以上任务"])aaaa = get_data_list()for i in range(1,len(aaaa)+1):if i == 1:yingyongval_n = list(set(yingyongval))if yingyongval_n == []:row = [i, '\n'.join(yingyongkey.split() + yingyongval_n)]writer.writerow(row)else:yingyongval_new = [str(i + 1) + '、' + str(yingyongval_n[i - 1]) for i in range(0, len(yingyongval_n))]row = [i, '\n'.join(yingyongkey.split() + yingyongval_new)]writer.writerow(row)elif i == 2:chenhuival_n = list(set(chenhuival))if chenhuival_n == []:row = [i, '\n'.join(chenhui.split() + chenhuival_n)]writer.writerow(row)else:chenhuival_new = [str(i + 1) + '、' + str(chenhuival_n[i - 1]) for i in range(0, len(chenhuival_n))]row = [i, '\n'.join(chenhui.split() + chenhuival_new)]writer.writerow(row)elif i == 3:rizhival_n = list(set(rizhival))if rizhival_n == []:row = [i, '\n'.join(rizhi.split() + rizhival_n)]writer.writerow(row)else:rizhival_new = [str(i + 1) + '、' + str(rizhival_n[i - 1]) for i in range(0, len(rizhival_n))]row = [i, '\n'.join(rizhi.split() + rizhival_new)]writer.writerow(row)elif i == 4:biangengval_n = list(set(biangengval))if biangengval_n == []:row = [i, '\n'.join(biangeng.split() + biangengval_n)]writer.writerow(row)else:biangengval_new = [str(i + 1) + '、' + str(biangengval_n[i - 1]) for i in range(0, len(biangengval_n))]row = [i, '\n'.join(biangeng.split() + biangengval_new)]writer.writerow(row)elif i == 5:shijianguanlival_n = list(set(shijianguanlival))if shijianguanlival_n == []:row = [i, '\n'.join(shijianguanli.split() + shijianguanlival_n)]writer.writerow(row)else:shijianguanlival_new = [str(i + 1) + '、' + str(shijianguanlival_n[i - 1]) for i in range(0, len(shijianguanlival_n))]row = [i, '\n'.join(shijianguanli.split() + shijianguanlival_new)]writer.writerow(row)elif i == 6:shengchanwentival_n = list(set(shengchanwentival))if shengchanwentival_n == []:row = [i, '\n'.join(shengchanwenti.split() + shengchanwentival_n)]writer.writerow(row)else:shengchanwentival_new = [str(i + 1) + '、' + str(shengchanwentival_n[i - 1]) for i in range(0, len(shengchanwentival_n))]row = [i, '\n'.join(shengchanwenti.split() + shengchanwentival_new)]writer.writerow(row)elif i == 7:rongliangval_n = list(set(rongliangval))if rongliangval_n == []:row = [i, '\n'.join(rongliang.split() + rongliangval_n)]writer.writerow(row)else:rongliangval_new = [str(i + 1) + '、' + str(rongliangval_n[i - 1]) for i in range(0, len(rongliangval_n))]row = [i, '\n'.join(rongliang.split() + rongliangval_new)]writer.writerow(row)elif i == 8:zhaojihuiyival_n = list(set(zhaojihuiyival))if zhaojihuiyival_n == []:row = [i, '\n'.join(zhaojihuiyi.split() + zhaojihuiyival_n)]writer.writerow(row)else:zhaojihuiyival_new = [str(i + 1) + '、' + str(zhaojihuiyival_n[i - 1]) for i in range(0, len(zhaojihuiyival_n))]row = [i, '\n'.join(zhaojihuiyi.split() + zhaojihuiyival_new)]writer.writerow(row)elif i == 9:gongzuo1val_n = list(set(gongzuo1val))if gongzuo1val_n == []:row = [i, ''.join(gongzuo1.split() + gongzuo1val_n)]writer.writerow(row)else:print("nihao")gongzuo1val_new = [str(i + 1) + '、' + str(gongzuo1val_n[i - 1]) for i in range(0, len(gongzuo1val_n))]row = [i, '\n'.join(gongzuo1.split() + gongzuo1val_new)]writer.writerow(row)elif i == 10:gongzuo2val_n = list(set(gongzuo2val))if gongzuo2val_n == []:row = [i, '\n'.join(gongzuo2.split() + gongzuo2val_n)]writer.writerow(row)else:gongzuo2val_new = [str(i + 1) + '、' + str(gongzuo2val_n[i - 1]) for i in range(0, len(gongzuo2val_n))]row = [i, '\n'.join(gongzuo2.split() + gongzuo2val_new)]writer.writerow(row)elif i == 11:gongzuo3val_n = list(set(gongzuo3val))if gongzuo3val_n == []:row = [i, '\n'.join(gongzuo3.split() + gongzuo3val_n)]writer.writerow(row)else:gongzuo3val_new = [str(i + 1) + '、' + str(gongzuo3val_n[i - 1]) for i in range(0, len(gongzuo3val_n))]row = [i, '\n'.join(gongzuo3.split() + gongzuo3val_new)]writer.writerow(row)elif i == 12:zonghegongzuo1val_n = list(set(zonghegongzuo1val))if zonghegongzuo1val_n == []:row = [i, '\n'.join(zonghegongzuo1.split() + zonghegongzuo1val_n)]writer.writerow(row)else:zonghegongzuo1val_new = [str(i + 1) + '、' + str(zonghegongzuo1val_n[i - 1]) for i in range(0, len(zonghegongzuo1val_n))]row = [i, '\n'.join(zonghegongzuo1.split() + zonghegongzuo1val_new)]writer.writerow(row)elif i == 13:zonghegongzuo2val_n = list(set(zonghegongzuo2val))if zonghegongzuo2val_n == []:row = [i, '\n'.join(zonghegongzuo2.split() + zonghegongzuo2val_n)]writer.writerow(row)else:zonghegongzuo2val_new = [str(i + 1) + '、' + str(zonghegongzuo2val_n[i - 1]) for i inrange(0, len(zonghegongzuo2val_n))]row = [i, '\n'.join(zonghegongzuo2.split() + zonghegongzuo2val_new)]writer.writerow(row)elif i == 14:zonghegongzuo3val_n = list(set(zonghegongzuo3val))if zonghegongzuo3val_n == []:row = [i, '\n'.join(zonghegongzuo3.split() + zonghegongzuo3val_n)]writer.writerow(row)else:zonghegongzuo3val_new = [str(i + 1) + '、' + str(zonghegongzuo3val_n[i - 1]) for i inrange(0, len(zonghegongzuo3val_n))]row = [i, '\n'.join(zonghegongzuo3.split() + zonghegongzuo3val_new)]writer.writerow(row)elif i == 15:zonghegongzuo4val_n = list(set(zonghegongzuo4val))if zonghegongzuo4val_n == []:row = [i, '\n'.join(zonghegongzuo4.split() + zonghegongzuo4val_n)]writer.writerow(row)else:zonghegongzuo4val_new = [str(i + 1) + '、' + str(zonghegongzuo4val_n[i - 1]) for i inrange(0, len(zonghegongzuo4val_n))]row = [i, '\n'.join(zonghegongzuo4.split() + zonghegongzuo4val_new)]writer.writerow(row)elif i == 16:zonghegongzuo5val_n = list(set(zonghegongzuo5val))if zonghegongzuo5val_n == []:row = [i, '\n'.join(zonghegongzuo5.split() + zonghegongzuo5val_n)]writer.writerow(row)else:zonghegongzuo5val_new = [str(i + 1) + '、' + str(zonghegongzuo5val_n[i - 1]) for i inrange(0, len(zonghegongzuo5val_n))]row = [i, '\n'.join(zonghegongzuo5.split() + zonghegongzuo5val_new)]writer.writerow(row)elif i == 17:qita1val_n = list(set(qita1val))if qita1val_n == []:row = [i, '\n'.join(qita1.split() + qita1val_n)]writer.writerow(row)else:qita1val_new = [str(i + 1) + '、' + str(qita1val_n [i - 1]) for i inrange(0, len(qita1val_n ))]row = [i, '\n'.join(qita1.split() + qita1val_new)]writer.writerow(row)elif i == 18:qita2val_n = list(set(qita2val))if qita2val_n == []:row = [i, '\n'.join(qita2.split() + qita2val_n)]writer.writerow(row)else:qita2val_new = [str(i + 1) + '、' + str(qita2val_n[i - 1]) for i inrange(0, len(qita2val_n))]row = [i, '\n'.join(qita2.split() + qita2val_new)]writer.writerow(row)elif i == 19:qita3val_n = list(set(qita3val))if qita3val_n == []:row = [i, '\n'.join(qita3.split() + qita3val_n)]writer.writerow(row)else:qita3val_new = [str(i + 1) + '、' + str(qita3val_n[i - 1]) for i inrange(0, len(qita3val_n))]row = [i, '\n'.join(qita3.split() + qita3val_new)]writer.writerow(row)elif i == 20:zhiduval_n = list(set(zhiduval))if zhiduval_n == []:row = [i, '\n'.join(zhidu.split() + zhiduval_n)]writer.writerow(row)else:zhiduval_new = [str(i + 1) + '、' + str(zhiduval_n[i - 1]) for i inrange(0, len(zhiduval_n))]row = [i, '\n'.join(zhidu.split() + zhiduval_new)]writer.writerow(row)elif i == 21:yichengqingkuangval_n = list(set(yichengqingkuangval))if yichengqingkuangval_n == []:row = [i, '\n'.join(yichengqingkuang.split() + yichengqingkuangval_n)]writer.writerow(row)else:yichengqingkuangval_new = [str(i + 1) + '、' + str(yichengqingkuangval_n[i - 1]) for i inrange(0, len(yichengqingkuangval_n))]row = [i, '\n'.join(yichengqingkuang.split() + yichengqingkuangval_new)]writer.writerow(row)