当前位置: 首页 > article >正文

【python实战】-- 选择解压汇总mode进行数据汇总20250314更新

系列文章目录

文章目录

  • 系列文章目录
  • 前言
  • 一、在镜片的基础上新增镜筒的汇总
  • 总结


前言

一、在镜片的基础上新增镜筒的汇总

import os
import shutil
import zipfile
import pandas as pd
import xlrd
import xlwt
import csv
from xlutils.copy import copy
from openpyxl import Workbook
from openpyxl import load_workbook
from os.path import dirname
from decimal import Decimal
from openpyxl.utils.dataframe import dataframe_to_rows
# 读写2007 excel
import openpyxl
from openpyxl.styles import numbers
from openpyxl.styles import Alignment
import glob
import tkinter as tk
from tkinter import messagebox
from tkinter import simpledialog


def del_old_zip(file_path):
    os.remove(file_path)
def decompress(file_path,root):
    z = zipfile.ZipFile(f"{file_path}","r")
    z.extractall(path=f"{root}")
    for names in z.namelist():
        if names.endswith(file_flag):
            z.close()
            return 1
    z.close()
    return 0 
def start_dir_make(root,dirname):
    os.chdir(root)
    os.mkdir(dirname)
    return os.path.join(root,dirname)
def rem_dir_extra(root,father_dir_name):
    try:
        for item in os.listdir(os.path.join(root,father_dir_name)):
            if not os.path.isdir(os.path.join(root,father_dir_name,item)):
                continue
            if item == father_dir_name and len(os.listdir(os.path.join(root,father_dir_name))) == 1:
                os.chdir(root)
                os.rename(father_dir_name,father_dir_name + '-old')
                shutil.move(os.path.join(root,father_dir_name + '-old', item),os.path.join(root))
                os.rmdir(os.path.join(root,father_dir_name + '-old'))
                rem_dir_extra(root,item)
            else:
                rem_dir_extra(os.path.join(root,father_dir_name),item)
    except Exception as e:
        print("清除文件夹出错"+str(e))
def get_allfile_msg(file_dir):
    for root, dirs, files in os.walk(file_dir):
        return root, dirs, [file for file in files if file.endswith('.xls') or file.endswith('.xlsx') or file.endswith('.csv')] 
def get_allfile_url(root, files):
    allFile_url = []
    for file_name in files:
        file_url = root + "/" + file_name
        allFile_url.append(file_url)
    return allFile_url
def get_file_name(path, suffix = ['.xlsx', '.xls','.csv']):  #'.xlsx', '.xls',
    tmp_lst = []
    for root,dirs,files in os.walk(path):
        for file in files:
            tmp_lst.append(os.path.join(root, file))
    return tmp_lst
def extract_last_part_of_path(path):
    return os.path.basename(path)
#定义读取csv_pandas
def read_csv_file(file_path):
    #参数:error_bad_lines=False跳过错误的行 delimiter=',',encoding = 'gbk',header = 0, engine='python'  sep = r"\s+\s{0}"  encoding = "iso-8859-1"
    return pd.read_csv(file_path,encoding = 'latin1',sep = r"\s+\s{0}",dtype=object,quotechar="'",delimiter=',',doublequote=True,engine="python",header = 1)   #第2行作为表头
def read_csv_file1(file_path):
    #参数:error_bad_lines=False跳过错误的行 delimiter=',',encoding = 'gbk',header = 0, engine='python'  sep = r"\s+\s{0}"  encoding = "iso-8859-1"
    return pd.read_csv(file_path,encoding = 'latin1',sep = r"\s+\s{0}",dtype=object,quotechar="'",delimiter=',',doublequote=True,engine="python",header = None,skiprows=lambda x:x not in [2,3,4])   #第2行作为表头
if __name__ == '__main__':
    #反射率标准S1和S2标准一致
    wave1start = 430
    wave1end = 530
    wave1standard = 1.5
    wave2start = 550
    wave2end = 780
    wave2standard = 1.1
    #***********************
    sort = input("请选择镜片还是镜筒:7-镜筒,9-镜片 \n")
    if sort == "7":
        mode = input("请选择汇总模式: 1解压 2汇总单机种  \n") 
        if mode == "1":
            zippath = input("请输入需解压的文件路径:\n")
            parent_path = zippath
            file_flag = '.zip'
            flag = 1
            while flag:
                for root,dirs,files in os.walk(parent_path):
                    for name in files:
                        if name.endswith(file_flag):
                            new_ws = start_dir_make(root,name.replace(file_flag,""))
                            zip_path = os.path.join(root,name)
                            flag = decompress(zip_path,new_ws)
                            del_old_zip(zip_path)
                            rem_dir_extra(root,name.replace(file_flag,""))
                            print(f'{root}\\{name}'.join(['文件:','\n解压完成\n']))
            rem_dir_extra(os.path.split(parent_path)[0],os.path.split(parent_path)[1])
            print("解压完成,请检查!!")
        elif mode == "2":  
            Summarypath = input("请输入汇总路径:汇总路径格式为:701/77b113e5-b3ad-4be2-8bcf-1544d5d901da/1.csv \n")
            #item = input("请输入汇总件号:(例如:39642A-701) \n")
            #user_input = input("请输入汇总model,LAB-L OR 反射率-F \n")
            position = input("请输入位置类型:上中下左右-5,上中下-3 \n")
            if position == "5":
                file_dir = Summarypath
                folder_count = sum(1 for _ in os.scandir(file_dir) if _.is_dir())
                count = 0
                item = 701
                with os.scandir(file_dir) as it:
                    wb = Workbook()
                    ws1 = wb.create_sheet('F')
                    ws2 = wb.create_sheet('L')   
                    ws1.cell(row = 1,column = 1).value = '文件名'   
                    ws1.cell(row = 2,column = 1).value = 'wave' 
                    ws2.cell(row = 2,column = 1).value = 'L*'   
                    ws2.cell(row = 3,column = 1).value = 'a*'  
                    ws2.cell(row = 4,column = 1).value = 'b*'  
                    alignment = Alignment(horizontal='center',vertical='center')
                    titles = ['data1','data2','data3','data4']
                    for l in range(380,1051):
                        ws1.cell(l-377,1).value = l
                        ws1.cell(l-377,1).alignment = alignment
                        continue
                    #*******************************
                    for entry in it:
                        count += 1
                        c = count
                        m = c - 1
                        if entry.is_dir():
                            print(entry.name)
                            file_dir1 = file_dir+'\\'+entry.name+"\\"
                            print(file_dir1)
                            for root,dirs,files in os.walk(file_dir1):
                                files_F = ['1.csv', '2.csv', '3.csv', '4.csv', '5.csv']
                                files_L = ['1Add.csv', '2Add.csv', '3Add.csv', '4Add.csv', '5Add.csv']
                                z = 0
                                for f in files_F:
                                    z += 1
                                    y = z - 1
                                    df = read_csv_file(file_dir1+f)
                                    df = df.iloc[:,1:5]
                                    for k,title in enumerate(titles,2):                    
                                        ws1.cell(row = 2,column = k+4*y+4*m).value = title
                                        ws1.cell(row = 2,column = k+4*y+4*m).alignment = alignment
                                        continue
                                    #源数据输出区域
                                    for i ,row in df.iterrows():
                                        #print(i)
                                        for j ,value in enumerate(row,start=1):
                                            ws1.cell(row = i+3,column = j+1+4*y+4*m).value = value
                                z = 0
                                for l in files_L:
                                    z += 1
                                    y = z - 1
                                    df = read_csv_file1(file_dir1+l)
                                    df = df.iloc[0:3,1:5]
                                    for k,title in enumerate(titles,2):                    
                                        ws2.cell(row = 1,column = k+4*y+4*m).value = title
                                        ws2.cell(row = 1,column = k+4*y+4*m).alignment = alignment
                                        continue
                                    #源数据输出区域
                                    for i ,row in df.iterrows():
                                        #print(i)
                                        for j ,value in enumerate(row,start=1):
                                            ws2.cell(row = i+2,column = j+1+4*y+4*m).value = value                            
                output_file_path=os.path.join(file_dir,f'Summaryout{item}.xlsx')
                wb.remove(wb['Sheet'])
                wb.save(output_file_path)    
            elif position == "3":
                file_dir = Summarypath
                folder_count = sum(1 for _ in os.scandir(file_dir) if _.is_dir())
                count = 0
                item = 701
                with os.scandir(file_dir) as it:
                    wb = Workbook()
                    ws1 = wb.create_sheet('F')
                    ws2 = wb.create_sheet('L')   
                    ws1.cell(row = 1,column = 1).value = '文件名'   
                    ws1.cell(row = 2,column = 1).value = 'wave' 
                    ws2.cell(row = 2,column = 1).value = 'L*'   
                    ws2.cell(row = 3,column = 1).value = 'a*'  
                    ws2.cell(row = 4,column = 1).value = 'b*'  
                    alignment = Alignment(horizontal='center',vertical='center')
                    titles = ['data1','data2','data3','data4']
                    for l in range(380,1051):
                        ws1.cell(l-377,1).value = l
                        ws1.cell(l-377,1).alignment = alignment
                        continue
                    #*******************************
                    for entry in it:
                        count += 1
                        c = count
                        m = c - 1
                        if entry.is_dir():
                            print(entry.name)
                            file_dir1 = file_dir+'\\'+entry.name+"\\"
                            print(file_dir1)
                            for root,dirs,files in os.walk(file_dir1):
                                files_F = ['1.csv', '2.csv', '3.csv']
                                files_L = ['1Add.csv', '2Add.csv', '3Add.csv']
                                z = 0
                                for f in files_F:
                                    z += 1
                                    y = z - 1
                                    df = read_csv_file(file_dir1+f)
                                    df = df.iloc[:,1:5]
                                    for k,title in enumerate(titles,2):                    
                                        ws1.cell(row = 2,column = k+4*y+4*m).value = title
                                        ws1.cell(row = 2,column = k+4*y+4*m).alignment = alignment
                                        continue
                                    #源数据输出区域
                                    for i ,row in df.iterrows():
                                        #print(i)
                                        for j ,value in enumerate(row,start=1):
                                            ws1.cell(row = i+3,column = j+1+4*y+4*m).value = value
                                z = 0
                                for l in files_L:
                                    z += 1
                                    y = z - 1
                                    df = read_csv_file1(file_dir1+l)
                                    df = df.iloc[0:3,1:5]
                                    for k,title in enumerate(titles,2):                    
                                        ws2.cell(row = 1,column = k+4*y+4*m).value = title
                                        ws2.cell(row = 1,column = k+4*y+4*m).alignment = alignment
                                        continue
                                    #源数据输出区域
                                    for i ,row in df.iterrows():
                                        #print(i)
                                        for j ,value in enumerate(row,start=1):
                                            ws2.cell(row = i+2,column = j+1+4*y+4*m).value = value                            
                output_file_path=os.path.join(file_dir,f'Summaryout{item}.xlsx')
                wb.remove(wb['Sheet'])
                wb.save(output_file_path)                  
            else:
                print("指令错误,请重新运行。")
        else:
            print("指令错误,请重新运行。")                                             
    elif sort == "9":
        mode = input("请选择汇总模式: 1解压 2汇总单镜片 3解压汇总单镜片 4汇总单机种多镜片 \n") 
        if mode == "1":
            zippath = input("请输入需解压的文件路径:\n")
            parent_path = zippath
            file_flag = '.zip'
            flag = 1
            while flag:
                for root,dirs,files in os.walk(parent_path):
                    for name in files:
                        if name.endswith(file_flag):
                            new_ws = start_dir_make(root,name.replace(file_flag,""))
                            zip_path = os.path.join(root,name)
                            flag = decompress(zip_path,new_ws)
                            del_old_zip(zip_path)
                            rem_dir_extra(root,name.replace(file_flag,""))
                            print(f'{root}\\{name}'.join(['文件:','\n解压完成\n']))
            rem_dir_extra(os.path.split(parent_path)[0],os.path.split(parent_path)[1])
            print("解压完成,请检查!!")
        elif mode == "2":   
            Summarypath = input("请输入汇总路径:路径格式:901/77b113e5-b3ad-4be2-8bcf-1544d5d901da \n")
            item = input("请输入汇总件号:(例如:39642A) \n")
            user_input = input("请输入汇总model,S1或S2或S1S2或S1&S2 \n")
            standerd_input = input("是否需要标准?\n")
            if standerd_input == "Y":
                wb = Workbook()
                #ws = wb.active
                #ws.title="Summary"
                ws = wb.create_sheet("S1S2")
                ws1 = wb.create_sheet("S1")
                ws2 = wb.create_sheet("S2")        
                #设置所有单元格的对齐方式为居中
                alignment = Alignment(horizontal='center',vertical='center')   
                titlesS1 = ['data1','data2','data3']    
                titlesS2 = ['data4','data5','data6']  
                titlesS1S2 = ['data1','data2','data3','data4','data5','data6']
                #第一列波段设置区域
                ws.cell(row = 1,column = 1).value = '判定'
                ws.cell(row = 1,column = 1).alignment = alignment
                ws.cell(row = 5,column = 1).value = '文件名'
                ws.cell(row = 5,column = 1).alignment = alignment
                ws.cell(row = 6,column = 1).value = 'wave'
                ws.cell(row = 6,column = 1).alignment = alignment
                
                ws1.cell(row = 1,column = 1).value = '判定'
                ws1.cell(row = 1,column = 1).alignment = alignment
                ws1.cell(row = 5,column = 1).value = '文件名'
                ws1.cell(row = 5,column = 1).alignment = alignment
                ws1.cell(row = 6,column = 1).value = 'wave'
                ws1.cell(row = 6,column = 1).alignment = alignment
                
                ws2.cell(row = 1,column = 1).value = '判定'
                ws2.cell(row = 1,column = 1).alignment = alignment
                ws2.cell(row = 5,column = 1).value = '文件名'
                ws2.cell(row = 5,column = 1).alignment = alignment
                ws2.cell(row = 6,column = 1).value = 'wave'
                ws2.cell(row = 6,column = 1).alignment = alignment
                for l in range(380,1051):
                    ws.cell(l-373,1).value = l
                    ws.cell(l-373,1).alignment = alignment
                    
                    ws1.cell(l-373,1).value = l
                    ws1.cell(l-373,1).alignment = alignment
                    
                    ws2.cell(l-373,1).value = l
                    ws2.cell(l-373,1).alignment = alignment
                    continue
                #*****************************************************************

                #读取指定文件夹
                #file_dir = os.getcwd()
                file_dir = Summarypath
                current_path = os.path.dirname(os.path.abspath(__file__))
                #file_dir = r"D:\Users\gxcaoty\Desktop\39526-905\一车间"
                root, dirs, files = get_allfile_msg(file_dir)
                allFile_url = get_allfile_url(root, files)
                dir_numbers = len(dirs)    #file_dir下的文件夹个数
                count = 0
                for root,dirs,files in os.walk(file_dir):
                    for file_path in glob.glob(os.path.join(root,'*.csv')):
                        if item in file_path and 'Add' not in file_path:
                            print(file_path)
                            xl = file_path
                            count += 1
                            c = count
                            m = c - 1
                            print(f"共发现 {m} 个文件!")
                            #print(files_chose)
                            try:
                                last_part = extract_last_part_of_path(xl)
                                #print(last_part)  #filename为文件名
                                filename = xl 
                                csv_data = read_csv_file(filename)
                                df = csv_data
                                if user_input == "S1":
                                    df = df.iloc[:,1:4]
                                    df = df.astype(float)
                                    #print(df)
                                    #**********************************************************
                                    combinedwave1 = f'{wave1start},{wave1end},{wave1standard}'
                                    combinedwave2 = f'{wave2start},{wave2end},{wave2standard}'
                                    #print(combinedwave1)
                                    ws1.cell(row = 2,column = 1).value = combinedwave1
                                    ws1.cell(row = 3,column = 1).value = combinedwave2
                                    #***********************************************************
                                    #计算判定区域
                                    for n in range(0,3):
                                        cal1 = df.iloc[wave1start-380+2:wave1end-380+2,n].max()  
                                        cal2 = df.iloc[wave2start-380+2:wave2end-380+2,n].max()  
                                        if cal1 <= wave1standard and cal2 <= wave2standard :
                                            ws1.cell(row = 1,column = n+2+3*m).value = "OK"
                                            ws1.cell(row = 1,column = n+2+3*m).alignment = alignment
                                        else:
                                            ws1.cell(row = 1,column = n+2+3*m).value = "NG"
                                            ws1.cell(row = 1,column = n+2+3*m).alignment = alignment
                                        #print(ave1,ave2)
                                        ws1.cell(row = 2,column = n+2+3*m).value = cal1
                                        ws1.cell(row = 3,column = n+2+3*m).value = cal2
                                        continue
                                    #文件名输出区域
                                    ws1.cell(row = 5,column = 2+3*m).value = last_part                     
                                    #标题输出区域(data1~data6)
                                    for k,title in enumerate(titlesS1,2):                    
                                        ws1.cell(row = 6,column = k+3*m).value = title
                                        ws1.cell(row = 6,column = k+3*m).alignment = alignment
                                        continue
                                    #源数据输出区域
                                    for i ,row in df.iterrows():
                                        #print(i)
                                        for j ,value in enumerate(row,start=1):
                                            ws1.cell(row = i+7,column = j+1+3*m).value = value

                                elif user_input == "S2":
                                    df = df.iloc[:,4:7]
                                    df = df.astype(float)
                                    #print(df)
                                    #**********************************************************
                                    combinedwave1 = f'{wave1start},{wave1end},{wave1standard}'
                                    combinedwave2 = f'{wave2start},{wave2end},{wave2standard}'
                                    #print(combinedwave1)
                                    ws2.cell(row = 2,column = 1).value = combinedwave1
                                    ws2.cell(row = 3,column = 1).value = combinedwave2
                                    #***********************************************************
                                    #计算判定区域
                                    for n in range(0,3):
                                        cal1 = df.iloc[wave1start-380+2:wave1end-380+2,n].max()  
                                        cal2 = df.iloc[wave2start-380+2:wave2end-380+2,n].max()  
                                        if cal1 <= wave1standard and cal2 <= wave2standard :
                                            ws2.cell(row = 1,column = n+2+3*m).value = "OK"
                                            ws2.cell(row = 1,column = n+2+3*m).alignment = alignment
                                        else:
                                            ws2.cell(row = 1,column = n+2+3*m).value = "NG"
                                            ws2.cell(row = 1,column = n+2+3*m).alignment = alignment
                                        #print(ave1,ave2)
                                        ws2.cell(row = 2,column = n+2+3*m).value = cal1
                                        ws2.cell(row = 3,column = n+2+3*m).value = cal2
                                        continue
                                    #文件名输出区域
                                    ws2.cell(row = 5,column = 2+3*m).value = last_part                     
                                    #标题输出区域(data1~data6)
                                    for k,title in enumerate(titlesS2,2):                    
                                        ws2.cell(row = 6,column = k+3*m).value = title
                                        ws2.cell(row = 6,column = k+3*m).alignment = alignment
                                        continue
                                    #源数据输出区域
                                    for i ,row in df.iterrows():
                                        #print(i)
                                        for j ,value in enumerate(row,start=1):
                                            ws2.cell(row = i+7,column = j+1+3*m).value = value

                                elif user_input == "S1S2":
                                    df = df.iloc[:,1:7]
                                    df = df.astype(float)
                                    #print(df)
                                    #**********************************************************
                                    combinedwave1 = f'{wave1start},{wave1end},{wave1standard}'
                                    combinedwave2 = f'{wave2start},{wave2end},{wave2standard}'
                                    #print(combinedwave1)
                                    ws.cell(row = 2,column = 1).value = combinedwave1
                                    ws.cell(row = 3,column = 1).value = combinedwave2
                                    #***********************************************************
                                    #计算判定区域
                                    for n in range(0,6):
                                        cal1 = df.iloc[wave1start-380+2:wave1end-380+2,n].max()  
                                        cal2 = df.iloc[wave2start-380+2:wave2end-380+2,n].max()  
                                        if cal1 <= wave1standard and cal2 <= wave2standard :
                                            ws.cell(row = 1,column = n+2+6*m).value = "OK"
                                            ws.cell(row = 1,column = n+2+6*m).alignment = alignment
                                        else:
                                            ws.cell(row = 1,column = n+2+6*m).value = "NG"
                                            ws.cell(row = 1,column = n+2+6*m).alignment = alignment
                                        #print(ave1,ave2)
                                        ws.cell(row = 2,column = n+2+6*m).value = cal1
                                        ws.cell(row = 3,column = n+2+6*m).value = cal2
                                        continue
                                    #文件名输出区域
                                    ws.cell(row = 5,column = 2+6*m).value = last_part                     
                                    #标题输出区域(data1~data6)
                                    for k,title in enumerate(titlesS1S2,2):                    
                                        ws.cell(row = 6,column = k+6*m).value = title
                                        ws.cell(row = 6,column = k+6*m).alignment = alignment
                                        continue
                                    #源数据输出区域
                                    for i ,row in df.iterrows():
                                        #print(i)
                                        for j ,value in enumerate(row,start=1):
                                            ws.cell(row = i+7,column = j+1+6*m).value = value

                                elif user_input == "S1&S2":
                                    #df = df.iloc[:,1:7]
                                    df1 = df.iloc[:,1:4]
                                    df2 = df.iloc[:,4:7]
                                    df1 = df1.astype(float)
                                    df2 = df2.astype(float)
                                    #**********************************************************
                                    combinedwave1 = f'{wave1start},{wave1end},{wave1standard}'
                                    combinedwave2 = f'{wave2start},{wave2end},{wave2standard}'
                                    #print(combinedwave1)
                                    ws1.cell(row = 2,column = 1).value = combinedwave1
                                    ws2.cell(row = 2,column = 1).value = combinedwave1
                                    ws1.cell(row = 3,column = 1).value = combinedwave2
                                    ws2.cell(row = 3,column = 1).value = combinedwave2
                                    #***********************************************************
                                    #计算判定区域
                                    for n in range(0,3):
                                        cal1 = df1.iloc[wave1start-380+2:wave1end-380+2,n].max()  
                                        cal2 = df1.iloc[wave2start-380+2:wave2end-380+2,n].max()  
                                        cal3 = df2.iloc[wave1start-380+2:wave1end-380+2,n].max()  
                                        cal4 = df2.iloc[wave2start-380+2:wave2end-380+2,n].max() 
                                        if cal1 <= wave1standard and cal2 <= wave2standard :
                                            ws1.cell(row = 1,column = n+2+3*m).value = "OK"
                                            ws1.cell(row = 1,column = n+2+3*m).alignment = alignment
                                        else:
                                            ws1.cell(row = 1,column = n+2+3*m).value = "NG"
                                            ws1.cell(row = 1,column = n+2+3*m).alignment = alignment

                                        if cal3 <= wave1standard and cal4 <= wave2standard :
                                            ws2.cell(row = 1,column = n+2+3*m).value = "OK"
                                            ws2.cell(row = 1,column = n+2+3*m).alignment = alignment
                                        else:
                                            ws2.cell(row = 1,column = n+2+3*m).value = "NG"
                                            ws2.cell(row = 1,column = n+2+3*m).alignment = alignment
                                        #print(ave1,ave2)
                                        ws1.cell(row = 2,column = n+2+3*m).value = cal1
                                        ws1.cell(row = 3,column = n+2+3*m).value = cal2
                                        ws2.cell(row = 2,column = n+2+3*m).value = cal3
                                        ws2.cell(row = 3,column = n+2+3*m).value = cal4
                                        continue
                                    #文件名输出区域
                                    ws1.cell(row = 5,column = 2+3*m).value = last_part   
                                    ws2.cell(row = 5,column = 2+3*m).value = last_part                  
                                    #标题输出区域(data1~data6)
                                    for k,title in enumerate(titlesS1,2):                    
                                        ws1.cell(row = 6,column = k+3*m).value = title
                                        ws1.cell(row = 6,column = k+3*m).alignment = alignment
                                        continue
                                    for k,title in enumerate(titlesS2,2):                    
                                        ws2.cell(row = 6,column = k+3*m).value = title
                                        ws2.cell(row = 6,column = k+3*m).alignment = alignment
                                        continue
                                    #源数据输出区域
                                    for i ,row in df1.iterrows():
                                        #print(i)
                                        for j ,value in enumerate(row,start=1):
                                            ws1.cell(row = i+7,column = j+1+3*m).value = value

                                    for i ,row in df2.iterrows():
                                        #print(i)
                                        for j ,value in enumerate(row,start=1):
                                            ws2.cell(row = i+7,column = j+1+3*m).value = value

                                else:
                                    print("非指定指令")
                            except Exception as e:
                                print(e)
                if user_input == "S1":
                    output_file_path=os.path.join(current_path,f'Summaryout{item}{user_input}.xlsx')
                    wb.remove(wb['S2'])
                    wb.remove(wb['S1S2'])
                    wb.remove(wb['Sheet'])
                    wb.save(output_file_path)

                elif user_input == "S2":
                    output_file_path=os.path.join(current_path,f'Summaryout{item}{user_input}.xlsx')
                    wb.remove(wb['S1'])
                    wb.remove(wb['S1S2'])
                    wb.remove(wb['Sheet'])
                    wb.save(output_file_path)

                elif user_input == "S1S2":
                    output_file_path=os.path.join(current_path,f'Summaryout{item}{user_input}.xlsx')
                    wb.remove(wb['S1'])
                    wb.remove(wb['S2'])
                    wb.remove(wb['Sheet'])            
                    wb.save(output_file_path)

                elif user_input == "S1&S2":
                    output_file_path=os.path.join(current_path,f'Summaryout{item}{user_input}.xlsx')
                    wb.remove(wb['S1S2'])
                    wb.remove(wb['Sheet'])
                    wb.save(output_file_path)

                else :
                    print("请检查程序!")  

            elif standerd_input == "N":
                wb = Workbook()
                #ws = wb.active
                #ws.title="Summary"
                ws = wb.create_sheet("S1S2")
                ws1 = wb.create_sheet("S1")
                ws2 = wb.create_sheet("S2")        
                #设置所有单元格的对齐方式为居中
                alignment = Alignment(horizontal='center',vertical='center')   
                titlesS1 = ['data1','data2','data3']    
                titlesS2 = ['data4','data5','data6']  
                titlesS1S2 = ['data1','data2','data3','data4','data5','data6']
                #第一列波段设置区域
                ws.cell(row = 5,column = 1).value = '文件名'
                ws.cell(row = 5,column = 1).alignment = alignment
                ws.cell(row = 6,column = 1).value = 'wave'
                ws.cell(row = 6,column = 1).alignment = alignment
                
                ws1.cell(row = 5,column = 1).value = '文件名'
                ws1.cell(row = 5,column = 1).alignment = alignment
                ws1.cell(row = 6,column = 1).value = 'wave'
                ws1.cell(row = 6,column = 1).alignment = alignment
                
                ws2.cell(row = 5,column = 1).value = '文件名'
                ws2.cell(row = 5,column = 1).alignment = alignment
                ws2.cell(row = 6,column = 1).value = 'wave'
                ws2.cell(row = 6,column = 1).alignment = alignment

                for l in range(380,1051):
                    ws.cell(l-373,1).value = l
                    ws.cell(l-373,1).alignment = alignment
                    
                    ws1.cell(l-373,1).value = l
                    ws1.cell(l-373,1).alignment = alignment
                    
                    ws2.cell(l-373,1).value = l
                    ws2.cell(l-373,1).alignment = alignment
                    continue
                #*****************************************************************

                #读取指定文件夹
                #file_dir = os.getcwd()
                file_dir = Summarypath
                current_path = os.path.dirname(os.path.abspath(__file__))
                #file_dir = r"D:\Users\gxcaoty\Desktop\39526-905\一车间"
                root, dirs, files = get_allfile_msg(file_dir)
                allFile_url = get_allfile_url(root, files)
                dir_numbers = len(dirs)    #file_dir下的文件夹个数
                count = 0
                for root,dirs,files in os.walk(file_dir):
                    for file_path in glob.glob(os.path.join(root,'*.csv')):
                        if item in file_path and 'Add' not in file_path:
                            print(file_path)
                            xl = file_path
                            count += 1
                            c = count
                            m = c - 1
                            print(f"共发现 {m} 个文件!")
                            #print(files_chose)
                            try:
                                last_part = extract_last_part_of_path(xl)
                                #print(last_part)  #filename为文件名
                                filename = xl 
                                csv_data = read_csv_file(filename)
                                df = csv_data
                                if user_input == "S1":
                                    df = df.iloc[:,1:4]
                                    df = df.astype(float)
                                    #print(df)
                                    #文件名输出区域
                                    ws1.cell(row = 5,column = 2+3*m).value = last_part                     
                                    #标题输出区域(data1~data6)
                                    for k,title in enumerate(titlesS1,2):                    
                                        ws1.cell(row = 6,column = k+3*m).value = title
                                        ws1.cell(row = 6,column = k+3*m).alignment = alignment
                                        continue
                                    #源数据输出区域
                                    for i ,row in df.iterrows():
                                        #print(i)
                                        for j ,value in enumerate(row,start=1):
                                            ws1.cell(row = i+7,column = j+1+3*m).value = value

                                elif user_input == "S2":
                                    df = df.iloc[:,4:7]
                                    df = df.astype(float)
                                    #print(df)
                                    #文件名输出区域
                                    ws2.cell(row = 5,column = 2+3*m).value = last_part                     
                                    #标题输出区域(data1~data6)
                                    for k,title in enumerate(titlesS2,2):                    
                                        ws2.cell(row = 6,column = k+3*m).value = title
                                        ws2.cell(row = 6,column = k+3*m).alignment = alignment
                                        continue
                                    #源数据输出区域
                                    for i ,row in df.iterrows():
                                        #print(i)
                                        for j ,value in enumerate(row,start=1):
                                            ws2.cell(row = i+7,column = j+1+3*m).value = value

                                elif user_input == "S1S2":
                                    df = df.iloc[:,1:7]
                                    df = df.astype(float)
                                    #print(df)
                                    #文件名输出区域
                                    ws.cell(row = 5,column = 2+6*m).value = last_part                     
                                    #标题输出区域(data1~data6)
                                    for k,title in enumerate(titlesS1S2,2):                    
                                        ws.cell(row = 6,column = k+6*m).value = title
                                        ws.cell(row = 6,column = k+6*m).alignment = alignment
                                        continue
                                    #源数据输出区域
                                    for i ,row in df.iterrows():
                                        #print(i)
                                        for j ,value in enumerate(row,start=1):
                                            ws.cell(row = i+7,column = j+1+6*m).value = value

                                elif user_input == "S1&S2":
                                    #df = df.iloc[:,1:7]
                                    df1 = df.iloc[:,1:4]
                                    df2 = df.iloc[:,4:7]
                                    df1 = df1.astype(float)
                                    df2 = df2.astype(float)
                                    #文件名输出区域
                                    ws1.cell(row = 5,column = 2+3*m).value = last_part   
                                    ws2.cell(row = 5,column = 2+3*m).value = last_part                  
                                    #标题输出区域(data1~data6)
                                    for k,title in enumerate(titlesS1,2):                    
                                        ws1.cell(row = 6,column = k+3*m).value = title
                                        ws1.cell(row = 6,column = k+3*m).alignment = alignment
                                        continue
                                    for k,title in enumerate(titlesS2,2):                    
                                        ws2.cell(row = 6,column = k+3*m).value = title
                                        ws2.cell(row = 6,column = k+3*m).alignment = alignment
                                        continue
                                    #源数据输出区域
                                    for i ,row in df1.iterrows():
                                        #print(i)
                                        for j ,value in enumerate(row,start=1):
                                            ws1.cell(row = i+7,column = j+1+3*m).value = value

                                    for i ,row in df2.iterrows():
                                        #print(i)
                                        for j ,value in enumerate(row,start=1):
                                            ws2.cell(row = i+7,column = j+1+3*m).value = value

                                else:
                                    print("非指定指令")
                            except Exception as e:
                                print(e)
                if user_input == "S1":
                    output_file_path=os.path.join(current_path,f'Summaryout{item}{user_input}.xlsx')
                    wb.remove(wb['S2'])
                    wb.remove(wb['S1S2'])
                    wb.remove(wb['Sheet'])
                    wb.save(output_file_path)

                elif user_input == "S2":
                    output_file_path=os.path.join(current_path,f'Summaryout{item}{user_input}.xlsx')
                    wb.remove(wb['S1'])
                    wb.remove(wb['S1S2'])
                    wb.remove(wb['Sheet'])
                    wb.save(output_file_path)

                elif user_input == "S1S2":
                    output_file_path=os.path.join(current_path,f'Summaryout{item}{user_input}.xlsx')
                    wb.remove(wb['S1'])
                    wb.remove(wb['S2'])
                    wb.remove(wb['Sheet'])            
                    wb.save(output_file_path)

                elif user_input == "S1&S2":
                    output_file_path=os.path.join(current_path,f'Summaryout{item}{user_input}.xlsx')
                    wb.remove(wb['S1S2'])
                    wb.remove(wb['Sheet'])
                    wb.save(output_file_path)

                else :
                    print("请检查程序!") 
            else:
                print("非指定指令!")
        elif mode == "3":
            zippath = input("请输入需解压的文件路径:\n")
            item = input("请输入汇总件号:(例如:39642A) \n")
            standerd_input = input("是否需要标准? \n")
            if standerd_input == "Y":
                parent_path = zippath
                file_flag = '.zip'
                flag = 1
                while flag:
                    for root,dirs,files in os.walk(parent_path):
                        for name in files:
                            if name.endswith(file_flag):
                                new_ws = start_dir_make(root,name.replace(file_flag,""))
                                zip_path = os.path.join(root,name)
                                flag = decompress(zip_path,new_ws)
                                del_old_zip(zip_path)
                                rem_dir_extra(root,name.replace(file_flag,""))
                                print(f'{root}\\{name}'.join(['文件:','\n解压完成\n']))
                rem_dir_extra(os.path.split(parent_path)[0],os.path.split(parent_path)[1])
                print("解压完成,请检查!!")
                print("请输入汇总model,S1或S2或S1S2或S1&S2")
                wb = Workbook()
                #ws = wb.active
                #ws.title="Summary"
                ws = wb.create_sheet("S1S2")
                ws1 = wb.create_sheet("S1")
                ws2 = wb.create_sheet("S2")
                #设置所有单元格的对齐方式为居中
                alignment = Alignment(horizontal='center',vertical='center')   
                titlesS1 = ['data1','data2','data3']    
                titlesS2 = ['data4','data5','data6']  
                titlesS1S2 = ['data1','data2','data3','data4','data5','data6']
                #第一列波段设置区域
                ws.cell(row = 1,column = 1).value = '判定'
                ws.cell(row = 1,column = 1).alignment = alignment
                ws.cell(row = 5,column = 1).value = '文件名'
                ws.cell(row = 5,column = 1).alignment = alignment
                ws.cell(row = 6,column = 1).value = 'wave'
                ws.cell(row = 6,column = 1).alignment = alignment
                
                ws1.cell(row = 1,column = 1).value = '判定'
                ws1.cell(row = 1,column = 1).alignment = alignment
                ws1.cell(row = 5,column = 1).value = '文件名'
                ws1.cell(row = 5,column = 1).alignment = alignment
                ws1.cell(row = 6,column = 1).value = 'wave'
                ws1.cell(row = 6,column = 1).alignment = alignment
                
                ws2.cell(row = 1,column = 1).value = '判定'
                ws2.cell(row = 1,column = 1).alignment = alignment
                ws2.cell(row = 5,column = 1).value = '文件名'
                ws2.cell(row = 5,column = 1).alignment = alignment
                ws2.cell(row = 6,column = 1).value = 'wave'
                ws2.cell(row = 6,column = 1).alignment = alignment
                for l in range(380,1051):
                    ws.cell(l-373,1).value = l
                    ws.cell(l-373,1).alignment = alignment
                    
                    ws1.cell(l-373,1).value = l
                    ws1.cell(l-373,1).alignment = alignment
                    
                    ws2.cell(l-373,1).value = l
                    ws2.cell(l-373,1).alignment = alignment
                    continue
                #*****************************************************************
                #读取指定文件夹
                #file_dir = os.getcwd()
                file_dir = parent_path
                current_path = os.path.dirname(os.path.abspath(__file__))
                #file_dir = r"D:\Users\gxcaoty\Desktop\39526-905\一车间"
                root, dirs, files = get_allfile_msg(file_dir)
                allFile_url = get_allfile_url(root, files)
                dir_numbers = len(dirs)    #file_dir下的文件夹个数
                
                user_input = input("请输入S1或S2或S1S2或S1&S2\n")
                count = 0
                for root,dirs,files in os.walk(file_dir):
                    for file_path in glob.glob(os.path.join(root,'*.csv')):
                        if item in file_path and 'Add' not in file_path:
                            print(file_path)
                            xl = file_path
                            count += 1
                            c = count
                            m = c - 1
                            print(f"共发现 {m} 个文件!")
                            #print(files_chose)
                            try:
                                last_part = extract_last_part_of_path(xl)
                                #print(last_part)  #filename为文件名
                                filename = xl 
                                csv_data = read_csv_file(filename)
                                df = csv_data
                                if user_input == "S1":
                                    df = df.iloc[:,1:4]
                                    df = df.astype(float)
                                    #print(df)
                                    #**********************************************************
                                    combinedwave1 = f'{wave1start},{wave1end},{wave1standard}'
                                    combinedwave2 = f'{wave2start},{wave2end},{wave2standard}'
                                    #print(combinedwave1)
                                    ws1.cell(row = 2,column = 1).value = combinedwave1
                                    ws1.cell(row = 3,column = 1).value = combinedwave2
                                    #***********************************************************
                                    #计算判定区域
                                    for n in range(0,3):
                                        cal1 = df.iloc[wave1start-380+2:wave1end-380+2,n].max()  
                                        cal2 = df.iloc[wave2start-380+2:wave2end-380+2,n].max()  
                                        if cal1 <= wave1standard and cal2 <= wave2standard :
                                            ws1.cell(row = 1,column = n+2+3*m).value = "OK"
                                            ws1.cell(row = 1,column = n+2+3*m).alignment = alignment
                                        else:
                                            ws1.cell(row = 1,column = n+2+3*m).value = "NG"
                                            ws1.cell(row = 1,column = n+2+3*m).alignment = alignment
                                        #print(ave1,ave2)
                                        ws1.cell(row = 2,column = n+2+3*m).value = cal1
                                        ws1.cell(row = 3,column = n+2+3*m).value = cal2
                                        continue
                                    #文件名输出区域
                                    ws1.cell(row = 5,column = 2+3*m).value = last_part                     
                                    #标题输出区域(data1~data6)
                                    for k,title in enumerate(titlesS1,2):                    
                                        ws1.cell(row = 6,column = k+3*m).value = title
                                        ws1.cell(row = 6,column = k+3*m).alignment = alignment
                                        continue
                                    #源数据输出区域
                                    for i ,row in df.iterrows():
                                        #print(i)
                                        for j ,value in enumerate(row,start=1):
                                            ws1.cell(row = i+7,column = j+1+3*m).value = value

                                elif user_input == "S2":
                                    df = df.iloc[:,4:7]
                                    df = df.astype(float)
                                    #print(df)
                                    #**********************************************************
                                    combinedwave1 = f'{wave1start},{wave1end},{wave1standard}'
                                    combinedwave2 = f'{wave2start},{wave2end},{wave2standard}'
                                    #print(combinedwave1)
                                    ws2.cell(row = 2,column = 1).value = combinedwave1
                                    ws2.cell(row = 3,column = 1).value = combinedwave2
                                    #***********************************************************
                                    #计算判定区域
                                    for n in range(0,3):
                                        cal1 = df.iloc[wave1start-380+2:wave1end-380+2,n].max()  
                                        cal2 = df.iloc[wave2start-380+2:wave2end-380+2,n].max()  
                                        if cal1 <= wave1standard and cal2 <= wave2standard :
                                            ws2.cell(row = 1,column = n+2+3*m).value = "OK"
                                            ws2.cell(row = 1,column = n+2+3*m).alignment = alignment
                                        else:
                                            ws2.cell(row = 1,column = n+2+3*m).value = "NG"
                                            ws2.cell(row = 1,column = n+2+3*m).alignment = alignment
                                        #print(ave1,ave2)
                                        ws2.cell(row = 2,column = n+2+3*m).value = cal1
                                        ws2.cell(row = 3,column = n+2+3*m).value = cal2
                                        continue
                                    #文件名输出区域
                                    ws2.cell(row = 5,column = 2+3*m).value = last_part                     
                                    #标题输出区域(data1~data6)
                                    for k,title in enumerate(titlesS2,2):                    
                                        ws2.cell(row = 6,column = k+3*m).value = title
                                        ws2.cell(row = 6,column = k+3*m).alignment = alignment
                                        continue
                                    #源数据输出区域
                                    for i ,row in df.iterrows():
                                        #print(i)
                                        for j ,value in enumerate(row,start=1):
                                            ws2.cell(row = i+7,column = j+1+3*m).value = value

                                elif user_input == "S1S2":
                                    df = df.iloc[:,1:7]
                                    df = df.astype(float)
                                    #print(df)
                                    #**********************************************************
                                    combinedwave1 = f'{wave1start},{wave1end},{wave1standard}'
                                    combinedwave2 = f'{wave2start},{wave2end},{wave2standard}'
                                    #print(combinedwave1)
                                    ws.cell(row = 2,column = 1).value = combinedwave1
                                    ws.cell(row = 3,column = 1).value = combinedwave2
                                    #***********************************************************
                                    #计算判定区域
                                    for n in range(0,6):
                                        cal1 = df.iloc[wave1start-380+2:wave1end-380+2,n].max()  
                                        cal2 = df.iloc[wave2start-380+2:wave2end-380+2,n].max()  
                                        if cal1 <= wave1standard and cal2 <= wave2standard :
                                            ws.cell(row = 1,column = n+2+6*m).value = "OK"
                                            ws.cell(row = 1,column = n+2+6*m).alignment = alignment
                                        else:
                                            ws.cell(row = 1,column = n+2+6*m).value = "NG"
                                            ws.cell(row = 1,column = n+2+6*m).alignment = alignment
                                        #print(ave1,ave2)
                                        ws.cell(row = 2,column = n+2+6*m).value = cal1
                                        ws.cell(row = 3,column = n+2+6*m).value = cal2
                                        continue
                                    #文件名输出区域
                                    ws.cell(row = 5,column = 2+6*m).value = last_part                     
                                    #标题输出区域(data1~data6)
                                    for k,title in enumerate(titlesS1S2,2):                    
                                        ws.cell(row = 6,column = k+6*m).value = title
                                        ws.cell(row = 6,column = k+6*m).alignment = alignment
                                        continue
                                    #源数据输出区域
                                    for i ,row in df.iterrows():
                                        #print(i)
                                        for j ,value in enumerate(row,start=1):
                                            ws.cell(row = i+7,column = j+1+6*m).value = value

                                elif user_input == "S1&S2":
                                    #df = df.iloc[:,1:7]
                                    df1 = df.iloc[:,1:4]
                                    df2 = df.iloc[:,4:7]
                                    df1 = df1.astype(float)
                                    df2 = df2.astype(float)
                                    #**********************************************************
                                    combinedwave1 = f'{wave1start},{wave1end},{wave1standard}'
                                    combinedwave2 = f'{wave2start},{wave2end},{wave2standard}'
                                    #print(combinedwave1)
                                    ws1.cell(row = 2,column = 1).value = combinedwave1
                                    ws2.cell(row = 2,column = 1).value = combinedwave1
                                    ws1.cell(row = 3,column = 1).value = combinedwave2
                                    ws2.cell(row = 3,column = 1).value = combinedwave2
                                    #***********************************************************
                                    #计算判定区域
                                    for n in range(0,3):
                                        cal1 = df1.iloc[wave1start-380+2:wave1end-380+2,n].max()  
                                        cal2 = df1.iloc[wave2start-380+2:wave2end-380+2,n].max()  
                                        cal3 = df2.iloc[wave1start-380+2:wave1end-380+2,n].max()  
                                        cal4 = df2.iloc[wave2start-380+2:wave2end-380+2,n].max() 
                                        if cal1 <= wave1standard and cal2 <= wave2standard :
                                            ws1.cell(row = 1,column = n+2+3*m).value = "OK"
                                            ws1.cell(row = 1,column = n+2+3*m).alignment = alignment
                                        else:
                                            ws1.cell(row = 1,column = n+2+3*m).value = "NG"
                                            ws1.cell(row = 1,column = n+2+3*m).alignment = alignment

                                        if cal3 <= wave1standard and cal4 <= wave2standard :
                                            ws2.cell(row = 1,column = n+2+3*m).value = "OK"
                                            ws2.cell(row = 1,column = n+2+3*m).alignment = alignment
                                        else:
                                            ws2.cell(row = 1,column = n+2+3*m).value = "NG"
                                            ws2.cell(row = 1,column = n+2+3*m).alignment = alignment
                                        #print(ave1,ave2)
                                        ws1.cell(row = 2,column = n+2+3*m).value = cal1
                                        ws1.cell(row = 3,column = n+2+3*m).value = cal2
                                        ws2.cell(row = 2,column = n+2+3*m).value = cal3
                                        ws2.cell(row = 3,column = n+2+3*m).value = cal4
                                        continue
                                    #文件名输出区域
                                    ws1.cell(row = 5,column = 2+3*m).value = last_part   
                                    ws2.cell(row = 5,column = 2+3*m).value = last_part                  
                                    #标题输出区域(data1~data6)
                                    for k,title in enumerate(titlesS1,2):                    
                                        ws1.cell(row = 6,column = k+3*m).value = title
                                        ws1.cell(row = 6,column = k+3*m).alignment = alignment
                                        continue
                                    for k,title in enumerate(titlesS2,2):                    
                                        ws2.cell(row = 6,column = k+3*m).value = title
                                        ws2.cell(row = 6,column = k+3*m).alignment = alignment
                                        continue
                                    #源数据输出区域
                                    for i ,row in df1.iterrows():
                                        #print(i)
                                        for j ,value in enumerate(row,start=1):
                                            ws1.cell(row = i+7,column = j+1+3*m).value = value

                                    for i ,row in df2.iterrows():
                                        #print(i)
                                        for j ,value in enumerate(row,start=1):
                                            ws2.cell(row = i+7,column = j+1+3*m).value = value

                                else:
                                    print("非指定指令")
                            except Exception as e:
                                print(e)

                if user_input == "S1":
                    output_file_path=os.path.join(current_path,f'Summaryout{item}{user_input}.xlsx')
                    wb.remove(wb['S2'])
                    wb.remove(wb['S1S2'])
                    wb.remove(wb['Sheet'])
                    wb.save(output_file_path)

                elif user_input == "S2":
                    output_file_path=os.path.join(current_path,f'Summaryout{item}{user_input}.xlsx')
                    wb.remove(wb['S1'])
                    wb.remove(wb['S1S2'])
                    wb.remove(wb['Sheet'])
                    wb.save(output_file_path)

                elif user_input == "S1S2":
                    output_file_path=os.path.join(current_path,f'Summaryout{item}{user_input}.xlsx')
                    wb.remove(wb['S1'])
                    wb.remove(wb['S2'])
                    wb.remove(wb['Sheet'])            
                    wb.save(output_file_path)

                elif user_input == "S1&S2":
                    output_file_path=os.path.join(current_path,f'Summaryout{item}{user_input}.xlsx')
                    wb.remove(wb['S1S2'])
                    wb.remove(wb['Sheet'])
                    wb.save(output_file_path)

                else :
                    print("请检查程序!")  
            elif standerd_input == "N":
                parent_path = zippath
                file_flag = '.zip'
                flag = 1
                while flag:
                    for root,dirs,files in os.walk(parent_path):
                        for name in files:
                            if name.endswith(file_flag):
                                new_ws = start_dir_make(root,name.replace(file_flag,""))
                                zip_path = os.path.join(root,name)
                                flag = decompress(zip_path,new_ws)
                                del_old_zip(zip_path)
                                rem_dir_extra(root,name.replace(file_flag,""))
                                print(f'{root}\\{name}'.join(['文件:','\n解压完成\n']))
                rem_dir_extra(os.path.split(parent_path)[0],os.path.split(parent_path)[1])
                print("解压完成,请检查!!")
                print("请输入汇总model,S1或S2或S1S2或S1&S2")
                wb = Workbook()
                #ws = wb.active
                #ws.title="Summary"
                ws = wb.create_sheet("S1S2")
                ws1 = wb.create_sheet("S1")
                ws2 = wb.create_sheet("S2")
                #设置所有单元格的对齐方式为居中
                alignment = Alignment(horizontal='center',vertical='center')   
                titlesS1 = ['data1','data2','data3']    
                titlesS2 = ['data4','data5','data6']  
                titlesS1S2 = ['data1','data2','data3','data4','data5','data6']
                #第一列波段设置区域
                ws.cell(row = 5,column = 1).value = '文件名'
                ws.cell(row = 5,column = 1).alignment = alignment
                ws.cell(row = 6,column = 1).value = 'wave'
                ws.cell(row = 6,column = 1).alignment = alignment
                
                ws1.cell(row = 5,column = 1).value = '文件名'
                ws1.cell(row = 5,column = 1).alignment = alignment
                ws1.cell(row = 6,column = 1).value = 'wave'
                ws1.cell(row = 6,column = 1).alignment = alignment
                
                ws2.cell(row = 5,column = 1).value = '文件名'
                ws2.cell(row = 5,column = 1).alignment = alignment
                ws2.cell(row = 6,column = 1).value = 'wave'
                ws2.cell(row = 6,column = 1).alignment = alignment
                for l in range(380,1051):
                    ws.cell(l-373,1).value = l
                    ws.cell(l-373,1).alignment = alignment
                    
                    ws1.cell(l-373,1).value = l
                    ws1.cell(l-373,1).alignment = alignment
                    
                    ws2.cell(l-373,1).value = l
                    ws2.cell(l-373,1).alignment = alignment
                    continue
                #*****************************************************************
                #读取指定文件夹
                #file_dir = os.getcwd()
                file_dir = parent_path
                current_path = os.path.dirname(os.path.abspath(__file__))
                #file_dir = r"D:\Users\gxcaoty\Desktop\39526-905\一车间"
                root, dirs, files = get_allfile_msg(file_dir)
                allFile_url = get_allfile_url(root, files)
                dir_numbers = len(dirs)    #file_dir下的文件夹个数
                
                user_input = input("请输入S1或S2或S1S2或S1&S2\n")
                count = 0
                for root,dirs,files in os.walk(file_dir):
                    for file_path in glob.glob(os.path.join(root,'*.csv')):
                        if item in file_path and 'Add' not in file_path:
                            print(file_path)
                            xl = file_path
                            count += 1
                            c = count
                            m = c - 1
                            print(f"共发现 {m} 个文件!")
                            #print(files_chose)
                            try:
                                last_part = extract_last_part_of_path(xl)
                                #print(last_part)  #filename为文件名
                                filename = xl 
                                csv_data = read_csv_file(filename)
                                df = csv_data
                                if user_input == "S1":
                                    df = df.iloc[:,1:4]
                                    df = df.astype(float)
                                    #print(df)
                                    #文件名输出区域
                                    ws1.cell(row = 5,column = 2+3*m).value = last_part                     
                                    #标题输出区域(data1~data6)
                                    for k,title in enumerate(titlesS1,2):                    
                                        ws1.cell(row = 6,column = k+3*m).value = title
                                        ws1.cell(row = 6,column = k+3*m).alignment = alignment
                                        continue
                                    #源数据输出区域
                                    for i ,row in df.iterrows():
                                        #print(i)
                                        for j ,value in enumerate(row,start=1):
                                            ws1.cell(row = i+7,column = j+1+3*m).value = value

                                elif user_input == "S2":
                                    df = df.iloc[:,4:7]
                                    df = df.astype(float)
                                    #print(df)
                                    #文件名输出区域
                                    ws2.cell(row = 5,column = 2+3*m).value = last_part                     
                                    #标题输出区域(data1~data6)
                                    for k,title in enumerate(titlesS2,2):                    
                                        ws2.cell(row = 6,column = k+3*m).value = title
                                        ws2.cell(row = 6,column = k+3*m).alignment = alignment
                                        continue
                                    #源数据输出区域
                                    for i ,row in df.iterrows():
                                        #print(i)
                                        for j ,value in enumerate(row,start=1):
                                            ws2.cell(row = i+7,column = j+1+3*m).value = value

                                elif user_input == "S1S2":
                                    df = df.iloc[:,1:7]
                                    df = df.astype(float)
                                    #print(df)
                                    #文件名输出区域
                                    ws.cell(row = 5,column = 2+6*m).value = last_part                     
                                    #标题输出区域(data1~data6)
                                    for k,title in enumerate(titlesS1S2,2):                    
                                        ws.cell(row = 6,column = k+6*m).value = title
                                        ws.cell(row = 6,column = k+6*m).alignment = alignment
                                        continue
                                    #源数据输出区域
                                    for i ,row in df.iterrows():
                                        #print(i)
                                        for j ,value in enumerate(row,start=1):
                                            ws.cell(row = i+7,column = j+1+6*m).value = value

                                elif user_input == "S1&S2":
                                    #df = df.iloc[:,1:7]
                                    df1 = df.iloc[:,1:4]
                                    df2 = df.iloc[:,4:7]
                                    df1 = df1.astype(float)
                                    df2 = df2.astype(float)
                                    #文件名输出区域
                                    ws1.cell(row = 5,column = 2+3*m).value = last_part   
                                    ws2.cell(row = 5,column = 2+3*m).value = last_part                  
                                    #标题输出区域(data1~data6)
                                    for k,title in enumerate(titlesS1,2):                    
                                        ws1.cell(row = 6,column = k+3*m).value = title
                                        ws1.cell(row = 6,column = k+3*m).alignment = alignment
                                        continue
                                    for k,title in enumerate(titlesS2,2):                    
                                        ws2.cell(row = 6,column = k+3*m).value = title
                                        ws2.cell(row = 6,column = k+3*m).alignment = alignment
                                        continue
                                    #源数据输出区域
                                    for i ,row in df1.iterrows():
                                        #print(i)
                                        for j ,value in enumerate(row,start=1):
                                            ws1.cell(row = i+7,column = j+1+3*m).value = value

                                    for i ,row in df2.iterrows():
                                        #print(i)
                                        for j ,value in enumerate(row,start=1):
                                            ws2.cell(row = i+7,column = j+1+3*m).value = value

                                else:
                                    print("非指定指令")
                            except Exception as e:
                                print(e)

                if user_input == "S1":
                    output_file_path=os.path.join(current_path,f'Summaryout{item}{user_input}.xlsx')
                    wb.remove(wb['S2'])
                    wb.remove(wb['S1S2'])
                    wb.remove(wb['Sheet'])
                    wb.save(output_file_path)

                elif user_input == "S2":
                    output_file_path=os.path.join(current_path,f'Summaryout{item}{user_input}.xlsx')
                    wb.remove(wb['S1'])
                    wb.remove(wb['S1S2'])
                    wb.remove(wb['Sheet'])
                    wb.save(output_file_path)

                elif user_input == "S1S2":
                    output_file_path=os.path.join(current_path,f'Summaryout{item}{user_input}.xlsx')
                    wb.remove(wb['S1'])
                    wb.remove(wb['S2'])
                    wb.remove(wb['Sheet'])            
                    wb.save(output_file_path)

                elif user_input == "S1&S2":
                    output_file_path=os.path.join(current_path,f'Summaryout{item}{user_input}.xlsx')
                    wb.remove(wb['S1S2'])
                    wb.remove(wb['Sheet'])
                    wb.save(output_file_path)

                else :
                    print("请检查程序!")  
            else:
                print("非指定指令!")
        elif mode == "4":
                parent_path = input("请输入汇总路径:路径格式:39827A/901/000b1664-57f5-4de9-adf2-c029898a669c \n")
                #parent_path = r"D:\Users\gxcaoty\Desktop\桌面文件\2025.2.21\TO柳洋text\39827A"
                file_dir = parent_path
                item_1 = input("请输入汇总机种:(例如:39642A)\n")
                item_p = input("请输入汇总P数: (例如:5)\n")
                item_2 = int('90'+str(item_p))
                standerd_input = input("是否需要标准? \n")
                if standerd_input == "Y":
                    folder_count = sum(1 for _ in os.scandir(file_dir) if _.is_dir())
                    print(folder_count)
                    user_input = input("请输入汇总model,S1或S2或S1S2或S1&S2 \n")
                    #standerd_input = input("是否需要标准?\n")   
                    with os.scandir(file_dir) as it:
                        for entry in it:
                            if entry.is_dir():
                                print(entry.name)
                                file_dir1 = file_dir+'\\'+entry.name
                                print(file_dir1)
                                file_dir2 = file_dir1.replace("\\","")
                                count = 0

                                wb = Workbook()
                                #ws = wb.active
                                #ws.title="Summary"
                                ws = wb.create_sheet("S1S2")
                                ws1 = wb.create_sheet("S1")
                                ws2 = wb.create_sheet("S2")        
                                #设置所有单元格的对齐方式为居中
                                alignment = Alignment(horizontal='center',vertical='center')   
                                titlesS1 = ['data1','data2','data3']    
                                titlesS2 = ['data4','data5','data6']  
                                titlesS1S2 = ['data1','data2','data3','data4','data5','data6']
                                #第一列波段设置区域
                                ws.cell(row = 1,column = 1).value = '判定'
                                ws.cell(row = 1,column = 1).alignment = alignment
                                ws.cell(row = 5,column = 1).value = '文件名'
                                ws.cell(row = 5,column = 1).alignment = alignment
                                ws.cell(row = 6,column = 1).value = 'wave'
                                ws.cell(row = 6,column = 1).alignment = alignment
                                
                                ws1.cell(row = 1,column = 1).value = '判定'
                                ws1.cell(row = 1,column = 1).alignment = alignment
                                ws1.cell(row = 5,column = 1).value = '文件名'
                                ws1.cell(row = 5,column = 1).alignment = alignment
                                ws1.cell(row = 6,column = 1).value = 'wave'
                                ws1.cell(row = 6,column = 1).alignment = alignment
                                
                                ws2.cell(row = 1,column = 1).value = '判定'
                                ws2.cell(row = 1,column = 1).alignment = alignment
                                ws2.cell(row = 5,column = 1).value = '文件名'
                                ws2.cell(row = 5,column = 1).alignment = alignment
                                ws2.cell(row = 6,column = 1).value = 'wave'
                                ws2.cell(row = 6,column = 1).alignment = alignment
                                for l in range(380,1051):
                                    ws.cell(l-373,1).value = l
                                    ws.cell(l-373,1).alignment = alignment
                                    
                                    ws1.cell(l-373,1).value = l
                                    ws1.cell(l-373,1).alignment = alignment
                                    
                                    ws2.cell(l-373,1).value = l
                                    ws2.cell(l-373,1).alignment = alignment
                                    continue
                                #*******************************

                                for root,dirs,files in os.walk(file_dir1):
                                    for file_path in glob.glob(os.path.join(root,'*.csv')):
                                        for j in range(901,item_2+1):
                                            item = item_1 + '-'  + str(j)
                                            if item in file_path and 'Add' not in file_path:
                                                print(file_path)
                                                xl = file_path
                                                count += 1
                                                c = count
                                                m = c - 1
                                                print(f"共发现 {c} 个文件!")
                                                try:
                                                    last_part = extract_last_part_of_path(xl)
                                                    filename = xl 
                                                    csv_data = read_csv_file(filename)
                                                    df = csv_data
                                                    if user_input == "S1&S2":
                                                        df1 = df.iloc[:,1:4]
                                                        df2 = df.iloc[:,4:7]
                                                        df1 = df1.astype(float)
                                                        df2 = df2.astype(float)
                                                        #**********************************************************
                                                        combinedwave1 = f'{wave1start},{wave1end},{wave1standard}'
                                                        combinedwave2 = f'{wave2start},{wave2end},{wave2standard}'
                                                        #print(combinedwave1)
                                                        ws1.cell(row = 2,column = 1).value = combinedwave1
                                                        ws2.cell(row = 2,column = 1).value = combinedwave1
                                                        ws1.cell(row = 3,column = 1).value = combinedwave2
                                                        ws2.cell(row = 3,column = 1).value = combinedwave2
                                                        #***********************************************************
                                                        #计算判定区域
                                                        for n in range(0,3):
                                                            cal1 = df1.iloc[wave1start-380+2:wave1end-380+2,n].max()  
                                                            cal2 = df1.iloc[wave2start-380+2:wave2end-380+2,n].max()  
                                                            cal3 = df2.iloc[wave1start-380+2:wave1end-380+2,n].max()  
                                                            cal4 = df2.iloc[wave2start-380+2:wave2end-380+2,n].max() 
                                                            if cal1 <= wave1standard and cal2 <= wave2standard :
                                                                ws1.cell(row = 1,column = n+2+3*m).value = "OK"
                                                                ws1.cell(row = 1,column = n+2+3*m).alignment = alignment
                                                            else:
                                                                ws1.cell(row = 1,column = n+2+3*m).value = "NG"
                                                                ws1.cell(row = 1,column = n+2+3*m).alignment = alignment

                                                            if cal3 <= wave1standard and cal4 <= wave2standard :
                                                                ws2.cell(row = 1,column = n+2+3*m).value = "OK"
                                                                ws2.cell(row = 1,column = n+2+3*m).alignment = alignment
                                                            else:
                                                                ws2.cell(row = 1,column = n+2+3*m).value = "NG"
                                                                ws2.cell(row = 1,column = n+2+3*m).alignment = alignment
                                                            #print(ave1,ave2)
                                                            ws1.cell(row = 2,column = n+2+3*m).value = cal1
                                                            ws1.cell(row = 3,column = n+2+3*m).value = cal2
                                                            ws2.cell(row = 2,column = n+2+3*m).value = cal3
                                                            ws2.cell(row = 3,column = n+2+3*m).value = cal4
                                                            continue
                                                        #文件名输出区域
                                                        ws1.cell(row = 5,column = 2+3*m).value = last_part   
                                                        ws2.cell(row = 5,column = 2+3*m).value = last_part                  
                                                        #标题输出区域(data1~data6)
                                                        for k,title in enumerate(titlesS1,2):                    
                                                            ws1.cell(row = 6,column = k+3*m).value = title
                                                            ws1.cell(row = 6,column = k+3*m).alignment = alignment
                                                            continue
                                                        for k,title in enumerate(titlesS2,2):                    
                                                            ws2.cell(row = 6,column = k+3*m).value = title
                                                            ws2.cell(row = 6,column = k+3*m).alignment = alignment
                                                            continue
                                                        #源数据输出区域
                                                        for i ,row in df1.iterrows():
                                                            #print(i)
                                                            for j ,value in enumerate(row,start=1):
                                                                ws1.cell(row = i+7,column = j+1+3*m).value = value

                                                        for i ,row in df2.iterrows():
                                                            #print(i)
                                                            for j ,value in enumerate(row,start=1):
                                                                ws2.cell(row = i+7,column = j+1+3*m).value = value
                                                    elif user_input == "S1S2":
                                                        df = df.iloc[:,1:7]
                                                        df = df.astype(float)
                                                        #**********************************************************
                                                        combinedwave1 = f'{wave1start},{wave1end},{wave1standard}'
                                                        combinedwave2 = f'{wave2start},{wave2end},{wave2standard}'
                                                        #print(combinedwave1)
                                                        ws.cell(row = 2,column = 1).value = combinedwave1
                                                        ws.cell(row = 3,column = 1).value = combinedwave2
                                                        #***********************************************************
                                                        #计算判定区域
                                                        for n in range(0,6):
                                                            cal1 = df.iloc[wave1start-380+2:wave1end-380+2,n].max()  
                                                            cal2 = df.iloc[wave2start-380+2:wave2end-380+2,n].max()  
                                                            if cal1 <= wave1standard and cal2 <= wave2standard :
                                                                ws.cell(row = 1,column = n+2+6*m).value = "OK"
                                                                ws.cell(row = 1,column = n+2+6*m).alignment = alignment
                                                            else:
                                                                ws.cell(row = 1,column = n+2+6*m).value = "NG"
                                                                ws.cell(row = 1,column = n+2+6*m).alignment = alignment
                                                            #print(ave1,ave2)
                                                            ws.cell(row = 2,column = n+2+6*m).value = cal1
                                                            ws.cell(row = 3,column = n+2+6*m).value = cal2
                                                            continue
                                                        #文件名输出区域
                                                        ws.cell(row = 5,column = 2+6*m).value = last_part                 
                                                        #标题输出区域(data1~data6)
                                                        for k,title in enumerate(titlesS1S2,2):                    
                                                            ws.cell(row = 6,column = k+6*m).value = title
                                                            ws.cell(row = 6,column = k+6*m).alignment = alignment
                                                            continue
                                                        #源数据输出区域
                                                        for i ,row in df.iterrows():
                                                            #print(i)
                                                            for j ,value in enumerate(row,start=1):
                                                                ws.cell(row = i+7,column = j+1+6*m).value = value

                                                    else :
                                                        print("指定错误,请重新运行!")
                                                except Exception as e:
                                                    print(e) 
                                if user_input == "S1&S2":
                                    output_file_path=os.path.join(entry,f'Summaryout{item_1}{"-"}{user_input}{entry.name}.xlsx')
                                    wb.remove(wb['S1S2'])
                                    wb.remove(wb['Sheet'])
                                    wb.save(output_file_path)
                                elif user_input == "S1S2":
                                    output_file_path=os.path.join(entry,f'Summaryout{item_1}{"-"}{user_input}{entry.name}.xlsx')
                                    wb.remove(wb['S1'])
                                    wb.remove(wb['S2'])
                                    wb.remove(wb['Sheet'])
                                    wb.save(output_file_path)
                                else:
                                    print("指令错误,请重新运行!")

                elif standerd_input == "N":
                    print("请输入汇总model,S1或S2或S1S2或S1&S2") 
                    folder_count = sum(1 for _ in os.scandir(file_dir) if _.is_dir())
                    print(folder_count)
                    user_input = input("请输入汇总model,S1或S2或S1S2或S1&S2 \n")
                    #standerd_input = input("是否需要标准?\n")   
                    with os.scandir(file_dir) as it:
                        for entry in it:
                            if entry.is_dir():
                                print(entry.name)
                                file_dir1 = file_dir+'\\'+entry.name
                                print(file_dir1)
                                file_dir2 = file_dir1.replace("\\","")
                                count = 0
                                wb = Workbook()
                                #ws = wb.active
                                #ws.title="Summary"
                                ws = wb.create_sheet("S1S2")
                                ws1 = wb.create_sheet("S1")
                                ws2 = wb.create_sheet("S2")        
                                #设置所有单元格的对齐方式为居中
                                alignment = Alignment(horizontal='center',vertical='center')   
                                titlesS1 = ['data1','data2','data3']    
                                titlesS2 = ['data4','data5','data6']  
                                titlesS1S2 = ['data1','data2','data3','data4','data5','data6']
                                #第一列波段设置区域
                                ws.cell(row = 5,column = 1).value = '文件名'
                                ws.cell(row = 5,column = 1).alignment = alignment
                                ws.cell(row = 6,column = 1).value = 'wave'
                                ws.cell(row = 6,column = 1).alignment = alignment
                                
                                ws1.cell(row = 5,column = 1).value = '文件名'
                                ws1.cell(row = 5,column = 1).alignment = alignment
                                ws1.cell(row = 6,column = 1).value = 'wave'
                                ws1.cell(row = 6,column = 1).alignment = alignment
                                
                                ws2.cell(row = 5,column = 1).value = '文件名'
                                ws2.cell(row = 5,column = 1).alignment = alignment
                                ws2.cell(row = 6,column = 1).value = 'wave'
                                ws2.cell(row = 6,column = 1).alignment = alignment
                                for l in range(380,1051):
                                    ws.cell(l-373,1).value = l
                                    ws.cell(l-373,1).alignment = alignment
                                    
                                    ws1.cell(l-373,1).value = l
                                    ws1.cell(l-373,1).alignment = alignment
                                    
                                    ws2.cell(l-373,1).value = l
                                    ws2.cell(l-373,1).alignment = alignment
                                    continue
                                #*******************************

                                for root,dirs,files in os.walk(file_dir1):
                                    for file_path in glob.glob(os.path.join(root,'*.csv')):
                                        for j in range(901,item_2+1):
                                            item = item_1 + '-'  + str(j)
                                            if item in file_path and 'Add' not in file_path:
                                                print(file_path)
                                                xl = file_path
                                                count += 1
                                                c = count
                                                m = c - 1
                                                print(f"共发现 {c} 个文件!")
                                                try:
                                                    last_part = extract_last_part_of_path(xl)
                                                    filename = xl 
                                                    csv_data = read_csv_file(filename)
                                                    df = csv_data
                                                    if user_input == "S1&S2":
                                                        df1 = df.iloc[:,1:4]
                                                        df2 = df.iloc[:,4:7]
                                                        df1 = df1.astype(float)
                                                        df2 = df2.astype(float)

                                                        #文件名输出区域
                                                        ws1.cell(row = 5,column = 2+3*m).value = last_part   
                                                        ws2.cell(row = 5,column = 2+3*m).value = last_part                  
                                                        #标题输出区域(data1~data6)
                                                        for k,title in enumerate(titlesS1,2):                    
                                                            ws1.cell(row = 6,column = k+3*m).value = title
                                                            ws1.cell(row = 6,column = k+3*m).alignment = alignment
                                                            continue
                                                        for k,title in enumerate(titlesS2,2):                    
                                                            ws2.cell(row = 6,column = k+3*m).value = title
                                                            ws2.cell(row = 6,column = k+3*m).alignment = alignment
                                                            continue
                                                        #源数据输出区域
                                                        for i ,row in df1.iterrows():
                                                            #print(i)
                                                            for j ,value in enumerate(row,start=1):
                                                                ws1.cell(row = i+7,column = j+1+3*m).value = value

                                                        for i ,row in df2.iterrows():
                                                            #print(i)
                                                            for j ,value in enumerate(row,start=1):
                                                                ws2.cell(row = i+7,column = j+1+3*m).value = value
                                                    elif user_input == "S1S2":
                                                        df = df.iloc[:,1:7]
                                                        df = df.astype(float)
                                                        #文件名输出区域
                                                        ws.cell(row = 5,column = 2+6*m).value = last_part                 
                                                        #标题输出区域(data1~data6)
                                                        for k,title in enumerate(titlesS1S2,2):                    
                                                            ws.cell(row = 6,column = k+6*m).value = title
                                                            ws.cell(row = 6,column = k+6*m).alignment = alignment
                                                            continue
                                                        #源数据输出区域
                                                        for i ,row in df.iterrows():
                                                            #print(i)
                                                            for j ,value in enumerate(row,start=1):
                                                                ws.cell(row = i+7,column = j+1+6*m).value = value
                                                    else:
                                                        print("指令错误,请重新运行!")
                                                except Exception as e:
                                                    print(e) 
                                if user_input == "S1&S2":
                                    output_file_path=os.path.join(entry,f'Summaryout{item_1}{"-"}{user_input}{entry.name}.xlsx')
                                    wb.remove(wb['S1S2'])
                                    wb.remove(wb['Sheet'])
                                    wb.save(output_file_path)
                                elif user_input == "S1S2":
                                    output_file_path=os.path.join(entry,f'Summaryout{item_1}{"-"}{user_input}{entry.name}.xlsx')
                                    wb.remove(wb['S1'])
                                    wb.remove(wb['S2'])
                                    wb.remove(wb['Sheet'])
                                    wb.save(output_file_path)
                                else:
                                    print("指令错误,请重新运行!")
        else:
            print("非指定指令!!")
    else:
        print("非指定指令!!")

总结

分享:
要想自己做决定,就不要和别人比较。虽然说参考别人的意见可以避免独断,但是只要和别人的想法做比较就永远无法做到独立决断。


http://www.kler.cn/a/585217.html

相关文章:

  • 61.Harmonyos NEXT 图片预览组件之数据模型设计与实现
  • API自动化测试实战:Postman + Newman/Pytest的深度解析
  • 注意力机制,层归一化,RBA。KAN-ODE,小波KAN
  • 如何使用Postman,通过Mock的方式测试我们的API
  • 【python】一文掌握 Conda 指令 (anaconda备忘清单)
  • 端口转发、隧道与Pivoting技术详解及区别解析
  • 数据类型及sizeof,进制转换
  • 蓝桥杯 排序题目【算法赛】
  • Unity光线追踪移动端降级适配技术指南
  • Mybatis 框架学习
  • C# Type类中Name、FullName、Namespace、AssemblyQualifiedName的区别
  • 了解一下HTTP的短连接和长连接
  • 从波士顿动力到Figure AI:探寻人工智能驱动的机器人智能化
  • UdpClient
  • 什么是 MyBatis?
  • 基于卡尔曼滤波的雷达光电多目标航迹融合算法matlab仿真
  • 基于ssm的宠物医院信息管理系统(全套)
  • 线程池参数调优
  • 游戏引擎学习第152天
  • AI时代研究卷积神经网络(CNN)工具与方法