Mega Code Archive

EXCEL AND DELPHI REVISITED

Title: EXCEL AND DELPHI REVISITED Question: If you are looking to expand Excel possibilities using Delphi, this article might prove useful. Here is provided an OOP aproach to interfacing Excel and Delphi. Also, I have programmed several functions that deal directly with internal Excel types. You can find the example files in http://www.financial-science.net Answer: At the core of the Interfacing units is the _xlfunction unit, which is included in the sample files. The xlfunctionlist object (That, although declared in this unit, you must create) has two main methods: add_function, which takes as parameters the name of the function to be used by Excel, a string that lets Excel know the parameters' types, the function name to appear in Excel, the arguments' names, the category to which it belongs (Excel groups functions under such categories), the shortcut text in case you want to provide one, and a 'macro-type' parameter that is 1 if you provide a funtion and 2 if you provide a command. Please note that this work is based on David Bolton's previous work. Once you have add-ed all your functions with add_function, you must call the register_all method. There are some other methods, such as unregister_all, that are (almost) self explanatory. If you provide, for example at installation, the registry with a category, and that category matches the Appname field, then tfunctionlist can read several fields from the computer registry. I currently use 'Username', 'Path' and 'DBPath'. Such fields are listed under hkey_current_user/software and, by default, the root key is MS (my initials) This way I can take care of data updating, etc. If you set the StartupWindow to a Form, then such form is shown (so that user see a copyright window). You must hide it manually. See example. First, select in Delphi IDE File|New|Other|DLL Wizard. include de {$E XLL} compiler directive to force extension to XLL. Every function that you intend to use from Excel has to be declared with the stdcall convention ( example: function multiply(arg1,arg2 : double) : double;stdcall; ) and has to be exported in an EXPORTS clause Note that the exported name does not have to match the function name: exports multiply name 'ExcelMult', but the name declared to add_function has to be the exported name (ExcelMult to follow above example). You have to initialize the XLLName variable; You can export functions on each unit, or all of them at once. You can use simple types and rely on Excel to manage type conversion. However, Excel uses a very flexible type called 'XLOper'. The definition is included in the xltypes unit (along with some other useful types) If you decide to rely on XLOper, then the parameters must be passed by reference (var). The return value has to be a pointer to XLOper (type pxloper is provided). The xll unit provides type conversion routines. Refer to the sample files for usage. Also, there is a function called XLAutofree that releases unused memory allocated to XLOper structures. just remember to set the XLbitDLLFree bit in the xltype field of the xloper so that excel calls back the xll for cleaning up. To do so, see in the examples the usage of XLAnytypeToOper. Keep in mind that some type conversion functions use SDL components (refer to www.lohninger.com) You can either purchase those or change the conversion functions. List of Included files _xlfunction defines TXLfunction and TXLfunctionlist types xll interfaces Excel4v routine provided by excel provides type conversion and memory management routines xlconst provides useful constants xltypes defines XLOper and some other useful types FastShareMem is included in case you need to call other dlls from the main one. Utilities Provides access to the registry and some useful stuff SampleFunctions name says it all XLLSample is the main file You have to export function xlAutoOpen() :smallint;stdcall; This function must take care of function registration. Just follow the example. Also you have to install in Delphi the Microsoft Office Automation server (included with Delphi). To do this click on the IDE the Component item, and then install packages... look for dclofficexp70.bpl package and install it in case it is not installed. It should be in your installation directoryin bin subdirectory. in the URL click the link 'if you are looking for Delphi Add Ins for Excel' and the the link 'Now the files'.