【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):