[Solved] Delphi: insert a textbox in a spreadsheet

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
johnhb
Posts: 8
Joined: Sat Dec 05, 2009 1:09 pm

[Solved] Delphi: insert a textbox in a spreadsheet

Post by johnhb »

[SOLVED] I'm new to this forum and not too sure where to post a question about Delphi and OpenOffice Calc. Basically I am trying to place a textbox into an OpenOffice spreadsheet. I am able to create the spreadsheet using Delphi but after a lot of searching the web I cannot find how to place (insert or add) a textbox into my spreadsheet using Delphi (VB would be ok as I can change it to suit). If this is the wrong place, please direct me to the right place. Sure hope someone can help or point me in the right direction.
Thanks, in anticipation.
:crazy:

Title Edited. A descriptive title for posts helps others who are searching for solutions and increases the chances of a reply (Hagar, Moderator).
Last edited by johnhb on Fri Dec 11, 2009 9:28 am, edited 2 times in total.
Johnb
OpenOffice 3.1 on Windows XP
User avatar
Hagar Delest
Moderator
Posts: 32627
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Where do I post this question

Post by Hagar Delest »

Hi and welcome to the forum.

I've moved your topic in a subforum of macros and will rename your thread. Please read the Survival Guide for the forum.
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
johnhb
Posts: 8
Joined: Sat Dec 05, 2009 1:09 pm

Re: Delphi: insert a textbox in a spreadsheet

Post by johnhb »

Thanks, Hagar. Appreciated
Johnb
OpenOffice 3.1 on Windows XP
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Delphi: insert a textbox in a spreadsheet

Post by FJCC »

I found this code on the oooforum http://www.oooforum.org/forum/viewtopic ... rm+control It was written for a ListBox, but required only a few simple changes to apply to a textbox. I also added a simple fix for the problem mentioned in the original thread that if a Form did not already exist in the sheet, the code would throw an error. The original author put in lots of comments, so I think you can get see how the code works. I hope that helps.

Code: Select all

' ************************** Code to create and Insert list box - not working ******************
' This macro demostrates how to create a control ( a listbox) and place it on a spreadsheet
' programmatically ie not via the IDE.

Dim frm
Dim oPage
Dim fcSheet ' the particular sheet onto which the listbox will be placed - Note you need to have
' created a sheet named "FC"
Dim items(0 to 4) as String 'items that will appear in the listbox
Dim pt as new com.sun.star.awt.Point ' a point structure that will be used to demonstrate
' how to move the created listbox after it has been
' placed on the spreadsheet

fcSheet = thisComponent.sheets().getByName("Sheet2")


oPage = fcSheet.getDrawPage() 'The drawpage for the sheet
'Test for existing form added by FJCC
If oPage.Forms.Count = 0 then
	NewForm = ThisComponent.createInstance("com.sun.star.form.component.Form")
	oPage.Forms.InsertByIndex(0, NewForm)
end if

frm = oPage.Forms.getByIndex(0) 'The form (a database form) that is associated with the drawpage
'Can be accessed ByIndex with an index of 0 or byName with a name of
'"Standard". Don't know what is the significance of this name "Standard"
'or what other sort of forms there may be. If anyone knows please let us know
'Creat the listbox
oTextBoxModel = createUnoService("com.sun.star.form.component.TextField")

'Create Shape for listbox - controls appear to be associated with shape objects that determine their position and size
oShape = thisComponent.createInstance("com.sun.star.drawing.ControlShape")
Dim pos as new com.sun.star.awt.Point
pos.X = 2000
pos.Y =1000
oShape.Position = pos
Erase pos
Dim size as New com.sun.star.awt.Size
size.Width = 5000
size.Height = 500
oShape.Size = size
Erase size

'Asign the model to the shape
oShape.Control = oTextBoxModel

'insert the control model into the first form of the forms collection of the draw page for the document
frm.insertByIndex(0,oTextBoxModel)
'Add the shape to the drawpage
oPage.add(oShape)
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
johnhb
Posts: 8
Joined: Sat Dec 05, 2009 1:09 pm

Re: Delphi: insert a textbox in a spreadsheet

Post by johnhb »

Thanks for the code FJCC, but I am still in trouble.
The line oTextBoxModel := createUnoService..... wont compile. I get an Undeclared Identifier error.
If I rem the line out and try to run the program to add the textbox I get an error (see attachment) at the line
opage := fcsheet.getDrawPage();
Here's my delphi(4) code:

Code: Select all

procedure TOOCalc.CreateTextbox(row, col:integer);
var
   frm, oPage, fcSheet, pt, newform: variant;
   oTextBoxModel, OShape, pos, size :variant;
   mytext: array[0..4] of string;
begin
     fcsheet := Document.getsheets.getbyname('Sheet1');
     opage := fcsheet.getDrawPage();
     if Opage.Forms.Count = 0 then
     begin
          Newform := Spreadsheet.createinstance('com.sun.star.form.component.form');
          oPage.Forms.InsertByIndex(0, NewForm);
     end;
     frm := oPage.Forms.getbyindex(0);
     //oTextBoxModel := createUnoService('com.sun.star.form.component.Textfield');
     oShape := Spreadsheet.createinstance('com.sun.star.drawing.ControlShape');
     pos := Spreadsheet.createinstance('com.sun.star.awt.point');
     pos.X := 2000;
     pos.Y := 1000;
     oShape.Positions := pos;
     //erase pos;
     size := Spreadsheet.createinstance('com.sin.star.awt.Size');
     size.Width := 5000;
     size.Height := 500;
     oShape.Size := size;
     oShape.Control := oTextBoxModel;
     frm.insertByIndex(0, oTextBoxModel);
     oPage.add(oShape);
end;
Adding a textbox programmatically to Excel was very simple and straightforward so I had hoped it would be similar in OpenOffice.
Hope you can help me. :crazy:
Attachments
OpenOfficeSS error.jpg
Johnb
OpenOffice 3.1 on Windows XP
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Delphi: insert a textbox in a spreadsheet

Post by FJCC »

createUnoService is an OOoBasic command. In this case I can just use this line in its place

Code: Select all

oTextBoxModel = ThisComponent.createInstance("com.sun.star.form.component.TextField")
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
johnhb
Posts: 8
Joined: Sat Dec 05, 2009 1:09 pm

Re: Delphi: insert a textbox in a spreadsheet

Post by johnhb »

Thanks again, FJCC.
I'm making some progress, slowly.

Code: Select all

     fcsheet := Document.sheets.getbyname('Sheet1');//getactivesheetname
     oPage := fcsheet.getDrawPage;
     if Opage.Forms.Count = 0 then
     begin
          Newform := Document.createinstance('com.sun.star.form.component.Form');
          oPage.Forms.InsertByIndex(0, NewForm);
     end;
     frm := oPage.Forms.getbyindex(0);
     oTextBoxModel := Document.createinstance('com.sun.star.form.component.TextField');
     oShape := Document.createinstance('com.sun.star.drawing.ControlShape');
     pos := Document.createinstance('com.sun.star.awt.Rectangle');
     size := Document.createinstance('com.sun.star.awt.Size');
When I check values for oTextBoxModel and oShape I get an address, but I just get unknown for pos and size. I think it has something to do with the com.sun.star.awt but I just don't know enough about OpenOffice API to resolve this issue.
Do you have any suggestions?
Johnb
OpenOffice 3.1 on Windows XP
johnhb
Posts: 8
Joined: Sat Dec 05, 2009 1:09 pm

Re: Delphi: insert a textbox in a spreadsheet

Post by johnhb »

I cannot find a delphi equivalent to the following:

Code: Select all

dim pos as New com.sun.star.awt.Point
dim size as New com.sun.star.awt.Size
I've tried to set pos and size as variants, and as arrays of variants but each time pos and size return as 'unknown'.
I have tried the delphi OOo tool but that will not compile in Delphi 4 and I spent some hours trying to convert it, but gave up because it was wasting too much time.

Hope someone out there can help me with this. This is a love job and I just want to add a textbox to an OpenOffice Calc sheet then place some text in the textbox.
Johnb
OpenOffice 3.1 on Windows XP
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Delphi: insert a textbox in a spreadsheet

Post by FJCC »

I can be of very little help with that, as I have only used OOoBasic and Python. A com.sun.star.awt.POINT is a Structure of the c.s.s.awt service. It is a structure because it has properties (e.g. X and Y values) but no methods. (At least that is my version of what defines a Structure)

I took a look at the documentation of the xray tool that has examples of how to implement it in Delphi and Visual Basic. Both languages are listed under the COM interface section, so I went to a web page about using VB to control OOo. There is a section there about using Structures in VB. It is titled "Passing a structure as a function arguments from VB". Perhaps that will help you. Bernard Marcelly is thanked for that section and he wrote the OOoDelphi tool. He also responds on this forum fairly regularly, so perhaps he'll see this and save you from my guessing.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
johnhb
Posts: 8
Joined: Sat Dec 05, 2009 1:09 pm

Re: Delphi: insert a textbox in a spreadsheet

Post by johnhb »

Thank you very my FJCC, you have been very kind with your assistance. I will take a look at your suggested web site to see what I can gleen from it.

Thanks again.
:super:
Johnb
OpenOffice 3.1 on Windows XP
johnhb
Posts: 8
Joined: Sat Dec 05, 2009 1:09 pm

Re: Delphi: insert a textbox in a spreadsheet

Post by johnhb »

FJCC - Just to let you know and any others that may be watching this post. I found that the delphi equivalent to the following:

Code: Select all

Dim Point as New com.sun.star.awt.Point
is

Code: Select all

var
Point : variant;
begin
...
Point := oServiceManager.Bridge_GetStruct('com.sun.star.awt.Point');
I am now able to put a rectangle on my spreadsheet. Next task is to insert some text.
Thanks again FJCC
Johnb
OpenOffice 3.1 on Windows XP
johnhb
Posts: 8
Joined: Sat Dec 05, 2009 1:09 pm

Re: Delphi: insert a textbox in a spreadsheet

Post by johnhb »

I've finally been able to insert a text box into my spreadsheet, colour the box and add text.

Code: Select all

     oTextBox := Document.createinstance('com.sun.star.form.component.TextField');
     oShape := Document.createinstance('com.sun.star.drawing.ControlShape');
     oPos := Document.Bridge_GetStruct('com.sun.star.awt.Point');
     oPos.X := 0;
     oPos.Y := 1000; //11000;
     oShape.Position :=opos;
     oSize := Document.Bridge_GetStruct('com.sun.star.awt.Size');
     oSize.Width := 5000;
     oSize.Height := 5000;
     oShape.Size := oSize;
     oShape.Control := oTextBox;
     oPage.add(oShape);
     oFont := Document.Bridge_GetStruct('com.sun.star.awt.FontDescriptor');
     oTextBox.MultiLine := true;
     oTextBox.ReadOnly := true;
     oTextBox.Align := 1;
     oTextBox.Border := 2;
     oTextBox.BorderColor :=100;
     oTextBox.Enabled := true;
     oTextBox.BackgroundColor := RGB(207,226,219);
     oTextBox.Text :=(mytext);
     oTextBox.TextColor := RGB(120,120,0);
     //oTextBox.Printable(0);
    //oTextBox.Printable := true;
     oFont.Name := 'Times New Roman';
However can someone explain why the oTextBox.Align, .0TextBox.Border.. etc all work but the oTextBox.Printable returns 'method not found' or if I use .Printable := true it returns Method not supported by automation. I'm puzzled by this.
Also I cannot work out how to change the font size.
Any suggested would be appreciated.
Johnb
OpenOffice 3.1 on Windows XP
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Delphi: insert a textbox in a spreadsheet

Post by FJCC »

In my Basic code I don't have any trouble with

Code: Select all

oTextBoxModel.printable = true
you might try the setPropertyValue method

Code: Select all

oTextBoxModel.setPropertyValue("Printable", True)
I believe the default value of Printable is true, so you can probably just leave it unchanged.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Post Reply