public static void TestGlobalReturnConversion()
{
// Conversions can be 'global' so they can be applied to any function.
// The conversion returns 'null' for cases (type + attribute combinations) where it should not be applied.
// This test simulates the return type conversion we would do to change async #N/A to something else
// (though using strings instead of the real ExcelError.ExcelErrorNA result).
var testLambda = (Expression <Func <string, object> >)(name => name);
var registration = new ExcelFunctionRegistration(testLambda);
var conversionConfig = new ParameterConversionConfiguration()
.AddReturnConversion((type, customAttributes) => type != typeof(object) ? null : ((Expression <Func <object, object> >)
((object returnValue) => returnValue.Equals("NA") ? (object)"### WAIT ###" : returnValue)), null);
var convertedRegistrations = ParameterConversionRegistration.ProcessParameterConversions(new[] { registration }, conversionConfig);
var converted = convertedRegistrations.First();
var compiled = (Func <string, object>)converted.FunctionLambda.Compile();
var resultNormal = compiled("XYZ");
Assert.AreEqual("XYZ", resultNormal);
var resultNA = compiled("NA");
Assert.AreEqual("### WAIT ###", resultNA);
}
public static void TestGlobalReturnConversion()
{
// Conversions can be 'global' so they can be applied to any function.
// The conversion returns 'null' for cases (type + attribute combinations) where it should not be applied.
// This test simulates the return type conversion we would do to change async #N/A to something else
// (though using strings instead of the real ExcelError.ExcelErrorNA result).
var testLambda = (Expression<Func<string, object>>)(name => name);
var registration = new ExcelFunctionRegistration(testLambda);
var conversionConfig = new ParameterConversionConfiguration()
.AddReturnConversion((type, customAttributes) => type != typeof(object) ? null : ((Expression<Func<object, object>>)
((object returnValue) => returnValue.Equals("NA") ? (object)"### WAIT ###" : returnValue)), null);
var convertedRegistrations = ParameterConversionRegistration.ProcessParameterConversions(new[] { registration }, conversionConfig);
var converted = convertedRegistrations.First();
var compiled = (Func<string, object>)converted.FunctionLambda.Compile();
var resultNormal = compiled("XYZ");
Assert.AreEqual("XYZ", resultNormal);
var resultNA = compiled("NA");
Assert.AreEqual("### WAIT ###", resultNA);
}
public ExcelFunctionRegistration UpdateHelpTopic(ExcelFunctionRegistration funcReg)
{
funcReg.FunctionAttribute.Category = Resources.ExcelHelpCategory;
funcReg.FunctionAttribute.HelpTopic = new Uri(new Uri(Resources.DocumentationBaseAddress), funcReg.FunctionAttribute.HelpTopic).ToString();
return(funcReg);
}
internal static FunctionExecutionHandler TimingHandlerSelector(ExcelFunctionRegistration functionRegistration)
{
// Eat the TimingAttributes, and return a timer handler if there were any
if (functionRegistration.CustomAttributes.RemoveAll(att => att is TimingAttribute) == 0)
{
// No attributes
return(null);
}
return(_handler.Value);
}
/////////////////////// Registration handler //////////////////////////////////
// (This code can be anywhere... - need not be in this class)
// In this case, we only ever make one 'handler' object
public static FunctionExecutionHandler CacheHandlerSelector(ExcelFunctionRegistration functionRegistration)
{
// Eat the TimingAttributes, and return a timer handler if there were any
if (functionRegistration.CustomAttributes.OfType <CacheAttribute>().Any())
{
// Get the first cache attribute, and remove all of them
var cacheAtt = functionRegistration.CustomAttributes.OfType <CacheAttribute>().First();
functionRegistration.CustomAttributes.RemoveAll(att => att is CacheAttribute);
return(new CacheFunctionExecutionHandler(cacheAtt.CacheTimeout));
}
return(null);
}
/// <summary>
/// Register method as excel function
/// </summary>
/// <param name="methodInfo"></param>
public void AddMethod(MethodInfo methodInfo)
{
var attribute = (IExcelFunctionAttribute)methodInfo.GetCustomAttributes(typeof(IExcelFunctionAttribute))
.Single();
var lambda = WrapMethod(methodInfo, attribute.IsFactory);
var parameters = methodInfo.GetParameters()
.Where(p => !p.ParameterType.GetInterfaces().Contains(typeof(IInjectable)))
.Select(p => new ExcelParameterRegistration(p))
.ToList();
var name = methodInfo.Name;
if (!methodInfo.IsStatic)
{
if (!attribute.IsFactory)
{
// add handle as a string parameter to resolve the instance
parameters.Insert(0, new ExcelParameterRegistration(
new ExcelArgumentAttribute()
{
AllowReference = true,
Description = "Object handle",
Name = "Handle"
}));
}
if (attribute.IsFactory)
{
var type = methodInfo.DeclaringType.DeclaringType;
while (!type.GenericTypeArguments.Any())
{
type = type.BaseType;
}
name = type.GenericTypeArguments.First().Name + "." + name;
}
else if (!methodInfo.DeclaringType.IsGenericType)
{
name = methodInfo.DeclaringType.BaseType.GenericTypeArguments.First().Name + "." + name;
}
else
{
// Generic base functions use the underlying type
name = methodInfo.DeclaringType.GenericTypeArguments.First().Name + "." + name;
}
}
var registration =
new ExcelFunctionRegistration(lambda, attribute.ToExcelFunctionAttribute(name), parameters);
_registrations.Add(registration);
}
public static void TestInstanceRegistration()
{
var instanceMethod = typeof(TestClass).GetMethod("GetContent");
var lambda = WrapInstanceMethod(instanceMethod);
// Create a new object every time the instance method is called... (typically we'd look up using a handle)
var paramConversionConfig = new ParameterConversionConfiguration()
.AddParameterConversion((string content) => new TestClass(content));
// TODO: Clean up how we suggest this code gets called
var reg = new ExcelFunctionRegistration(lambda);
var processed = ParameterConversionRegistration.ProcessParameterConversions(new[] { reg }, paramConversionConfig);
processed.RegisterFunctions();
}
/// <summary>
/// Currently only applied to functions that return object or string.
/// </summary>
/// <param name="functionRegistration"></param>
/// <returns></returns>
public static IFunctionExecutionHandler SuppressInDialogSelector(ExcelFunctionRegistration functionRegistration)
{
// Eat the TimingAttributes, and return a timer handler if there were any
if (functionRegistration.CustomAttributes.OfType <SuppressInDialogAttribute>().Any() &&
(functionRegistration.FunctionLambda.ReturnType == typeof(object) ||
functionRegistration.FunctionLambda.ReturnType == typeof(string)))
{
// Get the first cache attribute, and remove all of them
var suppressAtt = functionRegistration.CustomAttributes.OfType <SuppressInDialogAttribute>().First();
functionRegistration.CustomAttributes.RemoveAll(att => att is SuppressInDialogAttribute);
return(suppressAtt);
}
return(null);
}
public static void TestIgnoredGlobalReturnConversion()
{
var testLambda = (Expression<Func<string, object>>)(name => name);
var registration = new ExcelFunctionRegistration(testLambda);
var conversionConfig = new ParameterConversionConfiguration()
// Add a return conversion that is never applied
.AddReturnConversion((type, customAttributes) => null, null);
var convertedRegistrations = ParameterConversionRegistration.ProcessParameterConversions(new[] { registration }, conversionConfig);
var converted = convertedRegistrations.First();
var compiled = (Func<string, object>)converted.FunctionLambda.Compile();
var resultNormal = compiled("XYZ");
Assert.AreEqual("XYZ", resultNormal);
}
public static void TestReturnConversion()
{
//
var testLambda = (Expression <Func <string, int, string> >)((name, value) => name + value.ToString());
var registration = new ExcelFunctionRegistration(testLambda);
// This conversion takes the return string and duplicates it
var conversionConfig = new ParameterConversionConfiguration()
.AddReturnConversion((string value) => value + value);
var convertedRegistrations = ParameterConversionRegistration.ProcessParameterConversions(new[] { registration }, conversionConfig);
var converted = convertedRegistrations.First();
var compiled = (Func <string, int, string>)converted.FunctionLambda.Compile();
var result = compiled("asd", 42);
Assert.AreEqual("asd42asd42", result);
}
public static void TestIgnoredGlobalReturnConversion()
{
var testLambda = (Expression <Func <string, object> >)(name => name);
var registration = new ExcelFunctionRegistration(testLambda);
var conversionConfig = new ParameterConversionConfiguration()
// Add a return conversion that is never applied
.AddReturnConversion((type, customAttributes) => null, null);
var convertedRegistrations = ParameterConversionRegistration.ProcessParameterConversions(new[] { registration }, conversionConfig);
var converted = convertedRegistrations.First();
var compiled = (Func <string, object>)converted.FunctionLambda.Compile();
var resultNormal = compiled("XYZ");
Assert.AreEqual("XYZ", resultNormal);
}
public static void TestNestedLambdaConversion()
{
// The function duplicates the input string (inside a lambda that captures that input parameter)
var registration = new ExcelFunctionRegistration(typeof(ParameterConversionTests).GetMethod("TestMethodWithLambda", BindingFlags.NonPublic | BindingFlags.Static));
var conversionConfig = new ParameterConversionConfiguration()
.AddReturnConversion((type, customAttributes) => type != typeof(object) ? null : ((Expression<Func<object, object>>)
((object returnValue) => returnValue.Equals("NANA") ? (object)"### WAIT ###" : returnValue)), null);
var convertedRegistrations = ParameterConversionRegistration.ProcessParameterConversions(new[] { registration }, conversionConfig);
var converted = convertedRegistrations.First();
var compiled = (Func<string, object>)converted.FunctionLambda.Compile();
var resultNormal = compiled("XYZ");
Assert.AreEqual("XYZXYZ", resultNormal);
var resultNA = compiled("NA");
Assert.AreEqual("### WAIT ###", resultNA);
}
private static ExcelFunctionRegistration UpdateAttributesForRangeParameters(ExcelFunctionRegistration reg)
{
var rangeParams = reg.FunctionLambda.Parameters.Select((x, i) => new { _Parameter = x, _Index = i })
.Where(x => x._Parameter.Type == typeof(Excel.Range));
bool hasRangeParam = false;
foreach (var param in rangeParams)
{
reg.ParameterRegistrations[param._Index].ArgumentAttribute.AllowReference = true;
hasRangeParam = true;
}
if (hasRangeParam)
{
reg.FunctionAttribute.IsMacroType = true;
}
return(reg);
}
public static void TestParameterConversions()
{
//
var testLambda = (Expression <Func <string, int, string> >)((name, value) => name + value.ToString());
var registration = new ExcelFunctionRegistration(testLambda);
// This conversion applied a mix of parameter and string conversions
var conversionConfig = new ParameterConversionConfiguration()
.AddParameterConversion((double value) => (int)(value * 10))
.AddParameterConversion((string value) => value.Substring(0, 2))
.AddReturnConversion((string value) => value + value)
.AddReturnConversion((string value) => value.Length);
var convertedRegistrations = ParameterConversionRegistration.ProcessParameterConversions(new[] { registration }, conversionConfig);
var converted = convertedRegistrations.First();
var compiled = (Func <string, double, int>)converted.FunctionLambda.Compile();
var result = compiled("qwXXX", 4.2);
Assert.AreEqual("qw42qw42".Length, result);
}
public static void TestNestedLambdaConversion()
{
// The function duplicates the input string (inside a lambda that captures that input parameter)
var registration = new ExcelFunctionRegistration(typeof(ParameterConversionTests).GetMethod("TestMethodWithLambda", BindingFlags.NonPublic | BindingFlags.Static));
var conversionConfig = new ParameterConversionConfiguration()
.AddReturnConversion((type, customAttributes) => type != typeof(object) ? null : ((Expression <Func <object, object> >)
((object returnValue) => returnValue.Equals("NANA") ? (object)"### WAIT ###" : returnValue)), null);
var convertedRegistrations = ParameterConversionRegistration.ProcessParameterConversions(new[] { registration }, conversionConfig);
var converted = convertedRegistrations.First();
var compiled = (Func <string, object>)converted.FunctionLambda.Compile();
var resultNormal = compiled("XYZ");
Assert.AreEqual("XYZXYZ", resultNormal);
var resultNA = compiled("NA");
Assert.AreEqual("### WAIT ###", resultNA);
}
/// <summary>
/// Register property as excel function
/// </summary>
/// <param name="property"></param>
public void AddProperty(PropertyInfo property)
{
var getLambda = GetProperty(property);
var attribute = (IExcelFunctionAttribute)property.GetCustomAttributes(typeof(IExcelFunctionAttribute)).Single();
var handleParameter = new ExcelParameterRegistration(new ExcelArgumentAttribute()
{
Name = "Handle"
});
// we do not register twice the base properties
if (property.ReflectedType != property.DeclaringType)
{
return;
}
var className = property.ReflectedType.Name.Replace("Public", "");
var getRegistration = new ExcelFunctionRegistration(getLambda,
//attribute.ToExcelFunctionAttribute(property.DeclaringType.BaseType.GenericTypeArguments.First().Name + "." + "Get" +property.Name),
attribute.ToExcelFunctionAttribute(className + "." + "Get" + property.Name),
new List <ExcelParameterRegistration>()
{
handleParameter
});
_registrations.Add(getRegistration);
var bindLambda = BindProperty(property);
var bindRegistration = new ExcelFunctionRegistration(bindLambda,
attribute.ToExcelFunctionAttribute(className + "." + "Bind" + property.Name),
new List <ExcelParameterRegistration>()
{
handleParameter
});
_registrations.Add(bindRegistration);
}
public ExcelFunctionRegistrationWrapper(ExcelFunctionRegistration excelFunctionRegistration)
{
_excelFunctionRegistration = excelFunctionRegistration ?? throw new ArgumentNullException(nameof(excelFunctionRegistration));
}
public ExcelFunctionRegistration UpdateHelpTopic(ExcelFunctionRegistration funcReg)
{
funcReg.FunctionAttribute.HelpTopic = "http://WDataSci.com";
return(funcReg);
}
private static ExcelFunctionRegistration ProcessFunction(ExcelFunctionRegistration Registration)
{
return(ProcessFunctions(new ExcelFunctionRegistration[] { Registration }).Single());
}
private static void ProcessAndRegisterFunction(ExcelFunctionRegistration Registration)
{
ProcessAndRegisterFunctions(new ExcelFunctionRegistration[] { Registration });
}
public ExcelFunctionRegistration UpdateHelpTopic(ExcelFunctionRegistration funcReg)
{
funcReg.FunctionAttribute.HelpTopic = "http://www.duckduckgo.com";
return(funcReg);
}
public static void TestMapArrayRegistrations(TestCase testCase)
{
var methodInfo = testCase.MethodInfo;
////////////////////////////////////////////
// arrange
Assert.IsNotNull(methodInfo);
// wrap the method in a registrationentry
var registration = new ExcelFunctionRegistration(methodInfo);
registration.FunctionAttribute = new ExcelMapArrayFunctionAttribute();
//////////////////////////////////////////
// act - process the registration object
var processed = Enumerable.Repeat(registration, 1).ProcessMapArrayFunctions(testCase.Config).ToList();
//////////////////////////////////////////
// assert
Assert.AreEqual(1, processed.Count());
var processedRegistration = processed.First();
var functionDescription = registration.FunctionAttribute.Description;
Assert.IsTrue(functionDescription.StartsWith("Returns "));
var expectedOutputArray = testCase.ExpectedOutputData as object[, ];
if (expectedOutputArray != null && expectedOutputArray.GetLength(0) != 1 && expectedOutputArray.GetLength(1) != 1)
{
// confirm function description contains list of field names for array result
for (int col = 0; col != expectedOutputArray.GetLength(1); ++col)
{
var colHeader = expectedOutputArray[0, col] as string;
Assert.IsNotNull(colHeader);
var regex = new Regex(@"\b" + colHeader + @"\b", RegexOptions.IgnoreCase);
Assert.IsTrue(regex.Match(functionDescription).Success, functionDescription);
}
}
Assert.AreEqual(testCase.InputData.GetLength(0), registration.ParameterRegistrations.Count);
for (int param = 0; param != testCase.InputData.GetLength(0); ++param)
{
var inputArray = testCase.InputData[param] as object[, ];
if (inputArray != null && inputArray.GetLength(0) != 1 && inputArray.GetLength(1) != 1)
{
// confirm function description contains list of field names for this array param
for (int col = 0; col != inputArray.GetLength(1); ++col)
{
var colHeader = _recordsInputData[0, col] as string;
Assert.IsNotNull(colHeader);
var regex = new Regex(@"\b" + colHeader + @"\b", RegexOptions.IgnoreCase);
var parameterDescription = registration.ParameterRegistrations[param].ArgumentAttribute.Description;
Assert.IsTrue(regex.Match(parameterDescription).Success, parameterDescription);
}
}
}
//////////////////////////////////////////
// act - invoke the delegate
var output = processedRegistration.FunctionLambda.Compile().DynamicInvoke(testCase.InputData);
//////////////////////////////////////////
// assert
if (output == null)
{
Assert.IsNull(output);
}
else
{
Assert.AreEqual(testCase.ExpectedOutputData, output);
}
}
internal static FunctionExecutionHandler LoggingHandlerSelector(ExcelFunctionRegistration functionRegistration)
{
return(new FunctionLoggingHandler {
Index = _index++
});
}
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);
}
public static void TestParameterConversions()
{
//
var testLambda = (Expression<Func<string, int, string>>)((name, value) => name + value.ToString());
var registration = new ExcelFunctionRegistration(testLambda);
// This conversion applied a mix of parameter and string conversions
var conversionConfig = new ParameterConversionConfiguration()
.AddParameterConversion((double value) => (int)(value * 10))
.AddParameterConversion((string value) => value.Substring(0, 2))
.AddReturnConversion((string value) => value + value)
.AddReturnConversion((string value) => value.Length);
var convertedRegistrations = ParameterConversionRegistration.ProcessParameterConversions(new[] { registration }, conversionConfig);
var converted = convertedRegistrations.First();
var compiled = (Func<string, double, int>)converted.FunctionLambda.Compile();
var result = compiled("qwXXX", 4.2);
Assert.AreEqual("qw42qw42".Length, result);
}
private static ExcelFunctionRegistration UpdateFunctionAttributes(ExcelFunctionRegistration excelFunction)
{
excelFunction.FunctionAttribute.Name = excelFunction.FunctionAttribute.Name.ToUpperInvariant();
return(excelFunction);
}
public static void TestReturnConversion()
{
//
var testLambda = (Expression<Func<string, int, string>>)((name, value) => name + value.ToString());
var registration = new ExcelFunctionRegistration(testLambda);
// This conversion takes the return string and duplicates it
var conversionConfig = new ParameterConversionConfiguration()
.AddReturnConversion((string value) => value + value);
var convertedRegistrations = ParameterConversionRegistration.ProcessParameterConversions(new[] { registration }, conversionConfig);
var converted = convertedRegistrations.First();
var compiled = (Func<string, int, string>)converted.FunctionLambda.Compile();
var result = compiled("asd", 42);
Assert.AreEqual("asd42asd42", result);
}
