/// <summary>
/// Creates a new ExcelFunctionRegistration with the given LambdaExpression.
/// Uses the passes in attributes for registration.
///
/// The number of ExcelParameterRegistrations passed in must match the number of parameters in the LambdaExpression.
/// </summary>
/// <param name="functionLambda"></param>
/// <param name="functionAttribute"></param>
/// <param name="parameterRegistrations"></param>
public ExcelFunctionRegistration(LambdaExpression functionLambda, ExcelFunctionAttribute functionAttribute, IEnumerable <ExcelParameterRegistration> parameterRegistrations = null)
{
if (functionLambda == null)
{
throw new ArgumentNullException("functionLambda");
}
if (functionAttribute == null)
{
throw new ArgumentNullException("functionAttribute");
}
FunctionLambda = functionLambda;
FunctionAttribute = functionAttribute;
if (parameterRegistrations == null)
{
if (functionLambda.Parameters.Count != 0)
{
throw new ArgumentOutOfRangeException("parameterRegistrations", "No parameter registrations provided, but function has parameters.");
}
ParameterRegistrations = new List <ExcelParameterRegistration>();
}
else
{
ParameterRegistrations = new List <ExcelParameterRegistration>(parameterRegistrations);
if (functionLambda.Parameters.Count != ParameterRegistrations.Count)
{
throw new ArgumentOutOfRangeException("parameterRegistrations", "Mismatched number of ParameterRegistrations provided.");
}
}
// Create the lists - hope the rest is filled in right...?
CustomAttributes = new List <object>();
ReturnRegistration = new ExcelReturnRegistration();
}
public SsqExcelFunction(string name, Delegate func, ExcelFunctionAttribute excelFunctionAttribute, ExcelArgumentAttribute excelArgumentAttribute)
{
Name = name;
Delegate = func;
ExcelFunctionAttribute = excelFunctionAttribute;
ExcelArgumentAttributes = new List <object>()
{
excelArgumentAttribute
};
}
} // The method this entry was originally constructed with (may be useful for transformations).
// NOTE: 16 parameter max for Expression.GetDelegateType
public RegistrationEntry(MethodInfo methodInfo)
{
MethodInfo = methodInfo;
var paramExprs = methodInfo.GetParameters()
.Select(pi => Expression.Parameter(pi.ParameterType, pi.Name))
.ToArray();
FunctionLambda = Expression.Lambda(Expression.Call(methodInfo, paramExprs), methodInfo.Name, paramExprs);
// Need to make sure we have explicit
FunctionAttribute = methodInfo.GetCustomAttribute <ExcelFunctionAttribute>();
if (FunctionAttribute == null)
{
FunctionAttribute = new ExcelFunctionAttribute {
Name = methodInfo.Name
}
}
;
else if (string.IsNullOrEmpty(FunctionAttribute.Name))
{
FunctionAttribute.Name = methodInfo.Name;
}
ArgumentAttributes = new List <ExcelArgumentAttribute>();
foreach (var pi in methodInfo.GetParameters())
{
var argAtt = pi.GetCustomAttribute <ExcelArgumentAttribute>();
if (argAtt == null)
{
argAtt = new ExcelArgumentAttribute {
Name = pi.Name
}
}
;
else if (string.IsNullOrEmpty(argAtt.Name))
{
argAtt.Name = pi.Name;
}
ArgumentAttributes.Add(argAtt);
}
// Special check for final Params argument - transform to an ExcelParamsArgumentAttribute
// NOTE: This won't work with a custom derived attribute...
var lastParam = methodInfo.GetParameters().LastOrDefault();
if (lastParam != null && lastParam.GetCustomAttribute <ParamArrayAttribute>() != null)
{
var excelParamsAtt = new ExcelParamsArgumentAttribute(ArgumentAttributes.Last());
ArgumentAttributes[ArgumentAttributes.Count - 1] = excelParamsAtt;
}
}
public static RegistrationResults RegisterApi(OpenApiDocument apiDefinition, bool reregister = false)
{
List <Delegate> delegates = new List <Delegate>();
List <object> funcAttribs = new List <object>();
List <List <object> > argAttribsList = new List <List <object> >();
var functionsAdded = new List <string>();
foreach (var path in apiDefinition.Paths)
{
foreach (var operation in path.Value.Operations)
{
delegates.Add(CreateDelegateForOperation(path.Key, path.Value, operation.Key, operation.Value));
ExcelFunctionAttribute att = new ExcelFunctionAttribute();
att.Name = operation.Value.OperationId;
att.Description = operation.Value.Description;
att.HelpTopic = apiDefinition.ExternalDocs?.Url?.ToString();
att.SuppressOverwriteError = reregister;
funcAttribs.Add(att);
List <object> argAttribs = new List <object>();
foreach (var parameter in operation.Value.Parameters)
{
ExcelArgumentAttribute atta1 = new ExcelArgumentAttribute();
atta1.Name = parameter.Name;
atta1.Description = parameter.Description;
argAttribs.Add(atta1);
}
argAttribsList.Add(argAttribs);
functionsAdded.Add(att.Name);
}
}
ExcelIntegration.RegisterDelegates(delegates, funcAttribs, argAttribsList);
var registrationResults = new RegistrationResults
{
FunctionsAdded = functionsAdded
};
return(registrationResults);
}
// NOTE: 16 parameter max for Expression.GetDelegateType
// Copies all the (non Excel...) attributes from the method into the CustomAttribute lists.
// TODO: What about native async function, which returns 'Void'?
/// <summary>
/// Creates a new ExcelFunctionRegistration from a MethodInfo, with a LambdaExpression that represents a call to the method.
/// Uses the Name and Parameter Names from the MethodInfo to fill in the default attributes.
/// All CustomAttributes on the method and parameters are copies to the respective collections in the ExcelFunctionRegistration.
/// </summary>
/// <param name="methodInfo"></param>
public ExcelFunctionRegistration(MethodInfo methodInfo)
{
CustomAttributes = new List <object>();
var paramExprs = methodInfo.GetParameters()
.Select(pi => Expression.Parameter(pi.ParameterType, pi.Name))
.ToList();
FunctionLambda = Expression.Lambda(Expression.Call(methodInfo, paramExprs), methodInfo.Name, paramExprs);
var allMethodAttributes = methodInfo.GetCustomAttributes(true);
foreach (var att in allMethodAttributes)
{
var funcAtt = att as ExcelFunctionAttribute;
if (funcAtt != null)
{
FunctionAttribute = funcAtt;
// At least ensure that name is set - from the method if need be.
if (string.IsNullOrEmpty(FunctionAttribute.Name))
{
FunctionAttribute.Name = methodInfo.Name;
}
}
else
{
CustomAttributes.Add(att);
}
}
// Check that ExcelFunctionAttribute has been set
if (FunctionAttribute == null)
{
FunctionAttribute = new ExcelFunctionAttribute {
Name = methodInfo.Name
};
}
ParameterRegistrations = methodInfo.GetParameters().Select(pi => new ExcelParameterRegistration(pi)).ToList();
ReturnRegistration = new ExcelReturnRegistration();
ReturnRegistration.CustomAttributes.AddRange(methodInfo.ReturnParameter.GetCustomAttributes(true));
// Check that we haven't made a mistake
Debug.Assert(IsValid());
}
/// <summary>
/// Creates a new ExcelFunctionRegistration from a LambdaExpression.
/// Uses the Name and Parameter Names to fill in the default attributes.
/// </summary>
/// <param name="functionLambda"></param>
public ExcelFunctionRegistration(LambdaExpression functionLambda)
{
if (functionLambda == null)
{
throw new ArgumentNullException("functionLambda");
}
FunctionLambda = functionLambda;
FunctionAttribute = new ExcelFunctionAttribute {
Name = functionLambda.Name
};
ParameterRegistrations = functionLambda.Parameters
.Select(p => new ExcelParameterRegistration(new ExcelArgumentAttribute {
Name = p.Name
}))
.ToList();
CustomAttributes = new List <object>();
ReturnRegistration = new ExcelReturnRegistration();
}
public static IEnumerable <SsqExcelFunction> GetSsqExcelFunctions(SsqJson ssqJson)
{
List <SsqExcelFunction> ssqExcelFunctions = null;
try
{
ssqExcelFunctions = new List <SsqExcelFunction>();
foreach (KeyValuePair <string, UserDefinedFunction> udf in GetUserDefinedFunctions(ssqJson))
{
UserDefinedFunction userDefinedFunction = udf.Value;
QueryInformation queryInformation = userDefinedFunction.QueryInformation;
QueryParameter queryParameter = userDefinedFunction.QueryParameter;
Delegate excelFunction = new Func <string, object>(wkn_isin_ticker =>
{
object value;
try
{
Ssq ssq = new Ssq(queryParameter,
udf.Key);
value = ssq.TryQ(wkn_isin_ticker);
}
catch (Exception ex)
{
log.Error(ex.Message);
return(ExcelError.ExcelErrorGettingData);
}
return(value);
});
ExcelFunctionAttribute excelFunctionAttribute = new ExcelFunctionAttribute
{
Name = queryInformation.Name,
Description = queryInformation.Description + "\n" +
"Author: " + queryInformation.Author + " (" + queryInformation.AuthorEmail + ")\n" +
"Version " + queryInformation.Version + " of " + queryInformation.VersionDate + ", Provider: " + queryInformation.Provider,
Category = "FFE",
IsThreadSafe = true,
HelpTopic = !String.IsNullOrEmpty(queryInformation.HelpTopic) ? queryInformation.HelpTopic : null
};
ExcelArgumentAttribute excelArgumentAttribute = new ExcelArgumentAttribute {
Name = queryInformation.ExcelArgNameStockIdentifier, Description = queryInformation.ExcelArgDescStockIdentifier
};
ssqExcelFunctions.Add(new SsqExcelFunction(queryInformation.Name, excelFunction, excelFunctionAttribute, excelArgumentAttribute));
log.Debug("Created SSQ Excel function: {@SsqExcelFunction}", queryInformation.Name);
}
}
catch (Exception ex)
{
log.Error("Exception while creating SSQ Excel functions. {@ExceptionMessage}", ex.Message);
throw;
}
return(ssqExcelFunctions);
}
public void addDelegate(string functionName, string functionDescription, List <OpuxlArgumentAttribute> argumentAttributes)
{
dynamic fnDelegate;
// TODO Make this generic
switch (argumentAttributes.Count)
{
case 0:
fnDelegate = MakeDelegate(functionName);
break;
case 1:
fnDelegate = MakeDelegate(functionName, argumentAttributes.ElementAt(0));
break;
case 2:
fnDelegate = MakeDelegate(functionName, argumentAttributes.ElementAt(0), argumentAttributes.ElementAt(1));
break;
case 3:
fnDelegate = MakeDelegate(functionName, argumentAttributes.ElementAt(0), argumentAttributes.ElementAt(1), argumentAttributes.ElementAt(2));
break;
case 4:
fnDelegate = MakeDelegate(functionName, argumentAttributes.ElementAt(0), argumentAttributes.ElementAt(1), argumentAttributes.ElementAt(2), argumentAttributes.ElementAt(3));
break;
case 5:
fnDelegate = MakeDelegate(functionName, argumentAttributes.ElementAt(0), argumentAttributes.ElementAt(1), argumentAttributes.ElementAt(2), argumentAttributes.ElementAt(3), argumentAttributes.ElementAt(4));
break;
case 6:
fnDelegate = MakeDelegate(functionName, argumentAttributes.ElementAt(0), argumentAttributes.ElementAt(1), argumentAttributes.ElementAt(2), argumentAttributes.ElementAt(3), argumentAttributes.ElementAt(4), argumentAttributes.ElementAt(5));
break;
case 7:
fnDelegate = MakeDelegate(functionName, argumentAttributes.ElementAt(0), argumentAttributes.ElementAt(1), argumentAttributes.ElementAt(2), argumentAttributes.ElementAt(3), argumentAttributes.ElementAt(4), argumentAttributes.ElementAt(5), argumentAttributes.ElementAt(6));
break;
case 8:
fnDelegate = MakeDelegate(functionName, argumentAttributes.ElementAt(0), argumentAttributes.ElementAt(1), argumentAttributes.ElementAt(2), argumentAttributes.ElementAt(3), argumentAttributes.ElementAt(4), argumentAttributes.ElementAt(5), argumentAttributes.ElementAt(6), argumentAttributes.ElementAt(7));
break;
case 9:
fnDelegate = MakeDelegate(functionName, argumentAttributes.ElementAt(0), argumentAttributes.ElementAt(1), argumentAttributes.ElementAt(2), argumentAttributes.ElementAt(3), argumentAttributes.ElementAt(4), argumentAttributes.ElementAt(5), argumentAttributes.ElementAt(6), argumentAttributes.ElementAt(7), argumentAttributes.ElementAt(8));
break;
case 10:
fnDelegate = MakeDelegate(functionName, argumentAttributes.ElementAt(0), argumentAttributes.ElementAt(1), argumentAttributes.ElementAt(2), argumentAttributes.ElementAt(3), argumentAttributes.ElementAt(4), argumentAttributes.ElementAt(5), argumentAttributes.ElementAt(6), argumentAttributes.ElementAt(7), argumentAttributes.ElementAt(8), argumentAttributes.ElementAt(9));
break;
default:
throw new NotImplementedException("Can not handle an argument count of " + argumentAttributes.Count);
}
var attribute = new ExcelFunctionAttribute
{
// getVersion => Opus.GetVersion
Name = "Opus." + char.ToUpper(functionName[0]) + functionName.Substring(1),
Description = functionDescription
};
registerDelegate(fnDelegate, attribute, argumentAttributes);
}
private static ExcelFunctionRegistration ToExcelFunctionRegistration(IScript script, string FunctionName)
{
var paramExprs = script
.Parameters
.Select(x => Expression.Parameter(GetExcelRegistrationTypeFor(x), x.Name))
.ToList();
var methodInfo = typeof(ExcelScriptAddin).GetMethod(nameof(InternalRun), BindingFlags.Static | BindingFlags.NonPublic);
var paramsToObj = paramExprs.Select(x => Expression.Convert(x, typeof(object))); // cast parameter to object, otherwise won't match function signature of ExcelScriptAddin.Run
var paramsArray = Expression.NewArrayInit(typeof(object), paramsToObj.ToArray());
var methodArgs = new Expression[] { Expression.Constant(script, typeof(IScript)), paramsArray };
LambdaExpression lambdaExpression = Expression.Lambda(Expression.Call(methodInfo, methodArgs), FunctionName, paramExprs);
ExcelFunctionAttribute excelFunctionAttribute = new ExcelFunctionAttribute()
{
Name = FunctionName
};
if (!String.IsNullOrEmpty(script.Description))
{
excelFunctionAttribute.Description = script.Description;
}
IEnumerable <ExcelParameterRegistration> paramRegistrations = script
.Parameters
.Select((IParameter x) =>
{
var argumentAttribute = new ExcelArgumentAttribute()
{
Name = x.Name
};
var parameterRegistration = new ExcelParameterRegistration(argumentAttribute);
if (x.Type == typeof(Excel.Range) || x.Type == typeof(ExcelReference))
{
argumentAttribute.AllowReference = true;
}
if (x.IsOptional)
{
var optionalAttribute = new OptionalAttribute();
parameterRegistration.CustomAttributes.Add(optionalAttribute);
var defaultValueAttribute = new DefaultParameterValueAttribute(x.DefaultValue);
parameterRegistration.CustomAttributes.Add(defaultValueAttribute);
}
if (!String.IsNullOrEmpty(x.Description))
{
argumentAttribute.Description = x.Description;
}
return(parameterRegistration);
});
var reg = new ExcelFunctionRegistration(lambdaExpression, excelFunctionAttribute, paramRegistrations);
return(reg);
}
private static bool registerOneFunction(string method)
{
var request = new RestRequest("excel-service/info/" + method, Method.GET);
request.AddHeader("Authorization", "Bearer " + settings.token);
var response = settings.client.Execute <Utils.QLAPIInfo>(request);
Utils.QLAPIInfo info = response.Data;
if (info == null)
{
return(false);
}
info.provider = "excel-service";
var apiAttr = new ExcelFunctionAttribute
{
Name = info.method,
Category = "BCT Excel Addin",
Description = info.description
};
List <Utils.QLAPIParam> args = info.args;
List <object> argAttrs = new List <object>();
foreach (var arg in args)
{
var attr = new ExcelArgumentAttribute
{
Name = arg.name,
Description = arg.description
};
argAttrs.Add(attr);
}
Delegate func = null;
switch (args.Count)
{
case 0:
func = MakeExcelUDFArgs0(info);
break;
case 1:
func = MakeExcelUDFArgs1(info);
break;
case 2:
func = MakeExcelUDFArgs2(info);
break;
case 3:
func = MakeExcelUDFArgs3(info);
break;
case 4:
func = MakeExcelUDFArgs4(info);
break;
case 5:
func = MakeExcelUDFArgs5(info);
break;
case 6:
func = MakeExcelUDFArgs6(info);
break;
case 7:
func = MakeExcelUDFArgs7(info);
break;
case 8:
func = MakeExcelUDFArgs8(info);
break;
case 9:
func = MakeExcelUDFArgs9(info);
break;
case 10:
func = MakeExcelUDFArgs10(info);
break;
case 11:
func = MakeExcelUDFArgs11(info);
break;
case 12:
func = MakeExcelUDFArgs12(info);
break;
case 13:
func = MakeExcelUDFArgs13(info);
break;
case 14:
func = MakeExcelUDFArgs14(info);
break;
case 15:
func = MakeExcelUDFArgs15(info);
break;
case 16:
func = MakeExcelUDFArgs16(info);
break;
case 17:
func = MakeExcelUDFArgs17(info);
break;
case 18:
func = MakeExcelUDFArgs18(info);
break;
case 19:
func = MakeExcelUDFArgs19(info);
break;
case 20:
func = MakeExcelUDFArgs20(info);
break;
case 21:
func = MakeExcelUDFArgs21(info);
break;
}
if (func != null)
{
ExcelIntegration.RegisterDelegates(new List <Delegate> {
func
},
new List <object> {
apiAttr
},
new List <List <object> > {
argAttrs
});
return(true);
}
return(false);
}
