Incompatibility with Roslyn / .Net 4.6.1?

[Bogey]

Hi,

I was playing around with Roslyn / its C# Scripting API a little today. I think it has a lot of potentially very interesting use cases for Excel / Excel-DNA.
However I'm struggling to get it up and running as it seems to fail finding DLLs. The easiest might just be to look at a brief example:

Create a new project that targets .NET Framework 4.6.1. Install the following packages:

Install-Package Microsoft.CodeAnalysis.CSharp.Scripting
Install-Package Excel-DNA

and the following code will do:

using Microsoft.CodeAnalysis.CSharp.Scripting;
using System;

namespace RoslynExcelTest
{
    public static class TheAddin
    {
        public static object TheFunction()
        {
            try
            {
                return CSharpScript.EvaluateAsync<int>("1 + 2").Result;
            } catch (Exception ex)
            {
                return ex.ToString();
            }
        }
    }
}

Now when building and invoking this, it'll throw a System.IO.FileLoadException saying it can't System.Reflection.Metadata, Version=1.2.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a; even though the dll is part of the CSharp.Scripting nuget package and is present in the XLL directory.

As a quick and dirty fix, I've tried hooking up an AppDomain.CurrentDomain.AssemblyResolve handler and resolve these DLLs right from the given directory, however even then at some point it'll try finding DLLs that aren't even present in the packages (e.g. think System.Runtime.Loader.dll).

I think I've had this very issue when I tried creating a .Net 4.5 Project, and then dynamically loading (i.e. not linking in the project, but rather Assembly.LoadFile() etc. during runtime) the Roslyn assemblies; it created a huge chain of DLL dependencies which weren't really resolvable.

I'm not quite sure what's going on. I vaguely recall a Microsoft conference where they pointed out they were planning to ship newer features in distributable DLLs rather than requiring Framework updates.
So my gut feeling was that 4.6.1 has a lot of new core dlls (System.dll etc.), and maybe ExcelDNA, which I believe is built against a lower Framework version, has some of these elementary DLLs conflicting with these of Roslyn.
But then I recompiled Excel-DNA from Github under target frame work 4.6.1, and kept facing the same issue regardless.

Do you have any idea what's going on there?
It'd be pretty cool to get Roslyn/Scripting running there, but also who knows if that might even be a broader issue that could affect projects depending on very recent iterations of the framework.

Thanks!

[Govert van Drimmelen]

Hi Bogey,

You need to make sure the assembly redirections are put in the .xll.config file.

Easiest is to:

* Rename the App.Config file that the Roslyn package made to RoslynTest-AddIn.xll.config (your name...)

* Set its properties to Copy to Output Directory: Copy if Newer

To set it up for the packing, you have to add all the assemblies that are pulled in from the NuGet package. My .dna file ended up looking like this:

<DnaLibrary Name="RoslynScripting Add-In" RuntimeVersion="v4.0">

  <ExternalLibrary Path="RoslynScripting.dll" LoadFromBytes="true"    Pack="true" />

  

  <Reference Path="System.Reflection.Metadata.dll"                    Pack="true" />

  <Reference Path="System.Collections.Immutable.dll"                  Pack="true" />

  <Reference Path="System.Console.dll"                                Pack="true" />

  <Reference Path="System.Diagnostics.FileVersionInfo.dll"            Pack="true" />

  <Reference Path="System.Diagnostics.StackTrace.dll"                 Pack="true" />

  <Reference Path="System.IO.FileSystem.dll"                          Pack="true" />

  <Reference Path="System.IO.FileSystem.Primitives.dll"               Pack="true" />

  <Reference Path="System.Security.Cryptography.Algorithms.dll"       Pack="true" />

  <Reference Path="System.Security.Cryptography.Encoding.dll"         Pack="true" />

  <Reference Path="System.Security.Cryptography.Primitives.dll"       Pack="true" />

  <Reference Path="System.Security.Cryptography.X509Certificates.dll" Pack="true" />

  <Reference Path="System.Text.Encoding.CodePages.dll"                Pack="true" />

  <Reference Path="System.Threading.Thread.dll"                       Pack="true" />

  <Reference Path="System.Xml.XmlDocument.dll"                        Pack="true" />

  <Reference Path="System.Xml.XPath.dll"                              Pack="true" />

  <Reference Path="System.Xml.XPath.XDocument.dll"                    Pack="true" />

  <Reference Path="Microsoft.CodeAnalysis.CSharp.dll"                 Pack="true" />

  <Reference Path="Microsoft.CodeAnalysis.CSharp.Scripting.dll"       Pack="true" />

  <Reference Path="Microsoft.CodeAnalysis.dll"                        Pack="true" />

  <Reference Path="Microsoft.CodeAnalysis.Scripting.dll"              Pack="true" />

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

  <Reference Path="System.AppContext.dll"                             Pack="true" />

</DnaLibrary>

There has been an issue reported with the assembly redirection with Excel-DNA, but I have not followed it up yet. See: https://github.com/Excel-DNA/ExcelDna/issues/41

But I did not run into any this issue when trying out your example.

I agree it opens up some exciting possibilities.

A simple expression evaluator add-in would be a nice sample.

Let us know what you try next.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Bogey [f.me...@gmail.com]
Sent: 03 July 2016 04:14 PM
To: Excel-DNA
Subject: [ExcelDna] Incompatibility with Roslyn / .Net 4.6.1?

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Bogey]

Ah, shewt :) Thanks as usual Govert, did not notice it added an app.config at all.

First thing I'll try is to create a relatively simple AddIn that allows to create and store C# scripts right on a sheet, and invoke them in an UDF and/or macro-like manner. Should be relatively straightforward, I can put the result on github or so if any interest.

As for the second idea - I've come up with a draft that would esentially allow to replace the VBA editor with a C# editor, allowing to write C# UDFs and Macros and shipping it in a single-file, clean (no code placed in Worksheets or anything) .xlsm workbook, and without requiring the end user of that sheet to have any addin pre-installed at all.
That part should be manageable with a few tricks, however creating the actual Editor addin to create such a workbook is a ton of work (creating a proper C# editor with code completion and all that), will probably give it a try once 1-2 currently pending features are implemented in Roslyn; Anyway, to be honest I'm not sure that'll even be manageable in a reasonable amount of time.. but let's see ;)

[Govert van Drimmelen]

The first plan sounds like a really nice application of the Roslyn scripting, and would be quite sweet to have.

The second one is a minefield that has been discussed and explored from time to time. I think the approach I'm likely to take a bit further is a pair of add-ins, one in Excel, the other a VS extension, that uses VS as the editor and project manager interface (perhaps VS with the UI radically simplified). An early exploration of such a like is here: https://github.com/Excel-DNA/VSExcel

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Bogey [f.me...@gmail.com]

Sent: 03 July 2016 09:37 PM
To: Excel-DNA
Subject: [ExcelDna] Re: Incompatibility with Roslyn / .Net 4.6.1?

[Bogey]

Cool - I'll work out something for the first case & post in this group, may take a bit though as probably won't have a ton of time in the next 2-3 weeks.

For the second case, didn't know about the VSExcel project. Good luck, sounds pretty great to have!
It'll probably have a lot of very interesting use cases. Just a few relatively industry-specific thoughts from my point of view as somebody who works in finance:

1. Nearly nobody has, or is even allowed to have Visual Studio installed on work PCs.
2. In fact, nobody ever has admin rights on their machines. So anything that isn't non-portable doesn't really work.
3. Those programming their sheets with C# should easily be able to open an ExcelDna addin (the "editor" addin). But often those sheets will be shared - and I would definitely not count on other users having custom addins installed. Either because they won't know how to (much more likely than one would think), and/or because there won't be any good way to share a separate addin file (say, lack of common network drives).

So if one were to come up with a tool intending to provide VBA editor-like functionality and simplicity, these seem to be major (and annoying) concerns.

For #1 and #2, I've very briefly looked into Visual Studio Code, as that is a very nice portable editor. However, I vaguely recall it was written in some odd programming language I had zero experience with, so quickly dropped the idea of customizing this. Maybe RoslynPad could be a start - of course custom solutions will never be as great as VS, unfortunately.
For #3, think I've come up with a reasonable way of shipping everything in a simple .xls(m) file without any other requisites on the end user's side. If ever needed, give me a shout, can write up a sketch of a potential solution.

Anyways, will definitely follow the VSExcel project, anything going in the general direction of replacing VBA more conveniently is more than welcome ;-)

To post to this group, send email to exc...@googlegroups.com.

[Gareth Hayter]

Hi Bogey

I created something like this many years ago (I haven't updated it in years - as far as I can remember it only worked in Excel 2007 and 2010): http://www.slyce.com/VScript/index.html

Anyway, do some good market research before putting in too much time - I didn't do enough market validation and burnt quite a bit of time that I never got back ;-)

Kind regards,
Gareth.

[Ron]

Hi Bogey, Gareth,

I looked into that matter a few years ago and my main issue was the debugging. I doubt that anyone would ever use such tool if he or she can't debug the script step by step.

Before Roslyn, the only option I thought about was by running another process which would attach to the Excel process. But perhaps with Roslyn we can execute each statements of the code one by one like the VBA editor would do?

Anyway, the main question to answer first is whether the actual Excel/VBA users will be interested in developing in C#/VB.Net/F#? VBA is not popular among the software developers, but at least 95% of the users, if not 99%, are not software developers. I already found myself explaining the concept of local vs global variable to people who wrote huge macros without understanding the basics of the language. 

I would be excited to see such a tool, and even more excited to contribute, but I agree with Gareth about the "good market research" to be done first.

Regards,

Ron

[Bogey]

Hey Gareth,

nice project! Must have been a lot of work.
I certainly agree the market for this probably isn't massive in terms of numbers. Personally, IF I ever got around to trying this seriously, would most certainly be as a hobby project / open source; if nothing else, it'd at least be interesting to work at from a technical challenge-point of view.

Ron,
debugging is definitely a concern. Depends on the scale of the project too I guess, maybe a simple "better than nothing"-kind of project could indeed start without? VBA was "intended" for very small scripts only; not even sure the average VBA macro programmer knows about debugging? Assuming a .NET tool would be intended for similar use cases, while proper larger scale projects would probably use a proper dev environment anyway.

Anyways, yea I suppose attaching a debugging process would probably be usual way to go (MDbg?), not easy though.
I don't think Roslyn comes with any built in debugging functionality. Maybe you can find some cheeky workaround. E.g. it does make working with the syntax tree "easy" (well...) - so e.g. for classic breakpoint & variable inspection, maybe you could modify the code of any script in places where the user sets a breakpoint, gather all accessible vars at that code location (also fairly easy with Roslyn), then inject code into the syntax tree to pop up some thread-blocking GUI to display these until continuation? Just a brief idea, certainly not a very elegant debugging solution with a ton of potential downsides (threading..?) and isuses on its own, but maybe a very quick & dirty "good enough" kind of way

[Alan Stubbs]

That sounds really cool!  Random thought since you were talking about existing C# editors - LinqPad is a really nice tool, not sure if you could use that in any way?