I was tasked with writing a real-time Excel automation add-in in C# using RtdServer for work. I relied heavily on the knowledge that I came across in Stack Overflow. I have decide to express my thanks by writing up a how to document that ties together all that I have learned. Kenny Kerr's Excel RTD Servers: Minimal C# Implementation article helped me get started. I found comments by Mike Rosenblum and Govert especially helpful.
相关问题
- Sorting 3 numbers without branching [closed]
- Graphics.DrawImage() - Throws out of memory except
- Why am I getting UnauthorizedAccessException on th
- 求获取指定qq 资料的方法
- How to know full paths to DLL's from .csproj f
Following the previous two answers for the RTD server worked for me. However I ran into an issue when on an x64 machine running Excel x64. In my case, until I switched the "target platform" of the project to x64, Excel always showed #N/A.
(As an alternative to the approach described below you should consider using Excel-DNA. Excel-DNA allows you to build a registration-free RTD server. COM registration requires administrative privileges which may lead to installation headaches. That being said, the code below works fine.)
To create a real-time Excel automation add-in in C# using RtdServer:
1) Create a C# class library project in Visual Studio and enter the following:
2) Right click on the project and Add > New Item... > Installer Class. Switch to code view and enter the following:
3) Right click on the project Properties and check off the following: Application > Assembly Information... > Make assembly COM-Visible and Build > Register for COM Interop
3.1) Right click on the project Add Reference... > .NET tab > Microsoft.Office.Interop.Excel
4) Build Solution (F6)
5) Run Excel. Go to Excel Options > Add-Ins > Manage Excel Add-Ins > Automation and select "StackOverflow.RtdServer"
6) Enter "=RTD("StackOverflow.RtdServer.ProgId",,200)" into a cell.
7) Cross your fingers and hope that it works!
Calling UpdateNotify from the timer thread will eventually cause strange errors or disconnections from Excel.
The UpdateNotify() method must only be called from the same thread that calls ServerStart(). It's not documented in RTDServer help, but it is restriction of COM.
The fix is simple. Use DispatcherSynchronizationContext to capture the thread that calls ServerStart and use that to dispatch calls to UpdateNotify: