Using Excel as input/output platform for C# with Excel-DNA

This posting is presenting one cool way to use Excel-DNA. Within this scheme, we are not going to create XLL Addin worksheet function to be used in Excel, but instead we will use Excel only as a platform for data input/output for C#. This might be very useful option, if we have C# program and just want to feed it with input parameters from Excel worksheet and then return C# calculation results back to Excel for further use or analysis.

PROJECT RESULT

The end result of this small example project will be a simple C# program, which reads data from named Excel ranges, uses this data in C# program and finally returns calculation results back to named Excel ranges. For this project, I have been using Visual Studio 2010 Express with Framework 4.0.

PREPARATORY TASKS

Download and unzip Excel-DNA Version 0.30 zip file to be ready when needed. There is also a step-by-step word documentation file available within the distribution folder. In this project, we are going to follow these instructions.

STEP ONE : C# function

Create a new C# Class project "ExcelInterface". Rename created default "Class1.cs" to be "ExcelInterface.cs". For the class ExcelInterface.cs, copy the code in the box below and replace the existing code with this one.

Since we might need to use Windows Forms object in our program (MessageBox in Catch block), we need to create reference to System.Windows.Forms library (Project - Add reference - .NET - System.Windows.Forms). After this, build solution and save it.

You'll see a lot of errors at this point, but do not mind - those will disappear completely, as we create reference to Excel-DNA library next.

As we see, the only static class method execute (void) is not returning any value. Instead, it creates Excel application, reads data from named Excel ranges and after performing some calculations (discount factor df from r and t) or modifications with the data (multiplying matrix items with the value of pi), it writes results back to named Excel ranges. Note, that for objects typed with C# keyword  dynamic, we are not going enjoy any intellisense support. This means, that you have to be comfortable with Excel Object model (Application, Workbooks, Worksheets, Range, etc).

using System;
using ExcelDna.Integration;
using System.Windows.Forms;
//
namespace ExcelInterface
{
    public static class ExcelInterface
    {
        static double pi = Math.PI;
        //
        public static void execute()
        {
            try
            {
                // create Excel application object
                dynamic Excel = ExcelDnaUtil.Application;
                //
                // read values from named ranges
                double r = (double)Excel.Range["_r"].Value2;
                double t = (double)Excel.Range["_t"].Value2;
                //
                // calculate discount factor and return it to named range
                double df = Math.Exp(-r * t);
                Excel.Range["_df"] = df;
                //
                // read matrix from named range, define its dimensions and
                // create output 2-dim array
                dynamic Matrix = Excel.Range["_matrix"].Value2;
                int rows = Matrix.GetUpperBound(0);
                int columns = Matrix.GetUpperBound(1);
                double[,] result = new double[rows, columns];
                //
                // loop through matrix items and multiply each item with the value of pi
                for (int i = 0; i < rows; i++)
                {
                    for (int j = 0; j < columns; j++)
                    {
                        result[i, j] = (double)Matrix.GetValue(i + 1, j + 1) * pi;
                    }
                }
                //
                // return result array into named range
                Excel.Range["_matrix"] = result;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message.ToString());
            }
        }
    }
}

STEP TWO: Excel-DNA

Add reference to Excel-DNA library (Project - Add reference - Browse - \\ExcelDna.Integration.dll) and click OK. This dll file is inside the distribution folder what we just downloaded from Excel-DNA  website. From the properties of this reference, set Copy Local to be False.

Add new file as text file to project (Project - Add new item - Text file) and name it to be ExcelInterface.dna. CopyPaste the following xml code into this file.

<DnaLibrary Name="ExcelInterface" RuntimeVersion="v4.0">
  <ExternalLibrary Path="ExcelInterface.dll" />
</DnaLibrary>

From the properties of this dna file, set Copy to Output Directory to be Copy if newer.

Next, from the downloaded Excel-DNA folder (Distribution), copy ExcelDna.xll file into your project folder (\\Projects\ExcelInterface\ExcelInterface) and rename it to be ExcelInterface.xll. Then, add this xll file into your current project (Project - Add existing item). At this point, it might be that you do not see anything else, except cs files on this window. From drop down box on the bottom right corner of this window, select All files and you should see ExcelInterface.xll file what we just pasted into this ExcelInterface folder. Select this file and press Add. Finally, from the properties of this xll file, set Copy to Output Directory to be Copy if newer.

Build the solution. Everything should have gone well without any errors or warnings. At this point, my \\ExcelInterface\bin\Release folder looks like the following.

At this point, we are done with C# and Excel-DNA.

STEP THREE: Excel named ranges and "trigger program" for starting C# program

Open a new Excel workbook. From Excel Name Manager (Formulas - Name Manager), set range names (_r, _t, _df and _matrix) and initial values to these ranges as shown in the picture below.

Finally, we need to have "triggering program", which will start our actual C# program. In Excel workbook, open VBA editor. Insert a new standard VBA module and copyPaste the following code into this module.

Option Explicit
'
Sub tester()
    Application.Run ("execute")
End Sub
'

While this workbook is still open, doubleClick ExcelInterface.xll file in your \\ExcelInterface\bin\Release folder. After this, xll file content can be used by Excel and our C# function is available to be called from VBA program (Application.Run). VBA program tester will call and start C# program execute, which reads all input data, performs calculations and sends result data back to worksheet. We could also have one ActiveX button in our worksheet and then use Application.Run for starting C# program inside button event handler.

Run the program and we are done.

For learning more things about Excel-DNA, check out its homepage. Getting more information and examples with your problems, the main source is Excel-DNA google group. For those who would like to see useful examples using Excel-DNA in financial programs, there is an excellent book C# for Financial Markets (chapter 22) written by Daniel Duffy and Andrea Germani (published 2013). Finally, Excel-DNA is an open-source project, and we (the happy users) can invest its future development by making a donation.

Thanks again for reading.

-Mike