[Solved]TextShape within a CalcSpreadsheet,no output visible

[mango]

Hi all
I want to introduce a Textfield with corresponding text inside a spreadsheet.
For this I wrote the code below.
oDoc is the document, oSheet the corresponding sheet.
The code runs through, but:
In the corresponding sheet "oSheet" no output can be seen. Clicking on the area, where I expect the textfield, I can see an empty frame without border line.
and the print outputs deliver:
print oTextf.Position.X ---> 100 as set in the macro
print oTextf.CharFontName ---> Times New Roman ??? why, I set this to Courier?
print oTextf.String ---> this is expected to be xxx, but nothing is output, so that explains, why the textfield is empty, but why isn't the input accepted?

What did I miss? I get the TextShape instantiated and I add it to the sheet's drawpage.
Something with visibility or layer?
Could you give me some example code for spreadsheets (I found some for text documents, perhaps there is some difference?).
I'm not very acquainted to this style of programming with services and find it somewhat hard to use the documentation without some examples.

Furthermore I'd like to center the reference point of the textfield. Seems I could do it with GluePoints.
I got one defined, see the second code snippet.
How would I use this exactly?
There is some "EnhancedCustomShapeParameterPair" with its property GluePoints, but the code below (oTextf.GluePoints = mGluePoint2) gives me an error that the property is write-protected. What to do?

Thanks
Andreas

Code: Select all

oDrawPage = oSheet.DrawPage
oTextf = oDoc.createInstance("com.sun.star.drawing.TextShape")
oTextf.FillStyle = SOLID
mPoint.X = 100
mPoint.Y = 0
mSize.Width = 9000
mSize.Height = 5000
oTextf.FillColor = RGB(200,200,200)
oTextf.LineStyle = SOLID
oTextf.LineCOLOR = RGB(100,100,100)
otextf.Position = mPoint
oTextf.Size = mSize
oTextf.CharFontName = "Courier"
oTextf.CharHeight = 9
oDrawPage.add(oTextf)
oTextf.String("xxx")
print oTextf.Position.X
print oTextf.CharFontName
print oTextf.String

Code: Select all

mPointGlue.X = 4500
mPointGlue.Y = 2500
mGluePoint2.Position = mPointGlue
mGluePoint2.IsRelative = False
mGluePoint2.PositionAlignment = CENTER
mGluePoint2.Escape = HORIZONTAL
mGluePoint2.IsUserDefined = True

oTextf.GluePoints = mGluePoint2

[robleyd]

I don't do macros, but I notice you use
oTextf.String("xxx")
which looks like an array element

but all the other oTextf.xx values are assigned in the form
oTextf.property = value

Is this possibly a typo, or just that I am not familiar with the language?

[Sébastien C]

Hi

There are som bug in your code (SOLID have to be com.sun.star.drawing.FillStyle.SOLID)

BUT

There are some bugs in LibreOffice / OpenOffice too...

For example, you MUST put the object oDrawPage.add(oTextf) in your page BEFORE you turn the properties as you want.

I must to sleep... Bis morgen für die GluePoints

Code: Select all

Sub Main
 Dim mPoint As New com.sun.star.awt.Point
 Dim  mSize As New com.sun.star.awt.Size

 oDrawPage = thisComponent.Sheets(0).DrawPage
    oTextf = thisComponent.createInstance("com.sun.star.drawing.TextShape")
 oDrawPage.add(oTextf)

    mPoint.x = 100  :     mPoint.y = 0
 mSize.width = 9000 : mSize.height = 5000

 With oTextf
  .size         = mSize
  .name         = "hello, it's me"
  .string       = "Hello everybody !!!"
  .position     = mPoint
  .fillColor    = RGB(200,200,200)
  .lineColor    = RGB(100,100,100)
  .fillStyle    = com.sun.star.drawing.FillStyle.SOLID
  .lineStyle    = com.sun.star.drawing.FillStyle.SOLID
  .charHeight   = 9
  .charFontName = "Courier"
 End With
End Sub

[mango]

@robleyd:
thanks for this hint, I thought I tried it the other way round first, but I must have missed that
@Sébastien:
I would have spent a lot of time to get the enumeration work fine, thanks, now LineStyle and FillStyle work

However, the CharFontName still does not work, I somewhere read it is case sensitive, but even though, no success. I defined it as you did in your With-struct as property of the TextShape service. In some examples I found it was used as style, but documentation is not very clear to me regarding this. Is there still a simple mistake in my code or is there another way to define it?

And of course I would appreciate a hint on the glue points, after having had a good night's rest.
Thanks again
Andreas

[FJCC]

I tried Sebastein's code and it seems to set the CharFontName correctly. I switched the font to Dingbats, so it would be super obvious.

Code: Select all

 Sub Main
     Dim mPoint As New com.sun.star.awt.Point
     Dim  mSize As New com.sun.star.awt.Size

     oDrawPage = thisComponent.Sheets(0).DrawPage
        oTextf = thisComponent.createInstance("com.sun.star.drawing.TextShape")
     oDrawPage.add(oTextf)

        mPoint.x = 100  :     mPoint.y = 0
     mSize.width = 9000 : mSize.height = 5000

     With oTextf
      .size         = mSize
      .name         = "hello, it's me"
      .string       = "Hello everybody !!!"
      .position     = mPoint
      .fillColor    = RGB(200,200,200)
      .lineColor    = RGB(100,100,100)
      .fillStyle    = com.sun.star.drawing.FillStyle.SOLID
      .lineStyle    = com.sun.star.drawing.FillStyle.SOLID
      .charHeight   = 9
      .charFontName = "Dingbats"
     End With
    End Sub

[Sébastien C]

Hello everyone,

I'm sorry, but in my context (LibreOffice on a small ARMbian on Rock64), the charFontName works. Are you sure that the font in question is present on your machine? Have you, similarly, made one or two tests on machines other than yours? It is not certain that the correlation between fonts is very efficient under Windows 10 that you say use. I will be you, I should start testing with "Courier New". In any case, I have a little trouble finding you a solution while it works on my machine. But I recognize that I am in a context all the same a little different from yours.

For nothing to hide, I do not know anything about GluePoints... But the documentation that I have (in French, the Bible of Bernard Marcelly) is very complete on the subject. I will want to study the GluePoints. But you write:

I'd like to center the reference point of the textfield. Seems I could do it with GluePoints.

And I say, me, that it has nothing to do with the GluePoints ... So before spending three hours in a night ABSOLUTELY EXCITING on the subject, I tell myself that you should still try (and understand) the two lines added to the previous code ...

And you will tell me, in return, whether it suits you ... or not!

Code: Select all

Sub Main
 Dim mPoint As New com.sun.star.awt.Point
 Dim  mSize As New com.sun.star.awt.Size

 oDrawPage = thisComponent.Sheets(0).DrawPage
    oTextf = thisComponent.createInstance("com.sun.star.drawing.TextShape")
 oDrawPage.add(oTextf)

    mPoint.x =  100 :     mPoint.y = 0
 mSize.width = 9000 : mSize.height = 5000

 With oTextf
  .size                 = mSize
  .name                 = "hello, it's me"
  .string               = "Hello!!!"
  .position             = mPoint
  .fillColor            = RGB(200,200,200)
  .lineColor            = RGB(100,100,100)
  .fillStyle            = com.sun.star.drawing.FillStyle.SOLID
  .lineStyle            = com.sun.star.drawing.FillStyle.SOLID
  .charHeight           = 9
  .charFontName         = "Courier New"
  .textVerticalAdjust   = com.sun.star.drawing.TextVerticalAdjust.CENTER
  .textHorizontalAdjust = com.sun.star.drawing.TextHorizontalAdjust.CENTER
 End With
End Sub

[mango]

Hello Sébastien
thanks again for the input.
I discovered why the font was not recognized. I have to put the font setting after the .string, and this even within the With-struct. Seems that .string resets the Font settings to default. Do you know another way instead of .string that allows independent order of property settings?
The .textVerticalAdjust and .textHorizontalAdjust do work and that's a property I will need, too. But not exactly that, what I intend to do with the GluePoint.
The above properties will center the text, what I intend is to set the reference point of the whole TextShape to its center position. When building a text field manually, then by right-clicking "Position and Size" the dialog box will contain a base point, which can be set to all corners, to the mid position of the borders and to the center of the field. Looking at the documentation I thought that this is what the GluePoint feature would do, but I'm not sure either.
I hope I do not steal your nights, if it's not too time consuming I would appreciate your help on that, else I will use the workaround to simply shift the position of the TextShapes accordingly.
Thanks a lot
Andreas

[Sébastien C]

Failing to answer you, I slept... But the night brings advice, perhaps as much as reading the documentation.

I have good news for you: I reproduce, on my machine, with LibreOffice and not OpenOffice, what I consider like a bug, namely that the font is not taken into account if the charFontName is declared BEFORE the string. We touch on an area that is little exploited by the users; so this kind of small bugs is little corrected by programmers and we have to do with it.

But the Gluepoints (which I did not know existed) have nothing to do with the position of the TextShape. They are used to position and anchor possible connectors between shapes. The thing is doable by hand, or by macro. Some documentation (LibreOffice but it's the same) is available here about Gluepoints, and there, about the connectors. You will better understand what it is for. You will also find that the connectors are not accessible in the drawing toolbar of Calc. So do your tests in Draw and copy/paste in Calc. This shows you why macros are ... powerful.

If, however, you tell me that you need connectors on the one hand, but still more to define specific Gluepoints, to hang them, then I am ready to study this specific subject. But you should also know that by default, graphic forms already have four Gluepoints that can already be used again...

I have searched for a property that can specify a reference point as offered by the manual dialog box as you describe it to me. And I did not find any. On the other hand, it makes sense to me. You necessarily know the size of your TextShape when defining it. The API requires you to provide X and Y points, top and left, to position it. Is it really difficult for you to make this small calculation to divide by two the width and height of your TextShape to find the geometric center and apply this difference in the upper left corner??? If the manual dialog gives you nine basis reference points, the calculation offers you ... an infinity. Happy man you are!

But I am still open to continue this exchange that makes me, too, learn a lot about a field that I like: the graphical forms of Draw, even if here you apply them to Calc...

[mango]

Hello Sébastien
thanks for working this out. If the GluePoints are not for the purpose I wanted them to use, that's ok to know. You don't need to dig deeper into that.
And if you tell me that there is no way to reproduce the dialog box option of centering the TextShape, that's alright, too. I just thought that macro programming offers all the options one gets out of dialogs, but I then will shift it accordingly. That's not a big deal, and really a pity that out of infinity I only need one solution.
I hope that I now get almost all the properties I need to finish my macro, there is just one other thing that came to my mind. How can I switch off the gridlines? I'd need that for printing purposes. I searched in the view- and document services, but couldn't find anything. Do you know?
Thanks
Andreas

[Sébastien C]

Do you know?

I think I do

Three things...

You necessarily know the dialog box: Tools/Options/OpenOffice Calc/View (here, the help of LibreOffice)

Code: Select all

thisComponent.currentController.showGrid = False

You know too the dialog box: Format/Page/Page (here, the help of LibreOffice)
Here, we change the default page style.

Code: Select all

Sub changeStyle()
    oStyleFamilies = thisComponent.styleFamilies
       oPageStyles = oStyleFamilies.getByName("PageStyles")
           DefPage = oPageStyles.getByName("Default")
 DefPage.printGrid = False
End Sub

BUT, if you print your document with macro, you can again define a service for configuring the printing. In this context:

Code: Select all

Sub configPrinting()
 Dim conf As Object
 conf = thisComponent.createInstance("com.sun.star.comp.SpreadsheetSettings")
 conf.showGrid = False
End Sub

Have a good choice!

[mango]

Hello Sébastien
thanks again for the input. I will try to get my macro working this weekend. I'm confident that I got all the information I need now, but let's see.
Best regards
Andreas