What is the appropriate technology to pull data to into excel spreadsheet via a formula from a .NET source?

Question

I would like to be able to allow a user to enter a custom formula in a cell in Excel which will subscribe to data provided by an external .NET application. For example, the user might enter...

=getCurrentValue("ABC")

There is an external .NET (C#) application that is determining the value of "ABC" every minute.

The cell should display the initial value of "ABC" from the external application and update when the external application sends a new value.

In the past I think the external application would be a DDE server and the formula would be a DDE client formula but DDE is apparently dead. What is the "correct" technology to use to do this?

Note that the spreadsheet needs to pull the data via a formula. The information is not going into a fixed template excel file but into any spreadsheet into which the user enters a formula.

Thanks,

John

Alkali

FWIW, I thought the question was very clearly asked, and found the extraordinary high level of irrelevance present in the non-accepted answers to be completely mind blowing. – Raby

Answer 1

Excel RTD (Real Time Data) is the replacement for DDE. It allows you to push values to a cell whenever updates are available. If the process providing the updates is an external application, setting up a WCF channel between the RTD server and the external app should allow you to feed real-time updates into Excel.

Here are some links:

• How to create a RealTimeData server for Excel
• Excel RTD Servers: Minimal C# Implementation
• Excel RTD Servers: A Topic’s Initial Value
• Excel RTD Servers: How to use UpdateNotify Properly
• Excel RTD Servers: Multiple Topics in C#

Ledet

There are more of his articles, see the list on weblogs.asp.net/kennykerr/pages/My-Articles.aspx – Flair

Answer 2

You can communicate with Excel sheets through the Excel Api:

new Microsoft.Office.Interop.Excel.Application();

Younker

You could consider CSV files though. Most hostingproviders won't allow Office on there server so you could write to and CSV file. Although you can do much much more with Microsoft.Office.Interop.Excel, you can even create print area's and charts – Younker

The excel interop appears to be targeted at automating excel tasks or pushing data into excel. It can trigger a DDE request in excel for example. I have adjusted the question to reflect we want excel to pull the data (though updates will be pushed back once excel subscribes). DDE would be perfect but it appears to be no longer recommended. Is there a mechanism to register a formula/callback/something via interop? If so could you point me to an example? – Alkali

Something like this? oRng = oSheet.get_Range("C2", "C6"); oRng.Formula = "=A2 & \" \" & B2"; – Younker

The goal is to allow the user to enter in any arbitrary sheet a formula like "=ShowMostRecentExternalValue("IBM")". The cell would then display a value specific to "IBM" from the external application and the value would update as triggered by the external application (like a stock quote...could change 5 times in a second and then stay the same for a minute). In your example (as I understand it) the external application would have to know how to get the sheet, "C2", "C6", and some parameters to know which data to put in the cells. This was built into DDE. Looking for the equivalent. – Alkali