I've posted a minor update. Download ExcelDna-0.5.zip
29 June 2006 | Permalink | Comments (3)
ExcelDna 0.3
I have uploaded a new version of ExcelDna: ExcelDna-0.3.zip
Changes for this version:
Excel 2007
I have been following the information about Excel 2007. Some relevant blog links:
On the client side there will be no enhanced support in Excel 2007 for managed add-ins, but support for .xll add-ins have been significantly improved. I think it will be easy to incorporate these advances into ExcelDna, allowing seamless access to functionality like the large sheet, long strings and multi-threaded recalculation. I think using the .xll interface is looking very much like the right plan. I'll be adding support for the Excel 2007 functionality when users start asking, and when I have a version to test against. For now it is not an urgent issue for me, so let me know if you need this and can help, at least with the testing.
There will also be a server counterpart to Excel 2007, called Excel Services. Excel Services will not support VBA code or .xll add-ins directly. However there is a new model for user-defined functions using managed code (lots more info in the second link above).
For ExcelDna I have added the ability to directly use these Managed Add-Ins in the client. Reference the .dll containing the UDFs using the normal ExternalLibrary construct, making sure you have a copy of the Microsoft.Office.Excel.Server.Udf.dllin the same directory as the .xll or in your GAC. The UdfClass marked classes and UdfMethod marked methods will be exposed to Excel through ExcelDna as usual. I think this is a pretty nice solution for creating Managed Add-Ins that work in both Excel Services and the Excel Client (and any version of the client, not just Excel 2007). Let me know if you can confirm that it works. Currently MS suggests that you cross-compile a C++ library to managed and unmanaged code ;-)
As always I welcome your feedback.
28 May 2006 | Permalink | Comments (1)
I have made a minor update of the distribution, to fix a problem reported on a localised version of Excel. In particular, using Tools->Add-Ins to add the .xll would fail, even though opening the add-in using File->Open worked fine.
Related to the fix, I now use a Name attribute on the DnaLibrary tag in the .dna file to customise the library name. If there is no such tag, the name of the library is the filename (without extension). The library name is used as the default category and menu name, and appears in the Add-Ins dialog box.
10 February 2006 | Permalink | Comments (0)
Here is a .dna file giving examples of different aspects of integration with Excel:
<DnaLibrary Description="Sample Add-In">
<!-- This library has two projects, one in C#, the other in VB -->
<Project Language="CS">
<Reference AssemblyPath="System.Windows.Forms.dll"/>
<![CDATA[
using System;
using System.Windows.Forms;
using ExcelDna.Integration;
// This class implements the ExcelDna.Integration.IExcelAddIn interface.
// This allows the add-in to run code at start-up and shutdown.
public class MyAddIn : IExcelAddIn
{
public void AutoOpen()
{
MessageBox.Show("Now in AutoOpen.");
}
public void AutoClose()
{
MessageBox.Show("Now in AutoClose.");
}
} public class MyFunctions
{
// This function returns a string that describes its argument.
// For arguments defined as object type, this shows all the possible types that may be received.
// Also try this function after changing the
// [ExcelArgument(AllowReference=true)] attribute.
// In that case we allow references to be passed (registerd as type R).
// By default the function will be registered not
// to receive references AllowReference=false (type P).
[ExcelFunction(Description="Describes the value passed to the function.", IsMacroType=true)]
public static string Describe([ExcelArgument(AllowReference=false)]object arg)
{
if (arg is double)
return "Double: " + (double)arg;
else if (arg is string)
return "String: " + (string)arg;
else if (arg is bool)
return "Boolean: " + (bool)arg;
else if (arg is ExcelError)
return "ExcelError: " + arg.ToString();
else if (arg is object[,])
// The object array returned here may contain a mixture of different types,
// reflecting the different cell contents.
return string.Format("Array[{0},{1}]", ((object[,])arg).GetLength(0), ((object[,])arg).GetLength(1));
else if (arg is ExcelMissing)
return "Missing";
else if (arg is ExcelEmpty)
return "Empty";
else if (arg is ExcelReference)
return "Reference: " + XlCall.Excel(XlCall.xlfReftext, arg, true);
else
return "!?Unheard Of";
} // ExcelDna makes calling the Excel API easy:
// XlCall.Excel works like Excel4, but you just pass the parameters
// - no need for horrible XLOPERs
public static double CalcSin(double angle)
{
return (double)XlCall.Excel(XlCall.xlfSin, angle);
} [ExcelFunction(IsMacroType=true)]
public static object GetTheXllName()
{
return XlCall.Excel(XlCall.xlfGetWorkspace, 44);
} // One dimensional arrays are rows.
[ExcelFunction(Description="Sorts the given vector")]
public static double[] SortVector(double[] vector)
{
Array.Sort(vector);
return vector;
} // Arguments can have descriptions too
[ExcelFunction(Description="Another adding function.", Category="New Functions")]
public static double AddTwo([ExcelArgument(Description="First one!")]double a, [ExcelArgument(Description="OtherOne!", Name="SecondArg")]double b)
{
return a + b;
} // Arrays work as array formulae
// This returns a 2-D array with one column
// and two rows.
public static object GetSomeStuff()
{
return new object[,]{ {3} , {"abc"} };
} // This function will return (accept) only values larger than the current cell contents.
// Put =IncreaseValue(B1) into A1.
// Then put different numbers into B1 and watch how A1 changes.
[ExcelFunction(IsMacroType=true)]
public static double IncreaseValue(double newValue)
{
ExcelReference reference = (ExcelReference)XlCall.Excel(XlCall.xlfCaller);
object val = reference.GetValue();
if (val is double && (double)val > newValue)
return (double)val;
return newValue;
} // This is a macro that sets a block of cells
public static void SetSome()
{
ExcelReference r = new ExcelReference(2,5,3,6);
bool ok = r.SetValue(new object[,]{{3.4, 8.9}, {"Wow!", ExcelError.ExcelErrorValue}});
}
}
]]>
</Project>
<Project Language="VB">
<Reference AssemblyPath="System.Windows.Forms.dll"/>
<![CDATA[
Imports System.Windows.Forms
Public Module MyTestFunctions
' Some functions might be quite slow, so should not calculate
' in the function wizard.
Function SlowFunction(val)
If Excel.IsInFunctionWizard() Then
SlowFunction = "#IN WIZARD"
Else
SlowFunction = val * 2
End If
End Function ' There is easy access to the Excel automation object.
' ExcelDna.Integration.Excel.Application returns a reference
' to the Excel Application COM object.
' This can be used late-bound from VB, or using the PIA from C#
Function GetTheExcelVersion() as string
GetTheExcelVersion = Excel.Application.Version
End Function Function DoLater() as String
Excel.Application.OnTime(DateTime.Now, "RingBell")
DoLater = "#WAIT!"
End Function ' An easy way to add a menu and menu items for a command....
' This command sounds a tone and sets cell C3 to the value 42
<ExcelCommand(MenuName:="&My Menu", MenuText:="Ring &Bell")> _
Sub RingBell()
Console.Beep()
Dim r as New ExcelReference(3,3)
r.SetValue(42)
End Sub <ExcelCommand(MenuName:="&My Menu", MenuText:="Say &Hello")> _
Sub SayIt()
MessageBox.Show("Hellooooo!")
End Sub ' Commands that don't appear on a menu, can be assigned to a button
' or called from Tools->Macros->Macro and typing in the name.
Sub SayBye()
MessageBox.Show("Cheers!")
End Sub
End Module
]]>
</Project>
</DnaLibrary>24 January 2006 | Permalink | Comments (5)
Do this first:
Get ExcelDna: Version 0.20 on CodePlex , unzip in a convenient directory.
The .NET 2.0 runtime must be installed. The .NET Framework Version 2.0 Redistributable Package is available from Microsoft.
Macros security in Excel must not be 'Very High' or 'High' (if set to Medium -- it will prompt whether to enable each macro library). To use the .NET macros you will have to 'Enable' at the prompt.
1. Create a user-defined function in Visual Basic
Create a new text file, called Test1.dna (the same prefix as the .xll file), with contents:
<DnaLibrary>
<![CDATA[
Public Module MyFunctions
Function AddThem(x, y)
AddThem = x + y
End Function
End Module
]]>
</DnaLibrary>
Load Test1.xll in Excel (either File->Open or Tools->Add-Ins and Browse).
Troubleshooting
2. Creating a user-defined function in C#
<DnaLibrary Language="CS">
<![CDATA[
using ExcelDna.Integration;
public class MyFunctions
{
[ExcelFunction(Description="Joins a string to a number", Category="Useful functions")]
public static string JoinThem(string str, double val)
{
return str + val;
}
}
]]>
</DnaLibrary> 3. Making the functions from a compiled library available
ExcelDna can also load any compiled .NET library. Public static functions with a compatible signature are exported to Excel.
Create a file called TestLib.cs containing the following code:
using ExcelDna.Integration;
public class MyFunctions
{
[ExcelFunction(Description="Multiplies two numbers", Category="Useful functions")]
public static double MultiplyThem(double x, double y)
{
return x * y;
}
}
Compile TestLib.cs to TestLib.dll: from the command-line: c:\windows\microsoft.net\framework\v2.0.50727\csc.exe /target:library /reference:ExcelDna.Integration.dll TestLib.cs
Modify Test1.dna to contain:
<DnaLibrary>
<ExternalLibrary Path="TestLib.dll" />
</DnaLibrary>Reload the .xll and check =MultiplyThem(2,3)
19 January 2006 | Permalink | Comments (18)
ExcelDna brings .NET to Excel power users and developers. The primary target is the Excel user who currently writes VBA code for functions and macros, and would like to start using .NET. An additional audience is C/C++ -based .xll add-in developers who want to use the .NET framework to develop their add-ins.
ExcelDna is free for all use, and distributed under a permissive open-source license that also allows commercial use.
ExcelDna is developed using .NET 2.0, and users have to install the freely available .NET Framework 2.0 runtime. The integration is by an Excel Add-In (.xll) that exposes .NET code to Excel. The user code can be in text-based (.dna) script files (Visual Basic or C#), or compiled .NET libraries from any language.
Current status
ExcelDna allows exporting functions and commands from .NET scripts or compiled libraries. ExcelDna also supports automatic marshaling for calling the Excel C API from .NET, making it easy to port existing C/C++ code that uses the native Excel API. Access to the Excel automation interfaces is available from ExcelDna, and can be used late-bound from Visual Basic, or via a reference to the primary interop assemblies.
ExcelDna is at an early stage of development and has not been tested extensively. This means it might not entirely work as advertised. Please be patient and careful.
Future work
There are two themes for future work. Firstly to improve the current integration .xll:
Secondly, it would be really nice to have an editor or IDE for working with .dna scripts. This would then work like the VBA IDE - maybe even with debugging some day! Idealy it would be as easy to create a user-defined function in .NET as it is in VBA.
Related projects
You can help ...
Please contact me if you need assistance, have a comment or would like to help on the project.
19 January 2006 | Permalink | Comments (9)
