Excel Rx question - Detect workbook close.

38 views
Skip to first unread message

Kedar Kulkarni

unread,
Apr 14, 2022, 12:36:29 PMApr 14
to Excel-DNA
Hi All,
I am looking for way to determine if a RTD function was deleted from a cell by user or if the excel workbook that contained RTD functions is closed.

Our application tries to keep hold of the range as we would do some postprocessing on those cells (which has RTD functions). If user closes the workbook or deletes the cell formula, we need a way to unsubscribe from those future events and release range references.

Inspired from the example - 

I am taking just 10 values for the clock and if before 10 seconds, I delete the cell formula / close workbook, I can see that the onNext is not called but is there a way to notify my application that the RTD function has already called dispose(?) before onCompleted is called (which would be called after 10 seconds if user does not delete the cell / close workbook) and we can cleanup any resources here as onCompleted would never get a chance. 

static IObservable<string> GetObservableClock()
        {
            return Observable.Timer(dueTime: TimeSpan.Zero, period: TimeSpan.FromSeconds(1)).Take(10)  // small change to make this a finite stream
                             .Select(_ => DateTime.Now.ToString("HH:mm:ss"));
        }
 

Or is my expectation that my application should even be notified by this construct is wrong as I am planning to do something in main thread in after calculate event? 
I don't think that using some kind of hacky way of workbookAfterClose event is appropriate here as Excel DNA already knows that the function is no more alive.

thanks,
Kedar

Govert van Drimmelen

unread,
Apr 14, 2022, 2:49:48 PMApr 14
to exce...@googlegroups.com

Hi Kedar,

 

In general the RTD topics are notified when they are disconnected, e.g. when the user deletes the formula in the cell.

The Excel-DNA IObservable implementation respects and implements this, but the story is not trivial.

The IObservable that is returned from a function like the GetObservableClock() has a Subscribe method, which must return an object that implements IDisposable. When the underlying RTD topic is disconnected, this IDispose.Dispose() will be called, giving you a chance to clean up.

 

Now when using the Observable.Timer(…) helper, you don’t get an easy way to see this behaviour unless you wrap it up a bit.

 

Maybe something like the code below (where I use a bit of a hack to simplify the code, because I know that Subscribe will only be called once, so the object itself can the ‘IDisposable’ I return from the Subscribe call).

I’ve not checked whether the code compiles or runs correctly, so probably leaving you with some debugging to do…

 

-Govert

 

 

static IObservable<string> GetObservableClock()
{

        return new MyTimer();

}

 

public class MyTimer : IObservable<string>, IDisposable

{

    IObservable<string> _innerObservable;

    IDisposable _innerDisposable;

   

    public MyTimer()

    {

        _innerObservable =

            Observable.Timer(dueTime: TimeSpan.Zero, period: TimeSpan.FromSeconds(1)).Take(10)  // small change to make this a finite stream
                             .Select(_ => DateTime.Now.ToString("HH:mm:ss"));

    }

 

    public IDisposable Subscribe(IObserver observer)

    {

        _innerDisposable = _innerObservable.Subscribe(observer);

        return this;

    }

 

    public void Dispose()

    {

        // This will be called when the topic is disconnected

 

        _innerDisposable.Dispose();

 

        // Do any other cleanup here….

        Debug.Write("Dispose called for MyTimer");

--
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 view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/c6189558-b7a9-4bd3-8347-f77d41cf1d24n%40googlegroups.com.

Kedar Kulkarni

unread,
Apr 20, 2022, 2:22:17 PMApr 20
to Excel-DNA
thanks a lot for the pointers.. I am afraid it still did not work for me in certain scenarios. I will try to create an offline example first when get a chance and if successful, would create a new post.

It seems that my current rx is introducing a bit of backpressure as when I have < 10-20 functions, it works well ie it calls dispose before closing workbook. However, when I have 300+ RTD functions, the workbook is already closed and I still see onNext is being fired before it disposing the subscription. In debug mode, the delay is noticeable a bit but not in release mode hence I am keeping this on the backburner for the moment. I will continue to investigate when free.

thanks




Craig Crevola

unread,
May 9, 2022, 8:17:23 PMMay 9
to exce...@googlegroups.com

Hi Kedar

 

You can use Cancellation tokens and chaining of tokens to register your cleanup events. This can be used as a circuit breaker to ensure all sources are cleaned up. I have used this technique before with RTD and sockets connections to ensure proper cleanup and disposal. Here is a link to some documentation that you may find helpful:

 

https://docs.microsoft.com/en-us/dotnet/standard/threading/canceling-threads-cooperatively

https://docs.microsoft.com/en-us/dotnet/standard/threading/how-to-register-callbacks-for-cancellation-requests

 

 

I don’t use reactive objects with RTD, however, how I have handled it has been to create a top level token source, create a token and pass it to the dependant or chained process (in my case a socket subscription manager), from there register a callback to invoke when the token is cancelled, which cleans up the references etc. Then to notify the stop action call Cancel on the top level source. This will send the notification down to the dependants and your cleanup will be called, the same way a cancellation token on async events is used.

 

Hope that helps

 

Craig.

Kedar Kulkarni

unread,
May 12, 2022, 4:57:17 PMMay 12
to Excel-DNA
thanks Craig for the links and explanation, we are already using cancellation tokens. We have an existing framework that does the heavy lifting, we have partially minimized the impact of the issue already. 

It's more of a timing issue IMO as the cancellation is triggered in dispose() after the file is closed in the main UI thread by the garbage collector but till the time all rx functions get canceled, we have a bit of back pressure built. It is able to successfully cancel few actions but not all as excel is already processing actions in the queue after the workbook is closed. (The actions are against the main thread to change the formatting of the 'caller' cell.) In the debug mode the delay is noticeable but in release mode, it is performing better due to code optimization. All errors are handled so nothing is breaking as such. I just wanted to clear the queue of actions on the main thread before they execute to avoid it reaching an exception catch block.

We have now successfully combined the cells at the worksheet level using application.Union and we are executing less number of actions than before. If first action fails for the worksheet, we skip all actions against that worksheet so a lesser number of errors are encountered. It's now scalable due to code optimization - still, a few errors are thrown but not a priority as it is working. 

thanks...
Reply all
Reply to author
Forward
0 new messages