python逐列读取excel数据，读取文件夹下所有文件的文件名的数字序号，并去掉文件名的后缀

"""
此脚本用来逐列读取excel数据，读取文件夹下所有文件的文件名的数字序号，并去掉文件名的后缀
将读取的excel数据和数字序号一一对应填入新的excel表格
"""
import os
import pandas as pd
import numpy as np
import xlwt

def read_txt(path_txt):

    filelist = os.listdir(path_txt)
    filename_list = []
    for file in filelist:
        filename, extension = os.path.splitext(file)
        file_num = filename.split('_')[1]
        filename_list.append(file_num)
    return filename_list

def transform(sheet, sheet_row, sheet_col, data):
    for data_val, row_val, col_val in zip(data, sheet_row, sheet_col):
        sheet.write(row_val, col_val, data_val)
    return sheet

path_txt = './gsmap_mvk/'
path_excel = 'result.xls'

df = pd.read_excel(path_excel)
#读取excel文件并生成列表
df_list = df.values.tolist()
data_1 = []
data_2 = []
for val in df_list:
    data_1.append(val[0])
    data_2.append(val[1])

#生成txt文件名列表
num_txt = read_txt(path_txt)
print(num_txt)
sheet_row = []
sheet_col = []
for num in num_txt:
    sheet_row.append(int(num[:2]))
    sheet_col.append(int(num[2:4]))

print(sheet_row)
print(sheet_col)
#生成excel文件
workbook = xlwt.Workbook()
sheet1 = workbook.add_sheet(u'第一列', cell_overwrite_ok=True)
sheet1 = transform(sheet1, sheet_row, sheet_col, data_1)

sheet2 = workbook.add_sheet(u'第二列', cell_overwrite_ok=True)
sheet2 = transform(sheet2, sheet_row, sheet_col, data_2)
workbook.save('result_create.xls')