1. Products
  2.   Spreadsheet
  3.   .NET
  4.   ExcelDNA
 
  

Open Source .NET Library for Processing Excel Spreadsheets  

ExcelDNA allows you to create & deploy Excel Add-Ins using C#, F# or VB .NET.

Excel-DNA is an open source API to integrate .NET into Excel. The API allows you to create high-performance user-defined functions(UDFs) and more in your native (.xll) add-ins for Excel. You don't need any installation or registration, your entire add-in can be packed into a single .xll.

The Excel-DNA Runtime is free for all use and is distributed under a permissive open-source license that also allows commercial use. It is developed using .NET and users have to install the .NET Framework runtime. The Excel and the .NET code integrate with each other using Add-In (.xll) that exposes .NET code to excel. You can write your code in text-based (.dna) script files (C#, Visual Basic, or F#), or compiled .NET libraries (.dll).

Previous Next

Getting Started with Excel-DNA

The recommended way to install ExcelDNA is from NuGet, Please use the following command for faster installation.

Install ExcelDNA from NuGet

 Install-Package ExcelDna.AddIn

Create Excel Functions via .NET API

ExcelDNA gives C# .NET programmers the competency to generate Excel functions using C#. You can also use ExcelFunctionAttributes like Name, Description, Category, IsHidden, IsExceptionSafe, and more. The IsMacroType attribute changes the parameters Excel-DNA uses when registering the function.

How to Create & Use Excel Functions via .NET API

public class GetInfoFunctions
{
    [ExcelFunction(Description = "Returns the result of xlfGetCell.", IsMacroType = true)]
    public static object GetCell(int type_num, [ExcelArgument(AllowReference = true)] object reference)
    {
        return XlCall.Excel(XlCall.xlfGetCell, type_num, reference);
    }

    [ExcelFunction(Description = "Returns the result of xlfGetWorkspace.", IsMacroType = true)]
    public static object GetWorkspace(int type_num)
    {
        return XlCall.Excel(XlCall.xlfGetWorkspace, type_num);
    }

    [ExcelFunction(Description = "Returns the current list separator.", IsMacroType = true)]
    public static string GetListSeparator(int type_num)
    {
        object[,] workspaceSettings = (object[,])XlCall.Excel(XlCall.xlfGetWorkspace, 37);
        string listSeparator = (string)workspaceSettings[0, 4];
        return listSeparator;
    }
}

Support for Dynamic Arrays

The open source .NET API Excel-DNA also supports the use of dynamic arrays inside excel. When you write a dynamic array formula, it determines if the formula has the potential to return multiple values.

How to Makes an Array & Auto Resize the Result via C# API

public static object dnaMakeArrayAndResize(int rows, int columns, string unused, string unusedtoo)
        {
            object[,] result = dnaMakeArray(rows, columns);
            return ArrayResizer.dnaResize(result);

            // Can also call Resize via Excel - so if the Resize add-in is not part of this code, it should still work
            // (though calling direct is better for large arrays - it prevents extra marshaling).
            // return XlCall.Excel(XlCall.xlUDF, "Resize", result);
        }

        public static double[,] dnaMakeArrayAndResizeDoubles(int rows, int columns)
        {
            double[,] result = dnaMakeArrayDoubles(rows, columns);
            return ArrayResizer.dnaResizeDoubles(result);
        }
    }

Diagnostic Logging Support

ExcelDNA API allows .NET developers to use diagnostic logging while working with Excel Add-In. Excel-DNA uses the standard .NET System.Diagnostics.Trace mechanisms for diagnostic logging.

Log Warnings and Errors inside Add-in

public void AutoOpen()
{
    // Log warnings and errors to the Excel-DNA LogDisplay
    Trace.Listeners.Add(new LogDisplayTraceListener());
}

...
Trace.TraceInformation("Trace information!");
Trace.TraceWarning("Trace warning!");
Trace.TraceError("Trace error!");
 English