关于python3导出excel图片链接转图片且图片内嵌表格内实现

December 17, 2023
测试
测试
测试
测试
8 分钟阅读
from openpyxl import Workbook, load_workbook
from openpyxl.drawing.image import Image
from openpyxl.drawing.spreadsheet_drawing import AnchorMarker, TwoCellAnchor
import requests

def download(url,raw_path):
    file = url.split("/")[-1]
    file_name = f'{raw_path}/{file}'
    if os.path.exists(file_name):
        print(f"已存在{file_name}")
        return
    headers = {
        "Accept": "text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.9",
        "Accept-Language": "zh-CN,zh;q=0.9",
        "Cache-Control": "no-cache",
        "Connection": "keep-alive",
        "Pragma": "no-cache",
        "Sec-Fetch-Dest": "document",
        "Sec-Fetch-Mode": "navigate",
        "Sec-Fetch-Site": "none",
        "Sec-Fetch-User": "?1",
        "Upgrade-Insecure-Requests": "1",
        "User-Agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/103.0.0.0 Safari/537.36",
        "sec-ch-ua": "\".Not/A)Brand\";v=\"99\", \"Google Chrome\";v=\"103\", \"Chromium\";v=\"103\"",
        "sec-ch-ua-mobile": "?0",
        "sec-ch-ua-platform": "\"macOS\""
    }
    res = requests.get(url,headers=headers,verify=False,)
    with open(file_name,"wb") as fw:
        fw.write(res.content)

def item_list_to_excel(row_list):
  	#数据列表默认第一行为图片
    if not row_list:
        return None
    wb = Workbook()
    ws = wb.create_sheet("Sheet",0)  # 根据文件夹名称创建sheet
    raw_path =os.path.dirname(os.path.dirname(__file__))+"/images"
    if not os.path.exists(raw_path):
        os.makedirs(raw_path)
    pool = ThreadPoolExecutor(20)
    t_list = [ pool.submit(download,data["商品图片"],raw_path)  for data in row_list ]
    for t in t_list:
        t.result()


    # 设置第一行的字段
    column = 1
    for k in row_list[0].keys():
        ws.cell(row=1, column=column, value=k)
        column += 1

    m = 0
    for data in row_list:
        row_data = list(data.values())
        row_count  =len(row_data)
        #填写除图片字段的其他字段
        for index in range(1,row_count):
            ws.cell(row=m + 1 + 1, column=index+1, value=row_data[index])
        #插入图片
        file = row_data[0].split("/")[-1]
        image_path =  f'{raw_path}/{file}'
        img = Image(image_path)
        ws.row_dimensions[m+2].height = 180   #高是榜为单位
        ws.column_dimensions['A'].width = 50   #宽是字符为大卫
        _from = AnchorMarker(0, 50000, m + 1, 50000)  # 创建锚标记对象,设置图片所占的row
        to = AnchorMarker(1, -50000, m + 1 + 1, -50000)  # 创建锚标记对象,设置图片所占的row 从而确认了图片位置
        img.anchor = TwoCellAnchor('twoCell', _from, to)  # 将锚标记对象设置图片对象的锚属性,图形就具备了所在位置
        ws.add_image(img)  # 添加图片
        m+=1
    file_name = f'export_distributor_{datetime.datetime.now().strftime("%Y%m%d_%H%M")}.xlsx'
    wb.save(file_name)
    wb.close()
    print(f"保存文件")
    return file_name

继续阅读

更多来自我们博客的帖子

如何安装 BuddyPress
由 测试 December 17, 2023
经过差不多一年的开发,BuddyPress 这个基于 WordPress Mu 的 SNS 插件正式版终于发布了。BuddyPress...
阅读更多
Filter如何工作
由 测试 December 17, 2023
在 web.xml...
阅读更多
如何理解CGAffineTransform
由 测试 December 17, 2023
CGAffineTransform A structure for holding an affine transformation matrix. ...
阅读更多