Insert Images from Path

doryz · Post by **doryz** » Tue Mar 19, 2019 11:23 am

Hi,

I have a spreadsheet with mulitple columns, the first column is a file path to an image.
How can I use a Macro (or some other alternative) to load the images into the spreadsheet so I can see them from within the spreadsheet?
For example I would like a ./images/myimage.png in column 1 to show up as an embedded image on the same row in column 2..
How can this be done?

edit this topic's initial post · Post by **Villeroy** » Tue Mar 19, 2019 12:39 pm

File>New>Database...
[X] Connect to existing database
Type: Spreadsheet
Specify the spreadsheet which keeps your data.
Save the database in the directory above ./images/ (same path as spreadsheet).
--------------------------------------------------------------------------------
Now your spreadsheet table appears in the tables section.
Nothing has been converted, imported, copied. Your data are still in the spreadsheet. This is only a view on your spreadsheet. For editing you need a spreadsheet program.
###############################################
In the forms section choose "Use Wizard to Create Form".
Select your spreadsheet table and the fields you want to show together with the respective picture. Include the field with the image paths.
Choose a layout other than the suggested table grid.
The form shows the the first record of your list and a form navigation toolbar which allows you to navigate, sort and filter the list. The very last button opens an additional grid view on the form.
OK, close the form for now, right-click its icon and choose "Edit..."
Now have it opened in design view. Ctrl+Click the field which shows the image path. Then right-click the selected control and choose Replace_With>Image Control. Drag the box taller since it should display the linked image rather than text. In design view there should be a toolbar "Form design" or "Form control". Each of them has a toggle button on the second position which allows you to load the form (turn off design mode, switch to "live mode").
Play with the form.
Save the form.
Save the containing database document.
#########################################
Recent topic with more links, more descriptions, more options, e.g. the report builder extension allows you to create a database report with all pictures.

Things would be far more convenient if you would store all the data in a true database instead of linking a Base document to a spreadsheet.

edit this topic's initial post · Post by **Villeroy** » Tue Mar 19, 2019 12:54 pm

One thing you can do on the sheet directly is creating a field of hyperlinks which allows you to open images individually with an external program.

doryz · Post by **doryz** » Tue Mar 19, 2019 12:55 pm

This doesn't sound 100% right.
I just want to the image to appear next to the path in the spreadsheet, I don't want to create a database (as images are on the harddrive).
I just want to load the images with no buttons or dialogs into the spreadsheet, is that possible?

I don't want to need buttons to load images, just want to insert them so I can see them ..

edit this topic's initial post · Post by **Villeroy** » Tue Mar 19, 2019 2:07 pm

If you are an experienced programmer, it could be possible if you spend some week or two with programming. But then you would end up with hundreds (thousands?) of images pinned to their cells and the spreadsheet could become unusable for several reasons. Spreadsheet cells can not hold any data other than numbers or text. Pictures are attached to a separate layer and may be pinned to underlying cells on the spreadsheet layer. Pictures on sheets serve decorative or illustrative purposes. Small demo: Create a list with a few records and pictures attached manually. Then sort and filter the list.
A spreadsheet is a program for arithmetic calculations plus some limited list keeping capability. A database has unlimited list keeping capabilities. You can store all kinds of data (including pictures) within a database. Base is a database program that lets you work with databases. The above instructuions simply adds a "database view" on your spreadsheet without manipulating the spreadsheet itself.

edit this topic's initial post · Post by **Villeroy** » Tue Mar 19, 2019 2:25 pm

P.S.: A quick method to turn your spreadsheet list into an editable database table
Create a database directory in your working directory parallel to the images folder.
Select the sheet in question.
Make sure that there is one row of column labels on top of your list.
Save as dBase(*.dbf) in the database folder. This will save the active sheet only as one database table in the database folder. dBase is a database in a directory.

Create the database document but choose type "dBase" and point to the database directory instead of the spreadsheet file. The rest is the same.
If you have a database document connected to your spreadsheet already, open the database document, call menu:Edit>Database>Connection..., change the type from "Spreadsheet" to "dBase" and redirect the connection from the spreadsheet file to the database folder.
Now you can edit the list data in Base and you don't need the spreadsheet anymore.

edit this topic's initial post · Post by **Villeroy** » Tue Mar 19, 2019 3:49 pm

Villeroy wrote:One thing you can do on the sheet directly is creating a field of hyperlinks which allows you to open images individually with an external program.

Found an example sheet with some picture files: download/file.php?id=2297

doryz · Post by **doryz** » Tue Mar 19, 2019 3:58 pm

There is no option to create a new database in LiberaOffice Calc...
?

edit this topic's initial post · Post by **Villeroy** » Tue Mar 19, 2019 5:46 pm

Per default Ubuntu does not install Base

Code: Select all

sudo apt install libreoffice-base

or search the "Ubuntu Software" application for LibreOffice Base.

doryz · Post by **doryz** » Wed Mar 20, 2019 7:59 pm

But this means I cannot edit the csv values correcT?
Meaning I want to edit the cells next to the image based on the image, that's not possible with the datbase option right?

edit this topic's initial post · Post by **Villeroy** » Wed Mar 20, 2019 8:48 pm

The dBase database is editable.

Lupp · Post by **Lupp** » Fri Mar 22, 2019 5:39 pm

Basically it's not extremely complicated to insert images based on filepaths or URL contained in spreadsheet cells, but there are serious annoyances. Any script solution has to cope with the somehow incomplete API and ticklish little problems here and there. A specifically annoying fact is that the different LibO versions and AOO have different defaults concerning inserted images. So is the way images are influenced by changing height/width of rows/columns different, and not exactly optimal, imo, in any version.

Anyway the question was interesting, and as I am considering to do something alike myself, I took the risk to waste a few hours with the necessary studies and tests. As expected there were some surpises (No contradiction!).

What I have as an intermediary result is demonstrated in the attached example. It will also run in recent AOO, but with unwanted effects concerning the sizes. In LibO 6.1 or higher it should roughly act as is reasonable. The example is, however, not made specifically to meet the questioner's wishes . He (f/m) will need to make a few adaptions.

The script uses a dispach command for the insertion itself (and only for this task) because the API was changed more than once in this range. It is not sufficiently consistent in the current state, imo.

Insert Images from Path

Insert Images from Path

Re: Insert Images from Path

Re: Insert Images from Path

Re: Insert Images from Path

Re: Insert Images from Path

Re: Insert Images from Path

Re: Insert Images from Path

Re: Insert Images from Path

Re: Insert Images from Path

Re: Insert Images from Path

Re: Insert Images from Path

Re: Insert Images from Path