Click here to Skip to main content
15,860,972 members
Articles / Desktop Programming / MFC
Article

Excel Add-in Using Excel C API and COM

Rate me:
Please Sign up or sign in to vote.
4.86/5 (13 votes)
7 Jul 2003CPOL4 min read 149.7K   8.2K   56   18
Demonstrates an Excel add-in that uses the excel4 C API as well as COM automation techniques.

Sample Image - EasyIF_screenshot.jpg

Introduction

This sample add-in demonstrates how the Excel C API can be used to develop add-ins, in conjunction with COM automation. Back in the dark ages (before COM add-ins), programs like Excel and Word exposed a C API that developers could use to develop add-ins for. They even came up with special DLL file name extensions (XLL for Excel and WLL for Word) to denote these as Office add-in modules.

The general overview of how this application is constructed:

  • An Excel C API based DLL module that interfaces with Excel
  • Visual components added to the Excel menu structure using the C API
  • Standard MFC-style coding for the add-in UI, dialogs, etc.
  • COM automation to control certain aspects of Excel that are not exposed via the C API

The basic interface to the Excel C API involves exporting certain named functions that Excel calls automatically (similar to ISAPI). These functions then register the necessary Formulas, Menu items and Toolbars by interacting with Excel via the Excel4 function, passing the names and corresponding function pointers. Data is passed back and forth in this function using an unusual data structure, the XLOPER. This structure predates OLE and is very similar in concept to the VARIANT data type structure. There are some structure members and concepts specific to Excel, however. When the add-in functions are called, care must be taken not to assume that the function is running on any specific thread, since Excel calculation engine may call your function as part of a recalc, or the Excel UI may call the function as part of the formula builder.

Overview Of The Addin's Functionality

With EasyIf, simple to comprehensive logical functions such as if/then relationships, and lookup and reference functions such as VLOOKUP and HLOOKUP can be combined into one easy worksheet function, replacing complex and at times, limiting standard functions. This Excel add-in product is easy to use and maintain, and delivers powerful logical relationship comparisons, unprecedented lookup capabilities and accurate value selection that were only previously available through custom macro programming. The add-in contains a new comparative analysis function that, depending on the comparison criteria, Excel either does not offer or is so difficult and limiting to use via built-in logical and lookup/reference functions that users shun away from this comparative analysis thereby missing some of the value that Excel can provide. Click here for the official User's Guide for a more complete description of the functionality.

Secondary Topic Of Interest

The heart of the add-in's functionality involves a weighted pattern-matching engine. This engine allows for complex, exception based selection and determination. Consider the following example:

The rate for Programming services is $100, except if it is done by a Senior developer then the rate is $120. But, if the work is done by someone from the San Francisco area, the rates are $115 and $135 respectively. These rates apply for all customers, except for GOLD level customers who receive a 10% discount. Oh, by the way, we have also negotiated specific, lower rates for special customers based on contracts.

How would you:

  1. code this so that it is maintainable and
  2. be able to do an Excel spreadsheet on this?

With the Exception-based pattern-matching engine, you could set up a table that contains the following entries, where '*' denotes a wildcard.

Cust LevelCustomerLocationServiceWorkerClassRate
***PROG*100
***PROGSENIOR120
**SFPROG*115
**SFPROGSENIOR135
GOLD**PROG*90
GOLD**PROGSENIOR108
GOLD*SFPROG*103.50
GOLD*SFPROGSENIOR121.50
*Microsoft*PROG*76
*Microsoft*PROGSENIOR87

Using the pattern matching algorithm, this is all that would need to be maintained. The algorithm automatically selects the most fully matched entry, returning the rate in this case. Since it supports different data types for the columns, date-sensitivity could be added without any recoding by simply inserting additional columns. Columns can be configured to use extended comparison operators as well, including <, <=, >, >= and <>. Any number of these tables may be defined, each having a different structure and return type. For more information, consult the demo or Matrix.h and Matrix.cpp.

Important Program Note

The source code provided is for viewing only and will NOT compile, as it uses a third-party commercial library for certain functions. For those of you who have a license for Dundas Ultimate Grid 97 for MFC, you may have a chance of compiling it.

Conclusion

Hopefully, this code will illustrate that the good old fashioned C API can coexist with current COM technology, allowing the developer to use each technology where appropriate. Additionally, it provides a (hopefully) unique concept on how to easily handle complex decision trees and lookups of constantly changing values.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Web Developer
United States United States
Starting developing in 1980's using Basic, MSC 5.0, Btrieve and DBFs. Back when men were men and there were no wizards. Since then, I have developed payroll processing system, general ledger packages, billing systems, project accounting systems, order entry, a report writer, email systems, development frameworks, C++ based Web Application Server and others that have long been forgotten.

Professionally, I have been a developer, sr. developer, development lead, architect, chief architect, CTO, director of development, and consultant. I am also in charge of changing the bottled water when empty.

Comments and Discussions

 
GeneralMy vote of 5 Pin
OAlpha14-Mar-13 16:24
OAlpha14-Mar-13 16:24 
GeneralHello Pin
chaitanya shah13-Aug-07 4:55
chaitanya shah13-Aug-07 4:55 
Questionadding custom images to a toolbar button Pin
mousam.dubey13-Jul-07 2:33
mousam.dubey13-Jul-07 2:33 
Generalbuild the demo project Pin
tjj19-Nov-05 11:13
tjj19-Nov-05 11:13 
GeneralRe: build the demo project Pin
basementman21-Nov-05 4:00
basementman21-Nov-05 4:00 
GeneralWorks well but just one time Pin
thom1224-Aug-05 8:57
thom1224-Aug-05 8:57 
GeneralWrite value in cells Pin
excel newby22-Aug-05 10:44
sussexcel newby22-Aug-05 10:44 
GeneralRe: Write value in cells Pin
basementman22-Aug-05 10:46
basementman22-Aug-05 10:46 
GeneralCWinThread and CFrameWnd Pin
Nic_Gen22-Aug-05 4:06
Nic_Gen22-Aug-05 4:06 
GeneralRe: CWinThread and CFrameWnd Pin
basementman22-Aug-05 4:30
basementman22-Aug-05 4:30 
GeneralRe: CWinThread and CFrameWnd Pin
22-Aug-05 4:54
suss22-Aug-05 4:54 
GeneralCopy method Pin
daydremer7-Jul-04 15:26
daydremer7-Jul-04 15:26 
GeneralRe: Copy method Pin
basementman8-Jul-04 3:58
basementman8-Jul-04 3:58 
GeneralAddins for Excel Pin
Bolka25-Dec-03 1:53
Bolka25-Dec-03 1:53 
GeneralRe: Addins for Excel Pin
marraco26-Nov-05 14:59
marraco26-Nov-05 14:59 
GeneralExcel Add-In using VB.NET (or another .NET language) Pin
AlfredoDS7-Jul-03 4:05
AlfredoDS7-Jul-03 4:05 
GeneralRe: Excel Add-In using VB.NET (or another .NET language) Pin
basementman7-Jul-03 6:45
basementman7-Jul-03 6:45 
GeneralRe: Excel Add-In using VB.NET (or another .NET language) Pin
Anonymous24-Jul-03 5:25
Anonymous24-Jul-03 5:25 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.