I have recently worked with ExcelDNA for some sample projects to learn more about the technology. One of the features I am looking to implement is for users to create their own functions in C#, VB, or F# within Excel. The original path followed was to utilize the example code posted here by ExcelDNA's author. However, the code was for the pre-Roslyn compiler. There is another Stack Overflow post here that was also helpful in review.
For post-Roslyn compiler, I instead leveraged Rick Strahl's Westwind.Scripting library for compiling functions dynamically, and it worked great for compiling functions and allowing for registration in Excel. My code for that is here:
using ExcelDna.Integration;
using ExcelDna.IntelliSense;
using System.Reflection;
using System.Runtime.InteropServices;
using Westwind.Scripting;
namespace TestExcelDna
{
[ComVisible(false)]
public class AddIn : IExcelAddIn
{
public void AutoOpen()
{
try
{
// Rosyln warmup
// at app startup - runs a background task, but don't await
_ = RoslynLifetimeManager.WarmupRoslyn();
IntelliSenseServer.Install();
RegisterFunctions();
IntelliSenseServer.Refresh();
}
catch (Exception ex)
{
var error = ex.StackTrace;
Console.WriteLine(error);
}
}
public void AutoClose()
{
IntelliSenseServer.Uninstall();
}
public void RegisterFunctions()
{
var script = new CSharpScriptExecution() { SaveGeneratedCode = true };
script.AddDefaultReferencesAndNamespaces();
var code = $@"
using System;
namespace MyApp
{{
public class Math
{{
public Math() {{}}
public static string TestAdd(int num1, int num2)
{{
// string templates
var result = num1 + "" + "" + num2 + "" = "" + (num1 + num2);
Console.WriteLine(result);
return result;
}}
public static string TestMultiply(int num1, int num2)
{{
// string templates
var result = $""{{num1}} * {{num2}} = {{ num1 * num2 }}"";
Console.WriteLine(result);
result = $""Take two: {{ result ?? ""No Result"" }}"";
Console.WriteLine(result);
return result;
}}
}}
}}";
// need dynamic since current app doesn't know about type
dynamic math = script.CompileClass(code);
Console.WriteLine(script.GeneratedClassCodeWithLineNumbers);
// Grabbing assembly for below registration
dynamic mathClass = script.CompileClassToType(code);
var assembly = mathClass.Assembly;
if (!script.Error)
{
Assembly asm = assembly;
Type[] types = asm.GetTypes();
List<MethodInfo> methods = new();
// 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))
{
methods.Add(info);
}
}
Integration.RegisterMethods(methods);
}
else
{
//MessageBox.Show("Errors during compile!");
}
}
}
}
The above code works with registering the functions, but we don't get the goodness in the function wizard that is supplied from the ExcelFunction and ExcelArgument attributes. Therefore, I would like to utilize these attributes from the ExcelDna.Integration library.
However, when added to the code to be compiled, the compiler cannot find the ExcelDna.Integration library. The issue seems to be that an ExcelDna.Integration.dll is not included among the published artifacts. The adjustments from the code above for testing (which is not working) is here:
using ExcelDna.Integration;
using ExcelDna.IntelliSense;
using System.Reflection;
using System.Runtime.InteropServices;
using Westwind.Scripting;
namespace TestExcelDna
{
[ComVisible(false)]
public class AddIn : IExcelAddIn
{
public void AutoOpen()
{
try
{
// Rosyln warmup
// at app startup - runs a background task, but don't await
_ = RoslynLifetimeManager.WarmupRoslyn();
IntelliSenseServer.Install();
RegisterFunctions();
IntelliSenseServer.Refresh();
}
catch (Exception ex)
{
var error = ex.StackTrace;
Console.WriteLine(error);
}
}
public void AutoClose()
{
IntelliSenseServer.Uninstall();
}
public void RegisterFunctions()
{
var script = new CSharpScriptExecution() { SaveGeneratedCode = true };
script.AddDefaultReferencesAndNamespaces();
script.AddAssembly("ExcelDna.Integration.dll");
var code = $@"
using System;
using ExcelDna.Integration;
namespace MyApp
{{
public class Math
{{
public Math() {{}}
[ExcelFunction(Name = ""TestAdd"", Description = ""Returns 'TestAdd'"")]
public static string TestAdd(int num1, int num2)
{{
// string templates
var result = num1 + "" + "" + num2 + "" = "" + (num1 + num2);
Console.WriteLine(result);
return result;
}}
[ExcelFunction(Name = ""TestMultiply"", Description = ""Returns 'TestMultiply'"")]
public static string TestMultiply(int num1, int num2)
{{
// string templates
var result = $""{{num1}} * {{num2}} = {{ num1 * num2 }}"";
Console.WriteLine(result);
result = $""Take two: {{ result ?? ""No Result"" }}"";
Console.WriteLine(result);
return result;
}}
}}
}}";
// need dynamic since current app doesn't know about type
dynamic math = script.CompileClass(code);
Console.WriteLine(script.GeneratedClassCodeWithLineNumbers);
// Grabbing assembly for below registration
dynamic mathClass = script.CompileClassToType(code);
var assembly = mathClass.Assembly;
if (!script.Error)
{
Assembly asm = assembly;
Type[] types = asm.GetTypes();
List<MethodInfo> methods = new();
// 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))
{
methods.Add(info);
}
}
Integration.RegisterMethods(methods);
}
else
{
//MessageBox.Show("Errors during compile!");
}
}
}
}
Does anyone know how to utilize ExcelFunction and ExcelArgument attributes in dynamically compiled functions with ExcelDNA?
EDIT August 21, 2023
I have uploaded to GitHub a sample project repo which has a RegisterFunctionsWorks and RegisterFunctionsDoNotWork for those that want to play around with the code. The repo is found here.
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.