Page 1 of 1

[Solved] How to check if a cell in Calc "contains" an image

Posted: Sun May 19, 2019 11:43 pm
by Mondonauta
Hi all,
I created a macro in basic that iterate rows in a Calc sheet and for each row creates a text table in Writer
with some info contained in the sheet cells. Each row in Calc may have a cell containing an image,
but when i check the cell content type, it says the cell is empty.
What i understood is that a cell doesn't really contain an image, but this is contained in a different layer.
Ok, but then how can I check whether a cell "contains" an image or not?

thanks in advance

Re: How to check if a cell in Calc "contains" an image

Posted: Mon May 20, 2019 4:30 am
by UnklDonald418
Cells don't contain images. Images are contained in shapes that can be anchored to cells or pages. Shapes are on DrawPages.
Here is one way to find where images are anchored

Code: Select all

Dim i as Integer
Dim Anchors(2) as String

oPageObj = ThisComponent.DrawPages.getByIndex(0)  ' index of sheet containing images.
i = oPageObj.Count
ReDim Anchors(i)    
for i = 0 to   oPageObj.Count -1
      oShape =  oPageObj.getByIndex(i)
      Anchors(i) = oShape.Anchor.AbsoluteName
next i
If the image(s) of interest is not on the first Sheet then you will need to change the index number in getByIndex(0)
The above code should put the anchor address of each Shape in the array Anchors.
If the Shape is anchored to a cell, the results will be something like
$Sheet0.$E$19
but it could also be anchored to a page which would result in something like
$Sheet0.$A$1:$AMJ$1048576
 Edit: Be aware that not all shapes on a sheet contain images. If you only want images then change the loop to

Code: Select all

for i = 0 to   oPageObj.Count -1
      oShape =  oPageObj.getByIndex(i)
      if oShape.ShapeType = "com.sun.star.drawing.GraphicObjectShape" then
          Anchors(i) = oShape.Anchor.AbsoluteName
      end if
next i
 

Re: How to check if a cell in Calc "contains" an image

Posted: Mon May 20, 2019 6:16 am
by Zizi64
Where (which object) your images are anchored to? To a Cell, or to the Page? You can determine the ancoring object by your macros - as UnklDonald418 described it.

And you can estimate the reference of the Cell/s/ that the image will cover. You can calculate it based on the Column width, Row height values and the X, Y position values of the Image. The X, Y is the image position relatíve to the Anchor point.

Here is an example code: how to align an image to a specific cell. You must use it in a reverse wise.
https://forum.openoffice.org/en/forum/v ... =9&t=34544

Re: How to check if a cell in Calc "contains" an image

Posted: Mon May 20, 2019 9:15 am
by Mondonauta
UnklDonald418 wrote:Cells don't contain images. Images are contained in shapes that can be anchored to cells or pages. Shapes are on DrawPages.
Here is one way to find where images are anchored

Code: Select all

Dim i as Integer
Dim Anchors(2) as String

oPageObj = ThisComponent.DrawPages.getByIndex(0)  ' index of sheet containing images.
i = oPageObj.Count
ReDim Anchors(i)    
for i = 0 to   oPageObj.Count -1
      oShape =  oPageObj.getByIndex(i)
      Anchors(i) = oShape.Anchor.AbsoluteName
next i
If the image(s) of interest is not on the first Sheet then you will need to change the index number in getByIndex(0)
The above code should put the anchor address of each Shape in the array Anchors.
If the Shape is anchored to a cell, the results will be something like
$Sheet0.$E$19
but it could also be anchored to a page which would result in something like
$Sheet0.$A$1:$AMJ$1048576
 Edit: Be aware that not all shapes on a sheet contain images. If you only want images then change the loop to

Code: Select all

for i = 0 to   oPageObj.Count -1
      oShape =  oPageObj.getByIndex(i)
      if oShape.ShapeType = "com.sun.star.drawing.GraphicObjectShape" then
          Anchors(i) = oShape.Anchor.AbsoluteName
      end if
next i
 
thanks for your answer and help UnklDonald418, but my question is, while i'm iterating rows, and cells of each row, is there a way to know that 1 cell in that row has a shape anchored to it? i ask this because if in my sheet i have 100 rows full of info, but just 8 of them have pictures, it means that just when i manage those rows i have to create a text table in writer with 1 cell more for the image. With your code i understood that for each row i have to check if its cells' adresses are contained in the anchor array. am i right?

Re: How to check if a cell in Calc "contains" an image

Posted: Mon May 20, 2019 2:18 pm
by Mondonauta
Zizi64 wrote:Where (which object) your images are ancwored to? To a Cell, or to the Page? You can determine the ancoring object by your macros - as UnklDonald418 described it.

And you can estimate the reference of the Cell/s/ that the image will cover. You can calculate it based on the Column width, Row height values and the X, Y position values of the Image. The X, Y is the image position relatíve to the Anchor point.

Here is an example code: how to align an image to a specific cell. You must use it in a reverse wise.
viewtopic.php?f=9&t=34544

Thanks Zizi64 for your support, i will give a look at that code.

Re: How to check if a cell in Calc "contains" an image

Posted: Tue May 21, 2019 12:28 am
by UnklDonald418
I put together a function that if you pass it a Cell object, will return TRUE if there is a shape containing a graphic anchored to it.

Code: Select all

Function CellImageAnchor(oCell as Object) as Boolean

Dim oPageObj As Object
Dim  oConv1  As Object
Dim  oConv2  As Object
Dim sAA  As String
Dim sCA  As String
Dim shIdx  As Integer

'If Not Globalscope.BasicLibraries.isLibraryLoaded("MRILib") Then
'      Globalscope.BasicLibraries.LoadLibrary( "MRILib" )
'End If
'Dim oMRI as object
'oMRI = CreateUnoService( "mytools.Mri" )

 shIdx = oCell.CellAddress.Sheet
 CellImageAnchor = FALSE 
 oPageObj = ThisComponent.DrawPages.getByIndex(shIdx)  ' index of sheet containing images.
REM see "Section 6.6. Human readable address of cell"  in "Useful Macro Information For OpenOffice.org" by Andrew Pitonyak for details
 oConv1 = ThisComponent.createInstance("com.sun.star.table.CellAddressConversion")
 oConv2 = ThisComponent.createInstance("com.sun.star.table.CellAddressConversion")
 oConv2.Address = oCell.CellAddress
 sCA = oConv2.UserInterfaceRepresentation
for i = 0 to oPageObj.Count -1
      oShape = oPageObj.getByIndex(i)
'oMRI.inspect oShape
      if HasUnoInterfaces(oShape.Anchor,"com.sun.star.sheet.XCellAddressable") AND _ 
          oShape.ShapeType = "com.sun.star.drawing.GraphicObjectShape" then
          oConv1.Address = oPageObj.getByIndex(i).Anchor.CellAddress
         sAA = oConv1.UserInterfaceRepresentation
     	 If sCA = sAA then
	   	   CellImageAnchor = TRUE
	   	   Exit Function
     	 endif
     end if
next i

End Function
I tried to use the function EqualUNOObjects(CellAddress1, CellAddress2) for the comparison but couldn't get it to work so I converted the CellAddress objects to strings and compared those.
It seems work, at least with the limited testing I've done.

Code: Select all

sub TestCellImageAnchor
Dim oCell as Object
Dim retval as Boolean
  
oCell = ThisComponent.Sheets.getByIndex(9).getCellByPosition(4,18)
  retval = CellImageAnchor(oCell)
  oCell = ThisComponent.Sheets.getByIndex(9).getCellByPosition(5,18)
  retval = CellImageAnchor(oCell) 
   oCell = ThisComponent.Sheets.getByIndex(9).getCellByPosition(2,25)
  retval = CellImageAnchor(oCell)
    oCell = ThisComponent.Sheets.getByIndex(9).getCellByPosition(1,1)
  retval = CellImageAnchor(oCell)
     oCell = ThisComponent.Sheets.getByIndex(9).getCellByPosition(1,9)
  retval = CellImageAnchor(oCell) 
end sub
The test sheet (Sheet10) has graphic objects anchored to cells B10, C26 and E19. The sheet also has 2 control shapes anchored to cells and a graphic anchored to the page, which are all ignored by the function.

Re: How to check if a cell in Calc "contains" an image

Posted: Tue May 21, 2019 3:02 pm
by Lupp
UnklDonald418 wrote:I tried to use the function EqualUNOObjects(CellAddress1, CellAddress2) for the comparison but couldn't get it to work so I converted the CellAddress objects to strings and compared those.
We can get it simpler by using the property .AbsoluteName of the specific cell object on the one hand and of the .Anchor property of the inspected shape on the other hand. (I also tried the EqualUnoObjects function and failed. I consider it a bug; see link below.)

I also did not find a property giving access to the anchored shapes starting with the cell they need to be anchored to. It seems unavoidable to check all the shapes added to the respective DrawPage.

See also my bug report to the documentfoundation under https://bugs.documentfoundation.org/sho ... ?id=125421.

Re: How to check if a cell in Calc "contains" an image

Posted: Tue May 21, 2019 3:51 pm
by Mondonauta
UnklDonald418 wrote:I put together a function that if you pass it a Cell object, will return TRUE if there is a shape containing a graphic anchored to it.

Code: Select all

Function CellImageAnchor(oCell as Object) as Boolean

Dim oPageObj As Object
Dim  oConv1  As Object
Dim  oConv2  As Object
Dim sAA  As String
Dim sCA  As String
Dim shIdx  As Integer

'If Not Globalscope.BasicLibraries.isLibraryLoaded("MRILib") Then
'      Globalscope.BasicLibraries.LoadLibrary( "MRILib" )
'End If
'Dim oMRI as object
'oMRI = CreateUnoService( "mytools.Mri" )

 shIdx = oCell.CellAddress.Sheet
 CellImageAnchor = FALSE 
 oPageObj = ThisComponent.DrawPages.getByIndex(shIdx)  ' index of sheet containing images.
REM see "Section 6.6. Human readable address of cell"  in "Useful Macro Information For OpenOffice.org" by Andrew Pitonyak for details
 oConv1 = ThisComponent.createInstance("com.sun.star.table.CellAddressConversion")
 oConv2 = ThisComponent.createInstance("com.sun.star.table.CellAddressConversion")
 oConv2.Address = oCell.CellAddress
 sCA = oConv2.UserInterfaceRepresentation
for i = 0 to oPageObj.Count -1
      oShape = oPageObj.getByIndex(i)
'oMRI.inspect oShape
      if HasUnoInterfaces(oShape.Anchor,"com.sun.star.sheet.XCellAddressable") AND _ 
          oShape.ShapeType = "com.sun.star.drawing.GraphicObjectShape" then
          oConv1.Address = oPageObj.getByIndex(i).Anchor.CellAddress
         sAA = oConv1.UserInterfaceRepresentation
     	 If sCA = sAA then
	   	   CellImageAnchor = TRUE
	   	   Exit Function
     	 endif
     end if
next i

End Function
I tried to use the function EqualUNOObjects(CellAddress1, CellAddress2) for the comparison but couldn't get it to work so I converted the CellAddress objects to strings and compared those.
It seems work, at least with the limited testing I've done.

Code: Select all

sub TestCellImageAnchor
Dim oCell as Object
Dim retval as Boolean
  
oCell = ThisComponent.Sheets.getByIndex(9).getCellByPosition(4,18)
  retval = CellImageAnchor(oCell)
  oCell = ThisComponent.Sheets.getByIndex(9).getCellByPosition(5,18)
  retval = CellImageAnchor(oCell) 
   oCell = ThisComponent.Sheets.getByIndex(9).getCellByPosition(2,25)
  retval = CellImageAnchor(oCell)
    oCell = ThisComponent.Sheets.getByIndex(9).getCellByPosition(1,1)
  retval = CellImageAnchor(oCell)
     oCell = ThisComponent.Sheets.getByIndex(9).getCellByPosition(1,9)
  retval = CellImageAnchor(oCell) 
end sub
The test sheet (Sheet10) has graphic objects anchored to cells B10, C26 and E19. The sheet also has 2 control shapes anchored to cells and a graphic anchored to the page, which are all ignored by the function.
Thanks for your piece of code. it is a very good help and i can consider this thread solved, but i guess it's confirmed that in Calc the cell object doesn't have any knowledge if a shape is anchored to it or not... which i think would have been smarter.

Re: [Solved] How to check if a cell in Calc "contains" an im

Posted: Tue May 21, 2019 4:05 pm
by Lupp
Well, there also are unpublished properties / methods.
At least columns and rows should "know" the shapes anchored to their cells. After all the information is needed under specific conditions to make sure that the respective shapes get correctly resized when the height or the width of the cells they are anchored to is changed.

However, for realistic numbers of shapes added to a single sheet's DrawPage the resulting loss of efficiency in case of no specific access may be acceptable.

Re: [Solved] How to check if a cell in Calc "contains" an im

Posted: Tue May 21, 2019 5:11 pm
by Mondonauta
Lupp wrote:Well, there also are unpublished properties / methods.
At least columns and rows should "know" the shapes anchored to their cells. After all the information is needed under specific conditions to make sure that the respective shapes get correctly resized when the height or the width of the cells they are anchored to is changed.

However, for realistic numbers of shapes added to a single sheet's DrawPage the resulting loss of efficiency in case of no specific access may be acceptable.
Well Lupp, to be sincere i disagree with you when you say "no specific access may be acceptable for realistic numbers of shapes".
Of course i can survive it, but as i said before it would have been smarter and faster (from a programming point of view) if we had methods such as oCell.hasAnchoredShape() (returning True/False), oCell.getAnchoredShape() and (why not?) oCell.setAnchoredShape(oShape).
But of course that's just my point of view.

Re: [Solved] How to check if a cell in Calc "contains" an im

Posted: Wed May 22, 2019 1:52 am
by Lupp
Probably I didn't quite correctly express my thoughts in English. At least I'm not quite sure if your changeover of syntactical parts of my statement left the meaning unchanged.

Anyway (afaik):
Graphical elements may be anchored to cells in spreadsheets or to paragraphs of text in Writer. In case of Writer the text may belong to a subordinate object like a TextFrame or a cell of a TextTable. In no case the object to which the shape is anchored keeps a list of anchored objects. Even if you have a text document of 1000 pages with nested frames and tables and everything, you need to loop through the (one!) DrawPage if you want to find shapes. Only TextFrames, which are of a special shape type, are additionally added to the .TextFrames container of the document. Generally such redundancies are avoided. Of course, I cannot judge if not another way of implementation might have given better results.

Re: [Solved] How to check if a cell in Calc "contains" an im

Posted: Fri May 24, 2019 11:03 pm
by Lupp
From considering a different forum question:

Code: Select all

Function getSpreadsheetDrawPageShapesAnchoredHere(pAnchor)
REM Only a single cell or a complete spreadsheet are allowed
REM as the .Anchor of a shape!
Dim r()
getSpreadSheetDrawPageShapesAnchoredHere = r
On Error GoTo errorExit
dp = pAnchor.Spreadsheet.DrawPage
For k = 0 To dp.Count - 1
 kSh = dp(k)
 If kSh.Anchor.AbsoluteName=pAnchor.AbsoluteName Then
  Redim Preserve r(Ubound(r) + 1)
  r(Ubound(r)) = kSh
 End If
Next k
getSpreadSheetDrawPageShapesAnchoredHere = r
errorExit:
End Function