Providing ReferencedAssemblies to CSharpCodeProvider

[Herbert West]

Greetings,

Here are a few questions related to using CSharpCodeProvider to link dynamically-compiled code into a running exceldna session.

1. What is the proper way to provide a reference to the currently running assembly when it is one of my exceldna extension assemblies?
    Using System.Reflection.Assembly.GetExecutingAssembly returns a Reflection.Assembly object with an empty string for its Location
    (because it was loaded using Assembly.Load, so this is no surprise).

    The ManifestModule and FullName properties correctly reflect the name of my compiled assembly (in this case "edna1.dll") but
    strangely the CodeBase property has a reference to mscorlib.dll (any idea why?)

    If I execute a CompilerParameters.ReferencedAssemblies.Add("edna1.dll") there are no errors, but it appears that my edna1.dll assembly
    gets loaded a second time.  This happens whether or not I "pack" the "edna1.dll" into the dna file or not.

2. This question is very similar to the above one, but doesn't focus on all the things that seem to be going wrong.
    What assemblies (and possibly also compiler options) do I need to add as references in order to grant the new code access to
    ExcelDna.Integration features -- for example so that I can load new excel macros and functions.

Let me know if you would like an example testcase for #1.

Thanks for the assistance,

Herb

[Govert van Drimmelen]

Hi Herb,

I'm not sure it's easy to do what you are trying - to dynamically compile an assembly that references your assembly or the Excel-DNA assemblies.

In the latest source check-ins for Excel-DNA, I've made it possible to register delegates that are created at runtime. (In the past you could only register static methods on a class). Delegates give us significant flexibility in making wrappers for functions, or putting together code at runtime. 

Could you perhaps explain what you are trying to achieve - maybe I can suggest another approach that would work too?

Regards,

Govert

[Herbert West]

Thanks for responding, Govert.

I have made some progress on this.  I was able to expose my own assembly, edna1.dll, to dynamically-compiled

code.  By by this, i mean a new source module that is added at some point AFTER initialization (not in an autoOpen callback, 

for example).  I had to  do doing the following in my .dna file:

  <ExternalLibrary Path="edna1.dll" LoadFromBytes="false" Pack="false" />

i was unable to get this to work with either Pack="true" or LoadFromBytes="true".  I can't remember the exact symptoms

now, but I believe that I could not resolve the assembly if I packed it, and I had multiple loadings if i set LoadFromBytes.

Unfortunately, this was not enough to allow me to reference items from ExcelDna.Integration.dll.  I was able to make my code

work by placing a copy of ExcelDna.Integration.dll in my addin directory.  I know that's not what I am supposed to do, but otherwise

I cannot resolve the assembly.  I looked at the source code for exceldna and did see that ExcelDna.Integration is handled

specially by the assembly resolution mechanism -- but I am not familiar enough with the overall architecture to figure out why

this isn't sufficient.  

Perhaps its related to my CompilerOptions settings, since all of that runs in a separate process for the c# compiler

and doesn't see your custom resolver?  If so, is this also the problem with edna1.dll and in indeed any "packed" assembly?

I'm not sure if having this copy of ExcelDna.Integration.dll will cause other problems later.  I am comfortable guaranteeing that

it is exactly the same version as the one contained inside the main dll; I am just worried that there are some global structures

that will be duplicated by doing this.

Below are the relevant portions off my working F# code.  Here, when the "Compiler 2" menu item is selected, the F# function compiles a string containing

c# code and then ExcelIntegration.RegisterMethods successfully adds a new menu item called "Dynamic!" This new menu item executes the c# code,

which references f# module MyFunctions (the same module that contains all the code below).  This new menu item

correctly executes MyFunctions.dolog (not included in the source here), which writes to a global log window (thus proving it has linked to the

right edna1.dll assembly and not a new copy).

I greatly prefer having everything packed into a single file.  Perhaps you can explain why this doesn't work, and if it is or is not possible.

Can you point out any problems that can occur with using a copy of ExcelDna.Integration.dll?

Thank you for looking at this!

Herb

CODE SNIPPET FOLLOWS:

let wuggascript3 = """ 

  using System;

  using ExcelDna.Integration;

  namespace wugga {

    public class wuggaclass {

      [ExcelCommand(MenuName="My AddIn", MenuText="Dynamic!")]

      public static void Main() {

        Console.WriteLine("hello");

        MyFunctions.dolog("oh boy");

      }

    }

  }

"""

[<ExcelCommand(MenuName="My AddIn", MenuText="Compiler 2")>]

let cpdemo2 () =

  let compile snippet =

    use provider = new CSharpCodeProvider()

    let options = CompilerParameters(GenerateInMemory=true)

    options.ReferencedAssemblies.Add("edna1.dll") |> ignore

    options.ReferencedAssemblies.Add("ExcelDna.Integration.dll") |> ignore

    let ea=System.Reflection.Assembly.GetExecutingAssembly()

    options.CompilerOptions <- @" /lib:C:\Data\exceldna\edna1\edna1\bin\Debug"

    provider.CompileAssemblyFromSource(options, snippet)

  let cr = [|wuggascript3|] |> compile

  if cr.Errors.Count>0 then

    for er in cr.Errors do

      dolog (sprintf "%d:%d %s" er.Line er.Column er.ErrorText)

  else

    dolog ("successfully compiled: "+cr.CompiledAssembly.FullName)

    let asm = cr.CompiledAssembly

    let types = asm.GetTypes()

    let methods  = new List<MethodInfo>()

    for ty in types do

      for info in ty.GetMethods(BindingFlags.Public ||| BindingFlags.Static) do

        for attr in info.GetCustomAttributes(false) do

          let attribty = attr.GetType()

          if (attribty.FullName = "ExcelDna.Integration.ExcelFunctionAttribute" ||

              attribty.FullName = "ExcelDna.Integration.ExcelCommandAttribute") then methods.Add(info)

    ExcelIntegration.RegisterMethods(methods)

[Herbert West]

Govert,

This newer thread raises some of the same issues:

  https://groups.google.com/forum/#!topic/exceldna/q8gw06D5vQs

I am not sure if that poster can work around his problems by using lambda functions and the JITter, as you suggested.  I definitely need more--or--less

arbitrary access to the workbook.  Perhaps some additional delegate support would help.

Regards,

Herb

[Govert van Drimmelen]

Hi Herbert,

Assemblies to be referenced by the compiler have to be actual files on disk. The compiler is invoked as an external executable, and does not run in the context of the Excel process or the add-in's AppDomain. So the in-memory assembly resolution used by Excel-DNA at runtime, whereby the packed assemblies are extracted in memory to resolve references, is not a mechanism that is useful in your case. As you've noticed, you get further when your assemblies are not packed. I'm not completely sure why problems arise when an extra unpacked copy of ExcelDna.Integration.dll is around, but it seems to be loaded twice sometimes, which causes problems.

If you have to run the compiler and want a packed .xll, I suggest you make a temp directory, and extract the assemblies to be referenced into the temp directory at runtime. While the extraction code in ExcelDna.Integration is internal, you can call it via reflection: 

on ExcelDnaIntegration you need to call 

    internal static byte[] GetAssemblyBytes(string assemblyName)

then write to a temp file that you clean up again afterwards.

All the code you'd want for that is in ExcelDna.Integration\Project.cs, where exactly this is done.

In your other message you say:

"I definitely need more--or--less arbitrary access to the workbook.  Perhaps some additional delegate support would help."

Neither of these require the on-the-fly compiling. There is additional support for registering delegates in the latest source version, and access to the workbook should not be an issue (I'm not sure how you relate this). 

Of course if you want to make a snippet compiler that really takes C# or VB.NET source from the user at runtime, compiles and runs that, then you'd need to invoke the compiler (or perhaps use the Roslyn or Mono.CSharp libraries).

But for generating functions on the fly, there should be no performance or functional advantage.

Can you explain a bit more about the use case is for this functionality?

That might help me to understand whether that's a feature we can add to the standard Excel-DNA - it should be easy since all the code is there already. We'd just have to decide on a stable API call. Something like:

ExcelIntegration.RegisterProject(Project proj) and make a public constructor for Project, or even

ExcelIntegration.RegisterFromSource(string language, string code).

Regards,

Govert

[Herbert West]

On Sunday, October 27, 2013 12:41:28 AM UTC-7, Govert van Drimmelen wrote:

Assemblies to be referenced by the compiler have to be actual files on disk. The compiler is invoked as an external executable, and does not run in the context of the Excel process or the add-in's AppDomain. So the in-memory assembly resolution used by Excel-DNA at runtime, whereby the packed assemblies are extracted in memory to resolve references, is not a mechanism that is useful in your case. As you've noticed, you get further when your assemblies are not packed.

Thank you, Govert.  It is completely clear why packed assemblies cannot be seen by the compiler when
it is a separate process.  Conversely, it is clear why an unpacked DLL works and why I should not have two copies
(one packed and one unpacked) in order to avoid double-loading.

I'm not completely sure why problems arise when an extra unpacked copy of ExcelDna.Integration.dll is around, but it seems to be loaded twice sometimes, which causes problems.

I believe what I am doing guarantees that it will be loaded twice, so I expect there will be problems.
Would it be possible (I mean not-too-difficult) for me to separate ExcelDna.Integration.dll from the packed assembly
so that both the exceldna loader and my own code look for it in the same unpacked location? 

 

In your other message you say:

"I definitely need more--or--less arbitrary access to the workbook.  Perhaps some additional delegate support would help."

Neither of these require the on-the-fly compiling. There is additional support for registering delegates in the latest source version, and access to the workbook should not be an issue (I'm not sure how you relate this). 

Yes, I was not clear. I meant that I need the arbitrary access on top of the on-the-fly compiling.  So I'm not interested
in a subset of capability where only certain types of code (like UDFs) can be registered at runtime.  Ok, even now I'm
not really saying what I want:  I want to us the full set of conveniences that exceldna offers, but I want to do it at
some unspecified time AFTER startup ;)
 

Of course if you want to make a snippet compiler that really takes C# or VB.NET source from the user at runtime, compiles and runs that, then you'd need to invoke the compiler (or perhaps use the Roslyn or Mono.CSharp libraries).

But for generating functions on the fly, there should be no performance or functional advantage.

Can you explain a bit more about the use case is for this functionality?

That might help me to understand whether that's a feature we can add to the standard Excel-DNA - it should be easy since all the code is there already. We'd just have to decide on a stable API call. Something like:

ExcelIntegration.RegisterProject(Project proj) and make a public constructor for Project, or even

ExcelIntegration.RegisterFromSource(string language, string code).

 I'm really just experimenting with bits and pieces of add-on functionality, figuring out what can and cannot be done.
This piece fits into something like a replacement for VBA, which allows you to add functions incrementally at
runtime without restarting.  (Yes, I know that this requires an effective restart of the VBA portion of the project,
which is very much like a reloading of a changed/recompiled XLL.  I suppose that's a viable option for me as well;
a bigger problem is that this code is shared by all workbooks that load the xll!).

Thanks for the clarifications,

Herb

[Govert van Drimmelen]

Hi Herbert,

Maybe you can have a separate add-in (.xll) that your main add-in loads and unloads. It might have some packed assemblies of your own inside, and then a Source tag.

So you have MyAddIn.dna:

<DnaLibrary RuntimeVersion="v4.0" >

  <Project Language="C#">

    <Reference Path="MyUtils.dll" Pack="true/>

    <SourceItem Path="TheSource.cs" />

  </Project>

</DnaLibrary>

Then have a MyAddIn.xll next to the .dna file.

When you want to recompile, you write out the source to the file "TheSource.cs" and reload the MyAddIn.xll.

So you have a normal add-in which was compiled on-the-fly.

-Govert

[Herbert West]

On Sunday, October 27, 2013 11:37:30 PM UTC-7, Govert van Drimmelen wrote:

Maybe you can have a separate add-in (.xll) that your main add-in loads and unloads. It might have some packed assemblies of your own inside, and then a Source tag.

<DnaLibrary RuntimeVersion="v4.0" >

  <Project Language="C#">

    <Reference Path="MyUtils.dll" Pack="true/>

    <SourceItem Path="TheSource.cs" />

  </Project>

</DnaLibrary>

Then have a MyAddIn.xll next to the .dna file.

When you want to recompile, you write out the source to the file "TheSource.cs" and reload the MyAddIn.xll.

So you have a normal add-in which was compiled on-the-fly.

Brilliant.  I think this is a good approach for a lot of practical reasons.  I really do need a separate xll because things
need to remain sane in the "original" or "base" xll in case something goes wrong with the compilation.  Doing a
reload nicely tidies up after the dynamic xll without messing up any other infrastructure that doesn't need to change.
And I like having the source be available at some well-defined place on disc for the debugger.

Great suggestion. Thanks.
Herb