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

Creating a macro - Writing a Script - Using the API

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

Postby Mondonauta » Sun May 19, 2019 11:43 pm

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
Last edited by Mondonauta on Tue May 21, 2019 3:52 pm, edited 2 times in total.
Windows 10, Openoffice 4.1.6
Mondonauta
 
Posts: 5
Joined: Sun May 19, 2019 11:16 pm

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

Postby UnklDonald418 » Mon May 20, 2019 4:30 am

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
 
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1159
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

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

Postby Zizi64 » Mon May 20, 2019 6:16 am

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
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.4; AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7984
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Postby Mondonauta » Mon May 20, 2019 9:15 am

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   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?
Windows 10, Openoffice 4.1.6
Mondonauta
 
Posts: 5
Joined: Sun May 19, 2019 11:16 pm

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

Postby Mondonauta » Mon May 20, 2019 2:18 pm

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.
Windows 10, Openoffice 4.1.6
Mondonauta
 
Posts: 5
Joined: Sun May 19, 2019 11:16 pm

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

Postby UnklDonald418 » Tue May 21, 2019 12:28 am

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.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1159
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

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

Postby Lupp » Tue May 21, 2019 3:02 pm

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.
Last edited by Lupp on Tue May 21, 2019 4:07 pm, edited 2 times in total.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2489
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

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

Postby Mondonauta » Tue May 21, 2019 3:51 pm

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   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.
Windows 10, Openoffice 4.1.6
Mondonauta
 
Posts: 5
Joined: Sun May 19, 2019 11:16 pm

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

Postby Lupp » Tue May 21, 2019 4:05 pm

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.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2489
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

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

Postby Mondonauta » Tue May 21, 2019 5:11 pm

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.
Windows 10, Openoffice 4.1.6
Mondonauta
 
Posts: 5
Joined: Sun May 19, 2019 11:16 pm

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

Postby Lupp » Wed May 22, 2019 1:52 am

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.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2489
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

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

Postby Lupp » Fri May 24, 2019 11:03 pm

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
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2489
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 3 guests