import pandas as pd
import numpy as np
from openpyxl import load_workbook
import os
import reclass doWork:def __init__(self):self.txt_file_dir = "txt_file"self.excel_name1 = r"template.xlsx" # 模板文件self.excel_name2 = r"statistics.xlsx" # 统计文件def get_dir(self): #获取文件路径及文件名for root,dirs,files in os.walk(self.txt_file_dir, followlinks=False): #遍历path,进入每个目录都调用visit函数，，有3个参数，root表示目录路径，dirs表示当前目录的目录名，files代表当前目录的文件名for filename in files:file_path = os.path.join(root, filename)flag = re.search("\.txt", file_path)if flag:yield file_pathelse:continuedef read_txt(self):# 读txt 输出: {filename:content}for txt_name in self.get_dir():with open(txt_name, encoding="utf-8") as f:txt_content = f.readlines()yield {txt_name:txt_content}def write_template(self):# 将每个txt写入文件名命名的sheelbook = load_workbook(self.excel_name1)writer=pd.ExcelWriter(self.excel_name1, engine='openpyxl')writer.book = bookfor content in self.read_txt():for k,v in content.items():sheet_conten = pd.DataFrame(columns=["temp"])index = 0sheet_name = re.search(r"\\(.*)", k).group(1)for line in v:line = line.strip()sheet_conten.loc[index] = [line]index += 1sheet_conten.to_excel(writer,sheet_name=sheet_name,index=0, header = None)#index=0：无索引 header = None 无列名writer.save()writer.close()def read_template(self):# 输出数据, 格式{sheet:[去重前，去重后]}statistics = {} # 去重后新增B列xl = pd.ExcelFile(self.excel_name1)writer=pd.ExcelWriter(self.excel_name1)#writer.book = booksheet_names = xl.sheet_names # 所有的sheet名称for sheet_name in sheet_names:A_col = xl.parse(sheet_name, names=["A"])B_col = A_col.drop_duplicates(subset=["A"],keep='first',inplace=False)B_col.columns=["B"]len_B = len(B_col.dropna())statistics[sheet_name] = [len(A_col), len_B]df_obj = pd.concat([A_col,B_col], sort=False, axis=1)df_obj.to_excel(writer,sheet_name=sheet_name,index=0, header = None)#index=0：无索引 header = None 无列名writer.save()writer.close()return statisticsdef data_statistics(self, data):# 将统计数据添加到统计表index_array = []value_array = []for i in data:index_array.append(i)value_array.append(data[i])df_obj = pd.DataFrame(value_array, columns=["去重前","去重后"], index=index_array)before = df_obj["去重前"].sum()after = df_obj["去重后"].sum()df_obj.loc["求和"] = [before, after]df_obj.to_excel(self.excel_name2, encoding="utf-8")def run(self):# 将Excel内容写入模板template.xlsxself.write_template()# 读取模板template.xlsx 将数据去重后新增一列data = self.read_template()# 添加到统计表self.data_statistics(data)if __name__ == '__main__':dw = doWork()dw.run()