Cancel ExcelAsyncUtil.Run()

[Jiri Pik]

Hi

 

Is it possible to cancel a ExcelAsyncUtil.Run() method while it’s being run? I.e. I am looking for some ID of the Run which I can then use to kill. 

Classic example is when the method takes too long, or the user removes the formula leading to this request.

[Govert van Drimmelen]

Hi Jiri,

For your first case, where the method takes too long, you need to handle it yourself (maybe set a timer, or a Delay task that fires after the timeout) and then return something to Excel as your function result when you time out. (What to return? Excel-DNA sets the result to #VALUE, but you could return some string that indicates the situation, say "!!! TIMEOUT").

Of course you can make a generic wrapper that handles the timeout - the point is you need no special support for Excel-DNA for this case.

Your second part is more tricky - where the user removes the formula from the cell, and you want to cancel a long-running operation.

There's no support for this in the ExcelAsyncUtil.Run(...) helper, but using the more powerful ExcelAsyncUtil.Observe(...) approach, you can get proper cancellation notification when the formula is removed.

I did some experiments on this in the Task support, that you can find in Distribution\Samples\Async\ExcelTaskUtil.cs.

There you'll find helpers like:

        public static object RunTask<TResult>(string callerFunctionName, object callerParameters, Func<CancellationToken, Task<TResult>> taskSource)

and

        public static object RunAsTask<TResult>(string callerFunctionName, object callerParameters, Func<CancellationToken, TResult> function)

which you can call with a function taking a CancellationToken (either your function returns a Task or is wrapped in a new Task).

When the user removes the formula from the cell, the CancellationToken will be signalled, which you can detect and abandon your work.

Your function might look like this:

// This is your implementation, taking a CancellationToken to get notifies if the formula is removed.

private static Task<string> FetchUrlAsync(string url, CancellationToken ct)

{

 ....

}

// This is the wrapper function, exported to Excel

[ExcelFunction(Description="Get data")]

public static object GetWebInfo(string url)

{

    return ExcelTaskUtil.RunTask("GetWebInfo", new object[] {url}, (ct) => FetchUrlAsync(url, ct));

}

There's a VB sample that includes cancellation in HttpClientVb.vb in the same directory.

----

So if you are using Task-based APIs, then there is a standard approach to cancelling from inside your function (resulting in a cancelled Task and #VALUE in Excel) and a standard approach to support cancelling from outside (take in a CancellationToken). The ExcelTaskUtil helpers match the semantics to the RTD feature in Excel as well as possible.

I'm busy trying to make some code generation that will automate creation of that wrapper function we need at the moment. With that, you should be able to have something like this:

[ExcelFunctionAsync(Description="Get data")]

public static Task<string> GetWebInfo(string url, CancellationToken ct)

{

 ....

}

and the rest will get generated and registered automatically

Cheers,

Govert

[Jiri Pik]

This is excellent. Thank you.

--
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 http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/groups/opt_out.