Python----xlsxwriter模块操作图片插入excel文件

"""文件夹中有一批图片，每个图片命名为图片的信息，为了快速将图片和图片名称插入excel表中，所以写了下面的程序-2020-07-18"""

文件夹名称：批处理202007131102

里面有若干个jpg图片

需求：想批量将图片插入excel文件并与图片名一一对应，图片名按顺序放A列，图片放B列

原始文件夹如下：

创建excel文件名为：SKU+图片.xlsx

EXCEL文件位置：图片文件夹内

执行python后效果如下：

代码如下：

# coding=utf-8
"""文件夹中有一批图片，每个图片命名为图片的信息，为了快速将图片和图片名称插入excel表中，所以写了下面的程序-2020-07-18"""

import xlsxwriter
import os


# 要操作的文件
book = xlsxwriter.Workbook('C:\\Users\\Administrator\\Desktop\\批处理202007131102\\SKU+图片.xlsx')

# 缩放比例
x_scale = 0.1
y_scale = 0.1

# 给sheet表取名为demo
sheet = book.add_worksheet('demo')
# 第一列数据 SKU名字
sku_biao_ti = sheet.write_string(0, 0, "SKU")
# 第二列数据 图片
tu_pian_lie = sheet.write_string(0, 1, "图片")
# 放图片的文件夹
filePath = 'C:\\Users\\Administrator\\Desktop\\批处理202007131102'

for i, j, k in os.walk(filePath):
    print(i, j, k)
    print("+++++", str(k))
    r = 1
    c = 0
    for tpm in k:
        if str(tpm).endswith('jpg'):
            tu_pian = "C:/Users/Administrator/Desktop/批处理202007131102/"+str(tpm)
            print(tu_pian)
            print(type(tu_pian))
            tu_pian_ming = tpm.replace(".jpg", "")
            print("-----", tu_pian_ming)
            skutu = sheet.insert_image(r, c+1, tu_pian, {'x_scale': x_scale, 'y_scale': y_scale})
            sku = sheet.write_string(r, c, tu_pian_ming)
            print("======", tpm)
            r = r+1

    book.close()

 

升级版：多一层文件夹，且可查看运行中print的图片名称

# coding=utf-8
"""文件夹中有一批图片，每个图片命名为图片的信息，为了快速将图片和图片名称插入excel表中，所以写了下面的程序-2020-07-18"""

import xlsxwriter
import os
import re
from PIL import Image


book = xlsxwriter.Workbook('C:\\Users\\Administrator\\Desktop\\批处理SKU+图片+文件夹名\\SKU+图片.xlsx')

# 缩放比例
x_scale = 0.1
y_scale = 0.1

sheet = book.add_worksheet('demo')
# 第一列数据 文件夹名字
folder_biao_ti = sheet.write_string(0, 0, "文件夹名")
# 第一列数据 SKU名字
sku_biao_ti = sheet.write_string(0, 1, "SKU")
# 第二列数据 图片
tu_pian_lie = sheet.write_string(0, 2, "图片")

filePath = 'C:\\Users\\Administrator\\Desktop\\批处理SKU+图片+文件夹名'

r = 1
c = 0
for i, j, k in os.walk(top=filePath):
# for i, j, k in os.walk(top=filePath, topdown=False):
    print("i:", i, "j:", j, "k:", k)
    print("+++++", str(k))
    # r = 1
    # c = 0
    for tpm in k:
        print("==文件名==：", tpm)
        if str(tpm).endswith('jpg') or str(tpm).endswith("JPG")or str(tpm).endswith("PNG")\
                or str(tpm).endswith("png")or str(tpm).endswith("JPEG")or str(tpm).endswith("jpeg"):
            # tu_pian = "C:/Users/Administrator/Desktop/批处理SKU+图片+文件夹名/"+str(tpm)
            # tu_pian = i+str(tpm)
            tu_pian1 = i+str("\ ")+str(tpm)
            tu_pian2 = tu_pian1.replace(" ", "")
            tu_pian = str(tu_pian2)         # 图片路径
            print("图片位置:", tu_pian)
            # print("type(tu_pian):", type(tu_pian))
            folder_name = i.replace("C:\\Users\\Administrator\\Desktop\\批处理SKU+图片+文件夹名\\", "")
            print("文件夹名：", folder_name)
            folder = sheet.write_string(r, c, folder_name)  # 文件夹名称

            tpm = tpm.replace("JPG", "jpg")
            tpm = tpm.replace("PNG", "jpg")
            tpm = tpm.replace("png", "jpg")
            tpm = tpm.replace("JPEG", "jpg")
            tpm = tpm.replace("jpeg", "jpg")
            tu_pian_ming = tpm.replace(".jpg", "")
            print("插入SKU：", tu_pian_ming)
            # postioning有以下允许的值：
            # 1.移动和调整单元格的大小
            # 2.移动但不调整单元格的大小（默认）
            # 3.不移动或调整单元格的大小
            skutu = sheet.insert_image(r, c+2, tu_pian, {'x_scale': x_scale, 'y_scale': y_scale})   # {'x_scale': x_scale, 'y_scale': y_scale}缩放
            # skutu = sheet.insert_image(r, c+1, tu_pian, {'x_offset': 15, 'y_offset': 10}, {'positioning': 1})     # {'x_offset': 15, 'y_offset': 10}偏移
            print("图片路径：", tu_pian)
            sku = sheet.write_string(r, c+1, tu_pian_ming)
            print("插入图片名：", tpm)
            r = r+1

book.close()
input("程序执行成功！！！任意键结束：")