【办公自动化实例】海量图片处理后插入excel中，一键搞定，加班不存在的。。。

一、需求

将文件夹内的图片处理后，添加到excel表格内，这种一般可以用来做信息展示。

二、思路

1 新建或者打开一个excel文件
2 读取图片文件名
3 切割图片（如果做其他处理，也可以继续添加功能）
4 将图片名称和图片写入excel文件
5 设置单元格列宽

三、整体代码

from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.drawing.image import Image
import os
import PIL
from openpyxl.utils import get_column_letter
from openpyxl.styles import Alignment
import re

def sort_key(s): #有数字的，按数字进行排序
    if s:
        try:
            c = re.findall(r'^\d+', s)[0]
        except:
            c = -1
        return int(c)

# 获取图片列表
def get_img_ls(path,layout):
	img_ls = []
	for file in os.scandir(path):
		if file.name.endswith(layout):
			img_ls.append(file.name)
	return img_ls


# 图片大小调整
def new_img_size(path,new_path,layout,ratio):
		img_ls = get_img_ls(path,layout)
		for file in img_ls:
			img = PIL.Image.open(f'{path}\\{file}')
			# 如果打算将图片按比例调整为ratio大小，以高度为基准，如下设置
			new_width = ratio
			new_height = img.height * new_width // img.width
			new_img = img.resize((new_width,new_height))
			new_img.save(f'{new_path}\\{file}')

def creat_excel(new_path,layout,max_row,output_filename):
	img_ls = get_img_ls(new_path,layout)
	img_ls.sort(key=sort_key)  #名称进行排序

	wb = Workbook()  
	# 也可以用设置好的模板,可以手动添加参数到函数creat_excel中
	# wb = load_workbook(filename = 'logo.xlsx')
	ws = wb.active
	#在指定的行范围添加图片，按行添加，图片在同一列，并自动添加图片名字
	n=0
	for cols in ws.iter_rows(min_row=1,max_row=max_row,min_col=1,max_col=2):
		img = Image(f'{new_path}//{img_ls[n]}')
		cols[0].value = img_ls[n][:-4]
		alignment = Alignment(horizontal = 'center',vertical='center',wrap_text=True)
		cols[0].alignment = alignment

		ws.add_image(img,cols[1].coordinate)
		n+=1
		print(f'已添加第{n}张图片至单元格{cols[1].coordinate}')
		if n >= len(img_ls):
			break
	#设置列，行宽

	max_row = ws.max_row
	max_col = ws.max_column
	width = 20
	height = width*5

	for i in range(1, ws.max_row+1):
	    ws.row_dimensions[i].height = height
	for i in range(1, ws.max_column+1):
	    ws.column_dimensions[get_column_letter(i)].width = width

	wb.save(output_filename)



if __name__ == '__main__':
	# 以下参数可以修改
	path = '.\\原始文件'
	new_path = '.\\处理后文件'
	layout = 'jpg'
	ratio = 150    # 设置图片缩放比例
	max_row = 20  # 设置表格的行数
	output_filename = 'logo.xlsx'

	if not os.path.exists(new_path):
		os.mkdir(new_path)

	new_img_size(path,new_path,layout,ratio)

	creat_excel(new_path,layout,max_row,output_filename)

四 2021-8-27更新的代码，将文字、图片按不同行插入

from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.drawing.image import Image
import os
import PIL
from openpyxl.utils import get_column_letter
from openpyxl.styles import Alignment
import re
def sort_key(s): #有数字的，按数字进行排序
    if s:
        try:
            c = re.findall(r'^\d+', s)[0]
        except:
            c = -1
        return int(c)

 #获取图片列表
def get_img_ls(path,layout):
	img_ls = []
	for file in os.scandir(path):
		if file.name.endswith(layout):
			img_ls.append(file.name)
	return img_ls


 #图片大小调整
def new_img_size(path,new_path,layout,ratio):
		img_ls = get_img_ls(path,layout)
		for file in img_ls:
			img = PIL.Image.open(f'{path}\\{file}')
			# 如果打算将图片按比例调整为ratio大小，以高度为基准，如下设置
			new_width = ratio
			new_height = img.height * new_width // img.width
			new_img = img.resize((new_width,new_height))
			new_img.save(f'{new_path}\\{file}')

def creat_excel(new_path,layout,max_row,max_col,output_filename):
	img_ls = get_img_ls(new_path,layout)
	img_ls.sort(key=sort_key)  #名称进行排序

	wb = Workbook()  
	# 也可以用设置好的模板,可以手动添加参数到函数creat_excel中
	# wb = load_workbook(filename = 'logo.xlsx')
	ws = wb.active


#在指定的行范围添加图片，按行添加，图片在同一列，并自动添加图片名字
	n=0
	for num1 in range(1,max_row,3):
		for cols in ws.iter_rows(min_row=1,max_row=max_row,min_col=1,max_col=max_col):
			for col in cols:
				if cols[0].row == num1 and  n <= len(img_ls)-1:
				
					col.value = img_ls[n][:-4]
					alignment = Alignment(horizontal = 'center',vertical='center',wrap_text=True)
					col.alignment = alignment
					n+=1
	m=0	
	for num2 in range(2,max_row,3):
		for cols in ws.iter_rows(min_row=1,max_row=max_row,min_col=1,max_col=max_col):
			for col in cols:
				if cols[0].row == num2 and  m <= len(img_ls)-1:
					img = Image(f'{new_path}//{img_ls[m]}')
				
					print(col.coordinate)
					ws.add_image(img,col.coordinate)
					m+=1
					print(f'已添加第{m}张图片至单元格{col.coordinate}')



	#设置列，行宽

	max_row = ws.max_row
	max_col = ws.max_column
	width = 20
	height = width*5
	# 修改图片行的行高
	for i in range(2, ws.max_row+1,3):
	    ws.row_dimensions[i].height = height

	# 修改名称行的行高
	for i in range(1, ws.max_row+1,3):
		 ws.row_dimensions[i].height = 20
		 
	# 修改列宽
	for i in range(1, ws.max_column+1):
		ws.column_dimensions[get_column_letter(i)].width = width

	wb.save(output_filename)



if __name__ == '__main__':
	# 以下参数可以修改
	path = '.\\原始文件'
	new_path = '.\\处理后文件'
	layout = 'jpg'
	ratio = 100    # 设置图片缩放比例
	max_row = 10  # 设置表格的行数
	max_col = 3   # 设置表格的列数
	output_filename = 'logo.xlsx'

	if not os.path.exists(new_path):
		os.mkdir(new_path)

	new_img_size(path,new_path,layout,ratio)

	creat_excel(new_path,layout,max_row,max_col,output_filename)