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

[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

[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   Expand viewCollapse view

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   Expand viewCollapse view 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

[Zizi64]

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

[Mondonauta]

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   Expand viewCollapse view

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   Expand viewCollapse view 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?

[Mondonauta]

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.

[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   Expand viewCollapse view

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   Expand viewCollapse view

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.

[Lupp]

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.

[Mondonauta]

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   Expand viewCollapse view

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   Expand viewCollapse view

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.

[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.

[Mondonauta]

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.

[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.

[Lupp]

From considering a different forum question:

Code: Select all   Expand viewCollapse view

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