Scale images in a readable manner in xlsxwriter

Question

I am using xlsxwriter to insert images and their metadata in excel cells. but I can't seem to scale it properly to get it in a readable manner.

my code is:

def resize_image(path,name):
    img=Image.open(path)
    img= img.resize((512,512))
    global temp_path
    full_path=os.path.join(temp_path,name)
    img.save(full_path)
    return full_path

def rotate_image(path ,name):
    img=Image.open(path)
    img= img.rotate(90,Image.NEAREST, expand=1)
    global temp_path
    full_path=os.path.join(temp_path,name)
    img.save(full_path)
    return full_path
    
def scale(size):
    row=100
    col=50
    return (row/size[0],col/size[1])

row=1
workbook=xlsxwriter.Workbook('data.xlsx')
bold = workbook.add_format({'bold': True})
worksheet=workbook.add_worksheet()
wrap = workbook.add_format({'text_wrap': True})
worksheet.set_column(1,4,50 ,wrap)

worksheet.write('A1','Image Name',bold)
worksheet.write('B1',"Resized Image",bold)
worksheet.write('C1',"Rotated Image",bold)
worksheet.write('D1',"Metadata",bold)
for i in glob.iglob(path+'\*.jpg',recursive=True):
    col=0
    size=np.asarray(Image.open(i)).shape
    x,y=scale(size)
    worksheet.set_row(row,50,wrap)
    name=get_image_name(i)
    worksheet.write(row,col, name)
    col+=1
    worksheet.insert_image(row,col,resize_image(i,name),{'x_scale':x, 'y_scale':y,'object_position': 1})

    col+=1
    worksheet.insert_image(row,col,rotate_image(i,name),{'x_scale':x, 'y_scale':y,'object_position': 1})

    col+=1

    worksheet.write(row,col,get_metadata(i),wrap)
    row+=1
workbook.close() 

and the output I am getting is output and I need something like this desired output. Can somebody tell what I am doing wrong? I tried scaling, changing width and height etc. but none seem to work.

EDIT here is my code with the changes in the comment:

image_data={}
row=1
workbook=xlsxwriter.Workbook('data.xlsx')
bold = workbook.add_format({'bold': True})
worksheet=workbook.add_worksheet()
wrap = workbook.add_format({'text_wrap': True})
worksheet.set_column(4,4,50)
worksheet.set_column(1,3,32)
worksheet.set_column(0,0,20)
worksheet.write('A1','Image Name',bold)
worksheet.write('B1',"Resized Image",bold)
worksheet.write('C1',"Rotated Image",bold)
worksheet.write('D1',"Metadata",bold)
for i in glob.iglob(path+'\*.jpg',recursive=True):
    col=0
    size=np.asarray(Image.open(i)).shape
    x,y=scale(size)
    worksheet.set_row(row,64)
    name=get_image_name(i)
    worksheet.write(row,col, name)
    col+=1
    worksheet.insert_image(row,col,resize_image(i,name),{'x_scale':x, 'y_scale':y})

    col+=1
    worksheet.insert_image(row,col,rotate_image(i,name),{'x_scale':x, 'y_scale':y})

    col+=1
    s=get_metadata(i)
    if(len(s)!=0):
        worksheet.write(row,col,s,wrap)
        
    
        image_data[name]=s
    else:
        print('No Metadata found for image {}'.format(name))
    row+=1
workbook.close() 

Answer 1

I didn't found a solution for automatically finding the right columns and row width for scaling, but maybe this solution can help you on the way.

import glob
import io
import os
from typing import Tuple

import xlsxwriter
from pil import Image


def buffer_image(image: Image, format: str = 'JPEG'):
    # Store image in buffer, so we don't have to write it to disk.
    buffer = io.BytesIO()
    image.save(buffer, format=format)
    return buffer, image


def resize(path: str, size: Tuple[int, int], format='JPEG'):
    image = Image.open(path)
    image = image.resize(size)
    return buffer_image(image, format)


def rotate(image: Image, rotation: int = 90, format='JPEG'):
    image = image.rotate(rotation, Image.NEAREST, expand=1)
    return buffer_image(image, format)


def create_header(worksheet: xlsxwriter.workbook.Worksheet):
    bold = workbook.add_format({'bold': True})
    worksheet.write('A1', 'Image Name', bold)
    worksheet.write('B1', "Resized Image", bold)
    worksheet.write('C1', "Rotated Image", bold)
    worksheet.write('D1', "Metadata", bold)
    return worksheet


if __name__ == '__main__':
    workbook = xlsxwriter.Workbook('image.xlsx')
    worksheet = workbook.add_worksheet()

    # Update the worksheet.
    wrap = workbook.add_format({'text_wrap': True})
    worksheet.set_column(0, 4, 27.5, wrap)
    worksheet = create_header(worksheet)

    for row, path in enumerate(glob.iglob("images/*.jpg"), start=1):
        worksheet.set_row(row, 150, wrap)

        # Add image name
        worksheet.write(row, 0, os.path.basename(path))

        # Add images
        image_buffer, image = resize(path, (512, 512), format='JPEG')

        data = {'x_scale': 200 / image.width, 'y_scale': 200 / image.height, 'object_position': 1}
        worksheet.insert_image(row, 1, path, {'image_data': image_buffer, **data})

        image_buffer, image = rotate(image, 90, format='JPEG')
        worksheet.insert_image(row, 2, path, {'image_data': image_buffer, **data})

        # Add metadata
        worksheet.write(row, 3, "Meta data", wrap)

    workbook.close()

Approach

I manually checked the preferred size of the column and row for a fixes image size of (200, 200). The values were 27.5 and 150 for the column and row respectively.

see the lines:

worksheet.set_column(0, 4, 27.5, wrap)
worksheet.set_row(row, 150, wrap)

Since I now know a fixed scaling ratio, I can calculate other image sizes by comparing this to the known case of (200, 200)

data = {'x_scale': 200 / image.width, 'y_scale': 200 / image.height, 'object_position': 1}

Extra

• instead of writing a temporary image to disk I keep the image in memory by using a buffer, which is approximately twice as fast.

  def buffer_image(image: Image, format: str = 'JPEG'):
      # Store image in buffer, so we don't have to write it to disk.
      buffer = io.BytesIO()
      image.save(buffer, format=format)
      return buffer, image
  

• The image format matters for the buffer, so there is a format argument in the resize and rotate functions. If you are using .png images JPEG has to be changed to PNG.