如何用Selenium/Pandas/Python将网页图片写入Excel而非URL？

要在Excel中插入实际图片，不能直接用pandas.to_excel完成，需要借助openpyxl库操作Excel单元格插入图片。以下是修改后的完整方案：

先安装依赖 #

pip install pandas openpyxl requests selenium

修改后的代码 #

import pandas as pd
from openpyxl import Workbook
from openpyxl.drawing.image import Image
from openpyxl.utils.dataframe import dataframe_to_rows
import requests
from io import BytesIO
from selenium import webdriver
from selenium.webdriver.common.by import By

# 初始化浏览器并爬取数据
driver = webdriver.Chrome()
images = []
image_contents = []  # 存储图片字节流，用于直接插入Excel
# 假设你已爬取到其他字段数据：house_name, description_details, house_price, specs, ratings, cancellation_policy, urls

# 爬取目标页面图片
driver.get("https://www.airbnb.com.au/rooms/50961691?adults=9&children=2&pets=1&search_mode=regular_search&check_in=2024-12-22&check_out=2024-12-28&source_impression_id=p3_1720759519_P3FseNFXiyBEU7hU&previous_page_section_name=1000&federated_search_id=7f29c222-115b-444d-bcdd-895c3e3151b4")
if len(driver.find_elements(By.CSS_SELECTOR, '#FMP-target')) > 0:
    image_src = driver.find_element(By.CSS_SELECTOR, '#FMP-target').get_attribute('src')
    print(image_src)
    images.append(image_src)
    # 下载图片到内存，无需保存本地
    try:
        response = requests.get(image_src)
        img_bytes = BytesIO(response.content)
        image_contents.append(img_bytes)
    except Exception as e:
        print(f"下载图片失败: {e}")
        image_contents.append(None)
else:
    photo = "No photo"
    print(photo)
    images.append(photo)
    image_contents.append(None)
driver.quit()

# 创建DataFrame，保留原数据结构
df = pd.DataFrame(zip(images, house_name, description_details, house_price, specs, ratings, cancellation_policy, urls),
                  columns=['Photo URL', 'Property Name', 'Description', 'Price', 'Specifications', 'Ratings', 'Cancellation Policy', 'Link'])

# 生成Excel并插入图片
wb = Workbook()
ws = wb.active

# 将DataFrame写入工作表
for r in dataframe_to_rows(df, index=False, header=True):
    ws.append(r)

# 调整列宽行高适配图片
ws.column_dimensions['A'].width = 20
for row in range(2, len(df)+2):
    ws.row_dimensions[row].height = 100

# 新增图片列，插入实际图片
ws.insert_cols(1)
ws.cell(row=1, column=1, value="Photo")

for row_idx in range(2, len(df)+2):
    img_content = image_contents[row_idx-2]
    if img_content is not None:
        try:
            img = Image(img_content)
            # 缩小图片适配单元格
            img.width = 150
            img.height = 100
            ws.add_image(img, f'A{row_idx}')
        except Exception as e:
            print(f"插入图片失败: {e}")
            ws.cell(row=row_idx, column=1, value="加载失败")
    else:
        ws.cell(row=row_idx, column=1, value="No photo")

# 保存文件
wb.save("房源信息带图片.xlsx")

关键说明 #

• 用image_contents存储图片字节流，无需落地本地文件
• 通过openpyxl.Image类直接将内存中的图片插入Excel单元格
• 提前调整列宽和行高，确保图片显示完整
• 处理图片下载失败、无图片的异常场景

内容的提问来源于stack exchange，提问作者Clay Burnett