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 ?" 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.