I want to create user-defined formula in Excel addin by ExcelDna ,and before I get the api response I don't know the name of the formula.All of the formula is just like that(It's worked good):
public static object GETROOT(object Param1, object Param2)
{
GetApiResult("001659ca6e3ed001d9a9be59d903a15c", new object[]
{
Param1,
Param2
});
return "LOADING";
}
The GetApiResult
is an asynchronous function to get some data from server,and the fist parameter is a string.Based on that limits,I tried to create a DynamicMethod
to reach my goal,this is my code:
public static void UDFCreate(Detail detail)
{
#region
string APIID = detail.apiIndDetail.id; //
string name = detail.apiIndDetail.formula;//
string description = detail.apiIndDetail.name;//
List<string> paramList = new List<string>();//
var inputList = detail.apiIndInputs; //
foreach (var input in inputList)
{
paramList.Add(input.code);
}
Type[] types = new Type[paramList.Count];
foreach (string param in paramList)
{
int index = paramList.IndexOf(param);
types[index] = typeof(object);
}
#endregion
DynamicMethod mult = new DynamicMethod(name, typeof(object), types, typeof(UDFGenerator));
ILGenerator il = mult.GetILGenerator();
//EmitTwo(il, paramList, APIID);
il.Emit(OpCodes.Nop);
il.Emit(OpCodes.Ldarg_0);
il.Emit(OpCodes.Ldstr, APIID);
il.Emit(OpCodes.Ldc_I4, paramList.Count);
il.Emit(OpCodes.Newarr, typeof(object));
foreach (var param in paramList)
{
int index = paramList.IndexOf(param);
il.Emit(OpCodes.Dup);
il.Emit(OpCodes.Ldc_I4, index);
il.Emit(OpCodes.Ldarg_S, index + 1);
il.Emit(OpCodes.Stelem_Ref);
}
MethodInfo mi = typeof(UDFGenerator).GetMethod(nameof(UDFGenerator.GetApiResult));
il.Emit(OpCodes.Call, mi);
il.Emit(OpCodes.Nop);
il.Emit(OpCodes.Ldstr, "LOADING");
il.Emit(OpCodes.Ret);
List<Delegate> delegates = new List<Delegate>();
delegates.Add(mult.CreateDelegate(typeof(Func<object, object, object>)));
ExcelFunctionAttribute att = new ExcelFunctionAttribute
{
Name = name,
Description = description,
IsMacroType = true,
};
List<object> funcAttribs = new List<object>
{
att
};
List<object> argAttribs = new List<object>();
foreach (var input in inputList)
{
ExcelArgumentAttribute atta = new ExcelArgumentAttribute();
atta.Name = input.name;
argAttribs.Add(atta);
}
List<List<object>> argAttribsList = new List<List<object>>();
argAttribsList.Add(argAttribs);
try
{
ExcelIntegration.RegisterDelegates(delegates, funcAttribs, argAttribsList);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
Finally,the formula is created, but when i use it in excel, there always return the error A value used in the formula is the wrong data type
enter image description here