Is there any way to implement a custom data source that works with the "Import External Data" feature?
Let's say I have data in a file format that Excel doesn't understand. I'd like to implement an add-in (or whatever) for Excel that allows it to understand this format through the "External Data" feature. Obviously, I could just convert the file to something Excel already understands but, for the sake of user friendliness, I'd rather not have this additional step.
An add-in (or whatever) here would be an ODBC driver (or an OLEDB driver). I think you don't want to write one.
That's the case if you want to use Import External Data, but you can, of course, create an add-in which would parse and load the file on it's own. It will have it's own menu item then and will not be related with Import External Data.
I ended up creating a service that runs an HTTP server on localhost that serves up tab-delimited data. It was dead simple to develop and fits my needs perfectly. It integrates with the External Data feature with no need to develop an ODBC driver.
For almost any external data sources (including CVS mentioned above) there are ODBC/OLEDB/ MDAC drivers available in windows. If you can use this interchange data formats, thaten I would advice to continue with the Business Data List Connector (BDLC).
You have to create a custom list of type BDLC. Then it's very simple to setup directly in the list settings: You only have to provide the connection string, the select statement and the primary key (if any).
Then you have your data source connected with the option to updates it, manually or even automatically in background.
All list features can b eused, even alerts and workflows, if external data changes.
The BDLC for SharePoint (any editions) and WSS can be found here: http://www.layer2.de/EN/PRODUCTS/Pages/SharePoint-Business-Data-List-Connector.aspx
You could try XLLoop. This lets you implement excel functions (UDFs) on an external server.
So your spreadsheet could contain a function called LoadWidgetData(widgettype, date), which would call your server.
There are server implementations in many languages (Java, Ruby, Python, PHP).
BTW, I work on the project so let me know if you have any questions.