"""文件夹中有一批图片,每个图片命名为图片的信息,为了快速将图片和图片名称插入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("程序执行成功!!!任意键结束:")