Page 1 of 1
[Solved] Delphi: insert a textbox in a spreadsheet
Posted: Sun Dec 06, 2009 5:48 am
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.
Title Edited. A descriptive title for posts helps others who are searching for solutions and increases the chances of a reply (Hagar, Moderator).
Re: Where do I post this question
Posted: Sun Dec 06, 2009 10:21 am
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.
Re: Delphi: insert a textbox in a spreadsheet
Posted: Sun Dec 06, 2009 12:07 pm
by johnhb
Thanks, Hagar. Appreciated
Re: Delphi: insert a textbox in a spreadsheet
Posted: Mon Dec 07, 2009 12:19 am
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)
Re: Delphi: insert a textbox in a spreadsheet
Posted: Mon Dec 07, 2009 7:43 am
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.
Re: Delphi: insert a textbox in a spreadsheet
Posted: Mon Dec 07, 2009 3:46 pm
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")
Re: Delphi: insert a textbox in a spreadsheet
Posted: Tue Dec 08, 2009 1:56 am
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?
Re: Delphi: insert a textbox in a spreadsheet
Posted: Wed Dec 09, 2009 3:05 am
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.
Re: Delphi: insert a textbox in a spreadsheet
Posted: Wed Dec 09, 2009 6:55 am
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.
Re: Delphi: insert a textbox in a spreadsheet
Posted: Wed Dec 09, 2009 8:19 am
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.
Re: Delphi: insert a textbox in a spreadsheet
Posted: Wed Dec 09, 2009 8:59 am
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
Re: Delphi: insert a textbox in a spreadsheet
Posted: Thu Dec 10, 2009 9:13 am
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.
Re: Delphi: insert a textbox in a spreadsheet
Posted: Fri Dec 11, 2009 8:26 am
by FJCC
In my Basic code I don't have any trouble with
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.