XlsxWriter: insert specific images based on cell value throughout sheet

user_14348851 0
Viewed 365

I'm trying to figure out how to get XlsxWriter to loop through a sheet and add / replace cell value with specific images based on each cell value (e.g. cell value is 1 add pic1.jpg, cell value is 2 add pic2.jpg, etc.

My Excel sheet is something like this: Example sheet

I don't know how to make Xslxwriter check each cell value in a loop and use worksheet.insert_image to insert image if the cell matches the condition.

Any help is appreciated!

xlsxwriter jupyter-notebook excel
1 Answers

Download these test images and put them in the root directory along with the script (of course you can put them anywhere you want but you need to adjust the code to use an absolute path for example).

https://i.stack.imgur.com/9nVXn.jpg

https://i.stack.imgur.com/vIGO6.jpg

https://i.stack.imgur.com/Ujwa0.jpg

Code:

import pandas as pd
import numpy as np

# Create a test dataframe
df = pd.DataFrame({
    'Beverage': ['Juice', 'Milk', 'Beer', 'Water'],
    'Copenhagen': [1, 1, 1, 3],
    'Berlin': [2, 1, 1, 3],
    'Oslo': [1, 1, 1, np.nan],
    'Helsinki': [2, 1, np.nan, np.nan]
})

# Pass the df into xlsxwriter
writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1', index=False)
workbook = writer.book
worksheet = writer.sheets['Sheet1']

# Create a cell format with lock protection turned off so that we can delete the value
unlocked_format = workbook.add_format({'locked': False})

# Iterate through the columns and the rows
max_row = df.shape[0]
max_column = df.shape[1]

for col in range(1,max_column):
    for row in range(0, max_row):
        if (df.iloc[row,col] == 1):
            # Empty the cell's value
            worksheet.write(row+1, col, None, unlocked_format)
            # Insert the image, adjust the scale properties according to the image's size
            worksheet.insert_image(row+1, col, 'pic1.png', {'x_scale': 0.1, 'y_scale': 0.1})
        elif (df.iloc[row,col] == 2):
            worksheet.write(row+1, col, None, unlocked_format)
            worksheet.insert_image(row+1, col, 'pic2.png', {'x_scale': 0.015, 'y_scale': 0.015})
        elif (df.iloc[row,col] == 3):
            worksheet.write(row+1, col, None, unlocked_format)
            worksheet.insert_image(row+1, col, 'pic3.png', {'x_scale': 0.015, 'y_scale': 0.015})

worksheet.write("A1", None, unlocked_format)

writer.save()

Initial Output: enter image description here

Final Output: enter image description here

This is one way to achieve what you want, i hope you got the idea!

user_115953930
Related