Solved

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

Posted on 2008-10-03
8
2,846 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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 

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
 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

813 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

9 Experts available now in Live!

Get 1:1 Help Now