Automation allows one application to control another application. The application being controlled is called an automation server (in our case Excel). The application controlling the server is called an automation controller.
There are two ways that automation servers can be accessed:
Late Binding (IDispatch interface)
When using this method, function names and parameter datatypes are resolved at runtime, all parameters are passed as variants.
As no errors in function names or parameter types are reported at compile time, this method is error prone.
As function names and parameter types need to be looked up at runtime, performance is slow.
The only advantage of this method for Delphi programming is that it is the only way optional parameters can be omitted from function calls.
Early Binding (Using type libraries/interfaces)
When using this method, function names and parameter datatypes are all resolved at compile time.
A type library needs to be imported into Delphi. A type library is a language neutral description of all the objects and functions exposed by a server. (This is similar in nature to a C header file).
All parameters need to be supplied, even when calling functions where the documentation states that some are optional. This enables many errors to be detected and corrected before ever running a program.
Performance is better than for late binding.
Due to the advantages of the second approach, the rest of the document demonstrates the basics of creating an application with early binding. All applications that use Excel Automation should use this technique unless there is a strong justification for not doing so.
Preparing the Type Library.
A pascal unit needs to be created from the type library file.
Select Project and Import Type Library menu item.
Press the add button and select the following file
Unfortunately, this leaves the project in a state that will not compile, this is because the excel_tlb unit redefines the word application to mean something else.
The easiest way to resolve this is to remove the excel_tlb unit from the project and only add it to the uses clause of the units that perform automation.
Documentation
The help file c:\program files\microsoft office\office\vbaxl8.hlp contains information about the objects available for excel.
The macro recorder allows VBA code to be created. This can then be taken and translated into Delphi quite easily.
Automation Example
Download code
The following code example demonstrates the creation of a simple spreadsheet and population of it with data. Ensure that the unit excel_tlb is included within the module.
It is strongly recommended that automation code is kept within a separate module as this will prevent problems caused by name clashes.
Unit sheet;
//--------------------------------------------------------------------
interface
//--------------------------------------------------------------------
uses
windows, sysutils, excel_tlb;
Procedure CreateSpreadsheet;
//--------------------------------------------------------------------
implementation
//--------------------------------------------------------------------
Procedure CreateSpreadsheet(filename : string);
var
xla : _Application;
xlw : _Workbook;
LCID : integer;
begin
xla := CoApplication.Create;
LCID := GetUserDefaultLCID;
try
xla.Visible[LCID] := true;
// blank workbook
//xlw := xla.Workbooks.Add(xlWBATWorksheet, LCID);
// new workbook based on template
xlw := xla.Workbooks.Add('c:\delphi\excel\sample\demo.xlt',LCID);
xla.Range['A1', 'A1'].Value := 'Date';
xla.Cells[1, 2].Value := FormatDateTime('dd-mmm-yyyy', Now);
xla.Cells[3, 1].Value := 'Numbers';
xla.Range['B3', 'E3'].Value := VarArrayOf([1, 10, 100, 1000]);
xla.Range['F3', 'F3'].Formula := '=Sum(B3:E3)';
OLEVariant(xla).Run('Demo', FormatDateTime('dd-mmm-yyyy', Now));
xlw.SaveAs(filename,xlWorkbookNormal,'','',False,False,xlNoChange,
xlLocalSessionChanges,true,0,0,LCID);
finally
xla.Quit;
end;
end;
//--------------------------------------------------------------------
end.
Add the type library unit to the uses clause.
uses
windows, sysutils, excel_tlb;
The first line of code creates an Excel application object.
xla := CoApplication.Create;
The following line of code retrieves the user default locale identifier. This is required by many of Excel’s methods and properties.
LCID := GetUserDefaultLCID;
The following line of code sets the visible property to true. This causes the excel window to become visible. This is useful for checking that the code is working correctly during development.
Note : This call takes the LCID parameter. Unfortunately this is not documented within the Excel help file. The file c:\program files\borland\Delphi 3\imports\excel_tlb.pas shows the function property and method definitions.
xla.visible[LCID] := true;
The following creates a new worksheet, assigning a reference to it within a Delphi variable. Under VBA, the template parameter is optional, Under Delphi it must be supplied.
xlw := xla.Workbooks.Add('c:\delphi\excel\sample\demo.xlt', LCID);
Note : You do not need to supply an Excel template file (.xlt), though it is the best way to set the formatting information. The more that can be done within excel, the less needs to be done within Delphi. This leads to simpler and more maintainable systems.
To create a blank workbook use:
xlw := xla.Workbooks.Add(xlWBATWorksheet, LCID);
The following two lines each set a single cell with a value. They show the two different cell referencing techinques.
xla.Range['A1', 'A1'].Value := 'Date';
xla.Cells[1, 2].Value := FormatDateTime('dd-mmm-yyyy', Now);
The following line demonstrates setting a row of data with one call. This give a significant performance advantage.
xla.Range['A2', 'D2'].Value := VarArrayOf([1, 10, 100, 1000]);
The following line demonstrates setting a formula.
xla.Range['E2', 'E2'].Formula := '=Sum(a2:d2)';
The follwing line of code executes a VBA function which is stored within the template file. The code does looks a bit puzzling at first. By typecasting xla to an OLEVariant forces the call to use late binding rather than early. (Causing the method name and parameters to be resolved at runtime rather than compile time). This is required for this call as Delphi cannot tell how many parameters the macro ‘Demo’ has.
OLEVariant(xla).Run( 'Demo', FormatDateTime('dd-mmm-yyyy', Now));
The following code saves the spreadsheet as filename. All parameters must be supplied even although most are optional within VBA.
xlw.SaveAs( filename, xlWorkbookNormal, '', '',False,False, xlNoChange, xlLocalSessionChanges,
true, 0, 0, LCID);
The following line of code closes Excel and de-allocates any memory associated with it.
xla.quit;
Summary
Always use early binding.
If late binding is required for some calls, use early binding in general and typecast the object variable to an OLEVariant for the calls that require late binding.
Do not include the type library unit within your project.
Keep automation code within a separate unit.
Use the Excel macro recorder to prototype your automation code.
Use the vbaxl8.hlp file for Excel object programming information.
Use the unit excel_tlb.pas to check for Delphi parameter types and numbers.
Load up Excel spreadsheet templates (.xlt files) that contain predefined formatting and then super-impose data. This is faster and reduces the programming required to create formatted spreadsheets. Templates MUST be stored with the application in the application preectory. This will remove the possibility of name clashes. Template files can also contain Excel macros that can be run from Delphi.
Ensure that the quit method is called for the Excel application object (xla.quit). Not calling xla.quit will quickly exhaust all windows resources as multiple instances of Excel will be created.
It is easy to check for multiple instances of Excel by using the NT Task Manager Processes Page (press CTL+ALT+Del to access this.)
On large spreadsheets, setting multiple cells on each automation call will dramatically improve performance. Try not to sacrifice code readability.
Appendix A – Performance
All tests were done on a P166/64mb memory. The initial timings were not recorded. This ensured that excel was available from cache rather than loading from disk. This made it easier to record consistent timings. For a real application there will be approx a 5 second delay in loading excel the first time it is used within the session.
The test involved loading a blank spreadsheet with 10 cols by n rows worth of numeric data. Three techniques were used to test performance:
Populating the sheet one cell at a time.
Populating the spreadsheet one row at a time.
Populating the spreadsheet in one go.
These timings do not include the time involved in retrieving data from the database. This will add a significant amount of time for the creation of large spreadsheets.
All the times shown below are in minutes, time was measured to the nearest second.
Spreadsheet size (rows * columns) Cell at a time Row at a time Sheet at a time
10 * 10 0:01 0:01 >0:01
100 * 10 0:07 0:01 0:01
1000 * 10 1:13 0:07 0:05
5000 * 10 5:22 0:35 0:25
Approx. cells/second 150 1500 2000
Only small spreadsheets can be efficiently populated one cell at a time.
Larger sheets should be populated a row at a time.
The additional complexity of implementing a sheet at a time data population is not worthwhile.
Using the clipboard to transfer data is not recommended as this will corrupt the clipboard and may lead to obscure code.
Saving data as a CSV file and loading it into Excel may be faster, but will require formatting to be done within code and make coding/error handling more complex.
The procedures used for testing:
//-----------------------------------------------------------------------
procedure FillByCell;
var
xla : _Application;
xlw : _Workbook;
LCID : integer;
i,j : integer;
begin
xla := CoApplication.Create;
LCID := GetUserDefaultLCID;
try
xlw := xla.Workbooks.Add(xlWBATWorksheet, LCID);
for i:=1 to ROWS do begin
for j:=1 to 10 do begin
xla.Cells[i,j] := i+j;
end;
end;
xlw.close(false, '', false, LCID);
finally
xla.Quit;
end;
end;
//-----------------------------------------------------------------------
procedure FillByRow;
var
xla : _Application;
xlw : _Workbook;
CellFrom : string;
CellTo : string;
i,j : integer;
Row : array[1..10] of variant;
LCID : integer;
begin
xla := CoApplication.Create;
LCID := GetUserDefaultLCID;
try
xlw := xla.Workbooks.Add(xlWBATWorksheet, LCID);
for i:=1 to ROWS do begin
for j:=1 to 10 do begin
Row[j] := i+j;
end;
CellFrom := 'A' + InttoStr(i);
CellTO := 'J' + InttoStr(i);
xla.Range[CellFrom, CellTo].Value := VarArrayOf(Row);
end;
xlw.close(false, '', False, LCID);
finally
xla.Quit;
end;
end;
//-----------------------------------------------------------------------
procedure FillBySheet;
var
xla : _Application;
xlw : _Workbook;
CellFrom : string;
CellTo : string;
i,j : integer;
range : Variant;
row : array [1..10] of Variant;
LCID : integer;
begin
xla := CoApplication.Create;
LCID := GetUserDefaultLCID;
try
xlw := xla.Workbooks.Add(xlWBATWorksheet, LCID);
Range := VarArrayCreate([1, ROWS], varVariant);
for i:=1 to ROWS do begin
for j:=1 to 10 do begin
row[j] := i+j;
end;
Range[i] := VarArrayOf(row);
end;
CellFrom := 'A' + InttoStr(1);
CellTO := 'J' + InttoStr(ROWS);
xla.Range[CellFrom, CellTo].FormulaArray := Range;
xlw.close(false, '', False, LCID);
finally
xla.Quit;
end;
end;
Appendix 2 – Using a Delphi Wrapper Class
Download code
Rather than embedding Automation calls preectly within your application, this example demonstrates how a simple Delphi class can be used to encapsulate the Excel objects for your applicaions. This can give you a simpler interface to the objects and may help deal with any changes to the object interfaces in future versions of Excel.
unit sheet;
interface
uses
EXCEL_TLB, windows, sysutils;
//-------------------------------------------------------------------------
type
tExcel = class
private
xla : _Application;
xlw : _Workbook;
LCID : integer;
procedure fSetVisible(Visible : boolean);
function fGetVisible : boolean;
procedure fSetCell(Cell : string; Value : OLEVariant);
function fGetCell(Cell : string) : OleVariant;
public
constructor create;
destructor destroy; override;
{ XlWBATemplate }
xlWBATChart = -4109;
xlWBATExcel4IntlMacroSheet = 4;
xlWBATExcel4MacroSheet = 3;
xlWBATWorksheet = -4167;
var
i, j: Integer;
Sheets: Variant;
begin
XLApplication := CreateOleObject('Excel.Application');
XLApplication.Visible := True;
XLApplication.Workbooks.Add;
XLApplication.Workbooks.Add(xlWBatChart);
XLApplication.Workbooks.Add(xlWBatWorkSheet);
XLApplication.Workbooks[2].Sheets.Add(,,1,xlChart);
XLApplication.Workbooks[3].Sheets.Add(,,1,xlWorkSheet);
for i := 1 to XLApplication.Workbooks.Count do begin
ListBox1.Items.Add('Workbook: ' + XLApplication.Workbooks[i].Name);
for j := 1 to XLApplication.Workbooks[i].Sheets.Count do
ListBox1.Items.Add(' Sheet: ' + XLApplication.Workbooks[i].Sheets[j].Name);
end;
end;
procedure TForm1.FormDestroy(Sender: TObject);
begin
if not VarIsEmpty(XLApplication) then begin
XLApplication.DisplayAlerts := False; // Discard unsaved files....
XLApplication.Quit;
end;
end;