|
The goal of
this two-part paper is to get you up to
speed automating Excel and Word from a
Delphi application. Most of the text
will focus on Excel, but you will find
that if you understand Excel automation,
that you need only a few hints to get
started automating Word. If you are
primarily interested in Word, I ask you
to patiently read through the material
on Excel, as almost all of it applies to
Word.
You can use Delphi to fully control
virtually all the features of Excel and
Word. There is very little that you can
do from inside Excel or Word that you
cannot also automate from outside Excel
or Word. In other words, both Excel and
Word can be fully controlled from Delphi
applications using OLE Automation.
For the most part the act of controlling
Excel or Word from a Delphi application
is not terribly challenging. Whatever
difficulty there is comes not from
Delphi's side of the equation, but from
the innate complexity of the Excel and
Word object hierarchies. Not that I find
the hierarchies unreasonable, but they
do encapsulate sufficient complexity to
require a significant period of study.
In particular, these automation classes
give you rather detailed control over
Word and Excel. Since these are complex
applications, the interface to them also
needs to be complex if it is not to be
considered under powered. The purpose of
this paper is to unscramble that
hierarchy and show its underlying
structure.
This paper is divided into two parts.
The first part shows automating Word and
Excel using variants and IDispatch, and
the second part shows how to perform the
same tasks with dispinterfaces and
standard COM interfaces. All of these
technologies are closely related.
However, there are two significant
differences between them:
-
Using variants is usually the
easiest, the most terse, but also
the slowest way to get things done
in terms of performance.
-
Using COM interfaces is usually the
most difficult, the most verbose,
but also yields the highest
performance.
There may be a short gap between the
time the first and second parts of the
paper are published.
When writing this paper I have attempted
to explain things as clearly as
possible. I'm aiming this paper
primarily at intermediate or experienced
programmers, but I hope it is accessible
to anyone who has a basic understanding
of how to use Delphi, Word and Excel.
Though you should not need a high level
of expertise to understand this paper, I
am trying to cover the subject in some
depth. Other sources, such as my book
Dephi 2 Unleashed (Chapter 29), and
the magazine the Delphi Informant
(June 1997), covers some of this same
material in a simpler, less in depth
fashion. For many people a more high
level, abstracted view may be more
appropriate. But I believe there is also
a big need for a more detailed look at
this subject, which is why I have
written this paper.
One final note: in my writing, I tend to
use repetition to emphasize important
points. This is a consciously exercised
technique designed to make this paper as
easy as possible to understand, while
simultaneously ensuring that you don't
accidentally skip over an essential
point. Another benefit of repetition is
that people tend to use articles of this
type as a reference. As a result, they
don't always read them from beginning to
end every time they refer to them. As a
result, it helps to give a one sentence
review of key points that relate to the
topic under discussion.
System
Requirements
This paper was written against Delphi
3.01 and Microsoft Office 97. Portions
of the paper would also work with Office
95, but the sections on interfaces, in
particular, require that you use Office
97.
To perform automation successfully with
Excel or Word you need a fairly powerful
system with lots of RAM. I've been
automating Excel for at least four
years. When I first started out, I
considered the technology a bit suspect
simply because it was terribly slow.
Now, however, our machines are powerful
enough to take Excel through its paces
in a few short moments. In particular,
if you have a Pentium 120 class machine
or above, and at least 48 MB of ram,
then this technology works well for many
types of projects. Excel or Word will
now load quite quickly, and you can open
and insert data into them in the blink
of an eye. However, if you want to
iterate over lots of data inside a Word
or Excel document, then that can be a
bit time consuming when compared to
performing similar tasks inside of a
Delphi application.
The bottom line here is that if you know
Excel can do something well, and you
know your target machines are powerful
and will have Excel loaded on them, then
there is no reason to search for third
party components to perform spreadsheet
related functions. Instead, you can just
automate Excel from inside a Delphi
application and get your work done
professionally in just a few short
hours. The icing on the cake is that you
can then use MAPI to mail the results of
your work to anyone who has a mail
system and the ability to read Excel
files. The point being that the
recipient of your work need not actually
have a copy of your Delphi application
running when viewing the output from
your program. Instead you can just send
them the results in an Excel or Word
document. Word document viewers can be
downloaded for free from Microsoft's web
site at www.microsoft.com.
Getting
Started with Delphi and Excel
There are two different ways to run OLE
automation from inside Delphi. One
involves using interfaces, while the
second involves using an OLE class
called IDispatch along with a
Delphi type called a variant. Interfaces
give you the advantage of type checking
your code on the client side, as well as
relatively high performance. However, I
am going to start the paper working with
the somewhat easier to understand
IDispatch and variants technology,
and move on to cover interfaces after
all the basics are clearly established.
Do not worry if you don't yet understand
the differences between the two
techniques, as this subject will be
cleared up over the course of the paper.
At this stage, you just need to be aware
that there are at least two ways to
access OLE automation objects from
Delphi, and that I am going to start out
by showing you one that uses
IDispatch and variants.
The following code, found in Listing 1,
shows a bare bones example of a Delphi
application that launches Excel. Just
skim over the code for now, as I will
spend the rest of this section of the
paper explaining how it works.
Listing 1: The main form from the
Excel1 application found with the code
samples that accompany this article.
unit Main;
interface
uses
Windows, Messages, SysUtils,
Classes, Graphics, Controls,
Forms, Dialogs, StdCtrls;
type
TForm1 = class(TForm)
Button1: TButton;
procedure Button1Click(Sender: TObject);
procedure FormDestroy(Sender: TObject);
private
public
V: Variant;
end;
var
Form1: TForm1;
implementation
uses
ComObj;
{$R *.DFM}
procedure TForm1.Button1Click(Sender: TObject);
begin
V := CreateOleObject('Excel.Application');
V.Visible := True;
end;
procedure TForm1.FormDestroy(Sender: TObject);
begin
if not VarIsEmpty(V) then
V.Quit;
end;
end.
You can find this example on disk, in
the program called Excel1.dpr. The code
does nothing more than create an
instance of Excel, make it visible, and
then close it down when the user exists
the Delphi application. The code does
not check to make sure the user is not
creating multiple instances of the
application, but it does close down a
single copy of Excel when you exit.
From Delphi's side, you should always
start your automation applications by
including COMObj in the uses
clause. COMObj contains routines for
retrieving OLE automation objects, and
for dispatching calls to them. In
particular, you usually use
CreateOleObject to retrieve an
automation object, and behind the scenes
Delphi uses the COMObj routines
VarDispInvoke, DispatchInvoke
and GetIDsOfNames, to call an
object. I will give you a bit more
information on these routines in just
one moment. The rest is simply a matter
of using a built in Delphi type called a
variant to reference the objects
that reside inside Excel.
Here are a few simple lines of code that
launch Excel from inside Delphi:
var
V: Variant;
begin
V := CreateOleObject('Excel.Application');
V.Visible := True;
end;
The first line of code after the
begin statement launches Excel. The
call to CreateOleObject is relatively
complex, so I will explain it in the
next section of the paper.
After executing the first line, Excel
will come up in the background, entirely
offscreen, invisible to the user. This
may, in fact, be the effect you want to
achieve. However, when you first start
out programming Excel, and whenever you
are debugging your Excel automation
application, you probably want to be
able to see what is going on inside
Excel. Therefore, I set the Visible
property of the Excel Application
object equal to True. This
ensures that you can see what is
actually happening on the Excel server.
If you have thoroughly debugged your
application you may want to skip this
step, but I will include it in all the
examples.zip I cover in this paper.
Of course, I haven't told you enough yet
to make the code shown above entirely
comprehensible. What is the purpose, for
instance, of the variable V? What
does CreateOleObject actually do?
As it turns out, the answers to these
questions are non-trivial. The variable
V is a variant, and
CreateOleObject creates an instance
of a COM object called IDispatch
and returns it to you inside a
variant. But saying as much doesn't
help much if you don't understand COM,
IDispatch, and variants.
There are three courses I could take at
this point. One would involve an in
depth explanation of COM and OLE, a
second would give you only the minimal
amount of information needed to keep
going, and the third would be to find
some middle ground. In this case I am
going to opt for the latter solution,
and leave it up to you to pursue COM in
more depth on your own. I will however,
discuss this subject over the next few
paragraphs, and come back to it again in
the second part of the paper. If you
want even more information, some
references to get you started are as
follows:
-
I have an article on Delphi and
DCOM on my WEB site:
http://users.aol.com/charliecal,
and also cover the subject in the
book Delphi 2 Unleashed.
-
Microsoft has extensive
documentation on this subject:
http://ww.microsoft.com/com
-
The User's Guide from the Delphi 3
docs contains some text on this
subject in Chapter 25.
The only thing you really need to know
at this stage is that Microsoft has
created a special type of object
oriented programming called COM, which
allows you to retrieve and call the
methods of an object from a number of
different languages. The COM object
model is different from the one used by
native Delphi programmers, so Borland
gives you two choices:
You can followed Microsoft's lead and
call the methods of these special
objects off a variable type called a
variant. This is the technique described
in the first part of this paper.
You can follow a second, more technical
approach and use interfaces or
dispinterfaces.
One of the key differences between using
interfaces and using variants is that
interfaces allow you to call COM objects
using the much faster dispatching
technologies native to Object Pascal. As
I will explain in Part II of this
article, dispinterfaces follow a middle
path between the variant technology and
the interface technology.
COM is the underlying object model that
makes OLE and ActiveX programming
possible. At times I will use OLE and
COM as virtual synonyms. ActiveX is yet
a third very closely related technology,
but I will not touch on it in this
paper. However, it no longer incorrect
to also use the words COM and ActiveX as
virtual synonyms.
Variants get their name because
they can provide a wide variety
of functions, depending on the
circumstances. For instance, they can
contain a string, an integer, or, in
special cases, a COM object. In other
words, the type of variable held in a
variant varies from one occasion
to the next. That's why they call them
variants. (For more information, look up
"Variant Types" in the Delphi online
help, or else look at the declarations
for the structures used with variants
at the top of System.pas.)
CreateOleObject calls a number of
internal system wide OLE functions. The
end result of these series of calls is
that the function returns a COM object
to you containing an interface to
the object you want to call. In
particular, you get back a variant
that is wrapped around a COM object
called IDispatch. A combination
of the built in IDispatch
methods, and various Delphi technologies
covered briefly later in this paper,
allows you to call the methods of the
object your requested.
With all this in mind, let's go back and
view the two lines of code that retrieve
the Excel object:
V := CreateOleObject('Excel.Application');
V.Visible := True;
The first line of code asks for an
object that resides inside Excel called
Application. CreateOleObject
retrieves an instance of the object in
the form of an IDispatch
interface encapsulated inside a variant
called V. This variant is
valuable to you because it allows you to
call the methods and properties of the
Excel object using a very simple syntax.
For instance, you can access the
Visible property of the object by
simply writing V.Visible := True.
It would be mistake, however, to assume
that the line of code containing the
Visible property is doing the same thing
as a standard Delphi line of code that
looks like this:
Form1.Visible := True;
Admittedly these two lines look the
same, and have exactly the same syntax.
But internally, something very different
is going on. In particular, if you call
the Visible property of a Delphi
form object then the property is changed
almost instantly. Calling the Visible
property of an OLE automation variant
sets off a series of internal events
that end up resulting in a change to the
Visible property of an object
inside Excel, but there are many steps
that occur along the way. In particular,
several methods of IDispatch such
as GetIDsOfNames and Invoke
must first be called behind the scenes
before the call is complete.
This paper is not designed to cover the
mechanisms used in dispatching a call on
a variant encapsulated COM object, nor
is it necessary for you to understand
how it works in order to use this
technology. The key point to grasp is
merely that things aren't quite as
simple as they at first appear. Having
said all that, I will now show you how
to get into this subject a bit deeper if
you so desire, and if you have the
source to the VCL on your machine.
To get started, copy COMObj.pas and
COMObj.inc from the Delphi SourceRtlSys
directory to the same directory where
Excel1 is stored. Now rebuild the
project so these local copies of COMObj
are linked into your program. Put a
breakpoint on the line V.Visible :=
True, and then run the program. When
you get to the breakpoint, press F7 to
step into the code. You will find that
you are taken immediately to the
VarDispInvoke method found in
COMObj.pas. From there you will go to
GetIDsOfNames, and finally to
DispatchInvoke. What is happening
here is that the appropriate methods of
the IDispatch interface are being called
behind the scenes by Delphi in order to
"invoke" your call to Excel.
One of the lessons to be learned from
this is that at bottom, there is not
such a big difference between the
interface technology shown in the second
part of this paper and the variant based
technology I am discussing here. For
instance, IDispatch is an interface, and
ultimately this interface must be called
for the variant based technology to
work. In fact, IDispatch is designed in
such a way as to make the variant based
technology even more complex than the
standard interface technology seen in
the second half of this paper. Only
Delphi is able to hide that complexity
from you, so that you do not need to
understand it at all in order to use
variants to call automation objects.
(Once again, I need to emphasize that I
am not giving a full explanation of this
technology in this paper. If you want to
really understand IDispatch, then you
should check out the resources mentioned
earlier in this section of the paper.)
One of the biggest consequences of
calling the methods of an object off a
variant is that Delphi cannot type check
your code at design time. In other
words, Delphi does not really know
whether or not the Excel Application
object has a property called Visible. It
is taking you at your word when you
claim this is true. In this case, that
proves to be the correct thing to do.
However, it would also compile without
error the following code:
V.TransferMoney("From := Bill Gates", "To := Charlie Calvert", 100000);
This line of code is certainly
intriguing, but the Excel Application
object unfortunately does not support
it. This means that a program containing
it will compile and load without error,
but a call to the TransferMoney property
at run time will raise an exception.
Both Delphi and Excel are able to handle
this exception flawlessly, without
destabilizing the system in any way. It
is nice, however, if you can type check
at design time, rather than having to
wait to run time to see if all is set up
correctly. The interface and
dispinterface technologies covered in
Part II of this paper shows how to get
design time type checking of OLE
objects.
I've spent the last several paragraphs
describing IDispatch and
variants. This is an important
subject, but one that you need not
understand in depth in order to use this
technology. If all is not clear to you
yet, you can still continue without
fear. If you are hungry for more
details, be patient and I will return to
this subject Part II, or else you should
follow the links shown earlier in this
section.
After you have created an Excel
Application object, you need some way to
close it down. You can do this by
calling its Quit method:
if not VarIsEmpty(V) then
V.Quit;
What this code does is check to make
sure that the variant V
refers to something, and then it
attempts to call the Quit method
of the Excel application object. If V
is indeed a valid pointer to such an
object, then Excel will close. This code
is not perfect in all cases, since V
could contain a reference to something
other than an Excel Application object,
thereby allowing VarIsEmpty to
return true, even though the call to
V.Quit would fail. For instance, I
could write:
V := 10;
After making this call VarIsEmpty
would return false, but the call to
V.Quit would obviously fail.
However, in the Excel1 application,
found in Listing 1, V will
usually be either empty, or else
pointing to a COM object. Therefore the
code is reasonably robust. The key
point, at any rate, is that you don't
want to fail to Quit the
Application object or else you can
end up cluttering memory with instances
of this object. Remember that Excel owns
the Application object, and it
will not necessarily be removed from
memory just because you close your
Delphi application. In other words, you
should definitely call Application
Quit or else repeated calls to
Excel from a Delphi application will bog
down your machine by draining system
resources. |
HOME
