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?

Answer 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.

Answer 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})