Excel-DNA Dynamic Code from string

gistfile1.txt

<DnaLibrary Name="ExcelDna Test Dynamic Method" Language="C#">
<Reference Name="System.Windows.Forms" />
	<![CDATA[
	using System;
    using System.CodeDom.Compiler;
	using System.Collections.Generic;
    using System.IO;
    using System.Reflection;
    using System.Windows.Forms;
    using Microsoft.CSharp;
	using ExcelDna.Integration;

	public class Test : IExcelAddIn
	{
        // Just to test that we are loaded.
		public static double MyAdd(double d1, double d2)
		{
			return d1 + d2;
		}

		public void AutoOpen()
		{
            RegisterMyClass();
        }

		public void AutoClose()
		{
		}
        
        private void RegisterMyClass()
        {
            string code = 
                @"
                public class Script 
                { 
                    public static double MyDynamicAdd(double d1, double d2)
                    {
                        return d1 + d2;
                    }
                }";

        CompilerParameters cp = new CompilerParameters();
        cp.GenerateExecutable = false;
        cp.GenerateInMemory = true;
        cp.TreatWarningsAsErrors = false;
        cp.ReferencedAssemblies.Add("System.dll"); //, "System.Windows.Forms.dll", "ExcelDna.Integration.dll" );
        CSharpCodeProvider provider = new CSharpCodeProvider();
        CompilerResults cr = provider.CompileAssemblyFromSource(cp, new string[] { code });
        if (!cr.Errors.HasErrors)
        {
            Assembly asm = cr.CompiledAssembly;
            Type[] types = asm.GetTypes();
            List<MethodInfo> methods = new List<MethodInfo>();

            // Get list of MethodInfo's from assembly for each method with ExcelFunction attribute
            foreach (Type type in types)
            {
                foreach (MethodInfo info in type.GetMethods(BindingFlags.Public | BindingFlags.Static))
                {
//                    foreach (Attribute attr in info.GetCustomAttributes(false))
//                    {
//                        Type attribType = attr.GetType();
//                        if (attribType.FullName == "ExcelDna.Integration.ExcelFunctionAttribute" ||
//                            attribType.FullName == "ExcelDna.Integration.ExcelCommmandAttribute" )
//                        {
                            methods.Add(info);
//                        }
//                    }
                }
            }
            Integration.RegisterMethods(methods);
        }
        else
        {
            MessageBox.Show("Errors during compile!");
        }
    }
    }
]]>
</DnaLibrary>

Thanks so much for sharing the above! 😄

I'm aware this is a tad old, but would you know why it's not working when making RegisterMyClass static and calling it from a Ribbon-Button's click event?

Essentially, I'd like to register the dynamic code upon clicking a Ribbon-Button - but the following error is printed (it works when called from within AutoOpen; but only then):

Registration [Error] xlfRegister call failed for function or command: 'MyDynamicAdd'

Edit: I just started an SO question about this here and will report back if that yields a working solution. 😄

Edit 2: the solution is posted in the above SO question.

@govert, when attempting to implement the above in framework net6.0-windows, an exception is thrown stating "Operation is not supported on the platform" when hitting line 48 ("CompilerResults cr = provider.CompileAssemblyFromSource(cp, new string[] { code });"). Any thoughts on how to implement it 12 years later (I have been attempting for the past two days with no progress)?

@AtwoodTM Are you making an Excel add-in, or just using the snippet as an example of how to compile code at runtime?

In general, for C# and .NET under .NET 6 you'd need to use the Roslyn compiler infrastructure to compile code. So you need to reference the right NuGet package(s) and then instead of CSharpCodeProvider provider = new CSharpCodeProvider(); (meaing System.CodeDom.Compiler.CSharpCompiler) you need something like CodeDomCompiler compiler = new Microsoft.CodeDom.Providers.DotNetCompilerPlatform.CSharpCodeProvider();`

In the Excel-DNA context, if that's where you're coming from, we don't support the code-in-dna-file when running under .NET 6.

Hi @govert, thanks for the reply. I am just attempting to compile code at runtime. Had no issues with a Rosyln dynamic compilation when testing separately following Rick Strahl's blog here: https://weblog.west-wind.com/posts/2022/Jun/07/Runtime-CSharp-Code-Compilation-Revisited-for-Roslyn

However, when attempting to integrate with ExcelDna, I am getting "not supported with platform" errors again.

The goal of this proof of concept is to allow users to create their own custom functions in F# or C# and import them into Excel.

Hi @AtwoodTM - could you maybe post a test project to GitHub that I can try (and maybe post as an example for the future)?

Hi @govert, I was able to get the functions dynamically compiling, but am now having problems getting ExcelDna.Integration to be recognized within the code being compiled in order to use the ExcelFunction and ExcelArgument attributes. This seems to be due to ExcelDna.Integration not being a dll artifact at original compilation. I have posted a question related to this issue along with sample code on Stack Overflow here.

For anyone reading this thread in the future looking to dynamically compile C# functions, the first code sample works. The second sample testing ExcelFunction and ExcelArgument attributes does not work.

Hi @govert, I have uploaded the example code to a public GitHub repo. You will see there are two methods... RegisterFunctionsWorks and RegisterFunctionsDoesNotWork with the latter highlighting how ExcelFunctionAttribute and ExcelArgumentAttribute do not work with dynamically compiled functions.

https://github.com/AtwoodTM/TestExcelDna

The problem is that the dynamically compile assembly is not being loaded into the add-in's AssemblyLoadContext (where the type resolution works correctly). It looks like the way to fix this is to set the Westwind.Scripting.CSharpScriptExecution' object's AlternateAssemblyLoadContext` property to the ALC of the add-in, like this:

            var script = new CSharpScriptExecution() { SaveGeneratedCode = true };
            script.AlternateAssemblyLoadContext = AssemblyLoadContext.GetLoadContext(this.GetType().Assembly);
            script.AddDefaultReferencesAndNamespaces();

This seems to be good enough for your examples, and you should do it even in the case where you are not referencing ExcelDna.Integration from the dynamic compiled code, since you really want the new assembly and its dependencies loaded into the right ALC where possible.

In some cases the loading of other dependencies happens only when the code runs, and that might need some places where you enter a contextual reflection scope. So you might sometimes need code like this:

using (var ctx = System.Runtime.Loader.AssemblyLoadContext.EnterContextualReflection(this.GetType().Assembly))
{
   // ... run code that loads extra assemblies here
}

Unfortunately, there is no way to make the type loading works as well under .NET 6+ as it used to be under .NET Framework where we had AppDomains to isolate the add-ins.