python从excel里读取数据

1.从excel里读取数据

book=xlrd.open_workbook(r'D:\360MoveData\Users\Song\Desktop\Test.xls')
sheet1=book.sheets()[0]
nrows=sheet1.nrows
ncols=sheet1.ncols
print('表格总行数:',nrows)
print("表格总列数:",ncols)

col0_values=sheet1.col_values(0)
col1_values=sheet1.col_values(1)

2.数据清洗(去掉空字符)

while '' in col0_values:
    col0_values.remove('')

while '' in col1_values:
    col1_values.remove('')

3.将数据转为np.float64

x_copy=np.array(col0_values)
y_copy=np.array(col1_values)
y_copy1=np.array(col1_values)

for i in range(len(list(x_copy))):
    x_copy[i]=np.float64(x_copy[i])
    y_copy[i]=np.float64(y_copy[i])
    y_copy1[i] = np.float64(y_copy1[i])

4.写入excel

将基线写入excel
workbook=copy(book)
worksheet0=workbook.get_sheet(0)
for i in range(len(list(y_copy))):
    worksheet0.write(i,2,base[i])

将包络线写入excel
for i in range(len(list(y_copy))):
    worksheet0.write(i,3,enve[i])
workbook.save(r'D:\360MoveData\Users\Song\Desktop\Test.xls')