from openpyxl import Workbook,load_workbook from openpyxl.styles import * import xlrd import xlwt #计算bosa_vapd workbook1 = xlrd.open_workbook('0509-PIC BOSA Test Report_460units.xls') table = workbook1.sheet_by_name(sheet_name='Sheet1') #table_list = table.row_values(rowx=3, start_colx=0, end_colx=None) #table_list1 = table.col.values(colx=0,start_rowx=0,end_rowx=None) table_list1 = table.col_values(colx=3,start_rowx=3,end_rowx=None) #print(table_list1) num = 3 book = xlwt.Workbook(encoding='utf-8',style_compression=0) sheet = book.add_sheet('Sheet1',cell_overwrite_ok=True) col=('bosa_sn','bosa_type','bosa_pn','bosa_vapd','bosa_ith','bosa_imod','bosa_impd','bosa_refpwr','bosa_tiavcc','bosa_apd_resp','bosa_apd_comp','bosa_apd_max_i','darkcurrent_vapd10','darkcurrent_vop','pin_resp','rf_oma','rf_out_target','target_er_set') for i in range(0,len(col)): sheet.write(0,i,col[i]) bosa_type="PA6260" bosa_pn="PA6-GPON-ONU-BOSA" bosa_ith="80" bosa_imod="380" bosa_refpwr="2500" bosa_tiavcc="1" bosa_apd_comp="110" bosa_apd_max_i="10" pin_resp="-1" rf_oma="-1" rf_out_target="-1" target_er_set="13" a=1 print(len(table_list1)) for op in range(0,len(table_list1)): table_list = table.row_values(rowx=op+3, start_colx=0, end_colx=None) print(table_list) sn=table_list[0] vop=table_list[8] pop=table_list[4] gain=table_list[11] io=table_list[10] iop=table_list[9] bosa_vapd = round(vop*100) bosa_impd = round(pop) bosa_apd_resp = round(gain*8.5) darkcurrent_vapd10=round(io*100) darkcurrent_vop = round(iop*10) value=(sn,bosa_type,bosa_pn,bosa_vapd,bosa_ith,bosa_imod,bosa_impd,bosa_refpwr,bosa_tiavcc,bosa_apd_resp,bosa_apd_comp,bosa_apd_max_i,darkcurrent_vapd10,darkcurrent_vop,pin_resp,rf_oma,rf_out_target,target_er_set) for j in range(0,len(value)): sheet.write(op+1,j,value[j]) book.save('test1.xls')原始数据参数:
转换完后的数据:
import xlwt
book = xlwt.Workbook(encoding='utf-8',style_compression=0)
sheet = book.add_sheet('Sheet1',cell_overwrite_ok=True)
col=('bosa_sn','bosa_type','bosa_pn','bosa_vapd','bosa_ith','bosa_imod','bosa_impd','bosa_refpwr','bosa_tiavcc','bosa_apd_resp','bosa_apd_comp','bosa_apd_max_i','darkcurrent_vapd10','darkcurrent_vop','pin_resp','rf_oma','rf_oma','rf_out_target','target_er_set')
for i in range(0,len(col)):
sheet.write(0,i,col[i])
book.save('test.xls')
from openpyxl import Workbook,load_workbook
from openpyxl.styles import *
import xlrd
import xlwt
#wb = openpyxl.load_workbook('0509-NP2659G-BOSA参数模板表.xls')
#type(wb)
#workbook = xlrd.open_workbook('0509-NP2659G-BOSA参数模板表.xls')
#table = workbook.sheet_by_name(sheet_name='Sheet1')
#print(table)
workbook = xlwt.Workbook()
#计算bosa_vapd
workbook1 = xlrd.open_workbook('0509-PIC BOSA Test Report_460units.xls')
table = workbook1.sheet_by_name(sheet_name='Sheet1')
table_list = table.row_values(rowx=3, start_colx=0, end_colx=None)
print(table_list)
sn=table_list[0]
vop=table_list[8]
pop=table_list[4]
gain=table_list[11]
io=table_list[10]
iop=table_list[9]
bosa_vapd = round(vop*100)
bosa_impd = round(pop)
bosa_apd_resp = round(gain*8.5)
darkcurrent_vapd10=round(io*100)
darkcurrent_vop = round(iop*10)
ls=[sn,bosa_vapd,bosa_impd,bosa_apd_resp,darkcurrent_vapd10,darkcurrent_vop]
print(ls[0])
wb = Workbook()
wb_sheet = wb.create_sheet("Sheet1")
wb_sheet.cell(row=1,column=1,value=ls[0])
wb.save('bosa_config.xlsx')



