[Solved] Shapes within Calc, Positioning, Anchor Point

Creating a macro - Writing a Script - Using the API

[Solved] Shapes within Calc, Positioning, Anchor Point

Postby mango » Tue Mar 05, 2019 1:21 am

Hello altogether
I succeeded to write my macro containing Shapes like TextShape, EllipseShape and LineShape.
There is one annoying thing however that I didn't understand and it becomes more annoying as I want to add some additional features to my macro.
In my macro I set column width and row height by using SetColWidth and SetRowHeight. I do some border formatting of cells which serve as a grid for positioning my Shapes, so I'm depending on cells' size for positioning my Shapes.
I got to learn the anchor point of a shape is the upper left corner of its surrounding rectangle. I assume that for EllipseShape this is exactly the rectangle with both ellipse diameters as border lengths, for a TextShape it should be the rectangle of the Shape itself, for LineShape I would think of a rectangle with line width and length as rectangle border lengths.
What I could observe is that this seems to work fine for low row and column numbers but the higher row and column numbers are, the more are the Shapes shifted from their intended position. I had to introduce a linear shift function to position them correctly.
As I now will introduce rows with different height this becomes very tedious.
And what is more, the Shapes change their relative position to the grid when I use the view scaling slider of my sheet - different view scaling percentages lead to different positioning of shapes !!!

So what I want to know is:
- how are shapes positioned correctly?
- where is the anchor point and how can it be changed?
- is the shape's positioning rectangle identical to the shape's size?
- why does cell formatting by SetColWidth and SetRowHeight not match with Shape positioning?
- I cannot even scale my view without changing positioning of the elements - how can I get rid of this effect?
Thanks
Andreas
Last edited by mango on Sun Jun 02, 2019 10:43 pm, edited 1 time in total.
Apache Open Office 4.1.6 on Windows 10
mango
 
Posts: 25
Joined: Mon Jan 07, 2019 12:28 am

Re: Shapes within Calc, Positioning, Anchor Point

Postby Zizi64 » Mon Mar 11, 2019 7:56 am

Have you taken into account the fact that the shape will be larger by the half of the line thickness, but the reference rectangle remains the same?

Shape and line width.png


Can you upload the macro code and an ODF type sample file here?
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
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: 8233
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Shapes within Calc, Positioning, Anchor Point

Postby mango » Tue Mar 12, 2019 12:51 am

Oh, many thanks for answering, I already thought I was alone with my problem.
I'm not sure about that, as I was not aware about the line thickness of the, I suppose, surrounding rectangle. Where can I find it's thickness?
Maybe that is the reason.
As I will be out for ten days, I will dig into that then. Then I will be able to load some code, too. I beg you to hold on.
Thanks
Andreas
Apache Open Office 4.1.6 on Windows 10
mango
 
Posts: 25
Joined: Mon Jan 07, 2019 12:28 am

Re: Shapes within Calc, Positioning, Anchor Point

Postby Zizi64 » Tue Mar 12, 2019 7:45 am

I'm not sure about that, as I was not aware about the line thickness of the, I suppose, surrounding rectangle. Where can I find it's thickness?

- Manually, on the user interface:
Right click on the Shape - Line - Line (TAB) - Width

- From the macro program:
Use an object inspection tool like the MRI or the XrayTool to see all of the properties, methods, and other things of the programming objects.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
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: 8233
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Shapes within Calc, Positioning, Anchor Point

Postby musikai » Tue Mar 12, 2019 12:10 pm

As Zizi64 suggested use a Tool like MRI to get info about a selected object so you can find out a lot of things for yourself like:

With MRI installed select an object and run:
Code: Select all   Expand viewCollapse view
sub MRI
call InitializeMRI()

g = thiscomponent.currentSelection(0)

  oMRI.inspect( g )
end sub


You can find out e.g.:
Code: Select all   Expand viewCollapse view
Linewidth = g.LineWidth

rem--- Object
oPositionX = g.Position.X
oPositionY = g.Position.Y
oWidth = g.Size.Width
oHeight = g.Size.Height

rem--- Boundingbox
oBoundingboxPositionX = g.BoundRect.X
oBoundingboxPositionY = g.BoundRect.Y
oBoundingboxWidth = g.BoundRect.Width
oBoundingboxHeight = g.BoundRect.Height


The results will be different depending on the Linewidth as Zizi64 wrote.
Win7 Pro, Lubuntu 15.10, LO 4.4.7, OO 4.1.3
Free Project: LibreOffice Songbook Architect (LOSA)
http://struckkai.blogspot.de/2015/04/libreofficesongbookarchitect.html
musikai
Volunteer
 
Posts: 243
Joined: Wed Nov 11, 2015 12:19 am

Re: Shapes within Calc, Positioning, Anchor Point

Postby Villeroy » Tue Mar 12, 2019 1:40 pm

This is not callable unless the MRILib is loaded. In fact InitializeMRI is just a hint how you initialize MRI from your own code:
musikai wrote:call InitializeMRI()


Code: Select all   Expand viewCollapse view
sub myOwnMRI
REM load library from global scope ("My Macros")
REM this is what InitializeMRI suggests to do
Globalscope.BasicLibraries.LoadLibrary( "MRILib" )

g = thiscomponent.currentSelection(0)
REM call the Mri routine therein
Mri g
end sub

Alternatively, you may do the same thing as MRILib>Module1>Mri without loading the Basic library MRILib:
Code: Select all   Expand viewCollapse view
  oMRI = CreateUnoService( "mytools.Mri" )
  g = thiscomponent.currentSelection(0)
  oMRI.inspect( g )


However, you don't even need that since menu:Tools>AddOns>Mri (selection) is available from every office menu.
Select your thing and call menu:Tools>AddOns>Mri (selection) to inspect the selection.
menu:Tools>AddOns>Mri inspects the current component.
When you need to explore the capabilities of an event bind the event to macro MRILib>Module1>Mri before you bind it to your own code.
When you need to explore one of your StarBasic object variables, do as demonstrated above.
When using another macro language, the second code snippet above tells you what to do. Create an Mri instance just like any other instance of some office class and call its inspect method.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27114
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Shapes within Calc, Positioning, Anchor Point

Postby mango » Mon Mar 25, 2019 1:22 am

Hello all
now I'm back again and tried some things. Unfortunately I did not succeed yet.
I downloaded the MRI tool.
Selecting a shape and executing MRI gives me a list of properties and values, but I couldn't find something like a bounding box with a width specified. I mainly use TextShape and EllipseShape and LineShape. Where can I find these properties in MRI?

As Villeroy suggested I wrote a script to get out the properties with the intention to write out the properties to a file:
Code: Select all   Expand viewCollapse view
sub myOwnMRI
Dim s : s = "private:factory/swriter"
BasicLibraries.LoadLibrary( "MRILib" )

g = thiscomponent.currentSelection(0)
REM call the Mri routine therein
Mri g
oWriteDoc = StarDesktop.loadComponentFromURL(s, "_blank", 0, Array())
oText = oWriteDoc.getText()
oText.insertString ( oText.End, Join(g.BoundRect.X,g.BoundRect.Y, g.BoundRect.Width,g.BoundRect.Height ))
end sub

This fails with a runtime error, as BoundRect is not found.
As this property was mentioned in the post of musikai, I suppose, it will exist. But how can I access it? Do I have to load additional things?

Finally I had a look at Andrew Pitonyak's OOME Fourth Edition (Revision 633), but could not find anything related to Shape's bounding boxes?

At the moment, I'm at a dead end, I do not yet understand the concept behind this? And what is the logic behind a line width of the bounding box (surrounding rectangle)?

Hope someone can explain again.
Many thanks
Andreas
Apache Open Office 4.1.6 on Windows 10
mango
 
Posts: 25
Joined: Mon Jan 07, 2019 12:28 am

Re: Shapes within Calc, Positioning, Anchor Point

Postby Villeroy » Mon Mar 25, 2019 2:20 am

Snippet recorded by MRI:
Code: Select all   Expand viewCollapse view
Sub Snippet
  Dim oSheets As Variant
  Dim oObj1 As Variant
  Dim oDrawPage As Variant
  Dim oObj2 As Variant
  Dim oAnchor As Variant
  Dim aCellAddress As New com.sun.star.table.CellAddress

  oSheets = ThisComponent.getSheets()
  oObj1 = oSheets.getByIndex(0)
  oDrawPage = oObj1.getDrawPage()
 
  oObj2 = oDrawPage.getByIndex(0)
  oAnchor = oObj2.getAnchor()
  aCellAddress = oAnchor.getCellAddress()
 
End Sub
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27114
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Shapes within Calc, Positioning, Anchor Point

Postby musikai » Mon Mar 25, 2019 11:49 am

mango wrote:
Code: Select all   Expand viewCollapse view
oText.insertString ( oText.End, Join(g.BoundRect.X,g.BoundRect.Y, g.BoundRect.Width,g.BoundRect.Height ))

This fails with a runtime error, as BoundRect is not found.


No, I don't think BoundRect is not found. I tried your code here with a selected shape and it works after correcting the above line.

Join is a command to join an array but you don't have an array. And insertstring needs a third argument: True or False.
Code: Select all   Expand viewCollapse view
oText.insertString( oText.End, Join(array(g.BoundRect.X,g.BoundRect.Y, g.BoundRect.Width,g.BoundRect.Height)),true)
Win7 Pro, Lubuntu 15.10, LO 4.4.7, OO 4.1.3
Free Project: LibreOffice Songbook Architect (LOSA)
http://struckkai.blogspot.de/2015/04/libreofficesongbookarchitect.html
musikai
Volunteer
 
Posts: 243
Joined: Wed Nov 11, 2015 12:19 am

Re: Shapes within Calc, Positioning, Anchor Point

Postby Villeroy » Mon Mar 25, 2019 1:52 pm

Recent topic with a macro by Lupp: viewtopic.php?f=9&t=97425 (and a database solution free of macros).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27114
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Shapes within Calc, Positioning, Anchor Point

Postby Lupp » Tue Mar 26, 2019 1:36 am

Concerning the macro used for demonstration in a spreadsheet document attached to my post in the thread Villeroy linked to I want to emphasize that
Lupp wrote:...It will also run in recent AOO, but with unwanted effects concerning the sizes.


It will run. Yes, but it will not yield the result aimed at when I designed it.

Why?
The images are inserted with the help of a dispatch command. This way each image gets anchored to the cell having the focus when the command is called. I could have changed that (and anchored the images later to the sheet - called "Page" in this context), but I didn't because the images behaved the way I wanted in recent LibreOffice Calc.

They behave differently in AOO. "Why?" again.

For images (more genaral: shapes) anchored to cells, Calc traditionally tried to adapt the size when the respective cells changed their heights and/or widths. Since Calc isn't the best example for AI implementation, it did it in a sloppy way without regarding the aspect ratio. And exactly this causes the "unwanted effects" I mentioned. To get AOO to produce the same result as LibreOffice (V 6.1 or higher) the dilettante programmer I am would probably have needed an additional hour for finding the way and writing the code. I didn't.

Why?
The new handling of shapes in LibO is simply better. Firstly it offers an additional option to anchor a shape (may contain any image again) to a cell without any adaption to changing cell size. Secondly it offers (roughly) the previous behaviour but now regarding the aspect ratio in a clearly intelligent way. I personall will therefore only use LibO hence If I want to have shapes in spreadsheets.

This is not intended to persuade everybody to shift to LibO. There are disadvantages, too. Rapid development being short in manpower comes with flaws, and as I see it too much the resources were wasted for things of doubtable value. That must be accepted, however, if a project is pulled mostly by volunteers. You cannot keep them at it if they don't feel the freedom to pick one of their favourite charries now and then. Concerning this topic e.g. the new state still is lacking a method to switch between the two types of anchoring to a cell by user code.
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: 2528
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Shapes within Calc, Positioning, Anchor Point

Postby UnklDonald418 » Tue Mar 26, 2019 6:06 am

Selecting a shape and executing MRI gives me a list of properties and values, but I couldn't find something like a bounding box with a width specified. I mainly use TextShape and EllipseShape and LineShape. Where can I find these properties in MRI?

On the initial MRI screen from the object you named g, double click on BoundRect on the Properties page to open the Rectangle page.
You will see the size (Width, Height) and position (X,Y) of the object's bounding rectangle.
Select Height and then select the IDL Ref. button and your web browser should open the wiki page with the definition of a Rectangle structure.
If you haven't set up your MRI for your browser select Tools>Configuration
for the OpenOffice.org SDK directory use http://api.openoffice.org/
click on the Select button next to Browser Path to open a file picker dialog and navigate to and select the web browser you want to use.

On the Methods tab of the MRI screen, scroll down and click on insertString and then select the IDL Ref. button to see the wiki page showing the details on that 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: 1214
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Shapes within Calc, Positioning, Anchor Point

Postby mango » Wed Mar 27, 2019 1:53 am

Hello all
thanks for your answers.
I'm still a bit confused and have some more questions.
After correcting the insert string command, the MRI macro worked (thanks for that correction). I can see the output in text-file and in the opening MRI window. And there is the BoundRect, too.
What does not work is: Selecting a shape, then by menu Extras - Add Ons - MRI resp MRI <- selection (these two choices are offered). When I do this, none of the two options will show me BoundRect. I understood that this should work as well?!

Villeroy, you posted a snippet, writing "Snippet recorded by MRI". This confused me a bit, how is it recorded by MRI? I can use this snippet for examining a selection, too, and so getting the respective information of cell address, and where it's anchored?

This and the information of Lupp leads me to thinking that rather than giving concrete measures of length, I could anchor any shape directly to a cell, couldn't I?
Where is the anchor point exactly, upper left, middle ...? If that is possible, I think it could help me a lot, because the shapes I use are directly correlated to the grid.
Commentaries of Lupp made me doubt, if this will work fine regarding correct positioning changing cells width and height (I use AOO) , but I think, you didn't use shapes at all, only images, didn't you?

Thanks
Andreas
Apache Open Office 4.1.6 on Windows 10
mango
 
Posts: 25
Joined: Mon Jan 07, 2019 12:28 am

Re: Shapes within Calc, Positioning, Anchor Point

Postby UnklDonald418 » Wed Mar 27, 2019 5:54 am

The MRI page you see when you select Tools>Add-Ons>MRI <- selection shows that the object has ImplementationName of SwXTextRanges which isn't quite what you want. But notice that it has a Count property with a value of 1 so there must be another element. On the Methods page select getByIndex and you will get a dialog with only one choice, the value 0. Select that and then OK.
Now MRI displays an object with an ImplementationName of com.sun.star.comp.sc.ScShapeObj which had the BoundRect property.
which is the equivalent of
Code: Select all   Expand viewCollapse view
g = thiscomponent.currentSelection(0)

From Tools>Add-Ons>MRI you get the Model.
Graphic objects appear on DrawPages so select that.
You must find the correct sheet using the getByIndex method. If your graphics are on Sheet1 then select index 0
Now you should see an object with an ImplementationName of ScPageObj and should have a Count of the number of graphics objects on the selected Sheet.
Again use the getByIndex method to select your target graphics object. It should have the ImplementationName of com.sun.star.comp.sc.ScShapeObj
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: 1214
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Shapes within Calc, Positioning, Anchor Point

Postby Lupp » Wed Mar 27, 2019 4:02 pm

mango wrote:This and the information of Lupp leads me to thinking that rather than giving concrete measures of length, I could anchor any shape directly to a cell, couldn't I?


In fact there is nothing like an "Anchor Point". But you can assign either a SheetCell object or a Spradsheet object to the .Anchor property of a shape. The absolute measures given by the .Position property of a shape in Calc are always based on the topmost leftmost point of the area used to show the cell grid, given in the unit 1/100 mm, and valid for the view. The .Position values (.X and .Y) for shapes anchored to cells will therefore depend on column widths and row heights and may change when columns/rows get hidden/shown. An attempt to assign new values to those properties may not throw an error, but will simply be ignored.

The position of a shape changeable from user Sub is contained in the properties .HoriOrientPosition and .VertOrientPosition, and the interpretation of these properties depends on the kind of anchoring (To Cell or To Page [meaning spreadsheet in this context]).

But if you assign a value to these properties, you in fact call methods of the object which are in charge of keeping things consistent. This way such an assignment may change the anchor - or whatver - without notifying you of. I do not know specifications for all that, and I will not take the time to do a comprehensive research about it. Thus: If not you do the research, you should strictly restrict related user code to set the anchor (cell or sheet), and probably not too large positive values to the ...OrientPosition properties. What's too large? Depends on the anchoring type again. Do research.
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: 2528
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Shapes within Calc, Positioning, Anchor Point

Postby mango » Thu Mar 28, 2019 1:17 am

UnklDonald418, thanks for clarifying this, I could reproduce what you wrote, for an inexperienced user really very odd to get through that all.

Lupp, before I start a try next weekend, one more question:
Do you know about any negative effects, scaling the view or printing? I just experienced this with my present way of programming this, as I wrote in my first post - at least changing the scaling with the zoom slider - shapes were shifted relative to each other, their position was not maintained in a consistent way. I'd like to be sure that positioning the shapes really keeps them to their grid position and scaling will have a consistent effect on all of the shapes.
Apache Open Office 4.1.6 on Windows 10
mango
 
Posts: 25
Joined: Mon Jan 07, 2019 12:28 am

Re: Shapes within Calc, Positioning, Anchor Point

Postby Lupp » Thu Mar 28, 2019 2:23 am

Sorry. I next to exclusively am using LibreOffice, fresh version. My AOO 4.1.5 is only used for some experiments. In specific concerning shapes there were relevant changes in LibO recently, and this may have spoilt the reliability of my memories concerning previous behaviour.

mango wrote:... - at least changing the scaling with the zoom slider - shapes were shifted relative to each other, their position was not maintained in a consistent way.

I never experienced such an effect when changing the zoom factor, but if that's the case it's surely an effect of bugs, and I cannot imagine a way to get rid of it on the user level. If at all, by lots of programming. It may be, however, that you got unexpected effects due to the way AOO handles the resizing of shapes under changes of width and or height of the cells they are anchored to: Shapes completely inside don't resize at all. Shapes crossing cell borders are resized in a way I do not know a specification for. This obviously can results in irreversibility of simple user actions.

LibO 6.1 and higher have a clear and reasonable behaviour insofar.
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: 2528
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Shapes within Calc, Positioning, Anchor Point

Postby mango » Mon Apr 15, 2019 12:01 am

Sorry that I have to ask again:
I think there could be three ways to get a solution, but I do not get the clue to none of them yet:
1)
BoundRect: I could see out the MRI tool that there is a bounding Rectangle enhancing my shapes by 3 and that coincides pretty well with the shifted shapes.
But how can I access this property by scripting? Could not find anything in the documentation called like BoundRect. If I could set this to 0, possibly I could solve the problem
2)
I couldn't find how to get the anchor point of the shapes within my sheet, either. There is getAnchor() for the XTextContent and XResourceId interfaces, but is there something similar for shapes?
3)
As my shapes coincide with the grid, and thus I do have the respective row and column number, how could I retrieve the position of the cell?
There are various methods getPosition(), but they do not work for cells. The service SheetCell contains a property position, but I could not find how to use that in order to retrieve the cell's position in the sheet. Someone can tell me if that is possible? I would try to use this position to align my shape.

Thanks
Andreas
Apache Open Office 4.1.6 on Windows 10
mango
 
Posts: 25
Joined: Mon Jan 07, 2019 12:28 am

Re: Shapes within Calc, Positioning, Anchor Point

Postby UnklDonald418 » Mon Apr 15, 2019 4:07 am

Be aware that Shapes are on DrawPages and Cells are on Sheets. They use different approaches to positioning.

Using the MRI tool navigate to your BoundRect object.
On the Properties tab select Anchor.
Now select AbsoluteName to see the cell name of the anchor point if it is a cell, or a range if it is the Page.
Scroll down and select Position to see the absolute position of the anchor point.
Press <Ctrl>H and a script pane will open in the bottom of the MRI window.
You can copy that script to the clipboard and paste it into you macro.
The only unknown is oInitialTarget.
If you used Tools>Add-Ons>MRI to access your object, replace oInitialTarget with ThisComponent.
It you used Tools >Add-Ons>MRI Selection to access your object, replace oInitialTarget with oEv.Source.
Of course your macro must define oEv in Sub definition.
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: 1214
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Shapes within Calc, Positioning, Anchor Point

Postby Lupp » Mon Apr 15, 2019 9:11 am

(In a hurry)
I don't understand for what reason the OQ insists on using the .BoundRect property. The shape should have a .Size property after all.
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: 2528
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Shapes within Calc, Positioning, Anchor Point

Postby UnklDonald418 » Mon Apr 15, 2019 8:27 pm

As Lupp pointed out, Shape objects have both getSize() and getPosition() as well as setSize() and setPosition() methods.
The Height and Width values of the BoundRect are larger than the Shape object so that the Shape object will fit inside. There are no methods to adjust the Size and Position of a BoundRect, apparently those adjustments are made internally based on the Shape.
Using code I can easily manipulate the Size and Position of a Shape, but my attempts to adjust the Size and/or Position of the BoundRect were either ignored or when I tried setPropertyValue() it generated a PropertyVetoException.
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: 1214
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Shapes within Calc, Positioning, Anchor Point

Postby mango » Mon Apr 15, 2019 11:47 pm

Thanks for your answers.
Of course I do use the setSize() and setPosition() methods.
I'll give you a short excerpt of the macro
Code: Select all   Expand viewCollapse view
      nRowBeg = nRowDistBeg + nRowDist*2 + nRowStrings*2
      fHeightBeg = nRowBeg*fRowHeight - fShiftPosY
      IndexBegNote = 0 
      iCount = 0
      For i% = nRowStrings+1 To 1 Step -1
         IndexBegNote = IndexBegNote + aBegNoteString(iCount)
         For j% = nColStart + 1 to nColEnd
            IndexNote = ((IndexBegNote+j%-1) mod nNotes)
            If bNotesInScale(IndexNote) Then
               oTextf = oDoc.createInstance("com.sun.star.drawing.TextShape")
               mPoint.X = j%*fColWidth - fTextFieldWidth/2 - fTextFieldWidth*fFacTextfX*j%/nColEnd
               mPoint.Y = fHeightBeg + (i%-1)*fRowHeight - fTextFieldHeight/2 - fTextFieldHeight*fFacTextfY*(nRowBeg+i%)/(nRowDistBeg + nRowDist*nStringPattern + nRowStrings*nStringPattern)
               mSize.Width = fTextFieldWidth
               mSize.Height = fTextFieldHeight
           
               oDrawPage.add(oTextf)
               With oTextf
                 .FillColor = RGB(220,220,220)
                 .LineStyle = com.sun.star.drawing.LineStyle.SOLID
                 .LineCOLOR = RGB(100,100,100)
                 .textVerticalAdjust   = com.sun.star.drawing.TextVerticalAdjust.CENTER
                 .textHorizontalAdjust = com.sun.star.drawing.TextHorizontalAdjust.CENTER
                 .FillStyle = com.sun.star.drawing.FillStyle.SOLID
                 .String = aNotes(IndexNote)
                 .CharFontName = sCharFontName
                 .CharHeight = nCharHeightTextField
                 .CharWeight = com.sun.star.awt.FontWeight.BOLD
               End With
               oTextf.setPosition(mPoint)
               oTextf.setSize(mSize)
            End If
         Next j% 
         iCount = iCount + 1 
      Next i%

But that's exactly what's bugging me, setPosition(mPoint) does not coincide with the grid position, so I inserted that tedious linear correction function for mPointX and mPointY.
That did the trick for a while, but now I want to add some code with different row heights, and this will get more complicated. I want to get ride of that stuff as there should be a way to position the shapes right onto the grid, calculating the position by column width and row height. That's why I thought of anchor points, but I think, I may have misunderstood this concept.
What I could observe is that shifted positions of the shapes seem to correlate with the difference between BoundRect-size and shape size, by multiples of column resp. row number. Why this is so, no idea.
Do draw pages and grid not coincide? Or is there something fundamental, I'm still missing?
Thanks
Andreas
Apache Open Office 4.1.6 on Windows 10
mango
 
Posts: 25
Joined: Mon Jan 07, 2019 12:28 am

Re: Shapes within Calc, Positioning, Anchor Point

Postby UnklDonald418 » Tue Apr 16, 2019 7:34 pm

I put together a small macro that steps through all the Shapes on a selected DrawPage and fits them inside the Spreadsheet Cell they are anchored to, skipping the Shapes anchored to the Page.

Code: Select all   Expand viewCollapse view
REM  *****  BASIC  *****

Sub RepositionShape()

  Dim oDP as object
  Dim oShape as object
  Dim oAnchor as object
  Dim i as Integer
       
  oDP = ThisComponent.DrawPages.getByIndex(2)  'get DrawPage for target Calc Sheet

for i = 0 to oDP.count - 1
    oShape = oDP.getByIndex(i)   
    oAnchor = oShape.Anchor
REM check to see if Shape is anchored to a Sheet Cell
REM    if TRUE resize shape to fit inside the Cell
REM    if FALSE skip this shape because it is achored to the Page
   if oAnchor.SupportedServiceNames(0) = "com.sun.star.sheet.SheetCell" THEN
      oShape.setSize(oAnchor.Size)
      oShape.setPosition(oAnchor.Position) 
   End If
Next i
 
End Sub


To verify the problem you are seeing, I placed a few shapes at a good distance from the origin. The shapes I entered were larger and slightly offset from the anchor Cell.
After running the macro I recorded the results in the this screen shot.
temp3.png

As you can see the selected shape has been resized correctly but is significantly offset from the anchor point. The ellipse shape just above is similarly offset from its anchor. The rectangle to the right and above is anchored to the page.

Then I closed the spreadsheet document and reopened it with LO Version: 6.1.5.2 (x64) and ran the macro again. This screen shot shows the results.
temp4.png

Apparently there is a bug in AOO that has been addressed in LO.
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: 1214
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Shapes within Calc, Positioning, Anchor Point

Postby musikai » Tue Apr 16, 2019 10:10 pm

And to fit perfectly 1x linewidth must be substracted from the size. The borderline always shows half inside and half outside of an object. So 1/2 upper (or left) border linewidth + 1/2 lower (or right) border linewidth = 1 linewidth

This is what makes the (readonly) Boundrect always a bit larger than the size of an object.
Win7 Pro, Lubuntu 15.10, LO 4.4.7, OO 4.1.3
Free Project: LibreOffice Songbook Architect (LOSA)
http://struckkai.blogspot.de/2015/04/libreofficesongbookarchitect.html
musikai
Volunteer
 
Posts: 243
Joined: Wed Nov 11, 2015 12:19 am

Re: Shapes within Calc, Positioning, Anchor Point

Postby mango » Wed May 01, 2019 10:59 pm

Good to know that's a bug in Apache.
I downloaded LibreOffice 6.2.3.2 (x64) and everything seemed fine. Shapes were well positioned except a minor shift that I could live with.
But now I tried to print it all (and I need that printed) and I got the same mess again, everything shifted downwards in a way that it's useless to print.
I could try to shift it by an offset, so that the mismatch would be on the screen and printout fine, but I really wouldn't appreciate a solution like this.
Are there any differences with respect to screen and print-out, any setting for printing that can be changed?
Many thanks.
Apache Open Office 4.1.6 on Windows 10
mango
 
Posts: 25
Joined: Mon Jan 07, 2019 12:28 am

Re: Shapes within Calc, Positioning, Anchor Point

Postby Lupp » Sat May 04, 2019 3:36 pm

mango wrote:I downloaded LibreOffice 6.2.3.2 (x64) and everything seemed fine. Shapes were well positioned except a minor shift that I could live with.
But now I tried to print it all (and I need that printed) and I got the same mess again, everything shifted downwards in a way that it's useless to print.
Would you please attach an example showing this behaviour for you?
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: 2528
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Shapes within Calc, Positioning, Anchor Point

Postby mango » Sun May 05, 2019 7:59 pm

Is there a way to add attachments to forum posts?
Anyway, I now got rid of the grid based programming, I do not any longer use the border lines of the grid, I exchanged those lines by LineShapes. This seems to work, now I got all shapes positioned in the same manner.
Printing preview is fine, although I didn't get to print it yet.

Another problem arised:
I wanted to use musical symbols. They get very high unicode numbers as 119070. I tried to use the Chr-method, i.e. Chr(119070). That ended in an overflow-message. Entering Hex-Code did not work either.
Is that because there is a limit using Chr, do I have to load musical symbols (how will that work) to include them or what else could I do to get access to these symbols?
Apache Open Office 4.1.6 on Windows 10
mango
 
Posts: 25
Joined: Mon Jan 07, 2019 12:28 am

Re: Shapes within Calc, Positioning, Anchor Point

Postby robleyd » Mon May 06, 2019 1:21 am

[Forum] How to attach a document here Note maximum file size is 128K. If your file is larger, use a file sharing site such as Mediafire and post the link here. The link also contains information on how to anonymise your document if it contains confidential information.
Cheers
David
Apache OpenOffice Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2954
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Shapes within Calc, Positioning, Anchor Point

Postby mango » Wed May 08, 2019 12:55 am

I opened a new post regarding my unicode question, as this is not related to the original topic of this post.
Please view:
https://forum.openoffice.org/en/forum/viewtopic.php?f=20&t=97959
and answer there.
Thanks
Apache Open Office 4.1.6 on Windows 10
mango
 
Posts: 25
Joined: Mon Jan 07, 2019 12:28 am


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 6 guests