Write/read dynamic matrix between Excel and C# with Excel-DNA

This time I wanted to share some quick technical information about how to write or read dynamic matrix between Excel and C# program with Excel-DNA. The scheme what I am presenting here, applies to the case in which we use Excel as input/output platform for some C# program. By saying dynamic matrix, I am referring to the case, in which the dimensions of the input or output matrix are not known at compile time.

To make the long story shorter, you can use the existing program already presented in this blog posting as a base for this new example program. Just replace the content of ExcelInterface.cs file with the information given below.

C# PROGRAM

using System;
using ExcelDna.Integration;
using System.Windows.Forms;
//
namespace ExcelInterface
{
    public static class ExcelInterface
    {
        private static dynamic Excel;
        //
        public static void execute()
        {
            try
            {
                // Create Excel application and random number generator
                Excel = ExcelDnaUtil.Application;
                Random random = new Random(Guid.NewGuid().GetHashCode());
                //
                //
                //
                // CASE 1 : WRITE DYNAMIC MATRIX TO EXCEL
                int rows = (int)Excel.Range("_rows").Value2;
                int cols = (int)Excel.Range("_cols").Value2;
                double[,] matrix = new double[rows, cols];
                //
                // fill matrix with random numbers
                for (int i = 0; i < rows; i++)
                {
                    for (int j = 0; j < cols; j++)
                    {
                        matrix[i, j] = random.NextDouble();
                    }
                }
                // clear old data from output range by using Range.CurrentRegion property
                Excel.Range["_output"].CurrentRegion = "";
                //
                // resize output range to match with the dimensions of newly created random matrix
                Excel.Range["_output"].Resize[rows, cols] = matrix;
                //
                //
                //
                // CASE 2 : READ DYNAMIC MATRIX FROM EXCEL
                object[,] input = (object[,])Excel.Range["_output"].CurrentRegion.Value2;
                rows = input.GetUpperBound(0);
                cols = input.GetUpperBound(1);
                double sum = 0.0;
                //
                // sum all matrix items, calculate average and write it back to Excel
                foreach (object item in input) sum += (double)item;
                Excel.Range["_average"] = (sum / (rows * cols));
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message.ToString());
            }
        }
    }
}

In a nutshell, after creating ExcelDnaUtil.Application, we are able to use some extremely useful properties for manipulating Excel.Range object, such as CurrentRegion, Offset and Resize.

EXCEL WORKSHEET SETUPS

Dimensions for dynamic matrix output are given by the user in cells F4 and F5. Output matrix is printed to cell B8 (top left cell of output matrix). Input matrix is the same as output matrix and the C#-processed output (arithmetic average of all input matrix values) is printed into cell F6.

We have to set a couple of named Excel ranges for C# program. All the needed four named ranges are given in the screenshot below.

After implementing these range names, create a new button (Forms Controls) to this worksheet and put our C# program name into Macro box (execute). By doing this, we can get rid of the VBA code (Application.Run) completely. Any public static void method in our .NET code will be registered by Excel-DNA as a macro in Excel. Thanks for this tip to Govert Van Drimmelen (the inventor and author of Excel-DNA). After implementing all these steps, we are ready to use the program.

The purpose of this posting was to show how to handle dynamic data IO to/from Excel in C# program with Excel-DNA. 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. Excel-DNA is an open-source project, and we (the happy users) can invest its future development by making a donation.

Thanks for reading my blog and happy waiting until Christmas.

-Mike