Python通过openpyxl 处理数据、图片、宽高

pip install openpyxl   直接安装最新版

完整代码：

import os

from openpyxl.styles import Font, Border, Side, PatternFill
from openpyxl.workbook import Workbook

from openpyxl.drawing.spreadsheet_drawing import AnchorMarker, OneCellAnchor
from openpyxl.drawing.xdr import XDRPositiveSize2D
from openpyxl.utils.units import pixels_to_EMU
from openpyxl.drawing.image import Image


def offset_img(img, col, row):
    '''
    偏移量处理，防止左上角顶格
    '''
    p2e = pixels_to_EMU
    h, w = img.height, img.width
    size = XDRPositiveSize2D(p2e(w), p2e(h))
    marker = AnchorMarker(col=col, colOff=20000, row=row, rowOff=20000)
    img.anchor = OneCellAnchor(_from=marker, ext=size)


def test():
    wb = Workbook()
    ws = wb.active
   
    header = ['序号', '标题1', '标题2', '标题3']
    ws.append(header)
    data_source = []
    for cell in ws[1]:
        cell.font = Font(bold=True)
        cell.border = Border(left=Side(style='thin'), right=Side(style='thin'),
                             top=Side(style='thin'), bottom=Side(style='thin'))
        cell.fill = PatternFill(start_color='4874CB', end_color='4874CB', fill_type='solid')

    row = 1
    col = 0
    for obj in data_source:
        # 写入一行数据，item是一个字典
        data = [row,
                1,
                2,
                3
                ]
        ws.append(data)
        col_index = col + 3

        # 插入图片，并指定图片路径
        img = Image('img_path')

        # 可选：设置图片的大小
        img.width = 45
        img.height = 20

        offset_img(img, col_index, row)
        # 将图片插入到指定的单元格
        ws.add_image(img)

        # 设置宽高，单元格实线
        ws.row_dimensions[row + 1].height = 20
        for cell in ws[row + 1]:
            cell.border = Border(left=Side(style='thin'), right=Side(style='thin'),
                                 top=Side(style='thin'), bottom=Side(style='thin'))

        row += 1

    for column in ws.columns:

        for cell in column:
            ws.column_dimensions[cell.column_letter].width = 15
    wb.save('测试.xlsx')