How to get control of the size and shape of images using .insert_image() with XlsxWriter in macOS?

Question

I'm trying to write a Python script that builds a workbook of thousands of thumbnails of images from a few hundred folders to a few hundred sheets (one sheet per top-level folder).

The formatting of my sheets depends upon getting the thumbnails to have a controlled size, but I'm getting inconsistent results.

This comment on Improper Scaling of Large Images in XlsxWriter insert_image Method says:

Also, watch out for scaling in Excel for macOS which can be different than versions of Excel for Windows.

Related but different:

• Improper Scaling of Large Images in XlsxWriter insert_image Method
• Scale images in a readable manner in xlsxwriter

Question: How can I get control of the size and shape of images using .insert_image() with XlsxWriter in macOS? Right now it seems to be ignoring x_scale, y_scale and choosing the final size and shape based on the column dimensions:

The image in column A is scaled in x and y as 150%, 165%, in column B as 150%, 150%.

I am using MSExcel for Mac 16.66.1 (circa 2019) and XlsxWriter 1.3.7

Example of thumbnail, 200x200 pixels, 72 dpi

import xlsxwriter

# https://xlsxwriter.readthedocs.io/worksheet.html#insert_image
# https://xlsxwriter.readthedocs.io/example_images.html

def create_workbook(filename=None):
    if not isinstance(filename, str) or len(filename) == 0:
        filename = 'testwriter.xlsx'
    if isinstance(filename, str) and filename[-5:] != '.xlsx':
        filename +=  '.xlsx'
    workbook = xlsxwriter.Workbook(filename)
    workbook.window_width = 25000
    workbook.window_height = 16000
    fs = 14 # font size
    formats = dict()
    formats['header'] = workbook.add_format({'font_size': fs, 'align': 'center'}) # or fmt.set_font_size(14)
    formats['info'] = workbook.add_format({'font_size': fs, 'align': 'left',
                                      'font': 'Courier'}) 
    return workbook, formats

def create_sheet(workbook, sheetname, formats, n_columns):
    sheetname_local = str(sheetname)
    if not isinstance(sheetname_local, str) or len(sheetname_local) == 0:
        sheetname_local = 'noname'
    sheet = workbook.add_worksheet(sheetname_local)
    widths = [20] + n_columns*[50] 
    headings = ['name'] + ['imgs ' + str(i+1) for i in range(n_columns)]
    for i, (w, h) in enumerate(zip(widths, headings)):
        sheet.set_column(i, i, w) # https://stackoverflow.com/q/17326973/3904031
        sheet.write(0, i, h, formats['header'])
    return sheet


workbook, formats = create_workbook(filename='testAB.xlsx')
sheet = create_sheet(workbook, '2021', formats, n_columns=3)
img_fname = 'ima.jpg'
sheet.insert_image('A2', img_fname, {'x_scale': 1, 'y_scale': 1,
                                     'object_position': 2})
sheet.insert_image('B13', img_fname, {'x_scale': 1, 'y_scale': 1,
                                     'object_position': 2})
workbook.close()

Answer 1

This is an issue with how Windows originating Excel files are rendered in Excel for macOS.

Here for example is the output from the sample code on Windows, you can see that the scaling is 100% and the images are the same size:

Here is the same file saved on Windows (so created by Excel and not XlsxWriter but you could also create it from scratch to confirm) and opened on the macOS 13.1 with Excel 16.69:

As you can see the scaling is now 75% and in the first image it is 75%x83%.

Here is a similar Excel only issue reported on Microsoft Answers with an explanation, but no fix.