Cubicle Object Handler

Coordinator
Apr 26, 2013 at 7:39 PM
Discussions of the Object Handler
May 13, 2013 at 8:25 PM
Hi Tjaart,

This looks like a very interesting project!

I've only had a quick look at the code, but wanted to ask what you do to manage object lifetimes. It I create a new object in a cell, then clear the cell, do you have some kind of garbage collection process? One approach would be to use the Excel-DNA ExcelAsyncUtil / IObservable support which would notify you (via IDispose) when a data subscription (or object) is no longer used.

Cheers,
Govert
Coordinator
May 13, 2013 at 9:31 PM
Govert,

At the moment there is no automated lifetime management in the handler. Users can manually call cClear() to release all instances and Dispose will be called on all IDisposables but nothing more sophisticated than that.

It is a deficiency that I am aware of but leading up to this release I was preoccupied with testing the existing features.

Using the ExcelAsyncUtil, would that mean that each call that creates a handle would be an RTD function? It is definitely worth investigating. Some things I would like to investigate are:
1) How does the solution work with Excel's dependency tree, i.e. how would Excel treat recalculation of dependents?
2) What kind of performance impact would there be?
3) Can this be implemented to work with both local and remote handlers (given that remote handlers could have connections from multiple users)?

Alternatives I have thought of are:
1) A garbage collector that will scan open workbooks for handles references (probably unfeasible since it would be O(m.n) where m = # non-zero cells and n = # handles)
2) Each time the user creates an instance, to store the corresponding cell reference as meta-data within the handler. This can then be combined with a garbage collector to compare all the handles in the handler with their corresponding cell references. Probably more viable since it would be O(n).

Your thoughts would be welcome.


May 14, 2013 at 11:10 AM
Hi Tjaart,

The ExcelAsyncUtil-based code to manage handle lifetimes would go something like this:
lass ExcelObjectHandle : IExcelObservable //, IDisposable
    {
        readonly object _returnValue;
        readonly ExcelAction _onDispose;

        public ExcelObjectHandle(object returnValue, ExcelAction onDispose)
        {
            _returnValue = returnValue;
            _onDispose = onDispose;
        }

        // We expect to be called only once, ever
        public IDisposable Subscribe(IExcelObserver observer)
        {
            observer.OnNext(_returnValue);
            return Disposable.Create(() => _onDispose());
        }

        // public void Dispose()
        // {
        //    Alternative implementation could Dispose here, and return "this" from the subscription....?
        //}

        public static object CreateHandle(string callerFunctionName, object callerParameters, ExcelFunc getReturnValue, ExcelAction onDispose)
        {
            return ExcelAsyncUtil.Observe(callerFunctionName, callerParameters, () =>
                new ExcelObjectHandle(getReturnValue(), onDispose));
        }
    }
Which is used as:
    public static class MyClassFunctions
    {
        static Dictionary<ExcelReference, MyClass> _objectMap = new Dictionary<ExcelReference, MyClass>();
        static event EventHandler ObjectMapUpdated = delegate { };

        public static object MakeAnObject(string parameter)
        {
            var caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;
            if (caller == null) throw new InvalidOperationException("Function can only be called from a worksheet formula");

            MyClass instance;
            if (_deviceMap.TryGetValue(caller, out instance))
            {
                // We already have an instance in this cell
                // check whether the parameter points to the right object
                if (instance.Whatever != parameter)
                {
                    // Need to make a new one
                    RemoveObject(caller);
                }
            }

            if (instance == null)
            {
                instance = MakeNewInstance(parameters);
                _objectMap[caller] = instance;
                ObjectMapUpdated(null, EventArgs.Empty);
            }

            return ExcelObjectHandle.CreateHandle("MakeAnObject", parameter, () => "MyClass - " + parameters, () => RemoveObject(caller));
        }

        static void RemoveObject(ExcelReference caller)
        {
            MyClass instance;
            if (_objectMap.TryGetValue(caller, out instance))
            {
                instance.CleanUp();
                _objectMap.Remove(caller);
                ObjectMapUpdated(null, EventArgs.Empty);
            }
        }

 // More code to access the instance using from the map, and watch the event for changes.
        private static MyClass GetInstance(object instanceReference)
        {
            var isntanceRef = instanceReferenceas ExcelReference;
            if (isntanceRef == null) throw new ArgumentException("A cell reference must be entered");
            MyClass instance;
            if (!_objectMap.TryGetValue(isntanceRef , out isntance))
            {
                throw new ArgumentException("A valid instance reference must be entered");
            }
            return instance;
        }

         public static object GetInfo([ExcelArgument(AllowReference = true)] object instanceReference)
        {
              MyClass instance = GetInstance(instanceReference);
              return instance.GetInfo();
        }
Those are some fragments of some stuff I've tried.
What is happening here is that the handle is associated with the cell that call the constructor, and is retrieved by passing an ExcelReference that points to that cell. The constructor function is an RTD function (via the IExcelObservable wrapper and ExcelAsyncUtil.Observe. RTD gives us a reliable disconnect event when the formula is deleted or a parameter changes.

I've played with it a bit for my own use, and it seems to work OK, so it might be worth a further look.

-Govert
Coordinator
May 14, 2013 at 10:33 PM
Govert,

This looks really good. I'll see how I can integrate that into the existing framework.

One question, is an ExcelReference unique across worksheets and/or workbooks. Do we need to track those in addition to the reference to ensure uniqueness within an Excel instance?

Thanks.


May 15, 2013 at 9:40 AM
An ExcelReference should be unique in an Excel process instance - it contains a "SheetId" which is basically a pointer to the sheet. You should not persist the ExcelReference though - I think the SheetId would be different for a different run of Excel.

-Govert
Coordinator
May 19, 2013 at 9:54 PM
Govert,

I've been testing this and it seems to work very well. I've pushed updates to the source that adds two worksheet functions, "cMake" and "cPush" that are the RTD counterparts to "cInvoke" and "cSet" respectively.

It still needs further testing and might take a different form in the next release but you're welcome to try it out.

-Tjaart