读取文件写入报表_for i in range(len(ilist)): data[lo[i]] = io[i] pr-CSDN博客

本文链接：https://blog.csdn.net/qq_43708173/article/details/108257890

import matplotlib.pyplot as plt
import math
import pandas as pd
import numpy as np
import xlsxwriter
import os
from openpyxl import load_workbook

‘’’
获取文件列表path_list
‘’’
def receive_file_list(path):
if (os.path.exists(‘hw_form’)):
pass
else:
path_list = os.listdir(path)
print(path_list)
return path_list

‘’’
增益列表存放到列表GAIN_LIST
‘’’
#读取GAIN的函数
def find_GAIN(path):
data_GAIN_LIST = []
GAIN_list = []
path_list = receive_file_list(path)

for i in range(len(path_list)):
        with open(path + '/' + path_list[i], 'r') as file:
            data_GAIN = file.readline()
            data_GAIN_LIST .append(data_GAIN)
for i in range(len(data_GAIN_LIST)):
    GAIN = float(data_GAIN_LIST[i][5:10])
    GAIN_list.append(GAIN)
print(GAIN_list)
return GAIN_list

‘’’
相关数据的运算
‘’’
#读数据的函数
def find_single_data(filename):
print(path +’/’ + filename)
data = np.loadtxt(filename,skiprows=2)
return data

#把data按列进行分块函数
def data_split(data,i,j):
List = data[:, i:j]
return List

#求maxrE
def que_maxrE(L3,L5):
maxrE = np.max(np.sqrt(pow(10,L3/10)+ pow(10,L5/10)))
return maxrE
#求Co,Cx

def que_Co_Cx(L3,L4,L5,L6,maxrE):
Co = (pow(10,L3/20)* np.exp(1j * np.deg2rad(L4))+ pow(10,L5/20)* np.exp(1j * np.deg2rad(L6))) * 0.707 / maxrE
Cx = (pow(10,L3/20)* np.exp(1j * np.deg2rad(L4))- pow(10,L5/20)* np.exp(1j * np.deg2rad(L6))) * 0.707 / maxrE
return Co,Cx

#把所有的虚数值转化为dB值
def change_dB(mul):
mul_reshaped = mul.reshape(181, 360)
dB_value = 20 * np.log10(abs(mul_reshaped))
return dB_value
#重构Co,Cx的矩阵
def reshape_array(array):
array_H = np.hstack((array[270:360] , array[0:271]))
return array_H

#求出二维数组最大值及其索引
def the_maxium_index(mul):
pos = np.unravel_index(np.argmax(mul), mul.shape)
indx = pos[0]
indy = pos[1]
maxium = mul[indx,indy]
return indx,indy,maxium
‘’’
参数相关的计算函数
‘’’
‘’’
1.HBM,HBM 10dB
‘’’
#找出与max-3dB最接近的数字的索引
def return_3dB_index(maxium,array):
target = maxium - 3
index = (np.abs(array-target)).argmin()
return index
#找出max-10dB最接近的数字的索引
def return_10dB_index(maxium,array):
target = maxium - 10
index = (np.abs(array-target)).argmin()
return index
#求HBM, HBM10dB的值
def return_HBM(maxium,array,split_number):
split_number+=90
array_A = array[0:split_number]
array_B = array[split_number:360]
index1 = return_3dB_index(maxium,array_A)
index2 = split_number + return_3dB_index(maxium,array_B)
HBM = index2 - index1
return HBM
def return_HBM_10dB(maxium,array,split_number):
split_number+=90
array_A = array[0:split_number]
array_B = array[split_number:360]
index1 =return_10dB_index(maxium,array_A)
index2 = split_number + return_10dB_index(maxium,array_B)
HBM_10dB = index2 - index1
return HBM_10dB
‘’’
2.VBM,VBM 10dB
‘’’
#求VBM, VBM10dB的值
def return_VBM(maxium,array,split_number):
array_A = array[0:split_number]
array_B = array[split_number:180]
index1 = return_3dB_index(maxium,array_A)
index2 = split_number + return_3dB_index(maxium,array_B)
VBM = index2 - index1
return VBM
def return_VBM_10dB(maxium,array,split_number):
array_A = array[0:split_number]
array_B = array[split_number:180]
index1 =return_10dB_index(maxium,array_A)
index2 = split_number + return_10dB_index(maxium,array_B)
VBM_10dB = index2 - index1
return VBM_10dB
‘’’
3.FBR(Co)，FBR(Cx)
‘’’
def return_FBR_C(array,maxium):
array_new = np.hstack((array[0:30] , array[330:360]))
FBR_C = maxium - np.max(array_new)
return FBR_C
‘’’
4.XPD 0 ,XPD 60 ,XPD -60
‘’’
def return_XPD(CoH,CxH,degree):
XPD = CoH[degree] - CxH[degree]
return XPD

#创建文件夹
def create_dir_not_exist(path):
if not os.path.exists(path):
os.mkdir(path)
def create_dir(filename):
create_dir_not_exist(filename)
os.chdir(filename)
#使列表保留两位小数
def keep_two_digits(pixel_real_recognition_value):
list_ori = list(pixel_real_recognition_value) #原始列表
mid_np = np.array(list_ori) #列表转数组
mid_np_2f = np.round(mid_np,2) #对数组中的元素保留两位小数
list_new = list(mid_np_2f) #数组转列表
return list_new
#对Horit_list,Vorit_list分别减去180,90写入表格
def Horit_slove(Horit_list):
list_ori = list(Horit_list) #原始列表
Horit_array = np.array(list_ori) #列表转数组
Horit_new_1 = Horit_array - 180
Horit_new = list(Horit_new_1)
return Horit_new

def Vorit_slove(Vorit_list):
list_ori = list(Vorit_list) #原始列表
Vorit_array = np.array(list_ori) #列表转数组
Vorit_new_1 = Vorit_array - 90
Vorit_new = list(Vorit_new_1)
return Vorit_new

#找出异常元素
def find_error(list,limit):
i_lower = [i for (i,x) in enumerate(list) if x < limit]
x_lower = [x for (i,x) in enumerate(list) if x < limit]
return i_lower,x_lower
#开始标记单元格
def signing_cell_C(worksheet1,ilist,xlist,style):
if len(ilist) != 0 :
for i in range(len(ilist)):
worksheet1.write(‘C’ + str(ilist[i] + 2), xlist[i], style)
def signing_cell_L(worksheet1,ilist,xlist,style):
if len(ilist) != 0 :
for i in range(len(ilist)):
worksheet1.write(‘L’ + str(ilist[i] + 2), xlist[i], style)
def signing_cell_M(worksheet1,ilist,xlist,style):
if len(ilist) != 0 :
for i in range(len(ilist)):
worksheet1.write(‘M’ + str(ilist[i] + 2), xlist[i], style)
def signing_cell_N(worksheet1,ilist,xlist,style):
if len(ilist) != 0 :
for i in range(len(ilist)):
worksheet1.write(‘N’ + str(ilist[i] + 2), xlist[i], style)
def signing_cell_O(worksheet1,ilist,xlist,style):
if len(ilist) != 0 :
for i in range(len(ilist)):
worksheet1.write(‘O’ + str(ilist[i] + 2), xlist[i], style)
def signing_cell_P(worksheet1,ilist,xlist,style):
if len(ilist) != 0 :
for i in range(len(ilist)):
worksheet1.write(‘P’ + str(ilist[i] + 2), xlist[i], style)
‘’’
作图函数
‘’’
#GAIN,HBW,GBW 10dB类，一个数据，有上下限
def draw_picture_gain(list,limit_number_up,limit_number_low):
plt.figure(figsize=(10, 5)) # 设置画布的尺寸
plt.rcParams[‘font.sans-serif’] = [‘SimHei’]
plt.xlabel(u’File_Id’, fontsize=14) # 设置x轴，并设定字号大小
plt.ylabel(u’Gain(dBi)’, fontsize=14) # 设置y轴，并设定字号大小
x = np.arange(0, len(list)) + 1
x[0] = 1
my_x_ticks = np.arange(1,len(list) + 1, 1)
my_y_ticks = np.arange(1, limit_number_up + 5, 1)
plt.xticks(my_x_ticks)
plt.yticks(my_y_ticks)
plt.axis([1, len(list), limit_number_low - 5, limit_number_up + 5])
limit_number_up_list = [limit_number_up] * len(list)
limit_number_low_list = [limit_number_low] * len(list)

plt.grid()
plt.plot(x,list, color="deeppink", linewidth=2, marker='o')
plt.plot(x,limit_number_up_list, color="darkblue", linewidth=1, linestyle='--', marker='+')
plt.plot(x,limit_number_low_list, color="goldenrod", linewidth=1, linestyle='--', marker='+')

#HBW的折线图单列表，有上下限
def draw_picture_HBW(list,limit_number_up,limit_number_low):
plt.figure(figsize=(10, 5)) # 设置画布的尺寸
plt.rcParams[‘font.sans-serif’] = [‘SimHei’]
plt.xlabel(u’File_Id’, fontsize=14) # 设置x轴，并设定字号大小
plt.ylabel(u’HBW(°)’, fontsize=14) # 设置y轴，并设定字号大小
x = np.arange(0, len(list)) + 1
x[0] = 1
my_x_ticks = np.arange(1,len(list) + 1, 1)
my_y_ticks = np.arange(limit_number_low -10, limit_number_up + 10, 10)
plt.xticks(my_x_ticks)
plt.yticks(my_y_ticks)
plt.axis([1, len(list), limit_number_low -10,limit_number_up + 10])
limit_number_up_list = [limit_number_up] * len(list)
limit_number_low_list = [limit_number_low] * len(list)

plt.grid()
plt.plot(x,list, color="deeppink", linewidth=2, marker='o')
plt.plot(x,limit_number_up_list, color="darkblue", linewidth=1, linestyle='--', marker='+')
plt.plot(x,limit_number_low_list, color="goldenrod", linewidth=1, linestyle='--', marker='+')

#HBW_10dB 单列表，有上下限
def draw_picture_HBW_10dB(list,limit_number_up,limit_number_low):
plt.figure(figsize=(10, 5)) # 设置画布的尺寸
plt.rcParams[‘font.sans-serif’] = [‘SimHei’]
plt.xlabel(u’File_Id’, fontsize=14) # 设置x轴，并设定字号大小
plt.ylabel(u’HBW_10dB(°)’, fontsize=14) # 设置y轴，并设定字号大小
x = np.arange(0, len(list)) + 1
x[0] = 1
my_x_ticks = np.arange(1,len(list) + 1, 1)
my_y_ticks = np.arange(limit_number_low -20, limit_number_up + 10, 10)
plt.xticks(my_x_ticks)
plt.yticks(my_y_ticks)
plt.axis([1, len(list), limit_number_low -20,limit_number_up + 10])
limit_number_up_list = [limit_number_up] * len(list)
limit_number_low_list = [limit_number_low] * len(list)

plt.grid()
plt.plot(x,list, color="deeppink", linewidth=2, marker='o')
plt.plot(x,limit_number_up_list, color="darkblue", linewidth=1, linestyle='--', marker='+')
plt.plot(x,limit_number_low_list, color="goldenrod", linewidth=1, linestyle='--', marker='+')

#FBR 双列表，有下限
def draw_picture_FBR(list_a,list_b,limit_number_low):
plt.figure(figsize=(10, 5)) # 设置画布的尺寸
plt.rcParams[‘font.sans-serif’] = [‘SimHei’]
plt.xlabel(u’File_Id’, fontsize=14) # 设置x轴，并设定字号大小
plt.ylabel(u’FBR(dBi)’, fontsize=14) # 设置y轴，并设定字号大小
x = np.arange(0, len(list_a)) + 1
x[0] = 1
my_x_ticks = np.arange(1,len(list_a) + 1, 1)
my_y_ticks = np.arange(1, limit_number_low + 10, 1)
plt.xticks(my_x_ticks)
plt.yticks(my_y_ticks)
plt.axis([1, len(list_a),limit_number_low - 5, limit_number_low + 10])
limit_number_low_list = [limit_number_low] * len(list_a)

plt.grid()
plt.plot(x,list_a, color="deeppink", linewidth=2, marker='o')
plt.plot(x, list_b, color="darkblue", linewidth=2, marker='o')
plt.plot(x,limit_number_low_list, color="goldenrod", linewidth=1, linestyle='--', marker='+')

#XPD 0 类，一个数据，只有下限
def draw_picture_XPD_0(list,limit_number_low):
plt.figure(figsize=(10, 5)) # 设置画布的尺寸
plt.rcParams[‘font.sans-serif’] = [‘SimHei’]
plt.xlabel(u’File_Id’, fontsize=14) # 设置x轴，并设定字号大小
plt.ylabel(u’XPD_0(dBi)’, fontsize=14) # 设置y轴，并设定字号大小
x = np.arange(0, len(list)) + 1
x[0] = 1
my_x_ticks = np.arange(1,len(list) + 1, 1)
my_y_ticks = np.arange(1, limit_number_low + 15, 1)
plt.xticks(my_x_ticks)
plt.yticks(my_y_ticks)
plt.axis([1, len(list), limit_number_low - 5, limit_number_low + 15])
limit_number_low_list = [limit_number_low] * len(list)

plt.grid()
plt.plot(x,list, color="deeppink", linewidth=2, marker='o')
plt.plot(x,limit_number_low_list, color="goldenrod", linewidth=1, linestyle='--', marker='+')

#XPD ±60 类，一个数据，只有下限
def draw_picture_XPD_60(list_a,list_b,limit_number_low):
plt.figure(figsize=(10, 5)) # 设置画布的尺寸
plt.rcParams[‘font.sans-serif’] = [‘SimHei’]
plt.xlabel(u’File_Id’, fontsize=14) # 设置x轴，并设定字号大小
plt.ylabel(u’XPD_±60(dBi)’, fontsize=14) # 设置y轴，并设定字号大小
x = np.arange(0, len(list_a)) + 1
x[0] = 1
my_x_ticks = np.arange(1,len(list_a) + 1, 1)
my_y_ticks = np.arange(1, limit_number_low + 20, 1)
plt.xticks(my_x_ticks)
plt.yticks(my_y_ticks)
plt.axis([1, len(list_a), limit_number_low - 5, limit_number_low + 20])
limit_number_low_list = [limit_number_low] * len(list_a)

plt.grid()
plt.plot(x,list_a, color="deeppink", linewidth=2, marker='o')
plt.plot(x, list_b, color="darkblue", linewidth=2, marker='o')
plt.plot(x,limit_number_low_list, color="goldenrod", linewidth=1, linestyle='--', marker='+')

#读取并处理文件夹下所有文件的目录
def find_all_data(path):
os.chdir(path)
path_list = receive_file_list(path)
#GAIN的获取
GAIN_list = find_GAIN(path)
#循环体外创建列表用于存储数据
HBM_list = []
HBM_10dB_list = []
VBM_list = []
VBM_10dB_list = []
FBR_Co_list = []
FBR_Cx_list = []
XPD_0_list = []
XPD_60_list = []
XPD_sub60_list = []
Horit_list = []
Vorit_list = []

for i in range(len(path_list)):
    data = find_single_data(path_list[i])
    L3 = data_split(data,2,3)
    L4 = data_split(data,3,4)
    L5 = data_split(data,4,5)
    L6 = data_split(data,5,6)
    #求出maxrE
    maxrE = que_maxrE(L3,L5)
    print(maxrE)
    #求出CoCx矩阵
    Co_Cx = que_Co_Cx(L3,  L4,  L5,  L6,  maxrE)
    Co = Co_Cx[0]
    Cx = Co_Cx[1]
    print('==========================')
    print(np.max(abs(Co)))
    Cx = Co_Cx[1]
    print(np.max(abs(Cx)))
    #判断具体谁为Co，Cx
    if np.max(abs(Co)) < np.max(abs(Cx)):
        Co, Cx = Cx, Co


    #转化为dB值进行处理
    Co_dB = change_dB(Co)
    Cx_dB = change_dB(Cx)


    #求出函数最大值及其二维索引
    param_Co = the_maxium_index(Co_dB)
    param_Cx = the_maxium_index(Cx_dB)
    indx_Co = param_Co[0]
    indy_Co = param_Co[1]
    maxium_Co = param_Co[2]
    indx_Cx = param_Cx[0]
    indy_Cx = param_Cx[1]
    maxium_Cx = param_Cx[2]
    print(indx_Co,indy_Co,maxium_Co)
    print(indx_Cx,indy_Cx,maxium_Cx)


    #取出max对应在CoH，CxH的行列
    CoH_i = Co_dB[indx_Co]
    CxH_i = Cx_dB[indx_Cx]
    CoV = Co_dB[:,indy_Co]
    CxV = Cx_dB[:,indy_Cx]

    #其中把CoH,CxH的后90行移到前面
    CoH = reshape_array(CoH_i)
    CxH = reshape_array(CxH_i)

    '''
    HBW,HBM 10dB
    '''

    #在CoH中max-3dB搜索对应的索引为HBW
    HBM = return_HBM(maxium_Co,CoH,indy_Co)
    HBM_10dB = return_HBM_10dB(maxium_Co, CoH,indy_Co)

    HBM_list.append(HBM)
    HBM_10dB_list.append(HBM_10dB)
    print(HBM_list)
    print(HBM_10dB_list)

    '''
       VBW,VBM 10dB
    '''

    VBM = return_VBM(maxium_Co, CoV,indx_Co)
    VBM_10dB = return_VBM_10dB(maxium_Co, CoV,indx_Co)

    VBM_list.append(VBM)
    VBM_10dB_list.append(VBM_10dB)
    print(VBM_list)
    print(VBM_10dB_list)

    '''
    FBR(Co)，FBR(Cx)
    '''

    FBR_Co = return_FBR_C(CoH,maxium_Co)
    FBR_Cx = return_FBR_C(CxH,maxium_Co)
    FBR_Co_list.append(FBR_Co)
    FBR_Cx_list.append(FBR_Cx)

    print(FBR_Co_list)
    print(FBR_Cx_list)

    '''
    XPD 0 ,XPD 60 ,XPD -60
    '''
    XPD_0     = return_XPD(CoH, CxH, 180)
    XPD_60    = return_XPD(CoH, CxH, 240)
    XPD_sub60 = return_XPD(CoH, CxH, 120)
    XPD_0_list.append(XPD_0)
    XPD_60_list.append(XPD_60)
    XPD_sub60_list.append(XPD_sub60)

    print(XPD_0_list)
    print(XPD_60_list)
    print(XPD_sub60_list)

    '''
    Horit Vorit 
    '''
    Horit = int(np.argwhere(CoH == maxium_Co))
    Vorit = int(np.argwhere(CoV == maxium_Co))
    Horit_list.append(Horit)
    Vorit_list.append(Vorit)
    print(Horit_list)
    print(Vorit_list)

'''
填表
'''

create_dir('hw_form')
book = xlsxwriter.Workbook('hw_form.xlsx')
title_1 = ['Frequency(MHZ)', 'Gain', 'Directivity', 'Losses', 'Horit', 'Vorit', 'HBW', 'HBW  10dB', 'VBW',
           'VBW 10dB', 'FBR Co', 'FBR Cx', 'XPD 0', 'XPD -60', 'XPD 60', 'SIR', 'Squint 3dB', 'EDE', 'THP 10dB',
           'FSU', 'SS 0°~-30°', 'SS -30°~-150°', 'THP 10dB']
title_A = ['PORT1 07']
title_B = path_list
title_C = keep_two_digits(GAIN_list)
title_F = Horit_slove(Horit_list)
title_G = Vorit_slove(Vorit_list)
title_H = HBM_list
title_I = HBM_10dB_list
title_J = VBM_list
title_K = VBM_10dB_list
title_L = keep_two_digits(FBR_Co_list)
title_M = keep_two_digits(FBR_Cx_list)
title_N = keep_two_digits(XPD_0_list)
title_O = keep_two_digits(XPD_sub60_list)
title_P = keep_two_digits(XPD_60_list)

#找出异常元素
c_ilist, c_xlist = find_error(title_C, 14)
l_ilist, l_xlist = find_error(title_L, 19)
m_ilist, m_xlist = find_error(title_M, 19)
n_ilist, n_xlist = find_error(title_N, 20)
o_ilist, o_xlist = find_error(title_O, 10)
p_ilist, p_xlist = find_error(title_P, 10)



#工作表样式
style1 = book.add_format({
    'border': 1, # 边框
    # 'align': 'center', # 水平居中
    # 'valign': 'vcenter', # 垂直居中
    'bold': True, # 加粗（默认False）
    'font': u'宋体', # 字体
    'fg_color': '#CCFFFF', # 背景色
    'color': 'black' ,#字体颜色
})

style2 = book.add_format({
    'border': 1, # 边框
    'font_size':12,
    'align': 'center', # 水平居中
    'valign': 'vcenter', # 垂直居中
    'font': u'宋体', # 字体
    'fg_color': 'white', # 背景色
    'color': 'black', #字体颜色
})

style3 = book.add_format({
    'border': 1, # 边框
    'font_size': 12,
    'align': 'center', # 水平居中
    'valign': 'vcenter', # 垂直居中
    'bold': True, # 加粗（默认False）
    'font': u'宋体', # 字体
    'fg_color': 'red', # 背景色
    'color': 'black' #字体颜色
})
worksheet1 = book.add_worksheet('data')
#写入表头文件
worksheet1.set_row(0, 30)
worksheet1.set_column('B:B', 40)
worksheet1.write('A1','',style1)
worksheet1.write_row('B1',title_1,style1)
worksheet1.write_column('A2', title_A, style1)
worksheet1.write_column('B2', title_B, style1)
worksheet1.write_column('C2', title_C, style2)
worksheet1.write_column('F2', title_F, style2)
worksheet1.write_column('G2', title_G, style2)
worksheet1.write_column('H2', title_H, style2)
worksheet1.write_column('I2', title_I, style2)
worksheet1.write_column('J2', title_J, style2)
worksheet1.write_column('K2', title_K, style2)
worksheet1.write_column('L2', title_L, style2)
worksheet1.write_column('M2', title_M, style2)
worksheet1.write_column('N2', title_N, style2)
worksheet1.write_column('O2', title_O, style2)
worksheet1.write_column('P2', title_P, style2)
#异常元素标记
signing_cell_C(worksheet1, c_ilist, c_xlist, style3)
signing_cell_L(worksheet1, l_ilist, l_xlist, style3)
signing_cell_M(worksheet1, m_ilist, m_xlist, style3)
signing_cell_N(worksheet1, n_ilist, n_xlist, style3)
signing_cell_O(worksheet1, o_ilist, o_xlist, style3)
signing_cell_P(worksheet1, p_ilist, p_xlist, style3)



worksheet2 = book.add_worksheet('chart')

draw_picture_gain(GAIN_list ,13,15)
plt.savefig('Gain.png', dpi=600, bbox_inches='tight')
worksheet2.insert_image('A1', 'Gain.png')

draw_picture_HBW(HBM_list, 120, 60)
plt.savefig('HBW.png', dpi=600, bbox_inches='tight')
worksheet2.insert_image('A25', 'HBW.png')

draw_picture_HBW_10dB(HBM_10dB_list, 140, 110)
plt.savefig('HBW_10dB.png', dpi=600, bbox_inches='tight')
worksheet2.insert_image('A50', 'HBW_10dB.png')

draw_picture_FBR(FBR_Co_list,FBR_Cx_list, 19)
plt.savefig('FBR.png', dpi=600, bbox_inches='tight')
worksheet2.insert_image('A75', 'FBR.png')

draw_picture_XPD_0(XPD_0_list, 20)
plt.savefig('XPD_0.png', dpi=600, bbox_inches='tight')
worksheet2.insert_image('A100', 'XPD_0.png')

draw_picture_XPD_60(XPD_60_list,XPD_sub60_list,10)
plt.savefig('XPD_60.png', dpi=600, bbox_inches='tight')
worksheet2.insert_image('A125', 'XPD_60.png')
book.close()
os.remove('Gain.png')
os.remove('HBW.png')
os.remove('HBW_10dB.png')
os.remove('FBR.png')
os.remove('XPD_0.png')
os.remove('XPD_60.png')

if name == ‘main’:
# 路径名
path = ‘C:/Users/tWX5331009/PycharmProjects/python_work/excel_plt2/form/cocx’
#求出所有数据
find_all_data(path)