How to display an array of POCO in Excel-Dna from .NET assembly

Question

I got a simple POCO "Employee" class as below.

public class Employee
{
    public string Name { get; set; }
    public int Age { get; set; }
    public double Salary { get; set; }
    public string Address { get; set; }
}

I create an array of that class. I registered this assembly to excel using excel dna. When the below method "GetEmployeeAsync" is invoked from excel, I am getting error as "#VALUE!" in every excel cell. How can I get that displayed in ExcelDna? When I return that as an object[] instead of Poco type, it works. Is it a limitation with Excel-Dna? Is it possible to return .NET types to excel through excel-dna?

ExcelFunction(Description = ".NET function Return Employee Details")]
public static object GetEmployeeAsync(string name)
{
    var empList= new List<Employee>()
    {
        new Employee {Name = name, Age = 30, Salary = 20.0, Address = "London"},
        new Employee {Name = name, Age = 30, Salary = 20.0, Address = "London"},
        new Employee {Name = name, Age = 30, Salary = 20.0, Address = "London"},
        new Employee {Name = name, Age = 30, Salary = 20.0, Address = "London"},
        new Employee {Name = name, Age = 30, Salary = 20.0, Address = "London"},
        new Employee {Name = name, Age = 30, Salary = 20.0, Address = "London"},
        new Employee {Name = name, Age = 30, Salary = 20.0, Address = "London"},
    };

    return empList;
}

_"I am getting error"_ - read [ask], research that error, show what you have tried and show the actual error. — CodeCaster, Apr 20 '18 at 09:37
@CodeCaster This is specific to excel-dna library. I have updated the question — Hunter, Apr 20 '18 at 09:50

score 1 · Accepted Answer · answered Apr 20 '18 at 12:20

Excel-DNA has built-in support only for .NET types that match types natively supported by the Excel C API. To support additional data types there needs to be some conversion - in this case from List<Employee> to an object[,] array. You can either do the conversion yourself, or you can use the Excel-DNA Registration helper library to register a conversion that will by applied at runtime. You could also implement a generic conversion that uses reflection to expose all the properties of your class, or something like that. But there is nothing like this 'in the box' currently.

For general Excel-DNA usage questions like this, the Excel-DNA Google group is best.

How to display an array of POCO in Excel-Dna from .NET assembly

1 Answers1