Asynchronous ExcelCommands

[ngm_google_groups]

Hi,

I'm looking for a way to call into Excel asychronously from .NET.
However, I think it would be better to describe what I want as
"asynchronous commands" instead of "asynchronous functions."

Here is a simple scenario:

Part 1: Fire off work

Some initial ExcelCommand (maybe run from a menu item) starts a .NET
background worker thread or a ThreadPool work item or a Task. This is
perfectly supported in the current Excel-Dna implementation.

Part 2: Post result to Excel workbook

When the background work is done, I want to come back and modify the
Excel workbook with the result. I don't just want to return function
values. I want to be able to add cells, remove cells, change cell
colors, etc. Essentially, I want an "ExcelSynchronizationContext" in
the style of the WindowsFormsSynchronizationContext which worker
threads can use to "post messages" back to the GUI thread. Any code
which runs on this context would have the same capabilities as code
running within a newly started ExcelCommand (it can modify the
workbook, access COM, etc).

As far as I know, neither Excel-Dna nor ManagedXLL offer anything like
this "out of the box."

I think that this ExcelSynchronizationContext implementation needs

- A queue of "pending work"
- Some way to poke Excel so that it calls a helper ExcelCommand. That
ExcelCommand implementation should pump the queue of pending work.

Here is my current ExcelDna implementation for this idea. It uses
Application.Run() to poke Excel. It works most of the time, but
Application.Run() sometimes raises an AccessViolationException which
causes the Excel process to crash. This is rare, but it is really bad
when it happens. Does anyone know of a way to make this more robust?
Maybe I should be using something else to poke Excel?

Thanks

[This (somewhat broken) implementation can be easily ported to
ManagedXLL (just use ManagedXLL attributes to register the commands)]

---
using System;
using System.Collections.Concurrent;
using System.Runtime.InteropServices;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;
using ExcelDna.Integration;
using Application = Microsoft.Office.Interop.Excel.Application;


namespace ExcelDnaTest {
public class ExcelSynchronizationContext : SynchronizationContext {
private const string RunMacroName="ExcelSyncContext.Run";
private static readonly ConcurrentQueue<Action> workItems=new
ConcurrentQueue<Action>();
private static readonly TimeSpan
BackoffTime=TimeSpan.FromSeconds(1);

[ExcelCommand(Name=RunMacroName)]
public static void Run() {
while(true) {
Action workItem;
if(!workItems.TryDequeue(out workItem)) {
return;
}
workItem();
}
}

public override void Send(SendOrPostCallback d, object state) {
throw new NotImplementedException();
}

public override void Post(SendOrPostCallback d, object state) {
workItems.Enqueue(() => d(state));
Task.Factory.StartNew(() => {
while(true) {
try {
var application=(Application)ExcelDnaUtil.Application;
application.Run(RunMacroName);
break;
} catch(COMException e1) {
//TODO report retry
if(IsRetry(e1)) {
Thread.Sleep(BackoffTime);
continue;
}
//TODO report error
return;
} catch(Exception e2) {
//TODO report error
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;
}
}
}


public class Class1 {
[ExcelCommand(Name="RunAsyncTest")]
public static void Run() {
MessageBox.Show("AsyncCommandTest invoked");
Task.Factory.StartNew(() => {
Thread.Sleep(4000);
new ExcelSynchronizationContext().Post(delegate {
var application=(Application)ExcelDnaUtil.Application;
application.Cells[1, 1]=String.Format("Done at {0}",
DateTime.Now);
}, null);
});
}
}
}

[Govert van Drimmelen]

Hi,

Wow! That's pretty cool.

I get it to run fine.
For other who want to try, my .dna file has

<DnaLibrary Name="AsyncCommand Test" Language='C#'
RuntimeVersion="v4.0">
<Reference Name="System.Windows.Forms" />
<Reference Name="Microsoft.Office.Interop.Excel" />
<![CDATA[
.... your code with an extra bracket at the end ....
]]>
</DnaLibrary>


I don't see any problems trying it a few times on my machine (XP,
Excel 2007).
Is there a reliable way to get the crash?

I only have the following thoughts:
1. Are you sure the Application object is happy in the case where it
crashes? I know you say it crashes in Application.Run, but maybe you
can put a call to Application.Version or something friendly, just to
check the object is really set up. There is an extra path in
ExcelDnaUtil.Application which will certainly be a problem if called
from the async thread since it uses the C API.
2. Maybe you need to set up a real SynchronizationContext on Excel's
thread - not sure exactly how to do this though (Message Hook?, make a
windows forms control?).
According to this you can call SynchronizationContext.Current in a
WorkbookActivate handler: http://www.calvert.ch/geodesix/source/multitasking.htm
I don't really understand why this works.

It certainly interesting to me and I'll think about it more.
For now it just works fine here....

-Govert


On Jan 21, 7:47 pm, ngm_google_groups <naju.manche...@gmail.com>
wrote:

[Naju Mancheril]

Govert,

Thanks for your quick response. I have some more information on this problem. I *think* the AccessViolationException is caused by the particular actions I perform ON the scheduler and not by the scheduler implementation itself.

This raises the exception

        ExcelTask.StartNew(() => Excel.UpdateStatusBar("server started"));

This does not

        var application=(Application)Excel.Application;

        ExcelTask.StartNew(() => {

          application.StatusBar="server started";

        });

The first approach tries to set the status bar with the XLL interface (Excel is not happy with this!). The second method tries to use the COM/VSTO/Interop/whatever interface. I guess Excel is not prepared to receive XLL work from within an Application.Run(). If you like, I can send you the precise call stack of the crash when I run my addin in ManagedXLL. The stack shows

ManagedXLL code (raises error)

My C# code (the code listed above)

ManagedXLL code (plumbing to invoke my ExcelCommand)

ExcelTask.StartNew()

Callers of ExcelTask.StartNew() (C# methods run by my worker thread)

Thanks for your suggestions. The geodesix documentation is excellent.

I am currently shying away from the WorkbookActivate trick since it has more moving parts than this Application.Run() approach. To make it work, I think I would need to hook up some C# code which runs at startup. The hook would probably be an XLL hook. This code would then register for the COM event. Finally, when the event was fired, it would grab and stash the SynchronizationContext. The less I need to mix XLL and COM, the happier I will be ;-).

For now, the ExcelSynchronizationContext appears to work well. I would be happy to help you test/fix bugs/whatever in the future. Feel free to use/abuse/distribute the code. We find it extremely useful. Perhaps others would find it useful as well.

[Govert van Drimmelen]

Hi,

I've pinched some of your code already :-) to do an array resizing
function that seems to work OK:
http://excel-dna.net/2011/01/30/resizing-excel-udf-result-arrays/

I agree an approach that calls Application.Run to invoke an .xll macro
as you implement it now should work perfectly.

It's not clear to me in what context the access violation problem
occurs. To my understanding you are actually in an XLL macro context
where C API (XlCall) calls are fine.
Th code below works fine for me - setting with COM, with the C API and
setting the status bar all work fine.

Maybe your Excel.UpdateStatusBar is just bad...?

-Govert

[ExcelCommand(Name="RunAsyncTest2")]
public static void Run2() {
Task.Factory.StartNew(() => {
Thread.Sleep(2000);
var sc = new ExcelSynchronizationContext();
sc.Post(delegate {

var application=(Application)ExcelDnaUtil.Application;
application.Cells[1, 1]=String.Format("Done at {0}",
DateTime.Now);
}, null);

Thread.Sleep(2000);
sc.Post(delegate {
new ExcelReference(3,3,3,3).SetValue(string.Format("Done
at {0}", DateTime.Now));

// Here I update the status bar
XlCall.Excel(XlCall.xlcMessage, true, "Hello");
}, null);
Thread.Sleep(2000);
sc.Post(delegate {
XlCall.Excel(XlCall.xlcMessage, false);
}, null);
});

[kissinger chen]

Hi Govert,

I already read your async samples, I have the same problem so I find this thread.
My question is that I have many result, so I need to post the result to range, instead of the same cell.

For example, My result is 10 rows 10 columns. I need to post a 10 * 10 range. Is the async function to post 10 * 10 range?

Thanks.

Kissinger

[Govert van Drimmelen]

Hi Kissinger,

Excel makes it very hard to mix async and array formulas. I don't have a good plan for this at the moment - I'm open to suggestions on how we might implement this better.

My best suggestion is to split the async call and the array return into two parts:

* A single-cell async formula that internally keeps the result array in some kind of dictionary, and returns a handle to the calling cell.

* An array function that takes the handle, looks up the result internally and returns the array (possible with the ArrayResizing helping to turn the result into an array formula of the right size).

This is not perfect, as it is a bit tricky to use and you need to use an extra cell for the handle, but it should work well.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of kissinger chen [kche...@gmail.com]
Sent: 17 March 2016 02:28 AM
To: Excel-DNA
Subject: [ExcelDna] Re: Asynchronous ExcelCommands

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.