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
