MS Office automation with C++Builder

[Books] [Courses & Consulting] [Code Samples] [Links] [Home] [Mail me]

Creating MS Office controllers with C++Builder 3 or 4

Natalia Elmanova

It is a short excerpt from an article published in Computer Press CD (Russia), vol.12, 1998.

An idea to create these examples was generated after reading a wonderful article of Charlie Calvert about Word/Excel Automation with Delphi 4. You can find it at http://www.borland.com/techvoyage.

1. An introduction

If we want to operate an Automation server, we must know its available methods and properties. As a rule, we can find their description in help files and the documentation provided by the vendor of this server (for example, MS Office and Seagate Crystal Reports Professional contain such help files). But in a general case, we must explore the type library of this server.

Let us look at the MS Excel type library (its name is Excel8.olb in the case of using MS Office 97). It contains a lot of properties and methods, and all of them can be used for an Automation.

To operate the server, we need to create an instance of a Variant object ( C++Builder contains an appropriate class) and call the CreateOleObject function.

Variant XL;
...
XL=CreateOleObject("Excel.Application.8");

The parameter of this function is the name of COM object to create. We can find it in the Registry.

Let us mention that there is a hierarchy of objects inside MS Office applications, and the most of them can be a part of an object collection, which can be properties of another objects. For example, the Workbooks collection is a property of an Excel.Application object, the Worksheets collection is a property of a Workbook object, the Cells collection is a property of a Worksheet object. As for MS Word, it also contains collections such as Paragraphs, Words, Tables, etc.

To address to the collection member in C++Builder, we must use such syntax:

Variant MyWorkbook=XL.OlePropertyGet("WorkBooks").OlePropertyGet("Item",1);

2. About C++ syntax, late and early binding

Please draw attention: there are significant syntax differences between C++Builder and Delphi in this case.

The reason is that the actual method names in the case of Delphi are only strings placed in published interfaces (and, possibly, in type libraries), and nothing else. It is Pascal (or VB) that allows to use a syntax similar to using methods. But really it is not using methods as in the case of Pascal objects.

For example:

1) Form1.Show - in this case the real method of TForm is used (C++ analog is Form1->Show());

2) Var V:Variant;
...
V:=CreateOleObject('Excel.Application');
.....
V.Visible:=True;

In this case it seems that the "method" for setting "property" of variant object is used, but it is not a real method. It really means that here is a proxy object which marshals this request to a stub object in the Excel process, and then this stub object calls the real Excel method; the "Show" string may be or not be its real name; it is only a specially published string used to access it.

The same C++ code illustrates this:

Variant V;
V=CreateOleObject("Excel.Application");
V.OlePropertySet("Visible",true); // "Visible" is a string!

C++ does not allow such tricks with its syntax, as Pascal or VB allow, when variants are used.

But, really, in the case of C++ it is possible to use syntax similar to the same of Pascal. It can achieved by using early binding (it means importing the type library into the client application) and, therefore, by making classes which have these methods. In fact, these methods also marshal requests to a stub inside the Excel process.

In fact, COM works as any other distributed computing method (DCE, CORBA, etc.), and all of them use strings for naming methods, but these methods in client applications really marshal requests.

If the early binding is used, it is possible to check errors in the method names (misspelling and so on) during compilation, and all compound software (Excel and its controller) must work faster because of the direct access to the method table. But in this case it is possible to obtain the full incompatibility with the older Excel versions, because the "places" of the used Excel methods can be different.

In fact, Excel8.olb (Excel 97 type library) is very large, and an executable with an imported type library must also be large and, possibly, requires a lot of resources to be loaded. So if it is necessary to use only several methods (e.g. for showing, creating workbooks and filling cells), it is possible that the final productivity of the compound software is better if the variants and the late binding are used.

It is also possible that in the case of using the late binding a compatibility with older versions of Excel can be achieved, if the methods belonging only to older version of Excel are used. The COM specification demands that newer versions of COM servers must contain all old interfaces and their methods, and we must hope that Microsoft follows its own specification.

3. A simple example using variants

Let us create an example using such collections. This application must:

Create an Excel workbook with two pages;
Fill the first 10 cells of 2 columns with numbers;
Calculate the sum of these columns;
Change the color of interior and the font properties in one of these columns;
Save this workbook to a file.

//---------------------------------------------------------------------------
#include <vcl.h>
#pragma hdrstop
#include <ComObj.hpp>
#include <atl\atlvcl.h> //this line is necessary if C++Builder 4 is used
#include "xlauto2.h"
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma resource "*.dfm"
TForm1 *Form1;
Variant XL,v0,v1,v2;
//---------------------------------------------------------------------------
__fastcall TForm1::TForm1(TComponent* Owner)
: TForm(Owner)
{
}
//---------------------------------------------------------------------------
void __fastcall TForm1::Button1Click(TObject *Sender)
{
XL=CreateOleObject("Excel.Application.8");
XL.OlePropertySet("Visible",true);
v0=XL.OlePropertyGet("Workbooks");
v0.OleProcedure("Add");
v1=v0.OlePropertyGet("Item",1);
v0=v1.OlePropertyGet("Worksheets") ;
v0.OlePropertyGet("Item",1).OlePropertySet("Name","The yellow book-keeping ");
v0.OlePropertyGet("Item",2).OlePropertySet("Name","The red book-keeping ");
for (int j=1;j<3;j++)
{
v1=v0.OlePropertyGet("Item",j);
for (int i=1;i<11;i++)
{
v1.OlePropertyGet("Cells").OlePropertyGet("Item",i,1).OlePropertySet("Value",i);
v1.OlePropertyGet("Cells").OlePropertyGet("Item",i,2).OlePropertySet("Value",i*5);
v2=v1.OlePropertyGet("Cells").OlePropertyGet("Item",i,2);
v2.OlePropertyGet("Font").OlePropertySet("Color",clBlue);
v2.OlePropertyGet("Font").OlePropertySet("Bold",true);
v2.OlePropertyGet("Interior").OlePropertySet("ColorIndex",9-3*j);
}
v1.OlePropertyGet("Cells").OlePropertyGet("Item",11,1).OlePropertySet("Value","=SUM(A1:A10)");
v1.OlePropertyGet("Cells").OlePropertyGet("Item",11,2).OlePropertySet("Value","=SUM(B1:B10)");
}
XL.OlePropertySet("DisplayAlerts",false); //suppress the warning dialog on closing the server
XL.OlePropertyGet("Workbooks").OlePropertyGet("Item",1).OleProcedure("SaveAs","test.xls");
XL.OleProcedure("Quit");
XL=Unassigned;
}
//---------------------------------------------------------------------------

If we want not to show the Excel window, we must delete or comment the following line:

XL.OlePropertySet("Visible",true);

4. Using constants from the type library

This example uses the default parameters of workbooks and worksheets. How to change it?

Let us look at this code:

XL=CreateOleObject("Excel.Application.8");
XL.OlePropertySet("Visible",true);
v0=XL.OlePropertyGet("Workbooks");
v0.OleProcedure("Add",-4109);

This example makes Excel to create an empty sheet with a diagram using a non-default template.

The value –4109 is an Excel constant (the number of the template) which can be found in the Excel type library.

Now we can modify our example. Now we shall create a chart using the data of our worksheets, copy it to the clipboard, paste it to the Word document (look at the MSWord8.olb for details), and sign it.

//---------------------------------------------------------------------------
#include <vcl.h>
#pragma hdrstop
#include "ole2.h"
#include <ComObj.hpp>
#include <atl\atlvcl.h> //this line is necessary if C++Builder 4 is used
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma resource "*.dfm"
TForm1 *Form1;
Variant XL,v0,v1,v2, v22, vrange, WD,a,b,c;
//---------------------------------------------------------------------------
__fastcall TForm1::TForm1(TComponent* Owner): TForm(Owner)
{
}
//---------------------------------------------------------------------------
void __fastcall TForm1::Button1Click(TObject *Sender)
{
XL=CreateOleObject("Excel.Application.8");
XL.OlePropertySet("Visible",true);
v0=XL.OlePropertyGet("Workbooks");
v0.OleProcedure("Add");
v1=v0.OlePropertyGet("Item",1);
v0=v1.OlePropertyGet("Worksheets") ;
v22=v1.OlePropertyGet("Charts") ;
v22.OleProcedure("Add");
v0.OlePropertyGet("Item",1).OlePropertySet("Name","The yellow book-keeping ");
v0.OlePropertyGet("Item",2).OlePropertySet("Name","The red book-keeping ");
for (int j=1;j<3;j++)
{
v1=v0.OlePropertyGet("Item",j);
for (int i=1;i<11;i++)
{
v1.OlePropertyGet("Cells").OlePropertyGet("Item",i,1).OlePropertySet("Value",i);
v1.OlePropertyGet("Cells").OlePropertyGet("Item",i,2).OlePropertySet("Value",i*5);
v2=v1.OlePropertyGet("Cells").OlePropertyGet("Item",i,2);
v2.OlePropertyGet("Font").OlePropertySet("Color",clBlue);
v2.OlePropertyGet("Font").OlePropertySet("Bold",true);
v2.OlePropertyGet("Interior").OlePropertySet("ColorIndex",9-3*j);
}
v1.OlePropertyGet("Cells").OlePropertyGet("Item",11,1).OlePropertySet("Value","=SUM(A1:A10)");
v1.OlePropertyGet("Cells").OlePropertyGet("Item",11,2).OlePropertySet("Value","=SUM(B1:B10)");
}
vrange=v0.OlePropertyGet("Item",1).OlePropertyGet("Range","A1:A10");
v1=v22.OlePropertyGet("Item",1);
v2=v1.OlePropertyGet("SeriesCollection");
v2.OleProcedure("Add",vrange);
vrange=v0.OlePropertyGet("Item",1).OlePropertyGet("Range","B1:B10");
v2.OleProcedure("Add",vrange);
v1.OleProcedure("Select");
XL.OlePropertyGet("Selection").OleProcedure("Copy");
WD=CreateOleObject("Word.Application.8");
WD.OlePropertySet("Visible",true);
WD.OlePropertyGet("Documents").OleProcedure("Add");
a=WD.OlePropertyGet("Documents");
b=a.OleFunction("Item",1);
for (int i=1;i<5;i++)
{b.OlePropertyGet("Paragraphs").OleProcedure("Add");};
c=b.OleFunction("Range",1,2);
c.OleProcedure("Paste");
c=b.OleFunction("Range",3,3);
c.OlePropertySet("Text","An Excel chart");
XL.OlePropertySet("DisplayAlerts",false);
XL.OlePropertyGet("Workbooks").OlePropertyGet("Item",1).OleProcedure("SaveAs","test.xls");
XL.OleProcedure("Quit");
WD.OlePropertySet("DisplayAlerts",false); b.OleProcedure("SaveAs","test2.DOC");
WD.OleProcedure("Quit");
XL=Unassigned;
WD=Unassigned;
}
//---------------------------------------------------------------------------

To write such code is, of course, more convenient than to implement the word processor or spreadsheet functionality.

The similar Delphi example is in the article of Charlie Calvert

Last updated 31.12.99

You are the visitor of this page since 14 Jun 1999

This page hosted by . Get your own Free Home Page