Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Excel-Dna C# How to get values from selected range in excel worksheet

Tags:

c#

excel-dna

I am trying to get familiar with Excel-DNA but cannot find documentation on how I could loop through a selected range of values in worksheet cells. So, I would have a user defined function which would take as parameters a range of cells where I would have some data. Then I would loop trough this range of cells and do something with the data. How can I do this kind of basic operation? My code in Visual Studio could look something like this.

using System;
using System.Collections.Generic;
using ExcelDna.Integration;

namespace myUDF
{
    public static class Class1
    {
        [ExcelFunction(Name = "LoopArrayTester")]
        public static List<double> LoopArrayTester(??? range)
        {
            List<double> list = new List<double>();

            // loop through somehow the range in worksheet given
            // somehow in the method signature

            for(int i = 0; i < range.count; i++)
            {
                // get values of i'th cell in range and put it to list
                // or something.
            }
        }

        return list;
    }
}
like image 636
Markus Avatar asked Aug 30 '18 12:08

Markus


People also ask

What is Excel DNA?

Excel-DNA is an independent project to integrate . NET into Excel. With Excel-DNA you can make native (. xll) add-ins for Excel using C#, Visual Basic.NET or F#, providing high-performance user-defined functions (UDFs), custom ribbon interfaces and more. Your entire add-in can be packed into a single .

What is ExcelDna xfunctions64 xll?

ExcelDna. XFunctions. xll is a small add-in that implements two user-defined functions - XLOOKUP and XMATCH - that are compatible with the newly announced built-in functions.

How do I open an xll file in Excel?

XLL files can be opened with Microsoft Excel. If double-clicking an XLL file doesn't open it in Excel, you can do it manually via the File > Options menu. Select the Add-ins category and then choose Excel Add-ins in the Manage drop-down box. Choose the Go button and then Browse to locate it.


1 Answers

Easiest is to let your function declare the parameter as type object[,]. Then you'll get an array with the values from the input range. Your code might look like this:

public static object Concat2(object[,] values)
{
    string result = "";
    int rows = values.GetLength(0);
    int cols = values.GetLength(1);
    for (int i = 0; i < rows; i++)
    {
        for (int j = 0; j < cols; j++)
        {
            object value = values[i, j];
            result += value.ToString();
        }
    }
    return result;
}

Typically you'd want to check the type of the value object, and do something different based on that. The object[,] array passed from Excel-DNA could have items of the following types (depending on the data type of the values in the respective cells):

  • double
  • string
  • bool
  • ExcelDna.Integration.ExcelError
  • ExcelDna.Integration.ExcelEmpty
  • ExcelDna.Integration.ExcelMissing (if the function is called with no parameter, as =Concat2()).
like image 59
Govert Avatar answered Oct 10 '22 01:10

Govert