returning object handles using c#

[Joe Schmoe]

Hi all,

I'm trying to figure out how to manage the lifetime of objects that are returned back to Excel from my UDF.  I use a dictionary as suggested in this approach : https://groups.google.com/forum/#!searchin/exceldna/object$20handle/exceldna/zGPUrQ5wPqs/Jm3NW_40DfcJ

 but was looking for some way to make sure the object gets removed once the cell is deleted.  One post mentioned using RTD in F# to do this but I couldn't find an example using C# or at least a high level guide on how to set something like this up.  Does anyone have experience doing something like this or know conceptually how to use RTD to manage the lifetime of objects returned by a UDF?

Thanks!

[Joe Schmoe]

I saw the Worksheet_Change event and thought perhaps this could be used whenever a user deletes or changes a cell a C# method will check the dictionary to see if the object exists and if the action is to delete it'll remove it from the list.  i wonder if this would work...?

[Govert van Drimmelen]

Hi Joe,

I've put together a first attempt to implementing reference tracked object handles.

You'll find a helper class, and then some sample functions that create and use an object handle.

From here, I think there are many ways to improve further:

- Decide whether strings handles should be used like here, or rather use cell references as the 'handle'. This would allow you to return any value back to the cell.

- Deal with objects that might update - maybe create a new handle for every version, or update otherwise...

- Allow live updating objects, so that the 'properties' might be live in the sheet.

Let us know whether you can get it to work, and how this fits with your requirements.

Regards,

Govert

// -----------------------------------------------------------------------------------------------------------------

// Helper class

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using ExcelDna.Integration;

namespace ExcelDna.Utilities

{

    // NOTE: Objects made here should be immutable - i.e. once you have returned a handle for an object, do not ever change the object.

    //       It would be easy to make some 'setter functions' but hard to get the updates to flow right in the spreadsheet without more plumbing.

    public class ObjectHandler

    {

        Dictionary<string, object> _objects = new Dictionary<string, object>();

        long handleIndex = 1;

        // The combination of handleType and parameters will be used to uniquely identify the RTD topic

        // So createObject will only be called when a new handleType/parameters combination is used.

        public object GetHandle(string handleType, object[] parameters, Func<object> createObject)

        {

            return ExcelAsyncUtil.Observe(handleType, parameters, () =>

                {

                    object value = createObject();

                    string handle = handleType + ":" + handleIndex++;

                    _objects[handle] = value;

                    return new HandleObservable(this, handle);

                });

        }

        public bool TryGetObject(string handle, out object value)

        {

            return _objects.TryGetValue(handle, out value);

        }

        void Remove(string handle)

        {

            object value;

            if (TryGetObject(handle, out value))

            {

                _objects.Remove(handle);

                var disp = value as IDisposable;

                if (disp != null)

                {

                    disp.Dispose();

                }

            }

        }

        class HandleObservable : IExcelObservable, IDisposable

        {

            ObjectHandler _handler;

            string _handle;

            IExcelObserver _observer;

            public HandleObservable(ObjectHandler handler, string handle)

            {

                _handler = handler;

                _handle = handle;

            }

            public IDisposable Subscribe(IExcelObserver observer)

            {

                // We know this will only be called once, so we take some adventurous shortcuts (like returning 'this')

                _observer = observer;

                _observer.OnNext(_handle);

                return this;

            }

            public void Dispose()

            {

                _handler.Remove(_handle);

            }

        }

    }

}

// -----------------------------------------------------------------------------------------------------------------

// Sample Usage

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using ExcelDna.Integration;

using ExcelDna.Utilities;

namespace ObjectHandles

{

    public static class HandleTestFunctions

    {

        // Alternative is to make everything in ObjectHandler stat

        static ObjectHandler _objectHandler = new ObjectHandler();

        public static object TestHandleSample()

        {

            return "Hello from ObjectHandles!";

        }

        // A sample function that returns a handle 

        // (either the handle for the existing object of the given type and parameters, or a new one).

        public static object MatrixCreate(int rows, int cols)

        {

            // The first argument to GetHandle will identify the handle type, and will also be used in the created handle itself.

            // We use the input parameters ('rows' and 'columns' here) in two different ways:

            // 1. to distinguish different calls to the ObjectHandler.GetHandle, which accordingly decides whether to make a new handle or not

            // 2. as captured locals in the delegate passed for the createObject parameter, used when a new object must be made.

            return _objectHandler.GetHandle("Matrix", new object[] { rows, cols }, () => MatrixCreateImpl(rows, cols));

        }

        // A sample function that takes a handle, and uses the object behind it.

        public static object MatrixEntry(string matrixHandle, int row, int col)

        {

            object value;

            if (_objectHandler.TryGetObject(matrixHandle, out value))

            {

                double[,] matrix = (double[,])value;

                return matrix[row, col];

            }

            // No object for the handle ...

            return "!!! INVALID HANDLE";

        }

        // Creates a rows * cols matrix with entries like 1.02 for row 1, col 2

        private static object MatrixCreateImpl(int rows, int cols)

        {

            double[,] matrix = new double[rows, cols];

            for (int i = 0; i < rows; i++)

            {

                for (int j = 0; j < cols; j++)

                {

                    matrix[i, j] = i + j / 100.0;

                }

            }

            return matrix;

        }

    }

}

//--------------------------------------------------

In Excel:

A1: MatrixCreate(4,5)    ==> Matrix:1

A2: MatrixEntry($A$1, 2, 3)      ==> 2,03

[Alex Chirokov]

Hi

I came across this when looking for a way to implement object handels in excel. The code in example works well, but i was having some issues modifying it , because i dont fully how ExcelAsyncUtil.Observe works. Observe needs method name and list of parameters. This basically means that when i construct object of the same type with the same arguments, i get the existing handle and new object is not constructed. This is useful to have for example for data base connections. So instead of matrix size I passed guid as a Observe parameter in MatrixCreate, this did not work because excel started to call MatrixCreate repeatedly (i.e. excel was removing existing objects and creating new ones in the loop). This is a bit puzzling. Can anyone provide a bit more color on this ?
 
thank you

Alex-