from ast import Delete
from asyncio.windows_events import NULL
from ctypes import memset
from errno import WSAECONNREFUSED
from itertools import count
import os #读取脚本所在文件夹路径
import openpyxl
from openpyxl import Workbook
import datetime #时间戳转换
from openpyxl.styles import colors
from openpyxl.styles import Font
from openpyxl.styles import PatternFill
#图表
from openpyxl.chart import (
LineChart,
Reference,
)
######################################################
recorder_configure_name='configuration file.txt'
#recorder_log_file
recorder_log_file_tag='thermal recorder log'
device_file_tag='Tempdata'
device_handle_file_tag='[new]'
recorder_store_name="recorder_gap.xlsx"
summary_name=''
#configure file order as follow
Recorder_start_time_timestamp=0
Chamber_start_time_timestamp=0
DUT_start_time_timestamp=0
#configure the probe
probe_blank_cols= 0
probe_number=0
#configure sheet name length
SN_len=0
#
Defualt_test_flag=1
#time_map
soak_time_conf=[]
test_arr_map=[]
#读取配置文件返回interval 值
def configure_txt_read():
global Recorder_start_time_timestamp
global Chamber_start_time_timestamp
global DUT_start_time_timestamp
global probe_blank_cols
global probe_number
global Defualt_test_flag
global soak_time_conf
#open configure txt
with open(recorder_configure_name, 'r') as file:
configuration = file.readlines()
Recorder_start_time=configuration[0][len("recorder:"):]
Chamber_start_time=configuration[1][len("chamber:"):]
DUT_start_time=configuration[2][len("DVT:"):]
probe_number=int(configuration[3][len("probe_number(1/2/4):"):])
probe_blank_cols=probe_number+1
#read the configuration time
if configuration[4][len("Defualt test?(Y/N):"):].strip()=='Y':Defualt_test_flag=1
else:Defualt_test_flag=0
print("Defualt_test_flag",Defualt_test_flag)
if (Defualt_test_flag==0):
begain_flag=0
for line in configuration:
print("test"+line)
if line.strip()=="*end*":break
if(begain_flag==1):
soak_time_conf.append(line[len("soak_time1:"):].strip())
if line.strip()=="*begin*":begain_flag=1
if Debug_flag>=1:
print("Recorder_start_time:"+Recorder_start_time)
print("Chamber_start_time:"+Chamber_start_time)
print("DUT_start_time:"+DUT_start_time)
print("probe_number:",probe_number,"probe_blank_cols:",probe_blank_cols)
print("Defualt_test_flag:",Defualt_test_flag)
print("soak_time_conf:",soak_time_conf)
#转化为时间戳
#Recorder_start_time='2023-03-29 10:30:00'
dt = datetime.datetime.strptime(Recorder_start_time.strip(), "%Y-%m-%d %H:%M:%S")
Recorder_start_time_timestamp = dt.timestamp()
dt = datetime.datetime.strptime(Chamber_start_time.strip(), '%Y-%m-%d %H:%M:%S')
Chamber_start_time_timestamp = dt.timestamp()
dt = datetime.datetime.strptime(DUT_start_time.strip(), '%Y-%m-%d %H:%M:%S')
DUT_start_time_timestamp = dt.timestamp()
if(Recorder_start_time_timestamp>=DUT_start_time_timestamp):
deviation=int((Chamber_start_time_timestamp-Recorder_start_time_timestamp)/60)
if Debug_flag>=1:
print("interval:",deviation)
else:
#device+15 分钟之后与 与recorder的差值
deviation=int((DUT_start_time_timestamp-Recorder_start_time_timestamp)/60)+15
print("=====configure-part finish=====")
return deviation
def open_and_save_recorder(deviation):
global DUT_start_time_timestamp
if Debug_flag==1:
print("DUT_start_time_timestamp:",DUT_start_time_timestamp)
print("path:"+script_path)
timestamp_recorder=[]
timestamp_recorder_for_save=[]
temperature_recorder_chanel1=[]
temperature_recorder_chanel2=[]
temperature_recorder_chanel3=[]
temperature_recorder_chanel4=[]
deviation=deviation+4 #thermal recorder <自带几个空行>
interval_count=0
row=0
#添加首行
#open recorder log and find temperature point
for filename in os.listdir(script_path):
#print("find:"+filename)
if recorder_log_file_tag in filename:
with open(filename, 'r') as file:
recorder_data_line = file.readlines()
#print(recorder_data_line)
for line in recorder_data_line:
#print(row)
if ((row-deviation) % 15 == 0) or (row==deviation):
if(probe_number>=1):
recorder_data="%.2f"%(float(recorder_data_line[row].split(',')[1]))
temperature_recorder_chanel1.append(recorder_data)
if(probe_number>=2):
recorder_data="%.2f"%(float(recorder_data_line[row].split(',')[3]))
temperature_recorder_chanel2.append(recorder_data)
if(probe_number>=4):
recorder_data="%.2f"%(float(recorder_data_line[row].split(',')[5]))
temperature_recorder_chanel3.append(recorder_data)
recorder_data="%.2f"%(float(recorder_data_line[row].split(',')[7]))
temperature_recorder_chanel4.append(recorder_data)
#if have more chanel probe,you can add them here
#########################
timestamp_recorder.append(DUT_start_time_timestamp+15*60*interval_count)
interval_count=interval_count+1
row=row+1
break#if find the file ,then break
if Debug_flag>=2:
print("temperature_recorder_chanel1:",temperature_recorder_chanel1)
print("temperature_recorder_chanel2:",temperature_recorder_chanel2)
print("temperature_recorder_chanel3:",temperature_recorder_chanel3)
print("temperature_recorder_chanel4:",temperature_recorder_chanel4)
#pepare for excel
workbook = Workbook()
sheet = workbook.active
#save the temperature ,the time and timestamp
for timestamp in timestamp_recorder:
dt=datetime.datetime.fromtimestamp(timestamp)
timestamp_recorder_for_save.append(dt.strftime('%m-%d %H:%M'))
#存入
sheet.cell(row=1, column=1, value="Test_Time")
if probe_number>=1:sheet.cell(row=1, column=2, value="Recorder_Chanel_A")
if probe_number>=2:sheet.cell(row=1, column=3, value="Recorder_Chanel_B")
if probe_number>=4:
sheet.cell(row=1, column=4, value="Recorder_Chanel_C")
sheet.cell(row=1, column=5, value="Recorder_Chanel_D")
for i in range(len(timestamp_recorder_for_save)):
sheet.cell(row=i+2, column=1, value=(timestamp_recorder_for_save[i]))
if probe_number>=1:
sheet.cell(row=i+2, column=2, value=float(temperature_recorder_chanel1[i]))
if probe_number>=2:
sheet.cell(row=i+2, column=3, value=float(temperature_recorder_chanel2[i]))
if probe_number>=4:
sheet.cell(row=i+2, column=4, value=float(temperature_recorder_chanel3[i]))
sheet.cell(row=i+2, column=5, value=float(temperature_recorder_chanel4[i]))
workbook.save(recorder_store_name)
print("=====recorder-part finish=====")
def device_timstamp_interval_handle():
column_list = []
Delete_row_number_in_begaining=0
intervel_list_for_save=[]
#find the temperature file
for filename in os.listdir(script_path):
intervel_list_for_save.clear()
if device_file_tag in filename:
if Debug_flag>=1:
print("find:"+filename)
wb = openpyxl.load_workbook(filename)
sheet = wb.active
column_list.clear()
#从一个文件中读取数值存到另一个文件
for col in sheet.iter_cols(min_col=1, max_col=1):
for cell in col:
column_list.append(cell.value)
#coupute the interval and save data
if Debug_flag>=1:
print("time_stamp_list_lengh:",len(column_list))
for i in range(len(column_list)):
if(i<(len(column_list)-2)):
i=i+1
#print(column_list[i],column_list[i+1])
intervel_list_for_save.append(round(int((column_list[i])-int(column_list[i+1]))/60000))
#conpute
sheet.cell(row=1, column=3, value="sample interval")
for i in range(0,len(intervel_list_for_save)):
sheet.cell(row=i+2, column=3, value=(intervel_list_for_save[i]))
if(Debug_flag>=2):
print("intervel_list_for_save:",intervel_list_for_save)
#find the row to delete #此处没有含有标题行
Delete_row_number_in_begaining=0
for i in range(0,len(intervel_list_for_save)):
if (intervel_list_for_save[i] != (-15)):
break
Delete_row_number_in_begaining=Delete_row_number_in_begaining+1
if(Delete_row_number_in_begaining==len(intervel_list_for_save)):
Delete_row_number_in_begaining=0 #如果遍历到底都没有出现间隔则不删除任何行
else:
Delete_row_number_in_begaining=Delete_row_number_in_begaining+2#一个为title 一个为数组, 删掉的实际上是excel的row+2
#判断Missdata 误删
if(Delete_row_number_in_begaining>10):
Delete_row_number_in_begaining=0#如果删除行>8行,时间长度为2小时，则作废
if Debug_flag>=1:
print("Delete_row_number_in_begaining:",Delete_row_number_in_begaining)
#***************************bug***********************************************
#delet the row number #如果最开始的数据就可用的话，则会出现的删除Miss data 部分
for delet_line in range(2,Delete_row_number_in_begaining+1):#前闭后开 需要删除的序列
if(Debug_flag>=1):
print("delete row:",delet_line)
print("the tempdate in delete row:",(sheet.cell(row=2, column=2).value))
sheet.delete_rows(2)#保留第一行title,删除变化的第一组数据
wb.save(device_handle_file_tag+filename)
print("=====tempstamp interval finish=====")
def copy_recorder_data_to_device_data_and_get_difference():
#打开一个已知的文件读取相关数据到另一个已知的文件
#open the recorderdata
wb = openpyxl.load_workbook(recorder_store_name)
recorder_sheet = wb.active
column_time=recorder_sheet['A']
column_temperature_ch1=recorder_sheet['B']
column_temperature_ch2=recorder_sheet['C']
column_temperature_ch3=recorder_sheet['D']
column_temperature_ch4=recorder_sheet['E']
#insert the blank for device data in
for filename in os.listdir(script_path):
if device_handle_file_tag in filename:
if(Debug_flag>=1):
print("find:"+filename)
wb = openpyxl.load_workbook(filename)
device_sheet = wb.active
for insert_blank in range(probe_blank_cols): #0,1循环两次
device_sheet.insert_cols(2) #在选定列的前面
#copy the time from recorder
for i in range(len(column_time)):
cell_value = column_time[i].value
if cell_value is not None:
device_sheet.cell(row=i+1, column=2, value=cell_value)
#copy the different chanel from recorder
if probe_number>=1:
for i in range(len(column_temperature_ch1)):
cell_value = column_temperature_ch1[i].value
if cell_value is not None:
device_sheet.cell(row=i+1, column=3, value=cell_value)
if probe_number>=2:
for i in range(len(column_temperature_ch2)):
cell_value = column_temperature_ch2[i].value
if cell_value is not None:
device_sheet.cell(row=i+1, column=4, value=cell_value)
if probe_number>=4:
for i in range(len(column_temperature_ch3)):
cell_value = column_temperature_ch3[i].value
if cell_value is not None:
device_sheet.cell(row=i+1, column=5, value=cell_value)
cell_value = column_temperature_ch4[i].value
if cell_value is not None:
device_sheet.cell(row=i+1, column=6, value=cell_value)
#if you have more probe date to copy ,you can add here
#get the difference
if probe_number==1:
device_sheet.cell(row=1, column=4+probe_blank_cols, value="chanel_A_difference")
tempr_device=device_sheet['D']
tempr_recorder=device_sheet['C']
for i in range(1,len(tempr_device)):
device_cell_value = tempr_device[i].value
recorder_cell_value = tempr_recorder[i].value
#print("device",device_cell_value)
#print("recorder",recorder_cell_value)
if device_cell_value is not None and recorder_cell_value is not None:
device_sheet.cell(row=i+1, column=4+probe_blank_cols, value=round((float(device_cell_value)-float(recorder_cell_value)),3))
if probe_number==2: #c,d,e
device_sheet.cell(row=1, column=4+probe_blank_cols, value="chanel_A_difference")
device_sheet.cell(row=1, column=5+probe_blank_cols, value="chanel_B_difference")
tempr_recorder=device_sheet['C']
tempr_recorder2=device_sheet['D']
tempr_device=device_sheet['E']
for i in range(1,len(tempr_device)):
device_cell_value = tempr_device[i].value
recorder_cell_value = tempr_recorder[i].value
recorder_cell_value2 = tempr_recorder2[i].value
#print("device",device_cell_value)
#print("recorder",recorder_cell_value)
if device_cell_value is not None and recorder_cell_value is not None:
#G, H
device_sheet.cell(row=i+1, column=4+probe_blank_cols, value=round((float(device_cell_value)-float(recorder_cell_value)),3))
device_sheet.cell(row=i+1, column=5+probe_blank_cols, value=round((float(device_cell_value)-float(recorder_cell_value2)),3))
if probe_number==4: #c,d,e
device_sheet.cell(row=1, column=4+probe_blank_cols, value="chanel_A_difference")
device_sheet.cell(row=1, column=5+probe_blank_cols, value="chanel_B_difference")
device_sheet.cell(row=1, column=6+probe_blank_cols, value="chanel_C_difference")
device_sheet.cell(row=1, column=7+probe_blank_cols, value="chanel_D_difference")
tempr_recorder=device_sheet['C']
tempr_recorder2=device_sheet['D']
tempr_recorder3=device_sheet['E']
tempr_recorder4=device_sheet['F']
tempr_device=device_sheet['G']
for i in range(1,len(tempr_device)):
device_cell_value = tempr_device[i].value
recorder_cell_value = tempr_recorder[i].value
recorder_cell_value2 = tempr_recorder2[i].value
recorder_cell_value3 = tempr_recorder3[i].value
recorder_cell_value4 = tempr_recorder4[i].value
#print("device",device_cell_value)
#print("recorder",recorder_cell_value)
if device_cell_value is not None and recorder_cell_value is not None:
#G, H
device_sheet.cell(row=i+1, column=4+probe_blank_cols, value=round((float(device_cell_value)-float(recorder_cell_value)),3))
device_sheet.cell(row=i+1, column=5+probe_blank_cols, value=round((float(device_cell_value)-float(recorder_cell_value2)),3))
device_sheet.cell(row=i+1, column=6+probe_blank_cols, value=round((float(device_cell_value)-float(recorder_cell_value3)),3))
device_sheet.cell(row=i+1, column=7+probe_blank_cols, value=round((float(device_cell_value)-float(recorder_cell_value4)),3))
wb.save(filename)
print("=====the cpnpy and differnce conpute finish=====")
def collect_all_the_excel_file_to_collection():
test_len=len(test_arr_map)+1 #加一行 title
cont=0
global summary_name
#find the file to collect
output_workbook = openpyxl.Workbook()
for filename in os.listdir(script_path):
if device_handle_file_tag in filename:
input_workbook = openpyxl.load_workbook(filename)
input_sheet = input_workbook['My sheet'] #所给的文件
filename=filename[len(device_handle_file_tag):len(device_handle_file_tag)+SN_len+3] #only save the first two character
if(Debug_flag>=1):
print("sheetname:"+filename)
output_sheet = output_workbook.create_sheet(title=filename)
output_sheet.sheet_properties.tabColor = '7FFFAA'#设置sheet颜色
cont=0
#only copy the effective duration
for row in input_sheet.iter_rows(values_only=True):
# copy
output_sheet.append(row)
cont=cont+1
if(cont==test_len):break
#save the file and and name with date
current_time = datetime.datetime.now()
date=str(current_time.month)+'-'+str(current_time.day)
output_workbook.remove(output_workbook['Sheet']) #删除初始项
summary_name='T-Sensor Data Conclusion'+date+'-V1'+'.xlsx'
output_workbook.save(summary_name)
#delete the no use file
if Debug_flag<2:
for filename in os.listdir(script_path):
if device_handle_file_tag in filename:
os.remove(filename)
print("=====integratration finish=====")
def find_test_data_and_effective_test_time():
global test_arr_map
soak_time=[]
effective_time=4
if Debug_flag>=1:
print("Defualt_test_flag",Defualt_test_flag)
#defualt test situation
if Defualt_test_flag == 1:
soak_time=[8,6,7]
#configure test situation
else:
for i in range(len(soak_time_conf)):
soak_time.append(int(int(soak_time_conf[i])/15))
for duration in range(len(soak_time)):
for i in range(soak_time[duration]):test_arr_map.append(0)
for i in range(effective_time):test_arr_map.append(1)
if Debug_flag>=1:
print("test time arr:",test_arr_map)
print("=====test_time_map finish=====")
def probe_average_and_color_handle(ws,effect_average,chanel,ID):
probe_ID=ID
worksheet=ws
probe_chanel=chanel
effective_chanel_average=effect_average
##############
effective_continue_number=0
count=0
effective_chanel_sum=0.0
average_end =1
for cell in probe_chanel[1:len(test_arr_map)+1]:
if Debug_flag>=2:
print("[color]probe_chanelA_difference:",cell.value,"average_flag:",test_arr_map[count] )
#对平均值的duration的判断
if Debug_flag>=1:
print("map_arr:",test_arr_map[count],"count:",count,"len(test_arr_map):",len(test_arr_map))
if cell.value is not None and test_arr_map[count] ==1:
#########################
average_end=0
effective_continue_number=effective_continue_number+1
if( count>len(test_arr_map)-2):
if(count==len(test_arr_map)-1):
if Debug_flag>=1:
print("add! num:",effective_continue_number,"valnue",cell.value)
effective_chanel_sum=float(effective_chanel_sum)+float(cell.value)#计算平均值\
if Debug_flag>=1:
print("sum:",effective_chanel_sum)
average_end=1
else:
if(test_arr_map[count]==1 and test_arr_map[count+1]==0):
if Debug_flag>=1:
print("add! num:",effective_continue_number,"valnue",cell.value)
effective_chanel_sum=float(effective_chanel_sum)+float(cell.value)#计算平均值
if Debug_flag>=1:
print("sum:",effective_chanel_sum)
average_end=1
if average_end ==0:
if Debug_flag>=1:
print("add! num:",effective_continue_number,"valnue",cell.value,)
effective_chanel_sum=float(effective_chanel_sum)+float(cell.value)#计算平均值
if Debug_flag>=1:
print("sum:",effective_chanel_sum)
if(average_end==1):
print("conpute sum:",effective_chanel_sum,"all_num",effective_continue_number,"result",round(effective_chanel_sum/effective_continue_number,3))
effective_chanel_average.append(round(effective_chanel_sum/effective_continue_number,3))
#复原
effective_continue_number=0# 计算完毕重新开始计数
effective_chanel_sum=0
#根据数值判断写入的颜色
for average in effective_chanel_average:
if abs(average)>0.5:
worksheet.cell(row=count+2, column=4+probe_ID+2*probe_number, value=average).fill=PatternFill('solid',fgColor='DB7093') #初始4个,每增加一个CHANEL,两行空格，下一行开始计数
else:
worksheet.cell(row=count+2, column=4+probe_ID+2*probe_number, value=average)
########################
#chanel_color
if abs(cell.value)<0.5:
cell.fill = PatternFill('solid',fgColor='32CD32')
else:
cell.fill = PatternFill('solid',fgColor='DB7093')
count=count+1
if Debug_flag>=1:
print("effective_chanel_average_chanel_%d:"%probe_ID,effective_chanel_average)
def effective_duration_and_differnce_average_handle():
green='32CD32'
effective_chanel_average=[]
effective_chanel_average_2=[]
effective_chanel_average_3=[]
effective_chanel_average_4=[]
difference_average_begain_cols_name=65
if Debug_flag>=1:
print("len:,get test_arr_map:",len(test_arr_map),test_arr_map)
print("summary_name:"+summary_name)
workbook = openpyxl.load_workbook(summary_name)
sheet_names = workbook.sheetnames
for sheet_name in sheet_names:
worksheet = workbook[sheet_name]
if Debug_flag>=1:
print("[color]sheet_handle:"+sheet_name)
#handle the time corlor
time_cols=worksheet['B']
count=0 #遍历的计数
for cell in time_cols[1:]: #第一列是标题
if Debug_flag>=2:
print("[color]time_valnue:",cell.value,test_arr_map[count])
if cell.value is not None and test_arr_map[count] ==1:
cell.font = Font(color=green)#008000/FF0000
count=count+1
#handle the probe corlor
if probe_number ==1: probe_chanel=worksheet['F']
if probe_number ==2:
probe_chanel=worksheet['G']
probe_chanel2=worksheet['H']
if probe_number ==4:
probe_chanel=worksheet['I']
probe_chanel2=worksheet['J']
probe_chanel3=worksheet['K']
probe_chanel4=worksheet['L']
#write the chanle avrage title
for column_i in range(4+2*probe_number+1,4+3*probe_number+1):
worksheet.cell(row=1, column=column_i ,value="Ch_"+chr(difference_average_begain_cols_name)+"_Diff_avg")
difference_average_begain_cols_name=difference_average_begain_cols_name+1
#clear the date every sheet before to compute
effective_chanel_average.clear()#每一次写入平均值之前都清除内容
effective_chanel_average_2.clear()
effective_chanel_average_3.clear()
effective_chanel_average_4.clear()
#one probe
if probe_number>=1:
probe_average_and_color_handle(worksheet,effective_chanel_average,probe_chanel,1)
#tow probes
if probe_number>=2:
probe_average_and_color_handle(worksheet,effective_chanel_average_2,probe_chanel2,2)
#four probes
if probe_number>=4:
probe_average_and_color_handle(worksheet,effective_chanel_average_3,probe_chanel3,3)
###chanel 4##
probe_average_and_color_handle(worksheet,effective_chanel_average_4,probe_chanel4,4)
#save
workbook.save(summary_name)
print("=====effective duration finish=====")
def decorate_format_and_chart():
the_cols_chr=65
the_cols_lenth=probe_number*3+1+3
workbook = openpyxl.load_workbook(summary_name)
sheet_names = workbook.sheetnames
# set the width
for sheet_name in sheet_names:
worksheet = workbook[sheet_name]
for i in range(the_cols_lenth):
worksheet.column_dimensions[chr(the_cols_chr+i)].width = 15
#set the chart
chart = LineChart()
chart.title = sheet_name+'temp_test_chart'
#data = openpyxl.chart.Reference(worksheet, min_row=3, max_row=len(test_arr_map)+1, min_col=2, max_col=3+probe_number)
categories = Reference(worksheet, min_col=2, min_row=2, max_row=worksheet.max_row)
chart.x_axis.title = 'Test_time'
chart.x_axis.majorGridlines = None
# the data range
data = Reference(worksheet, min_col=3, min_row=1, max_col=3+probe_number, max_row=worksheet.max_row)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
chart_row = worksheet.max_row+3
column = 'C'
cell = f'{column}{chart_row}'
worksheet.add_chart(chart, cell)
#worksheet.add_chart(chart,'A40')
workbook.save(summary_name)
print("=====decorate finish=====")
def delete_the_new_file_at_begain():
for filename in os.listdir(script_path):
if device_handle_file_tag in filename:
os.remove(filename)
print("=====clear the enviroment, finish=====")
Debug_flag=1 #0 no information
#1 main information
#2 specify information
#the script path
script_path =os.getcwd ()
print("==scipt_path :==", script_path)
delete_the_new_file_at_begain()
open_and_save_recorder(configure_txt_read())
device_timstamp_interval_handle()
copy_recorder_data_to_device_data_and_get_difference()
find_test_data_and_effective_test_time()
collect_all_the_excel_file_to_collection()
effective_duration_and_differnce_average_handle()
#decoration
decorate_format_and_chart()
print("=====ALL finish!=====")
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
