本教程分为图片批量插入以及图片单张插入;
废话不多说,直接上代码:
批量插入:
Excel表格式:
![](https://img-blog.csdnimg.cn/2021071810534189.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1Nwcml0ZV9IZW5yeQ==,size_16,color_FFFFFF,t_70)
Python代码:
import os
from openpyxl import load_workbook
from openpyxl.drawing.image import Image
#excelPath :Excel文件的路径
#sheetName :Excel文件中要操作的sheet名称
#picPathColunmNumber :图片全路径所在的列索引[从1开始](列索引1、2、3、4...)
#writeColunmName :图片要插入的列名(列名:A、B、C、D...)
#imgWidth :图片宽度
#imgHight :图片高度
#columnWidth :图片列宽度
#rowHight :每行高度
# 插入图片
def addImgToExcel(excelPath,sheetName,picPathColunmNumber,writeColunmName,imgWidth,imgHight,columnWidth,rowHight):
#加载Excel文件并读取指定Sheet
wb = load_workbook(excelPath)
ws = wb[sheetName]
#获取整个sheet的最大行数
Max_RowNumber=ws.max_row
# 从表格第二行开始插入图片
for rowid in range(2,Max_RowNumber+1):
#设置行高(除去第一行)
ws.row_dimensions[rowid].height = rowHight
#取到第N行第N列的值(图片的全路径)
picPath=ws.cell(rowid,picPathColunmNumber).value
#对得到的路径进行判断
if picPath and os.path.exists(picPath):
#设置图片尺寸大小
img = Image(picPath)
img.width = imgWidth
img.height = imgHight
# 写入图片
ws.add_image(img,str(writeColunmName)+str(rowid))
# 列宽
ws.column_dimensions[writeColunmName].width = columnWidth
else:
continue
wb.save(excelPath)
#方法入口
if __name__ == '__main__':
addImgToExcel('商标.xlsx','Sheet1',16,'H',65,71,8,55)
注意事项:
1.若用UiPath调用此段Python代码,请将代码中的注释以及中文删除,否则报错!!!
2.需安装os、openpyxl、pillow包
![](https://img-blog.csdnimg.cn/20210719113136664.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1Nwcml0ZV9IZW5yeQ==,size_16,color_FFFFFF,t_70)
![](https://img-blog.csdnimg.cn/20210719113309915.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1Nwcml0ZV9IZW5yeQ==,size_16,color_FFFFFF,t_70)
单一图片插入:
from openpyxl import load_workbook
from openpyxl.drawing.image import Image
def insertimg2excel(imgPath,excelPath,sheetName,write_location):
imgsize = (65, 71)
wb = load_workbook(excelPath)
ws = wb[sheetName]
ws.column_dimensions['N'].width = imgsize[0] * 0.13
img = Image(imgPath)
img.width, img.height = imgsize
ws.add_image(img,write_location)
ws.row_dimensions[1].height = imgsize[1] * 0.75
wb.save(excelPath)
看小编这么辛苦,给个赞再走吧!