openpyxl 插入图片并居中，切图片不会变形

1、计算方法

2、代码实现

import openpyxl
from openpyxl.styles import Alignment, Font, PatternFill, Border
import logging
from openpyxl.cell.cell import get_column_letter
from openpyxl.drawing.image import Image
from openpyxl.drawing.xdr import XDRPoint2D, XDRPositiveSize2D
from openpyxl.drawing.spreadsheet_drawing import AnchorMarker, OneCellAnchor
from openpyxl.utils.units import pixels_to_EMU

logger = logging.getLogger('error')


def insert_image(worksheet, start_row, start_col, height, image_url, image_size=None):
    """
    # 计算图片大小 img_size[0]=180; img_size[1]=80
    :param worksheet:  openpyxl.Worksheet对象
    :param start_row:   # 哪一行开始
    :param start_col:   # 那一列
    :param height:      # 合并了多少行
    :param image_url:   # 图片路径
    :param image_size:  # 在excel中图片显示的最大宽高
    :return:  # None
    """
    try:
        # 像素转换为EMU
        p2e = pixels_to_EMU
        # 获取图片
        img = Image(image_url)
        # 计算原图片的宽高比例
        per = img.width / img.height
        # 固定图片高为80，通过（原图片宽高比例）计算出宽为多少
        shr_image_width = image_size[1] * per  # w:h     shr_image_width:80
        # 计算出的宽高
        img_size = shr_image_width, image_size[1]
        # 如果图片的宽大于160，就以宽为固定边为160，计算高
        if shr_image_width > image_size[0]:
            shr_image_height = image_size[0] / per  # w:h     150:shr_image_hieght
            # 计算出实际的宽高
            img_size = image_size[0], shr_image_height
        # 把原图片固定为实际的宽高
        img.height, img.width = img_size
        # 计算出图片在excel中位置，是图片的大小
        size = XDRPositiveSize2D(p2e(img.height), p2e(img.width))
        # 设置图片在左右居中
        col_letter = get_column_letter(start_col)  # 定位哪一列
        width = worksheet.column_dimensions[col_letter].width
        _col = int((width * 8 - img.height) / 2)   # 左右偏移量，左右相等就居中
        # 设置图片上下居中（因为行方向合并了多个单元格）
        h2_lst = []   # 每个单元格累计的高度
        acc_height = 0
        for row in range(start_row, start_row + height):
            h = worksheet.row_dimensions[row].height
            if not h:
                worksheet.row_dimensions[row].height = 15
                acc_height += 15
                h2_lst.append(int(acc_height * 20 / 15))
            else:
                acc_height += h
                h2_lst.append(int(acc_height * 20 / 15))
        h2_lst.insert(0, 0)   # 从0开始
        top = int((h2_lst[-1] - img.width) / 2)  # 距离顶部的距离
        pos = []
        for nid, ele in enumerate(h2_lst):
            if top < ele:
                pos.append((nid, top))
        row = start_row + pos[0][0] - 1    # 定位哪一行
        index = pos[0][0]
        _row = pos[0][1] - h2_lst[index]   # 上下偏移量（相对于这个单元格）
        # 相对于单元格内的(x,y,top,left)偏移量
        marker = AnchorMarker(col=start_col - 1, colOff=p2e(_col), row=row, rowOff=p2e(_row))
        img.anchor = OneCellAnchor(_from=marker, ext=size)
        worksheet.add_image(img)
    except Exception as e:
        logger.error(e)
        pass


if __name__ == '__main__':
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.merge_cells(start_row=5, end_row=14, start_column=5, end_column=5)
    col_letter = get_column_letter(5)  # 定位哪一列
    ws.column_dimensions[col_letter].width = 25
    insert_image(ws, 5, 5, 9, '111.jpg', (160, 80))
    wb.save('xxx.xlsx')