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;
}
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.