excel - XlsxWriter:根据整个工作表中的单元格值插入特定图像

Question

我试图弄清楚如何让 XlsxWriter 循环遍历工作表并根据每个单元格值用特定图像添加/替换单元格值(例如，单元格值为 1 添加 pic1.jpg，单元格值为 2 添加 pic2.jpg，等等
我的 Excel 表是这样的:
Example sheet
我不知道如何让 Xslxwriter 循环检查每个单元格值，如果单元格符合条件，则使用 worksheet.insert_image 插入图像。
任何帮助表示赞赏!

Answer 1

下载这些测试图像并将它们与脚本一起放在根目录中(当然您可以将它们放在您想要的任何位置，但您需要调整代码以使用绝对路径)。
/image/9nVXn.jpg
/image/vIGO6.jpg
/image/Ujwa0.jpg
代码:

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()

初始输出:

最终输出:

这是实现您想要的一种方法，我希望您明白这一点!