Set width and height of an image when inserting via worksheet.insert_image

Question

From the docs, the insert_image function takes the following options:

{
    'x_offset':    0,
    'y_offset':    0,
    'x_scale':     1,
    'y_scale':     1,
    'url':         None,
    'tip':         None,
    'image_data':  None,
    'positioning': None,
}

The problem is that the size of input images I need to insert can vary, but the cell where they need to be is of a fixed size. Is it possible to somehow provide a width and a height and let Excel resize the image into the provided dimensions?

Solution 1

You can scale the image externally or within Excel using XlsxWriter and the x_scale and y_scale based on the the heights and widths of the cell(s) and the image.

For example:

import xlsxwriter

workbook = xlsxwriter.Workbook('image_scaled.xlsx')
worksheet = workbook.add_worksheet()

image_width = 140.0
image_height = 182.0

cell_width = 64.0
cell_height = 20.0

x_scale = cell_width/image_width
y_scale = cell_height/image_height

worksheet.insert_image('B2', 'python.png',
                       {'x_scale': x_scale, 'y_scale': y_scale})

workbook.close()

The advantage of scaling it like this is that the user can get back the original image by setting the scaling back to 100% in Excel.

Solution 2

I don't think it has a built in way to do scale and also keep aspect ratio. You will have to calculate it by yourself.

If you want to resize and submit the file at the target resolution (probably keeping your file size down), use pillow's thumbnail() method of images together with the xlsxwriter image_data option:

import io
from PIL import Image

def get_resized_image_data(file_path, bound_width_height):
    # get the image and resize it
    im = Image.open(file_path)
    im.thumbnail(bound_width_height, Image.ANTIALIAS)  # ANTIALIAS is important if shrinking

    # stuff the image data into a bytestream that excel can read
    im_bytes = io.BytesIO()
    im.save(im_bytes, format='PNG')
    return im_bytes

# use with xlsxwriter
image_path = 'asdf.png'
bound_width_height = (240, 240)
image_data = get_resized_image_data(image_path, bound_width_height)

# sanity check: remove these three lines if they cause problems
im = Image.open(image_data)
im.show()  # test if it worked so far - it does for me
im.seek(0)  # reset the "file" for excel to read it.

worksheet.insert_image(cell, image_path, {'image_data': image_data})

If you want to keep the original resolution and let excel do the internal scaling but also fit within the bounds you provide, you can calculate the correct scaling factor before giving it to excel:

from PIL import Image


def calculate_scale(file_path, bound_size):
    # check the image size without loading it into memory
    im = Image.open(file_path)
    original_width, original_height = im.size

    # calculate the resize factor, keeping original aspect and staying within boundary
    bound_width, bound_height = bound_size
    ratios = (float(bound_width) / original_width, float(bound_height) / original_height)
    return min(ratios)

# use with xlsxwriter
image_path = 'asdf.png'
bound_width_height = (240, 240)
resize_scale = calculate_scale(image_path, bound_width_height)
worksheet.insert_image(cell, image_path, {'x_scale': resize_scale, 'y_scale': resize_scale})

Comments

[Unos]

Thanks for your answer! Yeah I can resize before inserting too. The image is a PNG file, saved on disk.

[KobeJohn]

@Unos updated with basic code and notes for alternatives.

[Unos]

It's awesome to get an answer from you. I am trying out this approach too..does it make a difference if i put the image on a cell range, v/s if i merge the cell range first and then insert the image?

[KobeJohn]

@Unos updated with method that should work without creating new files and that also keeps your images within bounds at the original aspect ratio.

[jmcnamara]

It should be fine on a merged, or non-merged range of cells. Just multiply the cell height and width accordingly.

[KobeJohn]

@Unos another update - I think I found a way for you to use the built-in PIL thumbnail feature without saving new files. The other method also works as before if you want to keep the full size image in excel but also maintain the aspect ratio.

[Unos]

related q: how can we approximately calculate row height/col width in pixels?

[jmcnamara]

The default Excel cell width and height in pixels are shown in the example.