python: is there a way to merge an existing worksheet in my workbook with another sheet from another workbook?

Question

I have data in file1.xlsx (sheet 3) and a picture in file2.xlsx (sheet 1).

I want to place that picture next to data in sheet3.

I have tried to use openpyxl.drawing.image to insert image but it didn't work:

IOError: cannot identify image file

Then I tried xlsxwriter but it doesn't work on an existing workbook, so I end up with 2 workbooks.

Answer 1

Basically you need to extract the image from the source Excel sheet in file2.xlsx and then insert it into file1.xlsx. This can be done by using the worksheet ._images attribute which is a protected attribute.
Your code operation may depend on how many images and whether you know the location of them in file2.xlsx.
In the code example below we assume 1 image in file2.xlsx. The image is placed in file1.xlsx at the destination_cell location which I have set to 'G1' for this example.
If there is more than 1 image in file2.xlsx you would need to loop through the images in ws_source._images and find the one you need

Note; if you do not wish to use a protected attribute in your code directly you can use the openpyxl-image-loader wrapper for Openpyxl. It essentially creates a class that does the same thing but you do need to know the location cell of the image. The link gives an example how to utilise.

import openpyxl

# Workbook/worksheet with image
wb_source = openpyxl.load_workbook('file2.xlsx')
ws_source = wb_source['Sheet1']

# Workbook/worksheet where image is to be inserted
wb_destination = openpyxl.load_workbook('file1.xlsx')
ws_destination = wb_destination['Sheet3']

### Extract image from source and put in destination sheet 
destination_cell = 'G1' # The cell where the image is to be placed
for image in ws_source._images:
    ws_destination.add_image(image, destination_cell)

wb_destination.save(wb_destination)