xlsxwriter插入图像和txt转excel

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")