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 csv

path=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 result

def 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,yichengqingkuangval


if __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 in
                                             range(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 in
                                             range(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 in
                                             range(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 in
                                             range(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 in
                                             range(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 in
                                    range(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 in
                                    range(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 in
                                    range(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 in
                                    range(0, len(yichengqingkuangval_n))]
                    row = [i, '\n'.join(yichengqingkuang.split() + yichengqingkuangval_new)]
                    writer.writerow(row)