正文
Excel的线程 与 SynchronizationContext的实现
小程序：扫一扫查出行【扫一扫了解最新限行尾号】
复制小程序
COM组件的线程模型与Excel多线程的背景知识
COM组件的线程模型被称之为Apartment模型，COM对象初始化时其执行上下文(Execution Context)，他要么和单个线程关联STA(Single Thread Apartment ) 要么和多个线程关联MTA(Multi Thread Apartment)。Excel是一种STA线程的应用程序，使用多线程直接怼会出问题，必须借助线程的同步上下文实现线程间的消息传递。
1、Excel 开发中与线程相关的若干问题参考这个链接：
https://www.cnblogs.com/yangecnu/p/Some-Thread-Releated-Problems-and-Solutions-in-Excel-Development.html
2、SynchronizationContext的实现参考这个链接（共3篇文章，进入链接后可以找到下一章的链接）：
https://www.codeproject.com/Articles/31971/Understanding-SynchronizationContext-Part-I
3、ExcelDNA是什么
Excel-DNA is an independent project to integrate .NET into Excel. With Excel-DNA you can make native (.xll) add-ins for Excel using C#, Visual Basic.NET or F#, providing high-performance user-defined functions (UDFs), custom ribbon interfaces and more. Your entire add-in can be packed into a single .xll file requiring no installation or registration.
https://excel-dna.net/
4、ExcelDNA下实现一个自定义的SynchronizationContext
https://groups.google.com/forum/#!searchin/exceldna/synchronizationcontext%7Csort:date/exceldna/pyybn0lSP8k/3DW6NOekCgAJ
ExcelDNA中的解决方案
dynamic app;
dynamic worksheet; public void Test(IRibbonControl ribbonControl)
{
app = ExcelDnaUtil.Application;
worksheet = app.ActiveSheet;
worksheet.Cells[, ].Value = Thread.CurrentThread.ManagedThreadId;
Action<object> action = Run;
Task task = new Task(action,ExcelSynchronizationContext.Current);
task.Start(); }
private void Run(object context)
{
worksheet.Cells[, ].Value = Thread.CurrentThread.ManagedThreadId; for (int i = ; i < ; i++)
{
Thread.Sleep();
ExcelAsyncUtil.QueueAsMacro(UpdateUI, i);
}
} private void UpdateUI(object state)
{
int i = (int)state + ;
worksheet.Cells[i, ].Value = i;
worksheet.Cells[i, ].Value = Thread.CurrentThread.ManagedThreadId;
}
Task task = new Task(action,ExcelSynchronizationContext.Current); ExcelSynchronizationContext.Current为主线程的SynchronizationContext。
ExcelAsyncUtil.QueueAsMacro(UpdateUI, i); 其实是执行了SynchronizationContext的Post方法。 上面是ExcelDNA里集成的SynchronizationContext实现，有兴趣可以→ 尝试实现自定义一个SynchronizationContext
一个SendOrPostCallbackItem实例为一组SendOrPostCallback委托与方法、参数
internal enum ExecutionType
{
Post,
Send
}
internal class SendOrPostCallbackItem
{
object state;
ExecutionType executionType;
SendOrPostCallback callbackMethod; internal SendOrPostCallbackItem(SendOrPostCallback callback, object state, ExecutionType executionType)
{
this.callbackMethod = callback;
this.state = state;
this.executionType = executionType;
} internal void Execute()
{
if (executionType == ExecutionType.Post)
Post();
else
Send();
}
internal void Send()
{
throw new NotImplementedException();
} internal void Post()
{
callbackMethod(state);
}
}
DiyExcelSynchronizationContext，在ExcelDNA下实现一个SynchronizationContext
public class DiyExcelSynchronizationContext : SynchronizationContext
{
private const string RunMacroName = "ExcelSyncContext.Run";
private static readonly ConcurrentQueue<SendOrPostCallbackItem> queue = new
ConcurrentQueue<SendOrPostCallbackItem>();
private static readonly TimeSpan BackoffTime = TimeSpan.FromSeconds();
[ExcelCommand(Name = RunMacroName)]
public static void Run()
{
while (true)
{
SendOrPostCallbackItem workItem;
if (!queue.TryDequeue(out workItem))
{
return;
}
workItem.Execute();
}
}
dynamic application = ExcelDnaUtil.Application;
public override void Post(SendOrPostCallback d, object state)
{ SendOrPostCallbackItem item = new SendOrPostCallbackItem(d, state, ExecutionType.Post);
queue.Enqueue(item);
Task.Factory.StartNew(() =>
{
while (true)
{
try
{
application.Run(RunMacroName);
break;
}
catch (COMException e1)
{
if (IsRetry(e1))
{
Thread.Sleep(BackoffTime);
continue;
}
return;
}
catch (Exception e2)
{
return;
}
} });
}
public const uint RPC_E_SERVERCALL_RETRYLATER = 0x8001010A;
public const uint RPC_E_CANTCALLOUT_INASYNCCALL = 0x800AC472; public static bool IsRetry(COMException e)
{
var errorCode = (uint)e.ErrorCode;
switch (errorCode)
{
case RPC_E_SERVERCALL_RETRYLATER:
case RPC_E_CANTCALLOUT_INASYNCCALL:
return true;
default:
return false;
}
}
}
