[Feature Request] Insert images into calc cells

Hi, everyone. :slight_smile:

I have to admit, I get a TON of use out of Libreoffice calc these days. All the features are outstanding. Unfortunately, one of the big limitations I’ve run into is the inability to insert images directly into cells.

Yes, I’m aware that images can be made to hover over cells. But when you go and do things like sorting rows the images aren’t in the right place anymore. Also, the cells don’t automatically change size to fit around the images.

The perfect example of the functionality I have in mind is offered by Google’s Spreadsheet app. You can insert images directly into cells using this code:

=image("http://example.com/image.jpg")

In my mind, it doesn’t seem that difficult to have a similar function for libreoffice calc. And if calc could do that, it’d be an immensely more versatile spreadsheet program. There aren’t any drawbacks to incorporating such a feature that I’m aware of.

Anyways, wish you all the best. Thanks for creating such an awesome program.

-faulk

it would be great, for example if you want to calculate qr codes based on the value of adjacent columns’cells with such feature would be easy as drinking a cup of coffee! I like the idea, please advance it on bugzilla (https://bugs.documentfoundation.org/) hoping it will be implemented asap on calc!
Thanks in advance!!!

By default an inserted graphic (Insert > Picture > From File…) is anchored “To Page”, however a simple right-click on the graphic > Anchor > To Cell will change this. The graphic will then move with the cell. It is possible you are referring to the (in)ability to anchor graphics “As Character” in which case the related enhancement request is fdo#46871.

I do not find this working as you say. When setting the anchor, and then removing a row in the document before the image row, the image then moves. Am I missing something?

thanks

By “move with the cell” I meant in terms of copy / paste of the cell. The problem you indicate in relation to deleting the row / column containing the graphic appears related to bugs fdo#46868 (aspect ratio change) and fdo#77522 (graphic not deleted).

Over two years later I can confirm that copy / paste or cut / paste does not paste the image in the new location. Inserting rows before images, even when they are anchored, causes a complete mess.

Testing Anchor > To Cell on LO 4.2.4.2 shows that:

  1. Upon saving and reopening the document, images will have shifted down in their cell, possibly overlapping the row below if it was initially a tight fit.
  2. If you left click on an image in a cell you will see a blue border light up. This blue border is shifted above the image. This is probably indicative of the bug that caused the image shifting.
  3. If you increase a row’s height and that row already contains an image, you will notice that the image then stretches. This is very annoying.
  4. If you save the document to another file format, e.g. xls, the Anchor To Cell is lost. All images become Anchor To Page. Images are shifted. Very very annoying.

A way to slightly alleviate Anchor > To Cell pain is to set a keyboard shortcut (Tools / Customize…). I set ctrl-shift-C to Category: Format, Function: Change Anchor. Then you can paste an image, resize it to your cell, and immediately type ctrl-shift-c to anchor it.

Unfortunately, from what I’ve been told, very few people are working on improving LO’s user interface. Most effort is currently on LO Calc’s computational engine. UI-related bug reports and feature requests seem to go nowhere, even when people have been asking them for over 10 years.

Hi

This type of enhancement request is quite regular, which undoubtedly reflects a need.

As for me, I’m suspicious about sheets overloaded with non reworked images, unoptimized. The spreadsheet is first dedicated to calculate…

But I can propose you a workaround to the current limitations that can at least solve the cells sorting problem.

Insert images as background comments inserted into the cells.
Comments “follow” cells when sorting (see ImageInCell.ods for testing). This solution also allows to display or not the images, cell by cell.

Background’s comment can not be defined when it is inserted. So we must first insert the comment then show it then right click > Area, Bitmaps tab> Import to select the image.

Quote: “The spreadsheet is first dedicated to calculate…”

In fact such feature, if present in calc, could calculate for example a lot qr code in seconds inserting them into cells. This would be great IMHO!

I didn’t test it, but maybe this extension can help:

http://extensions.libreoffice.org/extension-center/imgcell-1

check this google sheets already having this feature please add it to libreoffice
https://support.google.com/docs/answer/3093333?rd=1

The original question here was about anchoring the image, and that is important, but there is a more fundamental piece missing in this puzzle, highlighted by Faulk’s original example which showed that the Google spreadsheet function =image(“http://example.com/image.jpg”) was drawing the image from an online, web-based source. Is that even a thing in Libre office? If it is, then happy days 'cause that’s exactly what I’m looking for.

This is a kind of forum where people ask questions. It’s not the place to post feature requests or to report bugs.

The right place for that is https://bugs.documentfoundation.org//

It’s true that there’s no quick and dirty trick to inserting an image directly into a cell in Excel. I’ve written an article explaining the simplest approach I’ve found in Excel, as well as another solution using the tool RowShare:

I’ve been looking for something like this (inserting images in tables, and being able to sort rows) for a long time - and finally found what might help; just use pyspread: pyspread

Here is a quick example:

  • Start pyspread
  • In cell 0:0 (A1) enter 2
  • Select cell 0:1 (B1)
  • From menu, click on Macro / Insert bitmap… and choose your image, open
  • Image is inserted, scaled to cell preserving aspect ratio, left-aligned

  • In cell 1:0 (A2) enter 1
  • In cell 1:1 (B2) enter with quotation marks “test text”
  • Select rows 0 and 1
  • On the toolbar, click Sort descending (up arrow)
  • The rows are re-sorted to 1 first and 2 second, the image remains in the cell it is supposed to:

Note that pyspread expects Python objects in cells, which is why you have to use quotation marks to enter plain text. Also, we can see the image has been embedded as wx.ImageFromData(... and a base-64 string - although images can also be linked to local files (then it’s just wx.Bitmap... and a local filesystem path, http URLs are not accepted, but there is probably a workaround).

Note that when you do re-sorting like this, any formatting of the image cell will be lost (and will be reset to default as on the above image), so in that case, you might want to have another cell elsewhere with a proper format for an image for the table, so you can do Format / Copy format from one cell and Format / Paste format to another cell.

The only problem with pyspread so far is that you cannot format/insert hyperlinks (it supports Pango markup, which is basically HTML, but Pango does not support the anchor tag <a href="...">link text</a>); also if you save a file and reopen it, it will not show the images because the document is in “safe mode”, and then you have to do File / Approve file to have the images show (see pyspread)

Otherwise, the images scale with the cell size (aspect ratio preserved), and can be aligned as text content is ( left, center or right ):

Maybe one day we’ll be able to do this in calc, too…