excel_处理

import pandas as pd
import os
import shutil
import openpyxl
from functools import reduce

class excel_deal():

    def __int__(self):
        try:
            os.mkdir("./result")
        except:
            shutil.rmtree("./result")
            os.mkdir("./result")

    def mul_sheet_to_one(self):
        job_path = os.getcwd()  # 获取当前工作路径
        result_path = job_path+"\\result\\"
        ori_path = job_path + "\\ori_file\\"
        # print(result_path)
        file = os.listdir(ori_path)# 获取当前目录下文件
        # 筛选文件下类型为xlsx的文件 类型为列表
        xlsx_files = [f for f in file if f.endswith('.xlsx')]
        print(xlsx_files)
        for file in xlsx_files:
            dfs_list = []
            dfs = pd.ExcelFile(ori_path+file)  # 加载excel
            sheet_names = dfs.sheet_names  # 获取所有的sheet名称
            for sheet_name in sheet_names:
                print(sheet_name)
                df = pd.read_excel(dfs,sheet_name=sheet_name)
                df["SOURCE"] = sheet_name  # 新增一列，标记数据来源
                dfs_list.append(df)
            df_merge = pd.concat(dfs_list,ignore_index=False)
            print(df_merge)
            with pd.ExcelWriter(result_path + str(file),engine="openpyxl",mode="w") as writer:
                df_merge.to_excel (writer)

    def mul_excel_to_one (self):
        job_path = os.getcwd()  # 获取当前工作路径
        result_path = job_path+"\\result\\"
        ori_path = job_path + "\\ori_file\\"
        file = os.listdir(result_path)  # 获取当前目录下文件
        xlsx_files = [f for f in file if f.endswith('.xlsx')]
        print(xlsx_files)
        df_list = []
        for file in xlsx_files:
            df = pd.read_excel(result_path+file)
            df_list.append(df)

        if not df_list:
            merged_result = pd.DataFrame()
        else:
            # 以第一个DataFrame为初始结果
            merged_result = df_list[0].copy()
            # 遍历剩余DataFrame，依次合并
            for df in df_list[1:]:
                merged_result = pd.merge(
                    merged_result,
                    df,
                    on=["PORT", "SOURCE"],  # 基于两列匹配
                    how="inner",# 内连接：只保留两列均匹配的行
                )
        print("合并结果：")
        print(merged_result)
        with pd.ExcelWriter("merge_result.xlsx", mode="w") as writer:
            merged_result.to_excel (writer)


yc = excel_deal()
# 将工作簿的多个sheet合并到一个excel中
#yc.mul_sheet_to_one()
# 将多个excel表的相同数据合并到一个表格中
yc.mul_excel_to_one()