Solved

Creating buttons, checkboxes and radioboxes in an Excel file using Delphi's TExcelWorksheet class

Posted on 2008-10-03
8
2,832 Views
Last Modified: 2013-11-23
Hi

I'm trying to create GUI controls like buttons, comboboxes, radiobuttons  in a pre-existing Excel file using the delphi classes   TExcelApplication , TExcelWorkbook and TExcelWorksheet.

(fyi, to create these controls in Excel itself you use the Forms toolbar to drag them onto the sheet)

I need some help doing this control creation in code from delphi, Ive already written the code to open an Excel file, add some text to a cell, then save it to a different file, see below.

So all i need is some extra code to create the components.
The TExcelWorksheet class has many functions that maybe part of the creation.
(See attached jpg showing the worksheet autocomplete functions for buttons. Combobox etc.. are all the same)

As i understand it i should have an .add function  ie  MyWorksheet.buttons.add(),  which i dont

Any help appreciated
Thanks

Ben.
// add excel97 to uses clause as well as Variants (if not already there)

// components needed, 1 button
 

procedure TForm1.Button1Click(Sender: TObject);

Var

  FExcel: TExcelApplication;

  FWkBk: TExcelWorkbook;

  FWS: TExcelWorksheet;

  lcid: integer;

  infile : string;

  outfile : string;
 

begin
 

  infile := 'c:\template.xls';

  outfile := 'c:\outfile.xls';
 

  if fileexists(infile) then

  begin

        try

            FExcel := TExcelApplication.Create(Self);

            FWkBk := TExcelWorkbook.Create(Self);

            FWS := TExcelWorksheet.Create(Self);
 

            lcid := LOCALE_USER_DEFAULT;

            FExcel.Connect;
 

            // to save without warning prompts

            FExcel.DisplayAlerts[lcid] := False;
 

            // open workbook

            FWkBk.ConnectTo(FExcel.Workbooks.Open(infile,

            EmptyParam,EmptyParam,EmptyParam,EmptyParam,

            EmptyParam,EmptyParam,EmptyParam,EmptyParam,

            EmptyParam,EmptyParam,EmptyParam,EmptyParam,lcid));
 

            //connect worksheet

            FWS.ConnectTo(FExcel.ActiveSheet as _Worksheet);

            FWS.Name := 'the name';
 

            // put some text into Cell A1

            FWS.Range['A1', 'A1'].Value := 'test123';
 

            // put two optionbuttons (radiobuttons) on the page

            // somewhere and set their 'cell link' to $B$1
 

            code needed here
 

            // save file (delete old one)

            if fileexists(outfile) then deletefile(outfile);

            FWkBk.SaveAs(outfile, EmptyParam, EmptyParam,

                       EmptyParam, EmptyParam, EmptyParam,

                       xlNoChange, EmptyParam, EmptyParam,

                       EmptyParam, EmptyParam, lcid);
 

        finally

            // close and free everything

            FWS.Disconnect;

            FWkBk.Disconnect;

            FExcel.DisplayAlerts[lcid] := True;

            FExcel.Quit;

            FExcel.Disconnect;

            FExcel.Free;

            FWkBk.Free;

            FWS.Free;

        end;

   end

   else

   begin

      showmessage('Template file '+infile+' was not found. (please create an empty xls file)');

   end;

end;

Open in new window

excel.jpg
0
Comment
Question by:_Ben_
  • 5
  • 3
8 Comments
 
LVL 21

Expert Comment

by:ziolko
ID: 22637613
you have to use OLEObjects.Add() of a worksheet

ziolko.
0
 

Author Comment

by:_Ben_
ID: 22639844
Thanks for responding.
But oleobjects has no .add either.
OLEObjects autocomplete looks identical to the picture above for buttons.

Ignoring autocomplete and trying add anyway gives undeclared identifier '.add'
0
 
LVL 21

Expert Comment

by:ziolko
ID: 22640891
try this:

var oleobj: IOLEObjects;
      btn: _OLEObject;

            oleobj := FWS.OLEObjects(EmptyParam, 0) as IOLEObjects;
            oleobj.Add('Forms.CommandButton.1', EmptyParam,
                                      False, False, EmptyParam,
                                      EmptyParam, EmptyParam, 186, 100, 72, 24, btn);

ziolko.
0
 

Author Comment

by:_Ben_
ID: 22642304
It looked good, but unfortunately I get  
Project1.exe raised exception class EIntfCastError with message 'Interface not supported
 on the line  oleobj := FWS.OLEObjects(EmptyParam, 0) as IOLEObjects;

Got any other ideas?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 21

Assisted Solution

by:ziolko
ziolko earned 250 total points
ID: 22642813
hmm try direct typecast:
oleobj := IOLEObjects(FWS.OLEObjects(EmptyParam, 0));

and it fails, try this:

var punk: IDispatch;

punk := FWS.OLEObjects(EmptyParam, 0);
if Assigned(punk) and (punk.QueryInterface(IOLEObjects, oleobj) = S_OK) then
  oleobj.Add(...)

ziolko.
0
 

Author Comment

by:_Ben_
ID: 22643177
Both of those ideas compile and the application runs, however the line

oleobj.Add('Forms.CommandButton.1', EmptyParam,False, False, EmptyParam,EmptyParam, EmptyParam, 186, 100, 72, 24, btn);

doesn't produce a button in the xls output file, it seems to ignore it.
0
 

Author Comment

by:_Ben_
ID: 22644081
i think ive figured it out, ya use  worksheet . shapes . AddFormControl

this code *seems* to work for two seperate groupboxes with 3 radio buttons in each

         FWS.Shapes.AddFormControl(xlgroupbox, 100, 10, 200, 200) ;
         FWS.Shapes.AddFormControl(xloptionbutton, 130, 30, 150, 60) ;
         FWS.Shapes.AddFormControl(xloptionbutton, 130, 60, 150, 60) ;
         FWS.Shapes.AddFormControl(xloptionbutton, 130, 90, 150, 60) ;

         FWS.Shapes.AddFormControl(xlgroupbox, 100, 300, 200, 200) ;
         FWS.Shapes.AddFormControl(xloptionbutton, 130, 330, 150, 60) ;
         FWS.Shapes.AddFormControl(xloptionbutton, 130, 360, 150, 60) ;
         FWS.Shapes.AddFormControl(xloptionbutton, 130, 390, 150, 60) ;

I'm still testing it though. it's pretty late here, 1am , i'll have a play with it tomorrow and confirm that this is working the way i need.
0
 

Accepted Solution

by:
_Ben_ earned 0 total points
ID: 22674945
I've figured out what i needed, with some help from ziolko

Here's some code to demo some of the things you can do
as well as some notes on a few things ive found.
unit Unit1;
 

interface
 

uses

  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,

  Dialogs, StdCtrls,excel97 ;
 

type

  TForm1 = class(TForm)

    Button1: TButton;

    procedure Button1Click(Sender: TObject);

  

  private

    { Private declarations }

  public

    { Public declarations }

  end;
 

var

  Form1: TForm1;
 

implementation
 

uses ComObj;
 

type

ToleEnum = type Integer;
 

{$R *.dfm}
 

 // add excel97 to uses clause as well as Variants (if not already there)
 
 

procedure TForm1.Button1Click(Sender: TObject);

const

msoTextOrientationHorizontal = $00000001;

Var

  FExcel: TExcelApplication;

  FWkBk: TExcelWorkbook;

  FWS: TExcelWorksheet;

  lcid: integer;

  infile : string;

  outfile : string;

  shape1  : shape;

  shape2  : shape;

  shape3   : shape;

  shape4  : shape;

  shape5    : shape;

  shape6    : shape;

  shape7   : shape;

  shape8    : shape;

  shape9   : shape;

  shape10  : shape;

  shape11   : shape;

  shape12 : shape;

  shape13 : shape;
 

begin
 

  infile := 'c:\template.xls';

  outfile := 'c:\outfile.xls';
 

  if fileexists(infile) then

  begin

        try

          FExcel := TExcelApplication.Create(Self);

          FWkBk := TExcelWorkbook.Create(Self);

          FWS := TExcelWorksheet.Create(Self);
 

          lcid := LOCALE_USER_DEFAULT;

          FExcel.Connect;
 

          // to save without warning prompts

          FExcel.DisplayAlerts[lcid] := False;
 

          // open workbook

          FWkBk.ConnectTo(FExcel.Workbooks.Open(infile,

          EmptyParam,EmptyParam,EmptyParam,EmptyParam,

          EmptyParam,EmptyParam,EmptyParam,EmptyParam,

          EmptyParam,EmptyParam,EmptyParam,EmptyParam,lcid));
 

          //connect worksheet

          FWS.ConnectTo(FExcel.ActiveSheet as _Worksheet);

          FWS.Name := 'the name';
 
 
 

          //put some text into Cell A1

          FWS.Range['A1', 'A1'].Value := 'test123';
 

          // put two optionbutton on the page somewhere and set

          // their 'cell link' to $B$1
 
 

          // cell manipulation

          // it takes 2 arguments incase you need them to specify a range

          // If your working with only one cell use emptyparam as the 2nd argument

          // eg

          FWS.Range['A1',emptyparam].Value := 'data put into a cell';

          FWS.Range['C1',emptyparam].Interior.Color := clblue;

          FWS.Range['B1','B10'].MergeCells := true ;
 
 

          // drawing boxes

          FWS.Range['G1','G2'].BorderAround(XLContinuous,xlThin,XlAutomatic,clblack);

          FWS.Range['G5','G10'].BorderAround(XLContinuous,xlThick,XlAutomatic,clblack);
 
 

          // gui opjects

          shape1 := FWS.Shapes.AddFormControl(xlgroupbox, 1, 100, 200, 200) ;

          // radiobuttons (optionbuttons) must be inside a groupbox to work correctly

          shape2 := FWS.Shapes.AddFormControl(xloptionbutton, 10, 130, 150, 60) ;

          shape3 := FWS.Shapes.AddFormControl(xloptionbutton, 10, 160, 150, 60) ;

          shape4 := FWS.Shapes.AddFormControl(xloptionbutton, 10, 190, 150, 60) ;
 

          shape5 := FWS.Shapes.AddFormControl(xlCheckBox, 200, 1, 50, 50) ;

          shape6 := FWS.Shapes.AddFormControl(xlButtonControl, 200, 70, 50, 50) ;

          shape7 := FWS.Shapes.AddFormControl(xlDropDown  , 200, 150, 50, 50) ;
 

          // editboxes dont work for some reasons. OLE error

          // editbox := FWS.Shapes.AddFormControl(xlEditBox  , 1, 160, 50, 50) ;
 

          shape8 := FWS.Shapes.AddFormControl(xlLabel   , 400, 70, 50, 50) ;

          shape9 := FWS.Shapes.AddFormControl(xlListBox   , 400, 120, 50, 50) ;

          shape10:= FWS.Shapes.AddFormControl(xlScrollBar   , 400, 200, 10, 50) ;

          shape11:= FWS.Shapes.AddFormControl(xlSpinner    , 400,300, 50, 50) ;
 

          //there is no xlTextBox but you can do it a different way for textboxes

          shape12  := FWS.Shapes.AddTextbox(msoTextOrientationHorizontal,500,10,100,100);
 

          // you can also go

          shape13 := FWS.Shapes.AddOLEObject('forms.textbox.1',emptyparam,emptyparam,emptyparam,emptyparam,emptyparam,emptyparam,500,300,100,100);

          // but you get a different newer looking type of control

          // this way also works for other controls ie, forms.checkbox.1 / forms.commandbutton.1 etc.  Its always .1 dunno why, probably some VB thing
 

          // to change *some* settings for each object you can use the shape class

          // that is returned above
 

          // eg, this works

          shape12.TextFrame.Characters(emptyparam,emptyparam).Caption := 'some label text';

          shape1.TextFrame.Characters(emptyparam,emptyparam).Caption := 'a groupbox title';

          //however you cant change others like the color or line type, ya get errors if you try eg.
 

          // Error = access violation

          // shape12.Fill.BackColor.RGB := clyellow;
 

          // Error = the stub recieved bad data

          // shape12.Line.Style := xlLineStyleNone;
 

          // This is because the AddFormControl function returns a shape class and it doesnt contain all

          // the functions nessesary to control all apsects of the component

          // To talk to, for example, a groupbox, you need a object of class groupbox not shape
 

          // typecasing like this doenst work

          // error = interface not supported

          //(shape12 as textbox).Interior.Color := clblack;
 

          // to get at the item as a proper class you need to typecast the FWS.<component>s(index) dispatch to its proper class
 

          // eg this works

          (FWS.TextBoxes(1) as textbox).Interior.Color := clyellow ;

          // 1 being the first groupbox you made in the sheet
 
 

          // lock worksheet

          FWS.Protect('password');
 

          // unlock worksheet

          FWS.Unprotect('password');
 
 

          // Notes:

          // the whole excel control interface is a pretty big mess, methods exist that work in some situations and not in others

          // my advice is to just keep trying the same thing different ways. Look for other properties that

          // may contain other methods for doing the same thing, its likely there will be more than one and only one will work.
 

          //  .Fill.Background.RGB and .Fill.Foreground.RGB sound like exactly what you want when setting color but they never seems to work

          // if you want to change the color look for .interior.color,
 

          // when working with protected worksheets you may need to adjust the  .locked   and .lockedtext options

          // locked is available from shape1.locked but lockedtext requires you to typecast eg, (FWS.TextBoxes(1) as textbox).lockedtext

          // locked stops them moving the component and lockedtext stops them entering text, they are both enabled by default.
 

          // the msoTextOrientationHorizontal constent is in office97.pas, the reason i dont include it is that some of its classes have the same name as

          // excel97 but are different and you sometimes get the wrong one when you declare stuff. 

          // you can add it to uses, but you may have to change some stuff to  var  myfoo : excel97.foo etc.. as var myfoo : foo;  may get the wrong one
 

         // save file (delete old one)

         if fileexists(outfile) then deletefile(outfile);

         FWkBk.SaveAs(outfile, EmptyParam, EmptyParam,

                       EmptyParam, EmptyParam, EmptyParam,

                       xlNoChange, EmptyParam, EmptyParam,

                       EmptyParam, EmptyParam, lcid);
 

        finally

            // close and free everything

            FWS.Disconnect;

            FWkBk.Disconnect;

            FExcel.DisplayAlerts[lcid] := True;

            FExcel.Quit;

            FExcel.Disconnect;

            FExcel.Free;

            FWkBk.Free;

            FWS.Free;

        end;

   end

   else

   begin

      showmessage('Template file '+infile+' was not found. (please create an empty xls file)');

   end;

end;
 

end.

Open in new window

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now