Importing Images from an Excel Sheet

Question

Hi team,

has anybody ever imported images from an excel spreadsheet? Ideally with the data from the spreadsheet as well?

It seems not possible, but I wouldn't be surprised if someone here has made the impossible happen ;)

Best

Florian

yuichi_katsumur · Accepted Answer

Hi @Florian_Vogt ,

As image data in Excel (.xlsx) files is stored internally in a ZIP format, you can extract images using the following JSL code.

// Open the Excel file (.xlsx) as a ZIP archive
za = Open( "$desktop\Big Class Families.xlsx", zip );
// Get the list of files inside the ZIP archive
dirlist = za << dir;
// Create a new data table with one column to store images
dt = New Table( "Import image from Excel", New Column( "Image", Expression ) );
// Initialize row index
row_i = 0;
// Loop through all files in the ZIP archive
For( p_i = 1, p_i <= N Items( dirlist ), p_i++,    
    // Check if the file has a .png extension (i.e., it's an image)
    If( Right( dirlist[p_i], 4 ) == ".png" /*png data*/,        
        // Read the PNG file as binary (blob) data
        myImageBlob = za << read( dirlist[p_i], Format( blob ) );        
        // Convert the binary data into an image object
        myImage = New Image( myImageBlob, "png" );        
        // Add a new row to the data table
        dt << Add Rows( 1 );
        row_i++;        
        // Insert the image into the first column of the new row
        dt[row_i, 1] = myImage;
    )
);

If you also need to retrieve associated data or the location of the images within the worksheet, I recommend using a Python library such as openpyxl.

I hope this helps!