How do you select a cell and have a image highlighted and brought to the foreground?

English
#1

I am trying to create a Bill-of-materials so, the list has some text and an associated image - script generated xls file.
What features does libreoffice provide that will allow me to click on a cell A1 and have the associated image in B1 highlighted/brought-to-foreground.

Currently I am anchoring each image to it’s cell but as you can see, when the script creates the images - each image overlaps/hides the previous one and clicking on the text does nothing to the image.

sheet with cell selected
sheet with cell selected1170×629 77 KB

You will note in the image that the individual images were moved manually by me - not programmatically.
The idea is to select text ‘yow3’ and have the associated yow3-image highlighted - how do I do that?

can I highlight/select cells B1 B2 and have the corresponding image highlighted?

version 6.1.5.2 Linux Debian 10.4

How to view an image only after clicking on a description
How do I programmatically create a Bill of Materials using ANY document format?
#2

Why are you complicating your task? Why are you trying to duplicate existing tools? Press F5 to open the Navigator, expand the Images section and click as much as you need

#3

https://i.imgur.com/zuFZI6w.png (because the store that receives my .xlsx file won’t know to do it… he will assume there are no images besides those two)

#4

It seems to me that @vek.m1234 are trying to alternately bring each image to the foreground, depending on which cell he click.

#5

He wants the guy in the store to go through the list in the cells and get the highlighted images according to the captions on them. How about giving each image a name? Add something after

msg = 'yow' + str(c)
draw.text((0,c*10), msg)

like draw.name=msg (not sure, I don’t use Python)

#6

I have given the images unique names programmatically but 1. that might be difficult to do since it will be on/part-of the image and can get hidden by the colors in the image. 2. it doesn’t solve the overlapping problem where images get hidden under other images.

#7

would it be possible to move over cells B1 B2 and have the corresponding image highlighted?

#8

Yes, of course it possible. But as you say “I have no idea what corresponding image matches my description”. Calc also don’t know which image is ‘yow3’ - drawing text on image is a part of image, not it name.

You can try to select the image by the line number - the third line will select the ‘Picture 3’ image with the ‘yow3’ caption. But this will only work as long as nothing is removed from column A or from the images, then confusion begins.

#9

Maybe this LibreOffice 6.1 release notes calc-images can help.

#10

the store that receives my .xlsx file won’t know to do it

Which file do you want send to the store? xlsx? Or ods?

#11

https://i.imgur.com/AbvzH1u.png nope - selecting by line number doesn’t select the image - XLSX :frowning:

#12

XLSX from Calc? I’m sorry, I think you’re out of luck :frowning:

#13

The .xls format was given with the OriginalQuestion. Now it’s .xlsx. Anyway:
Since I cannot imagine a way to implement the wanted behavior without relying on user code heavily steressing the API and/or the dispatcher, I feel sure it is impossible to get it work after export to an alien file format, not to speak of an alien software having opened the file.

#14

it’s likely the store will be using some form of Microsoft Excel - it’s a bill of materials that I am generating so… .XLSX or .XLS or something Microsoft Excel can open and display. (some random store using something modern i hope) The python code’s using the xlsxwriter module but I’ve just started with the sample examples to see what’s possible. (electronics component part list)

#15

Please report what you get.
I wouldn’t use outdated software like Excel/MSoffice with (basically still) proprietary file formats and being not capable of working with well designed templates based on styles. (Tell me if they are better meanwhile.)
Of course: Solutions based on user code always are beyond what’s standardized for general-purpose office suites, and should therefore be avoided if possible.
Though I didn’t have any Excel at hand for about 15 years now, I woud assume that it also would require some programming for the task, and that a solution wouldn’t even be completely interoperable between different versions of Excel then.

#16

Though I dissuaded from relying on user code, I want to supply an example here, showing in what way a task like I understand the one from the question might be solved with a few dozen lines of code.

ask263572demoImageGame_0.ods
(Always chek code contained in documents for probably malign parts before permitting execution.)

#17

could you post the code instead… how do I check the code contained in documents?

#18

I mostly prefer to supply code in a document also demonstrating the usage…In this case the code is shaped in a way needing to assign it to the Selection changed event of the sheet it should work in, and I cannot easily imagine a way omitting this setting.
Thus:
-1- Set your Macro Security to Medium.
You are then prompted for the permission to run code contained in a document being loaded.
-2- First time refuse the permission and inspect the code via >Tools>Macros>Edit Macros.
-3- Being sure the code is safe, reload the document, now permitting macro execution.
-4- Test everything.
-5- If you want to use the code on a regular basis: Move it to a module of the Standard library of the respective LibO installations.

I never save to .xls or .xlsx, and cannot know exactly what would happen then from own experience. Even if LibO Basic code is saved to an alien format (what I doubt) it would sureley not work if the document is opened with MS software.

#19

The code itself (Assignment to event needed!):

Sub onChgSelFromImagesAnchoredToCellsOfThisColumnShowOnlyThoseAnchoredToSelectedCell(pNewSel)
If NOT IsObject(pNewSel) Then Exit Sub
If NOT pNewSel.supportsService("com.sun.star.sheet.SheetCell") Then Exit Sub
sheet = pNewSel.Spreadsheet
With pNewSel.Celladdress
  colSel = .Column : rowSel = .Row
End With
dp = sheet.DrawPage
uDp = dp.Count - 1
For j = 0 To uDp
  j_sh = dp(j)
  If j_sh.supportsService("com.sun.star.drawing.GraphicObjectShape") Then
    j_a = j_sh.Anchor
    If j_a.supportsService("com.sun.star.sheet.SheetCell") Then
      With j_a.CellAddress
        If .Column=colSel Then
          If .Row=rowSel Then
            j_sh.Visible = True
          Else
            If j_a.Type<>0 Then j_sh.Visible = False
          End If
        End If
      End With
    End If
  End If
Next j
End Sub
#20

thank you - hmm - I’ve not used Libreoffice Basic but I was thinking more on the lines of Python scripts embedded in Calc CVE 2019-9848. I googled a bit and apparently Excel macros can be re-written in Libreoffice+PythonUNO and some more google revealed that you can use Excel VBA in Libreoffice… but :frowning: yeah no easy way to have any of this work in some random Excel PC - which leaves me wondering how to create a Bill-of-material to buy stuff :frowning:

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.