Setting ExcelFunctionAttributes at runtime
195 views
Skip to first unread message
Simon
May 3, 2009, 11:16:02 AM5/3/09
to ExcelDna
Hi,
Firstly, apologies to those who have already read this post on
codeplex...
When registering functions in a .NET assembly, is it possible to
override / replace the default attributes applied to a function?
So far we have been able to prevent the functions from automatically
registering by placing them in a Friend class and registering them
manually:
Dim methods As New Generic.List(Of MethodInfo)
Dim info As MethodInfo = GetType(HelloWorld).GetMethod
("HelloWorld", New Type() {})
methods.Add(info)
Integration.RegisterMethods(methods)
however, changing the custom attributes before calling RegisterMethods
seems to have no effect:
Dim ma As Object() = info.GetCustomAttributes(False)
If ma.Count > 0 Then
With CType(ma(0), ExcelDna.Integration.ExcelFunctionAttribute)
.Category = "My Custom Category"
.Description = "My Custom Description"
.Name = "My Custom Name"
End With
End If
Thanks, Simon
Firstly, apologies to those who have already read this post on
codeplex...
When registering functions in a .NET assembly, is it possible to
override / replace the default attributes applied to a function?
So far we have been able to prevent the functions from automatically
registering by placing them in a Friend class and registering them
manually:
Dim methods As New Generic.List(Of MethodInfo)
Dim info As MethodInfo = GetType(HelloWorld).GetMethod
("HelloWorld", New Type() {})
methods.Add(info)
Integration.RegisterMethods(methods)
however, changing the custom attributes before calling RegisterMethods
seems to have no effect:
Dim ma As Object() = info.GetCustomAttributes(False)
If ma.Count > 0 Then
With CType(ma(0), ExcelDna.Integration.ExcelFunctionAttribute)
.Category = "My Custom Category"
.Description = "My Custom Description"
.Name = "My Custom Name"
End With
End If
Thanks, Simon
Govert van Drimmelen
May 4, 2009, 6:12:38 AM5/4/09
to ExcelDna
Hi Simon,
I don't think you can edit the attributes on a method this way. The
attributes are baked in when the assembly is created, and I think if
you made a call to GetCustomAttributes after the changes you make, you
get back the unchanged attributes.
The long term answer is probably to have be a more flexible
registration plan for ExcelDna, which is on the cards, but you
shouldn't hold your breath :-(
Maybe there's another plan, if you explain a bit more of your use
case.
Regards,
Govert
I don't think you can edit the attributes on a method this way. The
attributes are baked in when the assembly is created, and I think if
you made a call to GetCustomAttributes after the changes you make, you
get back the unchanged attributes.
The long term answer is probably to have be a more flexible
registration plan for ExcelDna, which is on the cards, but you
shouldn't hold your breath :-(
Maybe there's another plan, if you explain a bit more of your use
case.
Regards,
Govert
Simon
May 6, 2009, 8:26:52 AM5/6/09
to ExcelDna
Hi Govert,
After walking through the ExcelDna code (have got a nice pretty
diagram if you want it) we decided to use a dynamic assembly.
One question though, would it be simple to modify ExcelDna to do the
equivelent of raising a OnRegisteringFunction event out to the
assembly being registered? This could give the opportunity to change
the FunctionAttributes and ArgumentAttributes through EventArgs.
FYI, our code solution:
Imports System
Imports System.Collections.Generic
Imports System.Reflection
Imports System.Reflection.Emit
Imports ExcelDna.Integration
Public Class Test
Implements IExcelAddIn
Public Sub AutoClose() Implements
Integration.IExcelAddIn.AutoClose
End Sub
Public Sub AutoOpen() Implements Integration.IExcelAddIn.AutoOpen
Integration.Integration.RegisterMethods(methods)
End Sub
Private Shared Function CreateMethod() As MethodInfo
Dim className As String = "Class1"
Dim asssemblyName As New AssemblyName
asssemblyName.Name = "Assembly1"
asssemblyName.Version = New Version(1, 0, 0, 0)
Dim assemblyBuilder As AssemblyBuilder =
AppDomain.CurrentDomain.DefineDynamicAssembly(asssemblyName,
AssemblyBuilderAccess.RunAndSave)
Dim moduleBuilder As ModuleBuilder =
assemblyBuilder.DefineDynamicModule(asssemblyName.Name & ".dll",
asssemblyName.Name & ".dll")
Dim typeBuilder As TypeBuilder = moduleBuilder.DefineType
(className, TypeAttributes.Class Or TypeAttributes.Public)
'Build the function name
Dim functionName As String = "Function1"
'Build the method attributes
Dim attributes As MethodAttributes = MethodAttributes.Static
Or MethodAttributes.Public
'Build the return type
Dim retType As Type = GetType(Integer)
'Build the signature
Dim signature As New Generic.List(Of Type)
signature.Add(GetType(String))
'Build the method
Dim methodBuilder As MethodBuilder = typeBuilder.DefineMethod
(functionName, attributes, retType, signature.ToArray)
Dim paramBuilder As ParameterBuilder =
methodBuilder.DefineParameter(1, ParameterAttributes.In, "param1")
'Generate the actual IL code
With methodBuilder.GetILGenerator
.Emit(OpCodes.Ldarg_0) 'Place the first parameter from
Excel onto the stack
.Emit(OpCodes.Call, GetType(ExcelDna.Core).GetMethod
("InternalMethod", New Type() {GetType(String)})) 'Call InternalMethod
.Emit(OpCodes.Ret) 'Return
End With
'Define the attributes to be applied to the new method.
'This will break if the implementation of
ExcelFunctionAttribute changes
With GetType(ExcelFunctionAttribute)
Dim constructor As ConstructorInfo = .GetConstructor(New
Type() {})
Dim namedFields As FieldInfo() = New FieldInfo() {.GetField
("Name"), .GetField("Description"), .GetField("Category")}
Dim fieldValues As Object() = New Object()
{"NiceFunctionName", "Nice long descrtipion with lots of text", "Nice
Category"}
Dim attributeBuilder As New CustomAttributeBuilder
(constructor, New Object() {}, namedFields, fieldValues)
methodBuilder.SetCustomAttribute(attributeBuilder)
End With
'Define the attributes to be applied to the new parameters.
With GetType(ExcelArgumentAttribute)
Dim constructor As ConstructorInfo = .GetConstructor(New
Type() {})
Dim namedFields As FieldInfo() = New FieldInfo() {.GetField
("Name"), .GetField("Description")}
Dim fieldValues As Object() = New Object()
{"NiceParameterName", "Nice long descrtipion with lots of text"}
Dim attributeBuilder As New CustomAttributeBuilder
(constructor, New Object() {}, namedFields, fieldValues)
paramBuilder.SetCustomAttribute(attributeBuilder)
End With
'Commit the class
Dim classType As Type = typeBuilder.CreateType()
'Return the methodinfo to the calling code
Return classType.GetMethod(functionName, signature.ToArray)
End Function
End Class
Public Class Core
Public Shared Function InternalMethod(ByVal param1 As String) As
Integer
Return param1.Length
End Function
End Class
Thanks for your feedback,
Simon.
> > Thanks, Simon- Hide quoted text -
>
> - Show quoted text -
After walking through the ExcelDna code (have got a nice pretty
diagram if you want it) we decided to use a dynamic assembly.
One question though, would it be simple to modify ExcelDna to do the
equivelent of raising a OnRegisteringFunction event out to the
assembly being registered? This could give the opportunity to change
the FunctionAttributes and ArgumentAttributes through EventArgs.
FYI, our code solution:
Imports System
Imports System.Collections.Generic
Imports System.Reflection
Imports System.Reflection.Emit
Imports ExcelDna.Integration
Public Class Test
Implements IExcelAddIn
Public Sub AutoClose() Implements
Integration.IExcelAddIn.AutoClose
End Sub
Public Sub AutoOpen() Implements Integration.IExcelAddIn.AutoOpen
Dim methods As New Generic.List(Of MethodInfo)
methods.Add(CreateMethod())
Integration.Integration.RegisterMethods(methods)
End Sub
Private Shared Function CreateMethod() As MethodInfo
Dim className As String = "Class1"
Dim asssemblyName As New AssemblyName
asssemblyName.Name = "Assembly1"
asssemblyName.Version = New Version(1, 0, 0, 0)
Dim assemblyBuilder As AssemblyBuilder =
AppDomain.CurrentDomain.DefineDynamicAssembly(asssemblyName,
AssemblyBuilderAccess.RunAndSave)
Dim moduleBuilder As ModuleBuilder =
assemblyBuilder.DefineDynamicModule(asssemblyName.Name & ".dll",
asssemblyName.Name & ".dll")
Dim typeBuilder As TypeBuilder = moduleBuilder.DefineType
(className, TypeAttributes.Class Or TypeAttributes.Public)
'Build the function name
Dim functionName As String = "Function1"
'Build the method attributes
Dim attributes As MethodAttributes = MethodAttributes.Static
Or MethodAttributes.Public
'Build the return type
Dim retType As Type = GetType(Integer)
'Build the signature
Dim signature As New Generic.List(Of Type)
signature.Add(GetType(String))
'Build the method
Dim methodBuilder As MethodBuilder = typeBuilder.DefineMethod
(functionName, attributes, retType, signature.ToArray)
Dim paramBuilder As ParameterBuilder =
methodBuilder.DefineParameter(1, ParameterAttributes.In, "param1")
'Generate the actual IL code
With methodBuilder.GetILGenerator
.Emit(OpCodes.Ldarg_0) 'Place the first parameter from
Excel onto the stack
.Emit(OpCodes.Call, GetType(ExcelDna.Core).GetMethod
("InternalMethod", New Type() {GetType(String)})) 'Call InternalMethod
.Emit(OpCodes.Ret) 'Return
End With
'Define the attributes to be applied to the new method.
'This will break if the implementation of
ExcelFunctionAttribute changes
With GetType(ExcelFunctionAttribute)
Dim constructor As ConstructorInfo = .GetConstructor(New
Type() {})
Dim namedFields As FieldInfo() = New FieldInfo() {.GetField
("Name"), .GetField("Description"), .GetField("Category")}
Dim fieldValues As Object() = New Object()
{"NiceFunctionName", "Nice long descrtipion with lots of text", "Nice
Category"}
Dim attributeBuilder As New CustomAttributeBuilder
(constructor, New Object() {}, namedFields, fieldValues)
methodBuilder.SetCustomAttribute(attributeBuilder)
End With
'Define the attributes to be applied to the new parameters.
With GetType(ExcelArgumentAttribute)
Dim constructor As ConstructorInfo = .GetConstructor(New
Type() {})
Dim namedFields As FieldInfo() = New FieldInfo() {.GetField
("Name"), .GetField("Description")}
Dim fieldValues As Object() = New Object()
{"NiceParameterName", "Nice long descrtipion with lots of text"}
Dim attributeBuilder As New CustomAttributeBuilder
(constructor, New Object() {}, namedFields, fieldValues)
paramBuilder.SetCustomAttribute(attributeBuilder)
End With
'Commit the class
Dim classType As Type = typeBuilder.CreateType()
'Return the methodinfo to the calling code
Return classType.GetMethod(functionName, signature.ToArray)
End Function
End Class
Public Class Core
Public Shared Function InternalMethod(ByVal param1 As String) As
Integer
Return param1.Length
End Function
End Class
Thanks for your feedback,
Simon.
>
> - Show quoted text -
Simon
May 6, 2009, 11:59:44 AM5/6/09
to ExcelDna
Hi Govert,
We have been able to acheive the required result by dynamically
creating an assembly and setting the attributes on that.
Our ultimate goal is to have an assembly of bland, generic function
names that the end user can name and categorise at run time. I guess
we could acheive this with a dna file but want to hide the
implementation detail from the user.
Our code so far (very much just proof of concepts at this stage):
Public Class Test
Implements IExcelAddIn
Public Sub AutoClose() Implements
Integration.IExcelAddIn.AutoClose
End Sub
Public Sub AutoOpen() Implements Integration.IExcelAddIn.AutoOpen
Dim functionBuilder As New ExcelFunctionBuilder
With functionBuilder
.CreateMethod("Function1")
.CreateMethod("Function2")
Integration.Integration.RegisterMethods(.GetMethods())
End With
End Sub
End Class
Public Class ExcelFunctionBuilder
Private _moduleBuilder As ModuleBuilder
Private _typeBuilder As TypeBuilder
Private Function GetModuleBuilder() As ModuleBuilder
If _moduleBuilder Is Nothing Then
Return _moduleBuilder
End Function
Private Function GetTypeBuilder() As TypeBuilder
If _typeBuilder Is Nothing Then
_typeBuilder = GetModuleBuilder.DefineType("Class1",
TypeAttributes.Class Or TypeAttributes.Public)
End If
Return _typeBuilder
End Function
Public Sub CreateMethod(ByVal functionName As String)
'Build the method attributes
Dim attributes As MethodAttributes = MethodAttributes.Static
Or MethodAttributes.Public
'Build the return type
Dim retType As Type = GetType(Integer)
'Build the signature
Dim signature As New Generic.List(Of Type)
signature.Add(GetType(String))
'Build the method
Dim methodBuilder As MethodBuilder =
GetTypeBuilder.DefineMethod(functionName, attributes, retType,
Public Function GetMethods() As Generic.List(Of MethodInfo)
'Commit the class
Dim classType As Type = GetTypeBuilder.CreateType()
'Return the methodinfos to the calling code
Return New Generic.List(Of MethodInfo)(classType.GetMethods
(BindingFlags.Static Or BindingFlags.Public))
End Function
End Class
Simon.
We have been able to acheive the required result by dynamically
creating an assembly and setting the attributes on that.
Our ultimate goal is to have an assembly of bland, generic function
names that the end user can name and categorise at run time. I guess
we could acheive this with a dna file but want to hide the
implementation detail from the user.
Our code so far (very much just proof of concepts at this stage):
Public Class Test
Implements IExcelAddIn
Public Sub AutoClose() Implements
Integration.IExcelAddIn.AutoClose
End Sub
Public Sub AutoOpen() Implements Integration.IExcelAddIn.AutoOpen
With functionBuilder
.CreateMethod("Function1")
.CreateMethod("Function2")
Integration.Integration.RegisterMethods(.GetMethods())
End With
End Sub
End Class
Public Class ExcelFunctionBuilder
Private _moduleBuilder As ModuleBuilder
Private _typeBuilder As TypeBuilder
Private Function GetModuleBuilder() As ModuleBuilder
If _moduleBuilder Is Nothing Then
Dim asssemblyName As New AssemblyName
asssemblyName.Name = "Assembly1"
asssemblyName.Version = New Version(1, 0, 0, 0)
Dim assemblyBuilder As AssemblyBuilder =
AppDomain.CurrentDomain.DefineDynamicAssembly(asssemblyName,
AssemblyBuilderAccess.RunAndSave)
_moduleBuilder = assemblyBuilder.DefineDynamicModule
asssemblyName.Name = "Assembly1"
asssemblyName.Version = New Version(1, 0, 0, 0)
Dim assemblyBuilder As AssemblyBuilder =
AppDomain.CurrentDomain.DefineDynamicAssembly(asssemblyName,
AssemblyBuilderAccess.RunAndSave)
(asssemblyName.Name & ".dll", asssemblyName.Name & ".dll")
End If
Return _moduleBuilder
End Function
Private Function GetTypeBuilder() As TypeBuilder
If _typeBuilder Is Nothing Then
_typeBuilder = GetModuleBuilder.DefineType("Class1",
TypeAttributes.Class Or TypeAttributes.Public)
End If
Return _typeBuilder
End Function
Public Sub CreateMethod(ByVal functionName As String)
'Build the method attributes
Dim attributes As MethodAttributes = MethodAttributes.Static
Or MethodAttributes.Public
'Build the return type
Dim retType As Type = GetType(Integer)
'Build the signature
Dim signature As New Generic.List(Of Type)
signature.Add(GetType(String))
'Build the method
Dim methodBuilder As MethodBuilder =
signature.ToArray)
Dim paramBuilder As ParameterBuilder =
methodBuilder.DefineParameter(1, ParameterAttributes.In, "param1")
'Generate the actual IL code
With methodBuilder.GetILGenerator
.Emit(OpCodes.Ldarg_0) 'Place the first parameter from
Excel onto the stack
.Emit(OpCodes.Call, GetType(Report7.Core).GetMethod
Dim paramBuilder As ParameterBuilder =
methodBuilder.DefineParameter(1, ParameterAttributes.In, "param1")
'Generate the actual IL code
With methodBuilder.GetILGenerator
.Emit(OpCodes.Ldarg_0) 'Place the first parameter from
Excel onto the stack
("InternalMethod", New Type() {GetType(String)})) 'Call InternalMethod
.Emit(OpCodes.Ret) 'Return
End With
'Define the attributes to be applied to the new method.
'This will break if the implementation of
ExcelFunctionAttribute changes
With GetType(ExcelFunctionAttribute)
Dim constructor As ConstructorInfo = .GetConstructor(New
Type() {})
Dim namedFields As FieldInfo() = New FieldInfo() {.GetField
("Name"), .GetField("Description"), .GetField("Category")}
Dim fieldValues As Object() = New Object() {functionName,
.Emit(OpCodes.Ret) 'Return
End With
'Define the attributes to be applied to the new method.
'This will break if the implementation of
ExcelFunctionAttribute changes
With GetType(ExcelFunctionAttribute)
Dim constructor As ConstructorInfo = .GetConstructor(New
Type() {})
Dim namedFields As FieldInfo() = New FieldInfo() {.GetField
("Name"), .GetField("Description"), .GetField("Category")}
"Nice long descrtipion with lots of text", "Nice Category"}
Dim attributeBuilder As New CustomAttributeBuilder
(constructor, New Object() {}, namedFields, fieldValues)
methodBuilder.SetCustomAttribute(attributeBuilder)
End With
'Define the attributes to be applied to the new parameters.
With GetType(ExcelArgumentAttribute)
Dim constructor As ConstructorInfo = .GetConstructor(New
Type() {})
Dim namedFields As FieldInfo() = New FieldInfo() {.GetField
("Name"), .GetField("Description")}
Dim fieldValues As Object() = New Object()
{"NiceParameterName", "Nice long descrtipion with lots of text"}
Dim attributeBuilder As New CustomAttributeBuilder
(constructor, New Object() {}, namedFields, fieldValues)
paramBuilder.SetCustomAttribute(attributeBuilder)
End With
End Sub
Dim attributeBuilder As New CustomAttributeBuilder
(constructor, New Object() {}, namedFields, fieldValues)
methodBuilder.SetCustomAttribute(attributeBuilder)
End With
'Define the attributes to be applied to the new parameters.
With GetType(ExcelArgumentAttribute)
Dim constructor As ConstructorInfo = .GetConstructor(New
Type() {})
Dim namedFields As FieldInfo() = New FieldInfo() {.GetField
("Name"), .GetField("Description")}
Dim fieldValues As Object() = New Object()
{"NiceParameterName", "Nice long descrtipion with lots of text"}
Dim attributeBuilder As New CustomAttributeBuilder
(constructor, New Object() {}, namedFields, fieldValues)
paramBuilder.SetCustomAttribute(attributeBuilder)
End With
Public Function GetMethods() As Generic.List(Of MethodInfo)
'Commit the class
Dim classType As Type = GetTypeBuilder.CreateType()
'Return the methodinfos to the calling code
Return New Generic.List(Of MethodInfo)(classType.GetMethods
(BindingFlags.Static Or BindingFlags.Public))
End Function
End Class
Public Class Core
Public Shared Function InternalMethod(ByVal param1 As String) As
Integer
Return 99
End Class
Public Class Core
Public Shared Function InternalMethod(ByVal param1 As String) As
Integer
End Function
End Class
Simon.
Govert van Drimmelen
May 6, 2009, 1:54:54 PM5/6/09
to ExcelDna
Hi Simon,
What if there were an overload of the Integration.RegisterMethods that
took a MethodInfo and the category and description etc info
explicitly? That would seem much simpler than an event you need to
handle.
I think you could do the name, category, description etc.
customisation be calling xlfRegister yourself, after ExcelDna has
registered the functions. You'll just have to find out what the
ordinal of the functions is, to match the internal "fxxx" name I use.
I'm not sure if you can get this from Excel though...
ExcelDna basically does what you describe for your add-in. The .xll
exports generic function names "f0", "f1" etc. and then these are
hooked up the the managed functions discovered at runtime, and
registered with Excel with the custom info. It sounds like you want an
ExcelDna without the .dna stuff, and slightly modified registration.
Looking at your code, it might be easiest for you to just make a
custom version of ExcelDna.Integration that does what you want.
I'm hoping to have some time to look at ExcelDna again in the
beginning of June, and want to try out some ideas to make this kind of
customisation easier. Basically the parts of XlAddIn and XlMethodInfo
that finds the functions and reads the attributes should move into the
ExcelDna.Integration assembly, where it is easy for you to modify
stuff safely. I'll post here when I eventually have something to show.
Regards,
Govert
What if there were an overload of the Integration.RegisterMethods that
took a MethodInfo and the category and description etc info
explicitly? That would seem much simpler than an event you need to
handle.
I think you could do the name, category, description etc.
customisation be calling xlfRegister yourself, after ExcelDna has
registered the functions. You'll just have to find out what the
ordinal of the functions is, to match the internal "fxxx" name I use.
I'm not sure if you can get this from Excel though...
ExcelDna basically does what you describe for your add-in. The .xll
exports generic function names "f0", "f1" etc. and then these are
hooked up the the managed functions discovered at runtime, and
registered with Excel with the custom info. It sounds like you want an
ExcelDna without the .dna stuff, and slightly modified registration.
Looking at your code, it might be easiest for you to just make a
custom version of ExcelDna.Integration that does what you want.
I'm hoping to have some time to look at ExcelDna again in the
beginning of June, and want to try out some ideas to make this kind of
customisation easier. Basically the parts of XlAddIn and XlMethodInfo
that finds the functions and reads the attributes should move into the
ExcelDna.Integration assembly, where it is easy for you to modify
stuff safely. I'll post here when I eventually have something to show.
Regards,
Govert
Simon
May 7, 2009, 1:24:25 PM5/7/09
to ExcelDna
Hi Govert,
Funny you say that, we have an XLL that was written several years ago
in C++ and it was converting this to .NET where we started hitting
problems.
We are converting my old C++ code to .NET as I am not a C++ programmer
and my old C/C++ code causes us lots of maintenance headaches. We are
using Dna as, within minutes of finding it, Dna just worked :-). We
realised using it would allow us to throw away my poor attempt at C
coding.
Our first attempt with .Net had a C++ (the xll) assembly refercing
a .Net assembly with generic exports like yours (f001, f002, etc.).
This seemed to work well in Office 2007 but failed under Office 2003.
I was wondering if there are known compatibility issues with older
versions of Office and, if this is the case, is why you start the .Net
framework by hand?
To answer your questions, I think an overload would be great. It could
take in the collection of MethodInfo and additionally a collection of
ExcelFunctionAttributes to match up against. However, what about any
decoration on the parameters? I guess it would be even nicer if
RegisterMethods took a simple collection of custom objects that
represented the methods to be registered with their parameters and
attributes, hiding the complexity of the framework specific stuff.
What do you think?
Thanks again, Simon.
> > Simon.- Hide quoted text -
Funny you say that, we have an XLL that was written several years ago
in C++ and it was converting this to .NET where we started hitting
problems.
We are converting my old C++ code to .NET as I am not a C++ programmer
and my old C/C++ code causes us lots of maintenance headaches. We are
using Dna as, within minutes of finding it, Dna just worked :-). We
realised using it would allow us to throw away my poor attempt at C
coding.
Our first attempt with .Net had a C++ (the xll) assembly refercing
a .Net assembly with generic exports like yours (f001, f002, etc.).
This seemed to work well in Office 2007 but failed under Office 2003.
I was wondering if there are known compatibility issues with older
versions of Office and, if this is the case, is why you start the .Net
framework by hand?
To answer your questions, I think an overload would be great. It could
take in the collection of MethodInfo and additionally a collection of
ExcelFunctionAttributes to match up against. However, what about any
decoration on the parameters? I guess it would be even nicer if
RegisterMethods took a simple collection of custom objects that
represented the methods to be registered with their parameters and
attributes, hiding the complexity of the framework specific stuff.
What do you think?
Thanks again, Simon.
Govert van Drimmelen
May 8, 2009, 8:47:19 AM5/8/09
to ExcelDna
Hi Simon,
I had many problems with a mess-up Microsoft made in the released
version of .Net 2.0, which basically broke the .Net loading in Office
apps. This might be the problem you ran into with Excel 2003 vs. Excel
2007. You could also have run into the C/C++ runtime versioning and
distribution problems which were introduced in Visual Studio 2005.
Maybe your Excel 2007 was the development machine and happened to have
the right version of the C runtime
I changed to an unmanaged loader to control these problems better, and
to deal with cases where the wrong version of .Net is already loaded
into the process, or where .Net is not even present on the machine. In
these cases Excel would give a rather unhelpful message saying that
the add-in is not a valid add-in, and helpfully offering to open
the .dll as a text file. I can now give nice messages and informative
messages if the add-in loading fails, and have no dependency on the C
runtime (it could not be statically linked in the IJW scenario).
In addition, I wanted to load the add-ins into separate AppDomains,
and hoped to manage the runtime hosting so that errors like stack
overflows are not fatal to the Excel process. I got the AppDomain part
right, but the CLR hosting interfaces don't work as documented (or as
I expect) when running in Excel, and I don't know where to even start
getting help with this. So it is still possible to crash Excel from
your add-in, which irritates me a bit.
Anyway, I'll keep your suggestion for a better RegisterMethods in mind
for the next version - maybe you could add it as an issue in CodePlex
(http://exceldna.codeplex.com)? But it sounds like you are sorted out
for now.
Thanks for the feedback,
Govert
I had many problems with a mess-up Microsoft made in the released
version of .Net 2.0, which basically broke the .Net loading in Office
apps. This might be the problem you ran into with Excel 2003 vs. Excel
2007. You could also have run into the C/C++ runtime versioning and
distribution problems which were introduced in Visual Studio 2005.
Maybe your Excel 2007 was the development machine and happened to have
the right version of the C runtime
I changed to an unmanaged loader to control these problems better, and
to deal with cases where the wrong version of .Net is already loaded
into the process, or where .Net is not even present on the machine. In
these cases Excel would give a rather unhelpful message saying that
the add-in is not a valid add-in, and helpfully offering to open
the .dll as a text file. I can now give nice messages and informative
messages if the add-in loading fails, and have no dependency on the C
runtime (it could not be statically linked in the IJW scenario).
In addition, I wanted to load the add-ins into separate AppDomains,
and hoped to manage the runtime hosting so that errors like stack
overflows are not fatal to the Excel process. I got the AppDomain part
right, but the CLR hosting interfaces don't work as documented (or as
I expect) when running in Excel, and I don't know where to even start
getting help with this. So it is still possible to crash Excel from
your add-in, which irritates me a bit.
Anyway, I'll keep your suggestion for a better RegisterMethods in mind
for the next version - maybe you could add it as an issue in CodePlex
(http://exceldna.codeplex.com)? But it sounds like you are sorted out
for now.
Thanks for the feedback,
Govert
Simon
May 12, 2009, 5:22:22 AM5/12/09
to ExcelDna
Hi Govert,
Your description of what happens when you try to open a C++ XLL
referencing .Net 2.0 is exactly the reason we found ExcelDna.
To close this thread off, I didn't realise I could call xlfRegister
directly. Knowing this, I question now whether changing
RegisterMethods is really needed, as calling xlfRegister will be good
enough. I also guess outputting the il, as we are, is a little
overkill (oh well, it's been fun learning).
Thanks again,
Simon.
> > > > Dim attributeBuilder As New- Hide quoted text -
>
> - Show quoted text -...
>
> read more »
Your description of what happens when you try to open a C++ XLL
referencing .Net 2.0 is exactly the reason we found ExcelDna.
To close this thread off, I didn't realise I could call xlfRegister
directly. Knowing this, I question now whether changing
RegisterMethods is really needed, as calling xlfRegister will be good
enough. I also guess outputting the il, as we are, is a little
overkill (oh well, it's been fun learning).
Thanks again,
Simon.
> I had many problems with a mess-up Microsoft made in the released
> version of .Net 2.0, which basically broke the .Net loading in Office
> apps. This might be the problem you ran into with Excel 2003 vs. Excel
> 2007. You could also have run into the C/C++ runtime versioning and
> distribution problems which were introduced in Visual Studio 2005.
> Maybe your Excel 2007 was the development machine and happened to have
> the right version of the C runtime
>
> I changed to an unmanaged loader to control these problems better, and
> to deal with cases where the wrong version of .Net is already loaded
> into the process, or where .Net is not even present on the machine. In
> these cases Excel would give a rather unhelpful message saying that
> the add-in is not a valid add-in, and helpfully offering to open
> the .dll as a text file. I can now give nice messages and informative
> messages if the add-in loading fails, and have no dependency on the C
> runtime (it could not be statically linked in the IJW scenario).
>
> In addition, I wanted to load the add-ins into separate AppDomains,
> and hoped to manage the runtime hosting so that errors like stack
> overflows are not fatal to the Excel process. I got the AppDomain part
> right, but the CLR hosting interfaces don't work as documented (or as
> I expect) when running in Excel, and I don't know where to even start
> getting help with this. So it is still possible to crash Excel from
> your add-in, which irritates me a bit.
>
> Anyway, I'll keep your suggestion for a better RegisterMethods in mind
> for the next version - maybe you could add it as an issue in CodePlex
> (http://exceldna.codeplex.com)?But it sounds like you are sorted out
> version of .Net 2.0, which basically broke the .Net loading in Office
> apps. This might be the problem you ran into with Excel 2003 vs. Excel
> 2007. You could also have run into the C/C++ runtime versioning and
> distribution problems which were introduced in Visual Studio 2005.
> Maybe your Excel 2007 was the development machine and happened to have
> the right version of the C runtime
>
> I changed to an unmanaged loader to control these problems better, and
> to deal with cases where the wrong version of .Net is already loaded
> into the process, or where .Net is not even present on the machine. In
> these cases Excel would give a rather unhelpful message saying that
> the add-in is not a valid add-in, and helpfully offering to open
> the .dll as a text file. I can now give nice messages and informative
> messages if the add-in loading fails, and have no dependency on the C
> runtime (it could not be statically linked in the IJW scenario).
>
> In addition, I wanted to load the add-ins into separate AppDomains,
> and hoped to manage the runtime hosting so that errors like stack
> overflows are not fatal to the Excel process. I got the AppDomain part
> right, but the CLR hosting interfaces don't work as documented (or as
> I expect) when running in Excel, and I don't know where to even start
> getting help with this. So it is still possible to crash Excel from
> your add-in, which irritates me a bit.
>
> Anyway, I'll keep your suggestion for a better RegisterMethods in mind
> for the next version - maybe you could add it as an issue in CodePlex
>
> - Show quoted text -...
>
> read more »
hmd
May 29, 2009, 12:04:36 PM5/29/09
to ExcelDna
Hi Govert. hi Simon.
I also think it would be great to have more control over the
registration process.
There are 2 points, where it would be useful for my project:
* Dealing with localized function and parameter descriptions.
* Hiding functions from the function wizard, that are not supported by
the current backend.
I tried modifying ExcelDna to get some kind of callback into my
assembly before the actually registering functions, where I could
modify the XlMethodInfo and XlParameterInfo.
Well, results are quite ugly: I had to expose parts from the Loader
assembly publicly and pass an additional parameter all the way down
the registration process. It works for the 2 cases described above,
but can not be considered a general solution :(
I personally don’t like the idea of having to deal with xlfRegister or
output my own il. ExcelDna does a really great job at it. So why
should I care to built it on my own?
Some thoughts about the registration process:
Registering all static public methods is not the best strategy in
every case. Often you have to hide methods by making them internal,
because you do not want them to be exposed in Excel.
Maybe let the user choose, whether he (1) want to use all methods, or
(2) only methods decorated with [ExcelFunction] or [ExcelCommand] or
(3) do not have anything registered.
Now having a RegisterMethods that takes an XlMethodInfo, and maybe
even a way to unregister a method, would allow the user to take full
control over the registration. This means, registering functions in
AutoOpen() or even at runtime.
With regards,
Martin
I also think it would be great to have more control over the
registration process.
There are 2 points, where it would be useful for my project:
* Dealing with localized function and parameter descriptions.
* Hiding functions from the function wizard, that are not supported by
the current backend.
I tried modifying ExcelDna to get some kind of callback into my
assembly before the actually registering functions, where I could
modify the XlMethodInfo and XlParameterInfo.
Well, results are quite ugly: I had to expose parts from the Loader
assembly publicly and pass an additional parameter all the way down
the registration process. It works for the 2 cases described above,
but can not be considered a general solution :(
I personally don’t like the idea of having to deal with xlfRegister or
output my own il. ExcelDna does a really great job at it. So why
should I care to built it on my own?
Some thoughts about the registration process:
Registering all static public methods is not the best strategy in
every case. Often you have to hide methods by making them internal,
because you do not want them to be exposed in Excel.
Maybe let the user choose, whether he (1) want to use all methods, or
(2) only methods decorated with [ExcelFunction] or [ExcelCommand] or
(3) do not have anything registered.
Now having a RegisterMethods that takes an XlMethodInfo, and maybe
even a way to unregister a method, would allow the user to take full
control over the registration. This means, registering functions in
AutoOpen() or even at runtime.
With regards,
Martin
Govert van Drimmelen
Jun 1, 2009, 5:14:24 AM6/1/09
to ExcelDna
Hi guys,
Thanks for the feedback - I really appreciate it. You can also add
specific requests to the issue tracker on the CodePlex site (http://
exceldna.codeplex.com).
I have a medium-term plan to refactor the ExcelDna insides to make
this kind of thing much neater.
I plan to make the ExcelDna.Loader module much smaller - essentially
just containing the unsafe portions and the marshaling of the basic
types, so that XLOPER can effectively stay hidden in there.
Stuff like reading the assemblies, building the IL wrappers and doing
the registration will all move into ExcelDna.Integration.
Thirdly, I'd like to build in some extensions points so that an add-in
can control exactly which functions are exported, how types are mapped
or marshaled, and have the ability to add general wrapper functions to
support things like object handles and async calls.
It all sounds a bit ambitious, but I can't see how to get there
without a fairly big initial leap. I've started out trying some ideas,
but I'm not sure when I'll have time to go further.
So all your suggestions and requests are very welcome to firm up my
ideas.
--Govert--
Thanks for the feedback - I really appreciate it. You can also add
specific requests to the issue tracker on the CodePlex site (http://
exceldna.codeplex.com).
I have a medium-term plan to refactor the ExcelDna insides to make
this kind of thing much neater.
I plan to make the ExcelDna.Loader module much smaller - essentially
just containing the unsafe portions and the marshaling of the basic
types, so that XLOPER can effectively stay hidden in there.
Stuff like reading the assemblies, building the IL wrappers and doing
the registration will all move into ExcelDna.Integration.
Thirdly, I'd like to build in some extensions points so that an add-in
can control exactly which functions are exported, how types are mapped
or marshaled, and have the ability to add general wrapper functions to
support things like object handles and async calls.
It all sounds a bit ambitious, but I can't see how to get there
without a fairly big initial leap. I've started out trying some ideas,
but I'm not sure when I'll have time to go further.
So all your suggestions and requests are very welcome to firm up my
ideas.
--Govert--
Reply all
Reply to author
Forward
0 new messages