Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2933
  • Last Modified:

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

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
_Ben_
Asked:
_Ben_
  • 5
  • 3
2 Solutions
 
ziolkoCommented:
you have to use OLEObjects.Add() of a worksheet

ziolko.
0
 
_Ben_Author Commented:
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
 
ziolkoCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
_Ben_Author Commented:
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
 
ziolkoCommented:
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
 
_Ben_Author Commented:
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
 
_Ben_Author Commented:
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
 
_Ben_Author Commented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now