xlsxwriter插入图像和txt转excel
import xlsxwriter import os # book = xlsxwriter.Workbook('res_1.xlsx') # sheet = book.add_worksheet(u'Images') # sheet.insert_image(0, 0, r'Part.bmp', {'x_scale': 0.21, 'y_scale': 0.2}) def deal_excel(dir_pth): # step1:生成excel和表 book = xlsxwriter.Workbook('res_1.xlsx') images_sheet = book.add_worksheet(u'Images') confidence_sheet = book.add_worksheet(u'Confidences') # step2:设置表的格式 for i in range(2, 50): images_sheet.set_row(i, 200) images_sheet.set_column('B:O', 20) confidence_sheet.set_column('A:AG', 20) # step3: 向表中插入内容 item = os.walk(dir_pth).__next__() # 获取目标路径 root_path = item[0] # 根目录 sub_dir_name_list = item[1] # 子文件夹名 sub_first_file_name = item[2][0] # 一个子文件名 sub_file_path = os.path.join(root_path, sub_first_file_name) # 子文件路径 sub_dir_path_list = [] # 子文件夹路径list for i in sub_dir_name_list: sub_dir_path = os.path.join(root_path, i) sub_dir_path_list.append(sub_dir_path) # 确定images_sheet的行坐标名排序 subdir_file_name_list = os.listdir(sub_dir_path_list[0]) subdir_file_name_list = ['ColorZ.bmp', 'CurvatureMinPart1.bmp', 'CurvatureMaxPart1.bmp', 'CurvatureRowPart.bmp','CurvatureColPart.bmp' , 'SlopeColorCol.bmp', 'SlopeColorRow.bmp'] # 读取图像,在images_sheet中插入内容 for i in range(len(subdir_file_name_list)): # 插入列坐标名 images_sheet.write(1, i+1, subdir_file_name_list[i]) for i in range(len(sub_dir_name_list)): # 插入横坐标名 images_sheet.write(i+2, 0, sub_dir_name_list[i]) for i in range(len(sub_dir_name_list)): # 根据行列名找到图片路径进行插入 for j in range(len(subdir_file_name_list)): img_path = os.path.join(dir_pth, sub_dir_name_list[i], subdir_file_name_list[j]) images_sheet.insert_image(i + 2, j + 1, img_path, {'x_scale': 0.21, 'y_scale': 0.2}) # 读取txt,在confidence_sheet中插入内容 f = open(sub_file_path, 'r', encoding='utf-8') x = 0 while True: # 按行循环,读取文本文件 line = f.readline() if not line: break for i in range(len(line.split(','))): item = line.split(',')[i] confidence_sheet.write(x, i, item) x += 1 f.close() if __name__ == '__main__': deal_excel(r"C:\Users\29939\Desktop\当前项目文件\res")