Handling of Images in Calc

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Albireo
Posts: 113
Joined: Wed Apr 15, 2009 12:05 pm

Handling of Images in Calc

Post by Albireo »

Hi!
I have written a program in AutoHotkey That adds and places a Picture into Calc (Which works)
but, I experiencing a variety of restrictions, that I do not know how they are managed.
This is the code(below) in Autohotkey language (I have not translated the code back to Basic).
Feel free to answer with examples from Basic or other programming language.

Code: Select all

#Warn ; Enable warnings to assist with detecting common errors.
SetBatchLines -1
;#SingleInstance force
;#NoEnv
; ;Persistent
; Process Priority,, High
SendMode Input ; Recommended for new scripts due to its superior speed and reliability. 
SetWorkingDir %A_ScriptDir% ; Ensures a consistent starting directory.

; Create a new Calc spreadsheet. 
oSM := ComObjCreate("com.sun.star.ServiceManager")
; objCoreReflection := objServiceManager.createInstance("com.sun.star.reflection.CoreReflection")
oDesk := oSM.createInstance("com.sun.star.frame.Desktop")	; Create the Desktop	
oArgs := ComObjArray(VT_VARIANT := 12, 3)
oArgs[0] := MakePropertyValue(oSM, "", "")

sURL := "private:factory/scalc"
oDoc := oDesk.loadComponentFromURL(sURL, "_blank", 0, oArgs)
; oSheet := oDoc.getSheets().getByIndex( 0 )
oSheet := oDoc.Sheets.getByIndex( 0 )

LogoName = LogoExpo.jpg
LogoPath = C:\Users\Personal\Documents\Expo\Logo\Logotyper 2012\Squid

Logo := LogoPath "\" LogoName
IfNotExist %Logo%
{	MsgBox 64, Rad %A_LineNumber% -> %A_ScriptName%, Missing image .: %LogoName% `nPath .: %LogoPath% `n`nProgram is ended!
	ExitApp
}

StringReplace ImageURL, Logo, \, `/, All
ImageURL := "file:///" ImageURL	; ImageURL := "C:/Users/Personal/Documents/Expo/Logo/Logotyper 2012/Squid/LogoExpo.jpg"
; MsgBox 64, Rad %A_LineNumber% -> %A_ScriptName%, Logo .:`n%Logo% `n`npicURL .:`n%ImageURL%

oImage := oDoc.createInstance("com.sun.star.drawing.GraphicObjectShape")	; oImagen_obj = ThisComponent.createInstance("com.sun.star.drawing.GraphicObjectShape")
oImage.GraphicURL := ImageURL
oSize := oImage.Size
oSize.Height := 5000
oSize.Width := 5000
oImage.Size := oSize
oPos := oImage.Position
oPos.X := 5000
oPos.Y := 5000
oImage.Position := oPos

oDP := oDoc.DrawPages.getByIndex(0)
oDP.add(oImage)

MsgBox 64, Rad %A_LineNumber% -> %A_ScriptName%, Ready!, 2
ExitApp

; - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
; Function MakePropertyValue(cName, uValue)
; Version 2 juni 2016
MakePropertyValue(oSM, cName, uValue)
{	; oSM := ComObjCreate("com.sun.star.ServiceManager")
	oPropertyValue := oSM.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
	
	oPropertyValue.Name := cName
	oPropertyValue.Value := uValue
	
	Return oPropertyValue
}
1.
Is there any method, to cause the image to place it behind other objects (like text)?

2.
In the example above, you must give values of both height and width. (the images loses usually the aspect ratio )
Is there a way where only one dimensions on the image is specified, and the other is follow?

3.
Is there any way in which the image follows the cells instead of x / y coordinates as the above example?

4.
Suppose a simple product catalog will be built up in Calc. To each product must an image and a barcode automatically be linked.
How to solve this in the best way?

5.
Is it possible to fill the entire cell with only one image? (even with merged cells)

6.
How do you know that the image will be included in the document (Calc)?

7.
Where to find more examples and ideas around image handle, primarily in Calc?

//Jan
OOo 4.1.X on Windows XP, Win7, 10
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Handling of Images in Calc

Post by Zizi64 »

2.
In the example above, you must give values of both height and width. (the images loses usually the aspect ratio )
Is there a way where only one dimensions on the image is specified, and the other is follow?
It is a program code. You can get the original width and height values, and you can calculate the ratio of the height and width, and then you can adjust the size of the picture based on the ratio value.
 Edit: See this macro example:
viewtopic.php?f=20&p=57216 
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Handling of Images in Calc

Post by Zizi64 »

5.
Is it possible to fill the entire cell with only one image? (even with merged cells)
Yes, it is possible.
The embedded pictures not located IN A CELL, but they are on a graphic layer above the cell-layer. You can align pictures to a cell or to some merged cells cell virtually by a macro code.

See this example:
viewtopic.php?f=9&t=34544&p=158333
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Handling of Images in Calc

Post by Zizi64 »

1.
Is there any method, to cause the image to place it behind other objects (like text)?
How do you meant it? Do you want to place one graphic object behind an another graphic object; or do you want to place a graphic object into the background of the cell(s) or the textual cell content?
Last edited by Zizi64 on Mon Jun 06, 2016 7:41 pm, edited 1 time in total.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Handling of Images in Calc

Post by Zizi64 »

6.
How do you know that the image will be included in the document (Calc)?
You can check if the image is embedded or linked (ba a macro code).

See this example:
https://gist.github.com/kuldikin/8980925
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Handling of Images in Calc

Post by Zizi64 »

7.
Where to find more examples and ideas around image handle, primarily in Calc?
Everywhere in the WWW; for example in this forum, too...


Please read Andrew Pitonyak's books:
http://www.pitonyak.org/oo.php


and you need study the API functions:
http://www.openoffice.org/api/
http://api.libreoffice.org/


and download and istall the XrayTool,
viewtopic.php?f=20&t=54217
or the MRI
http://extensions.services.openoffice.o ... ction-tool
You can examine the graphical (and other type) objects of the open source office suites with these tools.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Handling of Images in Calc

Post by Villeroy »

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
Albireo
Posts: 113
Joined: Wed Apr 15, 2009 12:05 pm

Re: Handling of Images in Calc

Post by Albireo »

Zizi64 wrote:
2.
...
 Edit: See this macro example:
viewtopic.php?f=20&p=57216 
point 2
The link above, reads only the width and height of an image that already exists on the worksheet. (If I understand link to "Adjusting the image to the" Original Size "through macro")

I want, try to read the image information (the width and height), before the picture inserted on the spreadsheet. ( I think it´s possible to do on another way with another external program)
I have no idé how to do in another way?
Anyway - thank you for this idea.
OOo 4.1.X on Windows XP, Win7, 10
Albireo
Posts: 113
Joined: Wed Apr 15, 2009 12:05 pm

Re: Handling of Images in Calc

Post by Albireo »

Zizi64 wrote:
1.
Is there any method, to cause the image to place it behind other objects (like text)?
How do you meani it? Do you want to place one graphic object behind an another graphic object; or do you want to place a graphic object into the background of the cell(s) or the textual cell content?
Yes!
I want to place a graphic object into the background of the cell(s) or the textual cell content.
OOo 4.1.X on Windows XP, Win7, 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Handling of Images in Calc

Post by Villeroy »

How would you do that in the GUI? If you can't do it in the GUI, it is very unlikely that you can do it by means of API calls.
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
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Handling of Images in Calc

Post by RoryOF »

Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Handling of Images in Calc

Post by Zizi64 »

Zizi64 wrote:
1.
Is there any method, to cause the image to place it behind other objects (like text)?

How do you meant it? Do you want to place one graphic object behind an another graphic object; or do you want to place a graphic object into the background of the cell(s) or the textual cell content?

Yes!
I want to place a graphic object into the background of the cell(s) or the textual cell content.
viewtopic.php?f=20&t=45027 :

Code: Select all

' push graphic to background
oShape.LayerId = 1

' push graphic to usual layer
oShape.LayerId = 0
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
Albireo
Posts: 113
Joined: Wed Apr 15, 2009 12:05 pm

Re: Handling of Images in Calc

Post by Albireo »

Zizi64 wrote:
5.
Is it possible to fill the entire cell with only one image? (even with merged cells)
Yes, it is possible.
The embedded pictures not located IN A CELL, but they are on a graphic layer above the cell-layer. You can align pictures to a cell or to some merged cells cell virtually by a macro code.

See this example:
viewtopic.php?f=9&t=34544&p=158333
Thank you!
Have tried to analyze the responses. But on this one, I got stuck.
Right now I don't know how to go further.
The code I want to translate (to Autohotkey), and test the function on, is .:

Code: Select all

Sub fitGraphics()

   Dim oDoc As Object
   Dim oSheet As Object
   Dim aCell As Object
   Dim g as Object
   Dim c As Integer
   Dim s As new com.sun.star.awt.Size
   Dim gp As new com.sun.star.awt.Point     
   Dim ap As new com.sun.star.awt.Point     
   Dim p As new com.sun.star.awt.Point     
   Dim xAdjust As Long
   Dim yAdjust As Long
   Dim rowHeight As Long
   Dim colWidth As Long
   
   oDoc = ThisComponent
   
   oSheet = oDoc.CurrentController.ActiveSheet
   
   c = oSheet.DrawPage.count
   
   Do While c >= 1
         g = oSheet.DrawPage(c - 1)
         if InStr(g.ShapeType,"GraphicObjectShape") > 0 then
            s = g.getSize()
            gp = g.getPosition()
            aCell = g.anchor
            rowHeight = oSheet.Rows(aCell.CellAddress.Row).Height
            colWidth = oSheet.Columns(g.anchor.CellAddress.Column).Width
            
            if rowHeight < s.Height then
               oSheet.Rows(aCell.CellAddress.Row).Height = s.Height
               yAdjust = 0
         else
            yAdjust = (rowHeight - s.Height) \ 2
         endif
         if colWidth < s.Width then
               oSheet.Columns(aCell.CellAddress.Column).Width = s.Width
               xAdjust = 0
         else
            xAdjust = (colWidth - s.Width) \ 2
         endif      
            ap = aCell.Position()
            gp = g.getPosition()
            if (rowHeight < s.Height) or colWidth < s.Width then
               g.setSize(s)
               g.setPosition(ap)
            else
               p.X = ap.X + xAdjust
               p.Y = ap.Y + yAdjust
               g.setPosition(p)
            endif
         endif
         c = c - 1
   Loop
   
End Sub
(is coming from this site .: Aligning images in cells - as I got from Zizi64)

Right now, the testing program looks like as follows.:

Code: Select all

; create a new Calc spreadsheet. 
oSM := ComObjCreate("com.sun.star.ServiceManager")
oDesk := oSM.createInstance("com.sun.star.frame.Desktop")	; Create the Desktop

oArgs := ComObjArray(VT_VARIANT := 12, 3)
; Öppna ett nytt tomt Kalkylblad
sURL := "private:factory/scalc"
oDoc := oDesk.loadComponentFromURL(sURL, "_blank", 0, oArgs)

oSheets := oDoc.getSheets()
oSheet := oDoc.getSheets().getByIndex( 0 )

; - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
; Insert a rectangle on the sheet
oDrawP := oDoc.getDrawPages().getByIndex(0)
oShape := oDoc.createInstance("com.sun.star.drawing.RectangleShape")

; Position
oPoint := oShape.Position
oPoint.X := 1000	; unit : 1/100mm
oPoint.Y := 1000	; unit : 1/100mm
oShape.Position := oPoint

; Size
oSize := oShape.Size
oSize.Height := 1200	; unit : 1/100mm
oSize.Width :=  1500	; unit : 1/100mm
oShape.Size := oSize
oDrawP.add(oShape)

; This code is coming from the site above.
s := oSM.Bridge_GetStruct("com.sun.star.awt.Size")	; Create a Size struct.
gp := oSM.Bridge_GetStruct("com.sun.star.awt.Point")
ap := oSM.Bridge_GetStruct("com.sun.star.awt.Point")
p := oSM.Bridge_GetStruct("com.sun.star.awt.Point")

oDrawPages := oDoc.DrawPages
c := oDrawPages.count	; Number of Sheets


While (c >= 1)
{	g := oSheet.DrawPage(c - 1)	; number on this Sheet

	if InStr(g.ShapeType,"GraphicObjectShape") > 0
	{	s := g.getSize()
		gp := g.getPosition()
		aCell := g.anchor
		rowHeight := oSheet.Rows(aCell.CellAddress.Row).Height
		colWidth := oSheet.Columns(g.anchor.CellAddress.Column).Width

		if rowHeight < s.Height
		{	oSheet.Rows(aCell.CellAddress.Row).Height := s.Height
			yAdjust := 0
		}
	}
	else
		yAdjust := ( rowHeight - s.Height ) `/ 2
		
	if colWidth < s.Width
	{	oSheet.Columns(aCell.CellAddress.Column).Width := s.Width
		xAdjust := 0
	}
	else
		xAdjust := ( colWidth - s.Width ) `/ 2
	
	ap := aCell.Position()
	gp := g.getPosition()
	if ( rowHeight < s.Height ) or ( colWidth < s.Width )
	{	g.setSize(s)
		g.setPosition(ap)
	}
	else
	{	p.X := ap.X + xAdjust
		p.Y := ap.Y + yAdjust
		g.setPosition(p)
	}
	
	; MsgBox 64, Rad %A_LineNumber% -> %A_ScriptName%, Yes! `nA_Index = %A_Index% `nc = %c%
	EnvSub c, 1
}

MsgBox 64, Rad %A_LineNumber% -> %A_ScriptName%, Ready!, 2
ExitApp


; - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
; Function MakePropertyValue(cName, uValue)
; Version 2 juni 2016
MakePropertyValue(oSM, cName, uValue)
{	; oSM := ComObjCreate("com.sun.star.ServiceManager")	Defined elsewhere

	oPropertyValue := oSM.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
	
	oPropertyValue.Name := cName
	oPropertyValue.Value := uValue
	
	Return oPropertyValue
}
It mostly work,s but the instruction that I've got stuck on is.:

Code: Select all

if InStr(g.ShapeType,"GraphicObjectShape") > 0
The message I got is
Error: 0x80020006 – Unknown Command
Specifically: ShapeType
- Is the created rectangle in the code, a wrong type to check and align to the cell?
- Can it be written on another way?
OOo 4.1.X on Windows XP, Win7, 10
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Handling of Images in Calc

Post by Zizi64 »

The 'InStr()' is a StarBasic function, but it is not AOO/LO API function.

Maybe your development tool has not such function named 'InStr' or if it exist, maybe it needed different parameters, or different type of parameters.
You can call the API functions from your application, but (maybe) you can not call the StarBasic functions (by this simple method) from a foreign application.

(I do not know anything about that 'Autohotkey' thing...)
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
Albireo
Posts: 113
Joined: Wed Apr 15, 2009 12:05 pm

Re: Handling of Images in Calc

Post by Albireo »

InStr() - StarBasic function .:
"Attempt to find string 2 in string 1. Returns 0 if not found and starting location if it is found." (explained by Andrew Pitonyak)
In this case, I hope that "GraphicObjectShape" is found in the variable.: g.ShapeType
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

In Autohotkey Help InStr - Autohotkey example

Code: Select all

Haystack := "The Quick Brown Fox Jumps Over the Lazy Dog"
Needle := "Fox"
If InStr(Haystack, Needle)
  MsgBox, The string was found.
Else
  MsgBox, The string was not found.
(The result was "The string was found")
Other "similar" instructions in autohotkey are .: IfInString and RegExMatch
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

There is something with "ShapeType" I don't understand.
I got the same error (Unknown Name - ShapeType) if I try to give a variabel that value like this .:

Code: Select all

....
   g := oSheet.DrawPage(0)   ; number on this Sheet
   GraphicType := g.ShapeType
   if InStr(GraphicType,"GraphicObjectShape") > 0   ; The rectangle above was found
....
The full AutoHotkey script above, is an external program as .:
- Opens an empty spreadsheet
- Draws a rectangle (which I thought manage).
- And the check if any image is on the sheet, (goes wrong).
OOo 4.1.X on Windows XP, Win7, 10
Albireo
Posts: 113
Joined: Wed Apr 15, 2009 12:05 pm

Re: Handling of Images in Calc

Post by Albireo »

Thanks!
Excuse my ignorance! (and stupid questions).

I have tried to check what type of Object "g" is With

Code: Select all

MsgBox % ComObjType(g, "Name")
in Autohotkey, but it gave no results.
(but i'm not sure I can do it on this way)

There is something wrong when the object "g" is assigned values.

Code: Select all

g := oSheet.DrawPage(c - 1)
Is there more information about "DrawPage"?
Maybe I must add some services, in some way? (com.sun.star.drawing) or add someting (oSheet.DrawPage.Forms) - (Forms is not correct)

I have no idea why "g" not get a value.
No problem with "c" "c = oSheet.DrawPage.count"
OOo 4.1.X on Windows XP, Win7, 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Handling of Images in Calc

Post by Villeroy »

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
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Handling of Images in Calc

Post by Zizi64 »

object inspection with MRI
... or install and use the XrayTool:
viewtopic.php?f=20&t=54217
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
Albireo
Posts: 113
Joined: Wed Apr 15, 2009 12:05 pm

Re: Handling of Images in Calc

Post by Albireo »

Zizi64 wrote:
object inspection with MRI
... or install and use the XrayTool:
viewtopic.php?f=20&t=54217
Do not think it helps when the instructions is running outside the calc?

Now I create a New spreadsheet with (VBA)

Code: Select all

Set oSM = CreateObject(“com.sun.star.ServiceManager”)
Set oDesk = oSM.createInstance(“com.sun.star.frame.Desktop”)
Set oDoc = oDesk.loadComponentFromURL(“private:factory/scalc”, “_blank”, 0, arg)
(for example)

I want to try the script with LibreOffice
How can I make the choice (LibreOffice / OpenOffice)?.

//Jan
OOo 4.1.X on Windows XP, Win7, 10
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Handling of Images in Calc

Post by Zizi64 »

Do not think it helps when the instructions is running outside the calc?
But you can examine the properties, methods, interfaces of the objects inside the AOO StarBasic
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
Post Reply