Excel-DNA
This is an open source project that lets you create native (.xll) add-ins for Excel using C#, VB.Net or F#.
The Excel C API is not directly accessible from .NET code but the Excel-DNA runtime allows managed assemblies to integrate with this low-level Windows API.
This lets you create high performance user-defined functions and requires no installation or registration
Allowing a user to install and run on a machine without admin access can be extremely useful.
Useful Links
link - docs.excel-dna.net/
link - nuget.org/packages/Excel-Dna
link - nuget.org/packages/ExcelDna.Integration/
link - github.com/Excel-DNA/ExcelDna
link - groups.google.com/g/exceldna
Supported Features
64-bit versions of Excel
Multi-threaded recalculation (Excel 2007 and later)
Custom Ribbon Interfaces (Excel 2007 and later)
RTD Servers (registration free)
Custom Task Panes (Excel 2007 and later)
Offloading UDF computations to a Windows HPC cluster (Excel 2010 and later)
Supports Two Types of async functions:
Type 1 - RTD Based Async
Uses ExcelAsyncUtil
Can interact with Excel while the function executes
This uses an internal RTD server internally.
Type 2 - Native Async
Only works with Excel 2010 or later
Cannot interact with Excel while the function executes
Can continue with other parts of the calculation tree while the function executes.
Function Declarations
You are able to define the function wizard category as well as the function description and argument descriptions.
You can set the function wizard category as well as the function and argument descriptions.
You can even provide a tooltip
These customisations can be made by adding .NET attributes to the function declarations.
You need to reference the ExcelDna.Integration.dll assembly.
You do not need to redistribute the ExcelDna.Integration file - a copy is embedded as a resource.
[ExcelDna.Integration.ExcelFunction(Description="HelloWorld", Name="myFunction")]
public static string MyFunction([ExcelArgument(Description=@"Text Value")] string sText)
{
return sText + sText;
}
AfterCalculate Event
Add a Reference to the Excel Interop.
Assemblies, Extensions: "Microsoft.Office.Interop.Excel" (version 15.0.0.0)
Register an event handler for the AfterCalculate event.
The "AppEvents_AfterCalculateEventHandler" will not appear in the intellisense.
using Excel = Microsoft.Office.Interop.Excel;
public class MyAddin : ExcelDna.Integration.IExcelAddIn
{
public void AutoOpen()
{
Excel.Application xlApp;
xlApp = (Excel.Application)ExcelDna.Integration.ExcelDnaUtil.Application;
xlApp.AfterCalculate += new Excel.AppEvents_AfterCalculateEventHandler(Method_RunAfterCalculate)
}
// only called when the user unticks the add-in and not when Excel closes
public void AutoClose()
{
}
[ExcelDna.Integration.ExcelFunction(Description="HelloWorld", Name="myFunction")]
public static string MyFunction([ExcelArgument(Description=@"Text Value")] string sText)
{
Method_PreProcessing((Excel.Application)ExcelDna.Integration.ExcelDnaUtil.Application);
return sText + sText;
}
// check settings, initialise start time, clear cache
private static void Method_PreProcessing(Excel.Application xlApp)
{
int workerThread;
int ioThread;
int availableWorkerThread;
int availableIOThread;
if (!_bStartRefresh)
{
// get diagnostics info
System.Threading.ThreadPool.GetMinThreads(out workerThread, out ioThread)
System.Threading.GetAvailableThreads(out availableWorkerThread, out availableIOThread)
ExcelDna.Integration.ExcelAsyncUtil.QueueAsMacro(() => logSomething());
lock (_oSingletonLock)
{
xlApp.AfterCalculate -= _afterCalcHandler;
xlApp.AfterCalculate += _afterCalcHandler;
}
}
}
private static void Method_RunAfterCalculate()
{
}
}
Caching Data
namespace MyNewXLL
{
public class MyAddin : ExcelDna.Integration.IExcelAddIn
{
private static bool _bStartRefresh;
private static int _iRefreshCount;
private static DateTime _dtRefreshStartTime;
private static readonly object _oSingletonLock = new Object();
private static Excel.AppEvents_AfterCalculateEventHandler _afterCalcHandler =
new Excel.AppEvents_AfterCalculateEventHandler(Method_AutoSizeColumns)
public void AutoOpen()
{
Excel.Application xlApp;
xlApp = (Excel.Application)ExcelDna.Integration.ExcelDnaUtil.Application;
xlApp.CalculationInterruptKey = Excel.CalculationInterruptKey.xlNoKey;
xlApp.AfterCalculate += _afterCalcHandler;
Method_ClearCache();
ExcelDna.Integration.ExcelIntegration.RegisterUnhandledExceptionHandler(ex => "EXCEPTION: " + ex.ToString());
}
// only called when the user unticks the add-in and not when Excel closes
public void AutoClose()
{
Method_ClearCache();
}
[ExcelDna.Integration.ExcelFunction(Description="HelloWorld", Name="myFunction")]
public static string MyFunction([ExcelArgument(Description=@"Text Value")] string sText)
{
Method_PreProcessing((Excel.Application)ExcelDna.Integration.ExcelDnaUtil.Application);
return sText + sText;
}
private static void Method_ClearCache()
{
}
// check settings, initialise start time, clear cache
private static void Method_PreProcessing(Excel.Application xlApp)
{
int workerThread;
int ioThread;
int availableWorkerThread;
int availableIOThread;
if (!_bStartRefresh)
{
// get diagnostics info
System.Threading.ThreadPool.GetMinThreads(out workerThread, out ioThread)
System.Threading.GetAvailableThreads(out availableWorkerThread, out availableIOThread)
ExcelDna.Integration.ExcelAsyncUtil.QueueAsMacro(() => logSomething());
lock (_oSingletonLock)
{
AppDomain domain = getDefaultDomain();
domain.SetData("REFRESH KEY", true);
_bStartRefresh = true;
_iRefreshCount = 1;
_dtRefreshStartTime = System.DateTime.Now();
xlApp.AfterCalculate -= _afterCalcHandler;
xlApp.AfterCalculate += _afterCalcHandler;
Method_ClearCache();
}
}
}
private static void Method_AutoSizeColumns()
{
}
}
Registry Keys
Need to add a registry key
HKCU\Software\Microsoft\Office\12.0\Excel\Options\ OPEN1 - /R "MyXLL.dll"
If the add-in is not in the default add-ins folder then the full path must be provided
The Excel-DNA runtime contains a small loader (the .XLL) that loads the .NET runtime, then checks the configuration (.dna) file and accordingly loads the managed assembly.
The managed assemblies are then inspected using the .NET Reflection API and the appropriate methods are registered.
Reference
ExcelDna.Integration.IExcelAddIn
ExcelDna.Integration.DnaLibrary
ExcelDna.Integration.ExcelArgument
ExcelDna.Integration.ExcelArgumentAttribute
ExcelDna.Integration.ExcelAsyncHandle
ExcelDna.Integration.ExcelAsyncUtil
QueueAsMacro
Run
ExcelDna.Integration.ExcelComAddIn
ExcelDna.Integration.ExcelComAddInHelper
ExcelDna.Integration.ExcelCommandAttribute
ExcelDna.Integration.ExcelDnaUtil
Application - the correct Application root object
ExcelLimits
ExcelVersion
MainManagedThreadId
SupportsDynamicArrays
WindowHandle
XllPath
XllPathInfo
ExcelDna.Integration.ExcelEmpty
ExcelDna.Integration.ExcelError
ExcelErrorNA
ExcelDna.Integration.ExcelErrorUtil
ExcelDna.Integration.ExcelFunctionAttribute
Description = ""
Name = ""
IsMacroType = false
ExcelDna.Integration.ExcelIntegration
RegisterUnhandledExceptionHandler
ExcelDna.Integration.ExcelLimits
ExcelDna.Integration.ExcelMissing
ExcelDna.Integration.ExcelReference
ExcelDna.Integration.ExcelSynchronizationContext
ExcelDna.Integration.ExternalLibrary
ExcelDna.Integration.IExcelObservable
ExcelDna.Integration.IExcelObserver
ExcelDna.Integration.Image
ExcelDna.Integration.Integration
ExcelDna.Integration.Project
ExcelDna.Integration.Reference
ExcelDna.Integration.SourceItem
ExcelDna.Integration.XlCall
Excel
xlfActiveCell
xlfCaller
xlfGetWorkspace
xlfSelection
xlcEcho
xlcFormatNumber
xlcFormulaGoto
xlcSelect
xlcWorkbookSelect
xlSheetNm
ExcelDna.Integration.XlCallException
ExcelDna.Integration.XlLibrary
© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopPrevNext