【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("非指定指令！！")
总结
分享：
要想自己做决定，就不要和别人比较。虽然说参考别人的意见可以避免独断，但是只要和别人的想法做比较就永远无法做到独立决断。