Curious about caching - What process does Excel-DNA use? what's the appDomain's lifecycle?

[andy-rsa]

I have some large data that my .NET code needs but I don't want to manage in Excel. Can I cache the data in .NET safely?

Q: as long as excel is up and running, is the process/appdomain is stable and constant?

My concern would be that the .net process/appdomain would be killed off and re-created between calls.

belief: if the process/appdomain is stable, I can create a dictionary and use that to cache results.

Background: I have UDF that return a lot of data, but I don't need to see it all in Excel. I do however, need all that data to make a subsequent call to other UDFs. I'd like to create a key for the data, store it in a cache at the process/appdomain level and only return the key to excel. I'd have a set of UDFs that all accept cache keys for the large sets of data (by convention).

Thank you

p.s. I did a summary search for similar topics but didn't find anything. MS Copilot gave an answer that indicated that Excel-DNA operates inside the Excel process space and that the appdomain for .NET would remain constant while the extension was installed, but i'd like to confirm before I get too deep.

[govert]

Hi @andy-rsa -

TL;DR For most uses you can assume a static variable is a good cache for the kind of data you have in mind.

The situation differs a bit according to whether you are targeting .NET Framework 4.x or .NET core (.NET 6+).

For .NET Framework, each add-in is isolated in its own AppDomain. A new AppDomain is created if you reload the add-in (e.g. with File -> Open or by unchecking and recheckling in the Add-Ins dialog). Otherwise, the AppDomain is stable and any types loaded and static data there will be retained for the duration of the Excel process.

For .NET core runtimes (.NET 6+) the runtime only supports a single AppDomain in the process. So, all add-ins that targeting .NET core are loaded into the same AppDomain. There is a partial isolation of the add-ins through the AssemblyLoadContext mechanism, though the extent of isolation provided is largely cooperative and depends also on what other libraries you load might be doing. Unloaded and reloading of add-ins is not supported well. But otherwise the lifetime of types and static variables are preserved.

One issue you'll run into with the caching and 'object handle' approach is how to clean the cache.
One approach is to use Excel's RTD mechanism to track whether the formula that creates the cached object and returns its handle is still 'alive'. There was a write-up of this approach here:
https://groups.google.com/g/exceldna/c/M6SY4b5tLQo/m/dAKVsWpsQ3IJ
and sample code here
https://www.codeproject.com/Articles/1097174/Interpolation-in-Excel-using-Excel-DNA

For the upcoming version 1.9 we are baking the object handle support into Excel-DNA itself.
I should be making the next pre-release version over the next few days. Then (after 1.9.0-alpha4) you should be able to do this:

    public class Calc
    {
        private double d1, d2;

        public Calc(double d1, double d2)
        {
            this.d1 = d1;
            this.d2 = d2;
        }

        public double Sum()
        {
            return d1 + d2;
        }
    }

  // ... In functions we can then return a 'handle' to objects of this type, or take the handle and use it.

        [ExcelFunction]
        [return: ExcelHandle]
        public static Calc MyCreateCalc(double d1, double d2)
        {
            return new Calc(d1, d2);
        }

        [ExcelFunction]
        public static double MyCalcSum([ExcelHandle] Calc c)
        {
            return c.Sum();
        }

Alternatively, you can mark the class with [ExcelHandle] to use it as a cached / object handle type.