自学教程：Python xlwings插入Excel图片的实现方法

import xlwings as xwwb = xw.Book()sht = wb.sheets['Sheet1']sht.pictures.add('1.jpg') # 使用相对路径会报错wb.save('test.xlsx')wb.close()

import osimport xlwings as xwwb = xw.Book()sht = wb.sheets['Sheet1']# sht.pictures.add('1.jpg') # 使用相对路径会报错sht.pictures.add(os.path.join(os.getcwd(), '1.jpg'))wb.save('test.xlsx')wb.close()

import osimport xlwings as xwwb = xw.Book()sht = wb.sheets['Sheet1']fileName = os.path.join(os.getcwd(), '1.jpg')sht.pictures.add(fileName, left=sht.range('B5').left, top=sht.range('B5').top, width=100, height=100)wb.save('test.xlsx')wb.close()

import osimport xlwings as xwwb = xw.Book('test.xlsx') # 打开已存在的Excel文件sht = wb.sheets['Sheet1']rng = sht.range('B2') # 目标单元格fileName = os.path.join(os.getcwd(), '1.jpg')width, height = 80, 80 # 指定图片大小left = rng.left + (rng.width - width) / 2 # 居中top = rng.top + (rng.height - height) / 2sht.pictures.add(fileName, left=left, top=top, width=width, height=height)wb.save()wb.close()

import osimport xlwings as xwfrom PIL import Imagedef add_center(sht, target, filePath, match=False, width=None, height=None, column_width=None, row_height=None):  '''Excel智能居中插入图片  优先级：match > width & height > column_width & row_height  建议使用column_width或row_height，定义单元格最大宽或高  :param sht: 工作表  :param target: 目标单元格，字符串，如'A1'  :param filePath: 图片绝对路径  :param width: 图片宽度  :param height: 图片高度  :param column_width: 单元格最大宽度，默认100像素，0 <= column_width <= 1557.285  :param row_height: 单元格最大高度，默认75像素，0 <= row_height <= 409.5  :param match: 绝对匹配原图宽高，最大宽度1557.285，最大高度409.5  '''  unit_width = 6.107 # Excel默认列宽与像素的比  rng = sht.range(target) # 目标单元格  name = os.path.basename(filePath) # 文件名  _width, _height = Image.open(filePath).size # 原图片宽高  NOT_SET = True # 未设置单元格宽高  # match  if match: # 绝对匹配图像    width, height = _width, _height  else: # 不绝对匹配图像    # width & height    if width or height:      if not height: # 指定了宽，等比计算高        height = width / _width * _height      if not width: # 指定了高，等比计算宽        width = height / _height * _width    else:      # column_width & row_height      if column_width and row_height: # 同时指定单元格最大宽高        width = row_height / _height * _width # 根据单元格最大高度假设宽        height = column_width / _width * _height # 根据单元格最大宽度假设高        area_width = column_width * height # 假设宽优先的面积        area_height = row_height * width # 假设高优先的面积        if area_width > area_height:          width = column_width        else:          height = row_height      elif not column_width and not row_height: # 均无指定单元格最大宽高        column_width = 100        row_height = 75        rng.column_width = column_width / unit_width # 更新当前宽度        rng.row_height = row_height # 更新当前高度        NOT_SET = False        width = row_height / _height * _width # 根据单元格最大高度假设宽        height = column_width / _width * _height # 根据单元格最大宽度假设高        area_width = column_width * height # 假设宽优先的面积        area_height = row_height * width # 假设高优先的面积        if area_width > area_height:          height = row_height        else:          width = column_width      else:        width = row_height / _height * _width if row_height else column_width # 仅设了单元格最大宽度        height = column_width / _width * _height if column_width else row_height # 仅设了单元格最大高度  assert 0 <= width / unit_width <= 255  assert 0 <= height <= 409.5  if NOT_SET:    rng.column_width = width / unit_width # 更新当前宽度    rng.row_height = height # 更新当前高度  left = rng.left + (rng.width - width) / 2 # 居中  top = rng.top + (rng.height - height) / 2  try:    sht.pictures.add(filePath, left=left, top=top, width=width, height=height, scale=None, name=name)  except Exception: # 已有同名图片，采用默认命名    passif __name__ == '__main__':  wb = xw.Book()  sht = wb.sheets['Sheet1']  filePath = os.path.join(os.getcwd(), '1.jpg')  filePath2 = os.path.join(os.getcwd(), '2.jpg')  add_center(sht, 'A1', filePath) # 默认值  add_center(sht, 'B2', filePath2) # 默认值  add_center(sht, 'C3', filePath, match=True) # 绝对匹配图片宽高  add_center(sht, 'D4', filePath, width=100) # 图片宽度为100像素  add_center(sht, 'E5', filePath, height=100) # 图片高度为100像素  add_center(sht, 'F6', filePath, width=100, height=100) # 图片高度为100像素  add_center(sht, 'G7', filePath, column_width=100) # 单元格最大宽度为100像素  add_center(sht, 'H8', filePath, row_height=100) # 单元格最大宽度为100像素  add_center(sht, 'I9', filePath, column_width=100, row_height=100) # 单元格最大高度或宽度为100像素