IronPython

[jbh]

I'm trying to see if I can get IronPython working with Excel-DNA.  Here is what I have tried:

I made a new Python Class Library Project using Sharp Develop and made the following MyClass.py file:

class MyClass:
    def __init__(self):
        pass
    @ExcelFunction(Description="Adds two numbers", Category="Useful functions")
    def AddSomeStuff(x,y):
        return x + y

I then setup a new folder and copied the ExcelDna.xll file into this folder and renamed it to 'MyClass.xll'.  I made a file named 'MyClass.dna':

<DnaLibrary>

<ExternalLibrary Path="MyClass.dll" />

</DnaLibrary>

In SharpDevelop, I added a reference to the ExcelDna.Integration.dll file.  I built the solution and copied the following files from the Release folder of the project into the directory containing MyClass.dna and MyClass.xll:

ExcelDna.Integration.dll

IronPython.dll

IronPython.Modules.dll

IronPython.Modules.xml

IronPython.xml

Microsoft.Dynamic.dll

Microsoft.Scripting.Debugging.dll

Microsoft.Scripting.dll

MyClass.dll

I then go to add the .xll file as an add-in in Excel, and I get the following error message:

External library could not be registered - Path: MyClass.dll

    Error: Could not load file or assembly 'file:///C:\Users\jeff.holman\Documents\Addins\ExcelDna-0.28\MyClass\MyClass.dll' or one of its dependencies. This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded.

I'm a little confused because I think I read that Excel-DNA supports .NET 4.0 and I think .NET 4.0 is the version of the runtime I have on my system, so I just don't know how I could even have a newer runtime than 4.0 if 4.0 is the latest.

Any ideas?  I'm completely new to this .NET stuff, and I can't seem to figure out what is going wrong or if what I am trying to do is even possible.  

Thanks!

http://stackoverflow.com/questions/1578010/ironpython-2-6-py-exe

External library could not be registered - Path: MyClass.dll

    Error: Could not load file or assembly 'file:///C:\Users\jeff.holman\Documents\Addins\ExcelDna-0.28\MyClass\MyClass.dll' or one of its dependencies. This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded.

[Govert van Drimmelen]

Hi,

You might like to check out http://www.pyxll.com, which integrates
Python into Excel using the same .xll interface that Excel-DNA uses to
integrate .NET.
It's not open source, but is free for non-commercial use. I have not
looked at it and I don't know how active the project is.

Anyway.... back to IronPython and .NET.
It would certainly be nice for me to figure out how to support
IronPython with Excel-DNA.

Your immediate problem is easy to solve. To tell Excel-DNA that you
want to run under .NET 4, you add a RuntimeVersion attribute to
the .dna file:
<DnaLibrary RuntimeVersion="v4.0">

<ExternalLibrary Path="MyClass.dll" />
</DnaLibrary>

Then I think your library will load, but no functions will be
registered with Excel because of a more serious problem:
IronPython does not generate an assembly with the usual .NET types,
like would be created from C#, F#, VB.NET etc. The types are types in
the dynamic runtime library (DLR).
This means the .NET reflection that Excel-DNA uses to investigate
which types and functions there are, won't work as-is, and
automatically getting the Delegates (like function pointers) to pass
to Excel is not so obvious. So this won't work easily with Excel-DNA
without an intermediate redirection layer. If we can get such a
redirection nicely sorted out in C#, it would be possible to
automatically generate and build it into Excel-DNA in future though.

There was a project to create a static wrapper for IronPython (http://
coils.codeplex.com/) but it never seems to have gotten off the ground.

So your starting point would be to make a C# or VB.NET stub library,
that calls your IronPython library.
Even this is not trivial, though it got much better in .NET 4 with the
introduction of the 'dynamic' type in C#. You can probably simplify
the code by combining with what they do here:
http://stackoverflow.com/questions/579272/instantiating-a-python-class-in-c
with these links, respectively using the python code directly and a
compiled library like the one you have:
http://www.ironpython.info/index.php/Using_Python_Classes_from_.NET/CSharp_IP_2.6
http://www.ironpython.info/index.php/Using_Compiled_Python_Classes_from_.NET/CSharp_IP_2.6

For good performance, most of the set-up (starting the Python engine,
getting hold of the functions etc) will be run once in the Add-in's
AutoOpen so that each function call should be very fast. But for now
maybe getting just one function to work, even if slow, will be good.

I think a reasonable design would be to create the IronPython .dll
without reference to the ExcelDna libraries, just containing your
computation code. Then the C# stub would set up the calls to the
Python library and would also contain the Excel-specific attributes
like function and argument descriptions and perhaps dealing with
default or missing values, errors etc.

If you are keen to try this, I'm happy to help wherever you get stuck.

Regards,
Govert

On Mar 10, 10:25 pm, jbh <jbhol...@gmail.com> wrote:
> I'm trying to see if I can get IronPython working with Excel-DNA.  Here is
> what I have tried:
>
> I made a new Python Class Library Project using Sharp Develop and made the
> following MyClass.py file:
>

> *class MyClass:

>     def __init__(self):
>         pass
>     @ExcelFunction(Description="Adds two numbers", Category="Useful
> functions")
>     def AddSomeStuff(x,y):
>         return x + y

> *

[Govert van Drimmelen]

OK,

I've made a start that works on my machine - I paste the whole .dna
file below. You should be able to just paste it into your .dna file
and it should work, giving you a =TestIPAdd(...) function in Excel.

I couldn't find the documentation for the DLR classes like ScriptScope
etc.
I changed the python function to be a member function
def AddSomeStuff(self,x,y):
return x + y
Otherwise I couldn't hook up the right variable - I'm sure this is
just that I don't know how to use the ScriptEngine / ScriptScope
classes.

Let us know if you can get it to work :-)

[Seeing this, I realize it should be quite easy to make an Excel
function that evaluates Python code passed from Excel...]

Regards,
Govert



<DnaLibrary RuntimeVersion="v4.0" Language="C#">
<Reference Name="System.Windows.Forms" />
<Reference Name="Microsoft.CSharp" />
<Reference Path="Microsoft.Scripting.dll" />
<Reference Path="IronPython.dll" />

<![CDATA[
using System;
using System.IO;
using System.Windows.Forms;
using ExcelDna.Integration;
using System.Reflection;
using IronPython.Hosting;
using Microsoft.Scripting.Hosting;

// IronPython code compiled to MyClass.dll
// (Note I added the self to AddSomeStuff -
// not sure how to make it work otherwise)
//
// class MyClass:
// def __init__(self):
// pass
// def AddSomeStuff(self,x,y):
// return x + y


public class MyAddIn : IExcelAddIn
{
public void AutoOpen()
{
try
{
string xllDirectory =
AppDomain.CurrentDomain.BaseDirectory;
string dllPath = Path.Combine(xllDirectory,
"MyClass.dll");
Assembly myclass = Assembly.LoadFile(dllPath);

ScriptEngine pyEngine = Python.CreateEngine();
pyEngine.Runtime.LoadAssembly(myclass);
ScriptScope pyScope =
pyEngine.Runtime.ImportModule("MyClass");
object myClass = pyEngine.Operations.Invoke(
pyScope.GetVariable("MyClass"));
IronTest.AddSomeStuff =
pyEngine.Operations.GetMember<Func<double, double,
double>>(
myClass, "AddSomeStuff");
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
}

public void AutoClose()
{
}
}

public class IronTest
{
public static Func<double, double, double> AddSomeStuff;

public static double TestIPAdd(double val1, double val2)
{
try
{
return AddSomeStuff(val1, val2);
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
return double.NaN;
}
}
}

]]>
</DnaLibrary>


I had to change

On Mar 10, 11:37 pm, Govert van Drimmelen <gov...@icon.co.za> wrote:
> Hi,
>

> You might like to check outhttp://www.pyxll.com, which integrates

> the code by combining with what they do here:http://stackoverflow.com/questions/579272/instantiating-a-python-clas...

> with these links, respectively using the python code directly and a

> compiled library like the one you have:http://www.ironpython.info/index.php/Using_Python_Classes_from_.NET/C...http://www.ironpython.info/index.php/Using_Compiled_Python_Classes_fr...

[jbh]

It worked. Thank you very much for example because I would not have
known how to do that in C# at all. I think my next step will be to
use your DNA file and put it into a C# project in SharpDevelop, just
so I can get more familiar with that kinda thing. The load time of
the addin isn't so bad (my users are used to large workbooks that take
a long time to open), but putting in the formula and copying down a
million rows is quite a bit slower than the native addition. I
expected that actually, and I'm not really going to be using add-ins
to re-implement addition, so I'm okay with that.

What I really want to do is pass in the Excel Object Model and mess
around with stuff like adding sheets, taking data from one sheet and
putting on to another for situations where lookups don't quite make
sense, etc. What started me down this whole path was reading this:

http://www.ironpython.info/index.php/Interacting_with_Excel

Essentially, I want to implement some macros in IronPython (not just
worksheet formulas), and I've made a few already that were a bit
easier to develop in python over vba, but I want to be able to put
these macros in an addin that user can install. This is where Excel-
DNA comes in. I'm going to do some poking around to see about passing
in the Application Object to a python script, but if you know of any
resources and feel like pointing me towards them, I certainly wouldn't
mind.

Thanks!

> > compiled library like the one you have:http://www.ironpython.info/index.php/Using_Python_Classes_from_.NET/C......

[Govert van Drimmelen]

Hi,

For getting the right Application object, you should be able to set it
from the C# wrapper. You get hold of the right instance by calling
ExcelDna.Integration.ExcelDnaUtil.Application. Then you need some
method on your Python library that will store it for use.

Please let us know if you get stuck or get to a nice example.

-Govert

> > > > Any ideas?  I'm completely new to this .NET stuff, and I can't seem to...
>
> read more »

[jbh]

Thanks for the response. I am trying to setup the addin so I can
debug in SharpDevelop. I have created two projects in one solution,
one is a C# class library. The other is my python class library from
before. I apologize if this is just a noob .NET/C# issue and not
related to Excel-DNA at all, so feel free to tell me if it isn't
related, and I'll try to ask on stackoverflow.

MyAddIn.cs:

/*
Added these references all as Local Copies - probably not necessary?

1.System.Windows.Forms
2.Microsoft.CSharp

3.ExcelDna.Integration (from Excel-DNA distribution folder)
4.IronPython (from IronPython folder)
5.IronPython.Modules (from IronPython folder)
6.Microsoft.Dynamic (from IronPython folder)
7.Microsoft.Scripting (from IronPython folder)
8.Microsoft.Scripting.Metadata (from IronPython folder)

9.mscorlib (I don't really know why I added this, but it was one of
the references in my IronPython class library)

10.MyClass (this is the reference to my IronPython class - I checked
to see that it gets copied in every time I rebuild the solution and it
does)

These were automatically added by SharpDevelop when I created the
project.
11.System
12.System.Core
13.System.Windows.Forms
14.System.Xml
15.System.Xml.Linq
*/

using System;
using System.IO;
using System.Windows.Forms;
using ExcelDna.Integration;
using System.Reflection;
using IronPython.Hosting;
using Microsoft.Scripting.Hosting;

public class MyAddIn : IExcelAddIn
{
public void AutoOpen()
{
try
{

//I've added the MyClass.dll reference to this
project so it does exist in the //Debug folder of this
project
string xllDirectory = Path.GetDirectoryName(@"C:\Users
\jeff.holman\Documents\SharpDevelop Projects\IronPythonExcelDNATest
\MyAddIn\bin\Debug\");

string dllPath = Path.Combine(xllDirectory,
"MyClass.dll");
Assembly myclass = Assembly.LoadFile(dllPath);
ScriptEngine pyEngine = Python.CreateEngine();
pyEngine.Runtime.LoadAssembly(myclass);
ScriptScope pyScope =
pyEngine.Runtime.ImportModule("MyClass");
object myClass = pyEngine.Operations.Invoke(
pyScope.GetVariable("MyClass"));
IronTest.AddSomeStuff =
pyEngine.Operations.GetMember<Func<double, double,
double>>(
myClass, "AddSomeStuff");
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
}
public void AutoClose()
{
}
}

public class IronTest
{
public static Func<double, double, double> AddSomeStuff;
public static double TestIPAdd(double val1, double val2)
{
try
{
return AddSomeStuff(val1, val2);
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
return double.NaN;
}
}
}

And here is my python project, MyClass.py:

class MyClass:
def __init__(self):
pass

def AddSomeStuff(self,x,y):
return x + y

I actually setup the project to debug following the instructions here:

http://www.blog.methodsinexcel.co.uk/2010/10/15/debugging-a-dna-solution/

I am able to step through the first few lines of C# code, so that is
progress! When I get to the following line:

pyEngine.Runtime.LoadAssembly(myclass);

I get an exception:

"Could not load file or assembly 'Microsoft.Dynamic, Version=1.0.0.0,
Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its
dependencies. The located assembly's manifest definition does not
match the assembly reference. (Exception from HRESULT: 0x80131040)"

But I have added the Microsoft.Dynamic reference to my project. It is
version 1.1.0.20. This is included in the IronPython distribution but
also in another location on my computer. I have tried setting the
reference to both, but they both have the same version number and
appear to be the same file size. Neither one works. Do I need
version 1.0.0.0 or am I doing something else wrong?










"Could not load file or assembly 'Microsoft.Dynamic, Version=1.0.0.0,
Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its
dependencies. The located assembly's manifest definition does not
match the assembly reference. (Exception from HRESULT: 0x80131040)"

> > > > > I then...
>
> read more »