I am in need of an exel api which allows the users to enter a formula in excel and retrieve results from a remote database. Capital IQ has an excel api with similar functionality as an example.
The program should be written in C++ to output an XLL library which calls an in process COM server to avoid registering the server on the users machine. The COM server can be written in C++ or C#, but should be called within the XLL as an inprocess COM server to avoid registering it on the users machine (most users will not have the required permissions to register COM).
1. The functions should be registered through the XLL
2. The function library should have caching logic to prevent making calls to the database for the same data that has already been retrieved. all functions should look to the cache before making a call to the database. Their should also be a method to clear the cache if needed (size to big, user request, etc.)
3. Very important, the library should have a mechinism to batch calls for data rather than open a connection each time data is requested.
4. The function should return the result to the calling cell through the function, not override the cell to equal the value (I can clarify this if needed).
5. The function should be able to return single values and a range of values without the need to enter the formula as a array.