[Solved] Shapes within Calc, Positioning, Anchor Point

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
mango
Posts: 25
Joined: Mon Jan 07, 2019 12:28 am

[Solved] Shapes within Calc, Positioning, Anchor Point

Post by mango »

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

Re: Shapes within Calc, Positioning, Anchor Point

Post by Zizi64 »

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; 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.
mango
Posts: 25
Joined: Mon Jan 07, 2019 12:28 am

Re: Shapes within Calc, Positioning, Anchor Point

Post by mango »

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

Re: Shapes within Calc, Positioning, Anchor Point

Post by Zizi64 »

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; 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.
musikai
Volunteer
Posts: 294
Joined: Wed Nov 11, 2015 12:19 am

Re: Shapes within Calc, Positioning, Anchor Point

Post by musikai »

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

sub MRI
call InitializeMRI()

g = thiscomponent.currentSelection(0)

  oMRI.inspect( g )
end sub
You can find out e.g.:

Code: Select all

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/li ... itect.html
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Shapes within Calc, Positioning, Anchor Point

Post by Villeroy »

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

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

  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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
mango
Posts: 25
Joined: Mon Jan 07, 2019 12:28 am

Re: Shapes within Calc, Positioning, Anchor Point

Post by mango »

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

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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Shapes within Calc, Positioning, Anchor Point

Post by Villeroy »

Snippet recorded by MRI:

Code: Select all

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
musikai
Volunteer
Posts: 294
Joined: Wed Nov 11, 2015 12:19 am

Re: Shapes within Calc, Positioning, Anchor Point

Post by musikai »

mango wrote:

Code: Select all

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

 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/li ... itect.html
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Shapes within Calc, Positioning, Anchor Point

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Shapes within Calc, Positioning, Anchor Point

Post by Lupp »

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 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Shapes within Calc, Positioning, Anchor Point

Post by UnklDonald418 »

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.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
mango
Posts: 25
Joined: Mon Jan 07, 2019 12:28 am

Re: Shapes within Calc, Positioning, Anchor Point

Post by mango »

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
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Shapes within Calc, Positioning, Anchor Point

Post by UnklDonald418 »

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

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.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Shapes within Calc, Positioning, Anchor Point

Post by Lupp »

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 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
mango
Posts: 25
Joined: Mon Jan 07, 2019 12:28 am

Re: Shapes within Calc, Positioning, Anchor Point

Post by mango »

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
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Shapes within Calc, Positioning, Anchor Point

Post by Lupp »

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 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
mango
Posts: 25
Joined: Mon Jan 07, 2019 12:28 am

Re: Shapes within Calc, Positioning, Anchor Point

Post by mango »

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
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Shapes within Calc, Positioning, Anchor Point

Post by UnklDonald418 »

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.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Shapes within Calc, Positioning, Anchor Point

Post by Lupp »

(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 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Shapes within Calc, Positioning, Anchor Point

Post by UnklDonald418 »

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.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
mango
Posts: 25
Joined: Mon Jan 07, 2019 12:28 am

Re: Shapes within Calc, Positioning, Anchor Point

Post by mango »

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

      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
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Shapes within Calc, Positioning, Anchor Point

Post by UnklDonald418 »

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

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.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
musikai
Volunteer
Posts: 294
Joined: Wed Nov 11, 2015 12:19 am

Re: Shapes within Calc, Positioning, Anchor Point

Post by musikai »

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/li ... itect.html
mango
Posts: 25
Joined: Mon Jan 07, 2019 12:28 am

Re: Shapes within Calc, Positioning, Anchor Point

Post by mango »

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
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Shapes within Calc, Positioning, Anchor Point

Post by Lupp »

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 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
mango
Posts: 25
Joined: Mon Jan 07, 2019 12:28 am

Re: Shapes within Calc, Positioning, Anchor Point

Post by mango »

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
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Shapes within Calc, Positioning, Anchor Point

Post by robleyd »

[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
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
mango
Posts: 25
Joined: Mon Jan 07, 2019 12:28 am

Re: Shapes within Calc, Positioning, Anchor Point

Post by mango »

I opened a new post regarding my unicode question, as this is not related to the original topic of this post.
Please view:
viewtopic.php?f=20&t=97959
and answer there.
Thanks
Apache Open Office 4.1.6 on Windows 10
Post Reply