T(Adv)Excel component 3.0
Copyright (c) 1996, 1998 by
Stefan Hoffmeister
(Stefan.Hoffmeister@Uni-Passau.de)
(Stefan.Hoffmeister@poboxes.com)
Portions copyright (c) 1996, 1997
Tibor F. Liska
(liska@sztaki.hu)
The freeware version of the component pack may
be used and copied freely.
*****************************************************************
LICENCED source code can be found in the
licenced.src
folder.
*****************************************************************
The source code for the FREEWARE version
can be found in the
free.src
folder.
*****************************************************************
THE REGISTERED VERSION MAY BE USED FREELY
IN COMMERCIAL APPLICATIONS WITHOUT ADDITIONAL
COST BUT MUST NEITHER BE REDISTRIBUTED NOR
RESOLD.
THIS SOFTWARE AND THE ACCOMPANYING
FILES ARE DISTRIBUTED "AS IS" AND
WITHOUT WARRANTIES AS TO PERFORMANCE
OR MERCHANTABILITY OR ANY OTHER
WARRANTIES WHETHER EXPRESSED OR IMPLIED.
Because of the various hardware and software
environments this software may be put into,
NO WARRANTY OF FITNESS FOR A PARTICULAR
PURPOSE IS OFFERED.
THE AUTHOR OF THIS SOFTWARE (STEFAN
HOFFMEISTER) CANNOT BE HELD LIABLE
FOR ANY DIRECT OR INDIRECT DAMAGE
ARISING THROUGH INSTALLATION AND /
OR USE OF THE SOFTWARE.
*****************************************************************
[ PLEASE DO HAVE A LOOK AT CBuilder.txt FOR IMPORTANT
Borland C++ Builder 3.0
and
Borland C++ Builder 1.0
compatibility notes. ]
T(Adv)Excel is _the_ free DDE component to talk to
Microsoft Excel. DDE is used for maximum data
transfer speed.
As of version 2.6 the registered version of this component
pack contains an easy to use caching technology that will
allow you to speed up your existing code by just changing
two lines:
Wrap up your calls to Put..At in BeginUpdate and EndUpdate
calls like
Excel.BeginUpdate; { start caching }
try
{ push large amounts of data }
for i := 1 to 10000 do
Excel.PutIntAt(i, 1, i); { write 10000 meaningless cells }
finally
Excel.EndUpdate; { flush cache and stop caching }
end;
Typically just adding these two lines will give you a
performance boost of 3000 to 7000 per cent (sic!).
This component pack contains code for EXTREMELY FAST transfer
speed, even better than the write cache ever will achieve.
Typical examples are
1000+ elements = 150 milliseconds (!) or better
on an old 486 DX2 80 system.
1000+ elements = 25 milliseconds (!) or better
on a Pentium II 233 system.
65000 elements in 120 milliseconds (!) on a
Win32 Pentium II 233 system
The high-speed code used in this component is by far the
fastest way (known to me) to push arbitrary data into
Excel and to get data from Excel, some orders of magnitude
faster than OLE automation, much more reliable and a lot
easier to use.
The high-speed code is crippled in the unregistered version
(obtained, for instance, from the Delphi Super Page): it will
alter approximately 50 per cent of the data transferred (see the
TRIAL folder).
REGISTRATION OF THE COMPONENT gives you *full* source code
also for the high-speed component code. The registered version
of the component comes with
- full source code (including functional high-speed code);
- comprehensive online help;
all for Delphi 1/2/3/4 and C++ Builder 1.0 / 3.0 and
- support through email
Registration details can be found at
http://www.shareit.com/programs/100506.htm
You can use the provided components to produce output tables
directly into Microsoft Excel 4.0, 5.0, 7.0 (aka 95), 8.0
(aka 97), including cell output, formatting, commands,
macro calls, and much more.
The components are partially based on work by Tibor Liska
(liska@sztaki.hu), who has released his own version of
TExcel (version 3.00 or higher), which does only include
a very limited *subset* of the functionality provided
by this component pack.
WARNING: This set of components is NOT source code compatible
with Tibor's component version 3.00 or higher.
Code that compiles in both Tibor's and this version
shows the exact same behaviour at runtime, though.
The attached demo project shows how easy it is to drive Excel.
If you want to see how each function works, check out the
source code - all functionality is demonstrated there and
explained in detail where necessary.
As the demo creates the component explicitly, it can be tried
*without* installing the component first.
Excel DDE support note:
-----------------------
So before asking questions like "How do I make Excel <something>?"
or "How do I use this command with Excel?" please consult the
Microsoft Excel 4.0 (!) macro help file, freely available from
the Microsoft WWW site (http://www.microsoft.com/).
This file should also be referenced in you Excel 97 online
documentation.
[ "macro97.exe" is the filename of the Excel 4.0 macro help
intended for Microsoft Excel 97; the macro help for
previous versions of Microsoft Excel does have a different
name ]
The various help files can be found in the Excel Knowledge Base
with varying filenames. Unfortunately Microsoft seems to change
the complete structure of their web site every other month, so
it is impossible to give more detailed pointers to locations.
NOTE to users of the version distributed PRIVATELY by
-----------------------------------------------------
Stefan Hoffmeister:
-------------------
You get full support through email. Please send questions
to Stefan.Hoffmeister@poboxes.com (alternative:
Stefan.Hoffmeister@Uni-Passau.de).
Files:
EXCELS .PAS Component source
[ in the private version this file
contains the high-speed code ]
EXCELS .D16 Component icons for Delphi 1.0
EXCELS .D32 Component icons for Delphi 2.0 / 3.0
and C++ Builder 1.0
EXCELS3 .DPK Package file for Delphi 3.0
EXCELS .HLP Online help [ONLY private version]
DEMOXL .DPR Demo project
DEMOXL1 .PAS Demo main unit
DEMOXL1 .DFM Demo main form
DEMOXL2 .PAS Demo auxiliary unit
DEMOXL2 .DFM Demo auxiliary form
DEMOXL .XLS Demo Excel macro collection
README .TXT Standard readme
AEXCEL .TXT This file
AEXCEL .INF Upload information
CBUILDER.TXT Important C++ Builder 1.0 notes
HISTORY .TXT Version history
The unit EXCELS.PAS contains three classes:
TCustomExcel = class(TComponent)
TExcel = class(TCustomExcel)
TAdvExcel = class(TExcel)
The high-speed version additionally has
TCustomXlTableData
TSetXlTableData
TGetXlTableData
plus
ExcelError = class(Exception)
=> the components' generic exception
and a couple of error strings, declared as "RESOURCESTRING"
for Delphi 3 and as normal constant strings for Delphi 1 and 2
and C++ Builder 1.0.
Documentation for the FREEWARE version:
---------------------------------------
Instead of extensive documentation and online help some words
about the interface.
If you want to see how each function works, check out the
demo - all functionality is demonstrated there and
explained where necessary.
============
TCustomExcel
============
procedure TCustomExcel.Connect;
To connect to Excel.
procedure TCustomExcel.Disconnect;
To disconnect from Excel.
function TCustomExcel.Request(const Item: string): string;
To request a DDE item.
Use 'SysItems' to retrieve all available DDE items
or use one of the specialized procedures below.
procedure TCustomExcel.Exec(const Cmd: string);
To execute Excel commands.
procedure TCustomExcel.LocateExcel;
To locate Excel path.
property Connected: Boolean
To check connection
property DDEConv: TDdeCLientConv
To use the DDE connection directly.
Usually not needed.
property Ready: Boolean
To check Excel status.
property Formats: TStringList
property Topics: TStringList
property Protocols: TStringList
property EditEnvItems: TStringList
Get specific Excel parameters.
These are wrappers around Request (see above).
property Selection: string
Get current Excel cell selection
function GetRectSelection(var TopLeft, BottomRight: TExcelCell): string;
Returns the current file and sheet and writes the
current (rectangular) selection to TopLeft and BottomRight.
If only a single cell is selected TopLeft = BottomRight.
If a whole column is selected, the corresponding row equals "0"
If a whole row is selected, the corresponding column equals "0"
procedure RetrieveSelection;
Parses the content of "property Selection" (see
above) into the public variable SelectionList.
This is particularly useful for multiple
(non-rectangular) selections (see GetRectSelection for returned
data). See the demo on how to use this functionality.
property RowChar: char read FRowChar;
property ColChar: char read FColChar;
These contain the *country-specific* chars
to address Excel columns and rows.
They are filled once data is retrieved (GetData***)
or the selection is parsed (GetRectSelection,
RetrieveSelection). Before that they are
*undefined* (= #0).
property ExeName: TFileName
To set Excel path.
property OnOpen: TNotifyEvent
Event handler called when Excel is connected.
property OnClose: TNotifyEvent
Event handler called when Excel is disconnected.
======
TExcel
======
procedure TExcel.AutoMoveActiveCell;
To move current cell due to property MoveActiveCell.
procedure TExcel.Select(Row: TExcelRow; Col: TExcelCol);
To select a cell of the active Excel sheet.
procedure TExcel.Move(deltaRow, deltaCol: Integer);
To select a cell relative to the current one.
procedure TExcel.Insert(const s: string);
To insert string into the current cell.
procedure TExcel.InsertAt(Row: TExcelRow; Col: TExcelCol; const s: string);
To insert string into the specified cell.
procedure TExcel.PutExt(e: Extended);
To insert real number into the current cell.
procedure TExcel.PutExtAt(Row: TExcelRow; Col: TExcelCol; e: Extended);
To insert real number into the specified cell.
procedure TExcel.PutStr(const s: string);
To insert string into the current cell.
procedure TExcel.PutStrAt(Row: TExcelRow; Col: TExcelCol; const s: string);
To insert string into the specified cell.
function GetData: string;
Retrieve a single cell from the current selection
in the current Excel file and sheet.
function GetDataAt(Row: TExcelRow; Col: TExcelCol): string;
Retrieve data for the given cell in the current
Excel file and sheet.
function GetDataAtFileSheet( Row: TExcelRow; Col: TExcelCol;
const FileSheet: string): string;
Same as above, but specify Excel file and sheet.
Pass in FileSheet a string returned by GetRectSelection
or a string listed in SelectionList.
This is the preferred use as it has less overhead than
GetDataAt.
property MoveActiveCell: TCellDir
To set direction to move.
TCellDir = (dirNone, dirUp, dirDown, dirLeft, dirRight);
property Decimals: Word
To specify number format for real numbers.
property DecimalSeparator: Char
To specify decimal separator for real numbers.
=========
TAdvExcel
=========
procedure TAdvExcel.OpenMacroFile(const Fn: TFileName);
To specify a macro file.
Needed only when macros are going to be called.
procedure TAdvExcel.CloseMacroFile;
To close an open macro file.
Note: Only one macro file can be opened at the same time.
procedure TAdvExcel.RunMacro(const Mn: string);
To execute a macro of the open macro file.
procedure TAdvExcel.StartTable;
To open a new Excel sheet where to send data.
It minimises Excel.
procedure TAdvExcel.EndTable;
To finish the table and bring up the result i.e. restore Excel.
procedure TAdvExcel.NewSheet(SheetType: TNewSheet; const TemplateName: string);
To create a new sheet in the current Excel workbook.
procedure TAdvExcel.NewWorkbook(SheetType: TNewSheet; const TemplateName: string);
To create a new Excel workbook.
procedure TAdvExcel.EchoOn;
procedure TAdvExcel.EchoOff;
To set Excel echo.
This speeds up "Put..." operations significantly.
procedure TAdvExcel.DisableInput;
procedure TAdvExcel.EnableInput;
To set Excel input.
Safer operations as the user cannot interfere.
procedure TAdvExcel.PutInt(i: Longint);
procedure TAdvExcel.PutIntAt(Row: TExcelRow; Col: TExcelCol; i: Longint);
To insert integer number.
procedure TAdvExcel.PutDate(d: TDateTime);
procedure TAdvExcel.PutDateAt(Row: TExcelRow; Col: TExcelCol; d: TDateTime);
To insert date.
procedure TAdvExcel.PutTime(d: TDateTime);
procedure TAdvExcel.PutTimeAt(Row: TExcelRow; Col: TExcelCol; d: TDateTime);
To insert integer time.
procedure TAdvExcel.PutDateTime(d: TDateTime);
procedure TAdvExcel.PutDateTimeAt(Row: TExcelRow; Col: TExcelCol; d: TDateTime);
To insert integer date and time.
procedure TAdvExcel.PutData(const AnArray: array of const);
procedure TAdvExcel.PutDataAt( Row: TExcelRow; Col: TExcelCol;
const AnArray: array of const;
FillDirection: TCellDir);
To insert array and arbitrary data.
Just stuff in anything you like.