openpyxl vs pywin32 vs others libraries: copy and paste the value of cells containing the =IMAGE() in Excel

Question

I'm trying to copy and paste the values of cells containing the formula =IMAGE(Url) in Excel. The result is an image embedded in the cell and no longer linked to the Url.

I am able to do it with pywin32 with the following script:

ws.Range(f"C2:C{last_row}").PasteSpecial(Paste=win32.constants.xlPasteValues)

Given my goal to automate the entire script within an Azure Function App, I understand that pywin32 isn't the ideal solution? Is there an equivalent of the above script using openpyxl or another library?

Thanks!

Answer 1

The below sample code uses the openpyxl library in Python to process an Excel file and replace image formulas with the URLs. The function paste_image_values loads an Excel workbook, iterates over specific cells, extracts URLs from image formulas, and replaces the formulas with the URLs.

from openpyxl import load_workbook
import requests
from io import BytesIO
from PIL import Image

# Load the Excel workbook
workbook = load_workbook("C://Users//Book1.xlsx")
ws = workbook.active

# Iterate through the cells
for row in ws.iter_rows():
    for cell in row:
        if cell.data_type == 'f' and cell.value.startswith('=IMAGE'):
            # Evaluate the formula to get the image URL
            url = cell.value.split('(')[1].split(')')[0]

            # Fetch the image from the URL
            response = requests.get(url)
            image = Image.open(BytesIO(response.content))

            # Save the image to a file (or do whatever you want with it)
            image_file_name = f'image_{cell.row}_{cell.column}.jpg'
            image.save(image_file_name)

            # Set the cell value to the image file name
            cell.value = image_file_name

# Save the modified workbook
workbook.save('modified_excel_file.xlsx')

It extracts images from an Excel file where the image URLs are specified as formulas, then saves those images locally and updates the Excel file with the local image file names.

Using http trigger azure function . pacakages used azure-functions, openpyxl,requests and Pillow

• I used link for IMAGE function

import azure.functions as func
import logging
from openpyxl import load_workbook
import requests
from io import BytesIO
from PIL import Image

def process_excel():
    # Load the Excel workbook (Replace the file path with the appropriate location)
    workbook = load_workbook("C://Users//Book1.xlsx")
    ws = workbook.active

    # Iterate through the cells
    for row in ws.iter_rows():
        for cell in row:
            if cell.data_type == 'f' and cell.value.startswith('=IMAGE'):
                # Evaluate the formula to get the image URL
                url = cell.value.split('(')[1].split(')')[0]

                # Fetch the image from the URL
                response = requests.get(url)
                image = Image.open(BytesIO(response.content))

                # Save the image to a file (or do whatever you want with it)
                image_file_name = f'image_{cell.row}_{cell.column}.jpg'
                image.save(image_file_name)

                # Set the cell value to the image file name
                cell.value = image_file_name

    # Save the modified workbook
    workbook.save('modified_excel_file.xlsx')

def main(req: func.HttpRequest) -> func.HttpResponse:
    logging.info('Python HTTP trigger function processed a request.')

    # Process the Excel file
    process_excel()

    name = req.params.get('name')
    if not name:
        try:
            req_body = req.get_json()
        except ValueError:
            pass
        else:
            name = req_body.get('name')

    if name:
        return func.HttpResponse(f"Hello, {name}. This HTTP triggered function executed successfully.")
    else:
        return func.HttpResponse(
             "This HTTP triggered function executed successfully. Pass a name in the query string or in the request body for a personalized response.",
             status_code=200
        )