Sub HideRows
oSheet = ThisComponent.CurrentController.ActiveSheet
oDP = oSheet.DrawPage
oSheets = ThisComponent.getSheets()
oSheet1 = oSheets.getByName("Sheet1")
oRows = oSheet1.getRows()
for i = 5 to 29
oRow = oRows.getByIndex(i)
oCell = oSheet1.getCellByPosition(0,i) 'First row has index 0
If oCell.Value = 0 Then
For k = 0 To oDP.Count - 1
oShape = oDP(k)
oAnchor = oShape.Anchor
AnchorRow = oAnchor.CellAddress.Row
If AnchorRow = i Then
oShape.Visible = False
End If
Next k
oRow.isVisible = False
End If
next i
End Sub
Sub ShowRows
oSheet = ThisComponent.CurrentController.ActiveSheet
oDP = oSheet.DrawPage
oSheets = ThisComponent.getSheets()
oSheet1 = oSheets.getByName("Sheet1")
oRows = oSheet1.getRows()
for i = 5 to 29
oRow = oRows.getByIndex(i)
oRow.isVisible = True
next i
For k = 0 To oDP.Count - 1
oShape = oDP(k)
oShape.Visible = True
Next k
End Sub
Attachments
Last edited by Hagar Delest on Sat Jul 31, 2021 9:59 am, edited 1 time in total.
Reason:tagged solved.
Select one of the pics.
menu:Tools>AddOns>Mri (selection)
Method getByIndex --> 0 (the only selected element)
browse the properties of that element.
Villeroy wrote:A database could do what you want.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
The way the images jump up on the page I don't think this is going to work. I did mark the check box Protect Position for all the images.
I did read that LibreOffice will hide the image if it is anchored to a cell in the hidden row. But I'm not sure I want to change all my oo installations to address this one issue
Click "Methods"
Double-click getByIndex() and choose 0
Now you see the methods of the first (and only) selected element, the picture.
Go back to "Properties".
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Thanks Villeroy
This is the output of a selected image. I can see that there is a printable property. I placed it into my macro like oShape.Printable = False, which has fixed the printing issue
There is still the issue of when I show all the rows and images, the images do not remember which cell they were anchored to, so they appear in the wrong place. I suppose I could create an array of points and use that to locate the images, but now my little macro is getting quite complex
Rather than use .awt.point, would it be simpler to use the Anchor property? Can it be used to anchor the image to a particular cell? If so, would you be able to provide an example of how the Anchor property is used (.uno.XInterface) is used?
1. Use linked images
2. When you want to hide an image, delete the image in the folder containing the linked images, or replace it with a 1 x 1 pixel image.
The image has property "Printable" and its current value is True. I would suspect that it will be invisible on the print-out when you set that property to False. You can check this directly in MRI. Choose Mode>Set in the MRI menu, double-click the "Printable" line and set the value to False or 0. Then have another look at the print preview.
And yes, with a database all that trouble would fall off.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Wow this MRI thing is amazing.
Villeroy, Printable = False is working in my script. To place the image back where it belongs what do you think of using something like oShape.Anchor.AbsoluteName ?
John_Ha, I see your logic but I think programmatically it is not any simpler, and I will still have the issue of the image loosing it's anchor position
Ok I have this code. Not sure if it will work but shows promise
I can record the anchor position of the images into an array in Sub HideRows, but when I call the Sub ShowRows the array info is not there. What is the method to save the array so it can be used by another Sub? Thanks!
Sub HideRows
oSheet = ThisComponent.CurrentController.ActiveSheet
oDP = oSheet.DrawPage
Dim AnchorAddress(30) as String
oSheets = ThisComponent.getSheets()
oSheet1 = oSheets.getByName("Sheet1")
oRows = oSheet1.getRows()
'record the anchor position of all images
For k = 0 To oDP.Count - 1
oShape = oDP(k)
AnchorAddress(k) = oShape.Anchor.AbsoluteName()
msgbox AnchorAddress(k)
Next k
'check rows for zero quantity
for i = 5 to 29
oRow = oRows.getByIndex(i)
oCell = oSheet1.getCellByPosition(0,i) 'First row has index 0
If oCell.Value = 0 Then
'loop thru all the images to find the one in this row
For k = 0 To oDP.Count - 1
oShape = oDP(k)
oAnchor = oShape.Anchor
AnchorRow = oAnchor.CellAddress.Row
If AnchorRow = i Then
oShape.Visible = False
oShape.Printable = False
End If
Next k
' oRow.isVisible = False
End If
next i
End Sub
Sub ShowRows
oSheet = ThisComponent.CurrentController.ActiveSheet
oDP = oSheet.DrawPage
oSheets = ThisComponent.getSheets()
oSheet1 = oSheets.getByName("Sheet1")
oRows = oSheet1.getRows()
for i = 5 to 29
oRow = oRows.getByIndex(i)
oRow.isVisible = True
next i
For k = 0 To oDP.Count - 1
oShape = oDP(k)
oShape.Visible = True
oShape.Printable = True
'oShape.Anchor.AbsoluteName = AnchorAddress(k) --> ERROR ON THIS LINE
Next k
End Sub
Answering my own question...
I am writing the anchor position of each image to a cell in the sheet
When I try to assign the anchor address to the image with
The "Anchor" of your shape is a sheet object. Its property set includes the same properties as a huge cell range plus some sheet specific properties (e.g. its "Name"). The "AbsoluteName" of a cell range is the string representation of its absolute address. This string depends on the size and position of the cell range but not vice versa. You can not replace the object by giving it another address. You have to exchange the actual object, something like oShape.Anchor = objCell but there you have some other problem: Can you tell which is the cell below a shape?
How to anchor many shapes to their top-left cells:
Get the drawing toolbar or the form controls toolbar and click the icon that looks like a standard mouse pointer arrow.
With a decent mouse (no touchpad) you can draw a rectangle around all your shapes (or as many as possible) in order to select all shapes completely included by that rectangle. Then you can anchor them to their top-left cells in one go.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Hi Villeroy
If should try to "exchange the actual object" then my old code to write the anchor address to a cell should instead be write the object data for .Anchor
But where can I store that for later retrieval, I'm pretty sure I can't save object data to sheet cells
REM ***** BASIC *****
Sub HideRows
oSheet = ThisComponent.CurrentController.ActiveSheet
oDP = oSheet.DrawPage
oRows = oSheet.getRows()
'write the position of all images to the sheet
For k = 0 To oDP.Count - 1
oShape = oDP(k)
oCell = oSheet.getCellByPosition(10, k)
oCell.Value = oShape.getPosition.x
oCell = oSheet.getCellByPosition(11, k)
oCell.Value = oShape.getPosition.y
Next k
'check rows for zero quantuty
For i = 5 to 29
oRow = oRows.getByIndex(i)
oCell = oSheet.getCellByPosition(0,i) 'First row has index 0
If oCell.Value = 0 Then
'loop thru all the images to find the one in this row, then hide it
For k = 0 To oDP.Count - 1
oShape = oDP(k)
oAnchor = oShape.Anchor
AnchorRow = oAnchor.CellAddress.Row
If AnchorRow = i Then
oShape.Visible = False
oShape.Printable = False
End If
Next k
oRow.isVisible = False
End If
Next i
End Sub
Sub ShowRows
oSheet = ThisComponent.CurrentController.ActiveSheet
oDP = oSheet.DrawPage
Dim Position as New com.sun.star.awt.Point
oRows = oSheet.getRows()
' show hidden rows
For i = 5 to 29
oRow = oRows.getByIndex(i)
oRow.isVisible = True
Next i
'get the stored image positions and place the images where they belong
For k = 0 To oDP.Count - 1
oShape = oDP(k)
oShape.Visible = True
oShape.Printable = True
oCell = oSheet.getCellByPosition(10, k)
Position.x = oCell.getValue
oCell = oSheet.getCellByPosition(11, k)
Position.y = oCell.getValue
oShape.Position = Position
Next k
End Sub
