/// <summary>
/// Convert excel range parameter to arrays and null objects to NA
/// </summary>
/// <returns></returns>
private ParameterConversionConfiguration GetParameterConversionConfig()
{
var objectRepository = Container.GetInstance <IObjectRepository>();
var paramConversionConfig = new ParameterConversionConfiguration()
// Register the Standard Parameter Conversions (with the optional switch on how to treat references to empty cells)
.AddParameterConversion(ParameterConversions.GetNullableConversion(treatEmptyAsMissing: false))
.AddParameterConversion(ParameterConversions.GetOptionalConversion(treatEmptyAsMissing: false))
// This parameter conversion adds support for string[] parameters (by accepting object[] instead).
// It uses the TypeConversion utility class defined in ExcelDna.Registration to get an object->string
// conversion that is consist with Excel (in this case, Excel is called to do the conversion).
.AddParameterConversion((object[] inputs) => inputs.Select(TypeConversion.ConvertToInt32).ToArray())
.AddParameterConversion((object[] inputs) => inputs.Select(TypeConversion.ConvertToString).ToArray())
// #ParameterConversion Convert handle to public object
.AddParameterConversion((object obj) => objectRepository.Get((string)obj))
//.AddParameterConversion((string handle) => handle.Contains("::") ? Container.GetInstance<ICreator>().Create(handle) : handle )
//.AddParameterConversion((Type type, ExcelParameterRegistration paramReg) =>
// (Expression<Func<object, IPublicObject>>)(obj => creator.Create((string)obj)), typeof(IPublicObject))
//paramReg.ArgumentAttribute.Name == "oTransaction" ? (Expression<Func<object, IPublicObject>>)(obj => creator.Create((string)obj)) : null, typeof(IPublicObject))
//(Expression<Func<object,IPublicObject>>) (obj => paramReg.ArgumentAttribute.Name == "hTransaction" ? creator.Create((string) obj) : null),typeof(IPublicObject))
.AddReturnConversion((object obj) => obj.IsDefault() ? ExcelError.ExcelErrorNA : obj)
// #ReturnConversion Convert public objects to its handle for excel display
.AddReturnConversion((IPublicObject obj) => obj.Handle);
return(paramConversionConfig);
}
internal static LambdaExpression NullableConversion(
ParameterConversionConfiguration config, Type type,
ExcelParameterRegistration paramReg, bool treatEmptyAsMissing,
bool treatNAErrorAsMissing)
{
// Decide whether to return a conversion function for this parameter
if (!type.IsGenericType || type.GetGenericTypeDefinition() != typeof(Nullable<>))
return null;
var innerType = type.GetGenericArguments()[0]; // E.g. innerType is Complex
LambdaExpression innerTypeConversion = ParameterConversionRegistration.GetParameterConversion(config, innerType, paramReg) ??
TypeConversion.GetConversion(typeof(object), innerType);
ParameterExpression input = innerTypeConversion.Parameters[0];
// Here's the actual conversion function
var result =
Expression.Lambda(
Expression.Condition(
// if the value is missing (or possibly empty)
MissingTest(input, treatEmptyAsMissing, treatNAErrorAsMissing),
// cast null to int?
Expression.Constant(null, type),
// else convert to int, and cast that to int?
Expression.Convert(Expression.Invoke(innerTypeConversion, input), type)),
input);
return result;
}
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 static IEnumerable<ExcelFunctionRegistration> ProcessParameterConversions(this IEnumerable<ExcelFunctionRegistration> registrations, ParameterConversionConfiguration conversionConfig)
{
foreach (var reg in registrations)
{
// Keep a list of conversions for each parameter
// TODO: Prevent having a cycle, but allow arbitrary ordering...?
var paramsConversions = new List<List<LambdaExpression>>();
for (int i = 0; i < reg.FunctionLambda.Parameters.Count; i++)
{
var initialParamType = reg.FunctionLambda.Parameters[i].Type;
var paramReg = reg.ParameterRegistrations[i];
var paramConversions = GetParameterConversions(conversionConfig, initialParamType, paramReg);
paramsConversions.Add(paramConversions);
} // for each parameter !
// Process return conversions
var returnConversions = GetReturnConversions(conversionConfig, reg.FunctionLambda.ReturnType, reg.ReturnRegistration);
// Now we apply all the conversions
ApplyConversions(reg, paramsConversions, returnConversions);
yield return reg;
}
}
internal static List<LambdaExpression> GetReturnConversions(ParameterConversionConfiguration conversionConfig, Type initialReturnType, ExcelReturnRegistration returnRegistration)
{
var appliedConversions = new List<LambdaExpression>();
// paramReg might be modified internally by the conversions, but won't become a different object
var returnType = initialReturnType; // Might become a different type as we convert
foreach (var returnConversion in conversionConfig.ReturnConversions)
{
var lambda = returnConversion.Convert(returnType, returnRegistration);
if (lambda == null)
continue;
// We got one to apply...
// Some sanity checks
Debug.Assert(lambda.Parameters.Count == 1);
Debug.Assert(lambda.Parameters[0].Type == returnType);
appliedConversions.Add(lambda);
// Change the Return Type to be whatever the conversion function returns
// for the next round of processing
returnType = lambda.ReturnType;
}
if (appliedConversions.Count == 0)
return null;
return appliedConversions;
}
static ParameterConversionConfiguration GetParameterConversionConfig()
{
// NOTE: The parameter conversion list is processed once per parameter.
// Parameter conversions will apply from most inside, to most outside.
// So to apply a conversion chain like
// string -> Type1 -> Type2
// we need to register in the (reverse) order
// Type1 -> Type2
// string -> Type1
//
// (If the registration were in the order
// string -> Type1
// Type1 -> Type2
// the parameter (starting as Type2) would not match the first conversion,
// then the second conversion (Type1 -> Type2) would be applied, and no more,
// leaving the parameter having Type1 (and probably not eligible for Excel registration.)
//
//
// Return conversions are also applied from most inside to most outside.
// So to apply a return conversion chain like
// Type1 -> Type2 -> string
// we need to register the ReturnConversions as
// Type1 -> Type2
// Type2 -> string
//
var paramConversionConfig = new ParameterConversionConfiguration()
// Register the Standard Parameter Conversions (with the optional switch on how to treat references to empty cells)
.AddParameterConversion(ParameterConversions.GetOptionalConversion(treatEmptyAsMissing: true))
// Register some type conversions (not the ordering discussed above)
.AddParameterConversion((string value) => new TestType1(value))
.AddParameterConversion((TestType1 value) => new TestType2(value))
// This is a conversion applied to the return value of the function
.AddReturnConversion((TestType1 value) => value.ToString())
.AddReturnConversion((Complex value) => new double[2] {
value.Real, value.Imaginary
})
// .AddParameterConversion((string value) => convert2(convert1(value)));
// This parameter conversion adds support for string[] parameters (by accepting object[] instead).
// It uses the TypeConversion utility class defined in ExcelDna.Registration to get an object->string
// conversion that is consist with Excel (in this case, Excel is called to do the conversion).
.AddParameterConversion((object[] inputs) => inputs.Select(TypeConversion.ConvertToString).ToArray())
// This is a pair of very generic conversions for Enum types
.AddReturnConversion((Enum value) => value.ToString(), handleSubTypes: true)
.AddParameterConversion(ParameterConversions.GetEnumStringConversion())
.AddParameterConversion((object[] input) => new Complex(TypeConversion.ConvertToDouble(input[0]), TypeConversion.ConvertToDouble(input[1])))
.AddNullableConversion(treatEmptyAsMissing: true, treatNAErrorAsMissing: true);
return(paramConversionConfig);
}
public TestCase(MethodInfo methodInfo, object[] inputData, object expectedOutputData, string name = "",
ParameterConversionConfiguration config = null)
{
MethodInfo = methodInfo;
InputData = inputData;
ExpectedOutputData = expectedOutputData;
Name =
(String.IsNullOrEmpty(name) ? MethodInfo.ToString() : name);
Config = config;
}
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();
}
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();
}
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 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);
}
static ParameterConversionConfiguration GetParameterConversionConfig()
{
var paramConversionConfig = new ParameterConversionConfiguration()
// Register the Standard Parameter Conversions (with the optional switch on how to treat references to empty cells)
.AddParameterConversion(ParameterConversions.GetNullableConversion(treatEmptyAsMissing: false))
.AddParameterConversion(ParameterConversions.GetOptionalConversion(treatEmptyAsMissing: false))
// From string to Type
.AddParameterConversion((string input) => ToType(input))
// This parameter conversion adds support for string[] parameters (by accepting object[] instead).
// It uses the TypeConversion utility class defined in ExcelDna.Registration to get an object->string
// conversion that is consist with Excel (in this case, Excel is called to do the conversion).
.AddParameterConversion((object[] inputs) => inputs.Select(TypeConversion.ConvertToString).ToArray())
.AddParameterConversion(RangeParameterConversion.ParameterConversion);
return(paramConversionConfig);
}
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);
}
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);
}
public TestCase(MethodInfo methodInfo, object[] inputData, object expectedOutputData,
ParameterConversionConfiguration config)
: this(methodInfo, inputData, expectedOutputData, "", config)
{
}
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 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);
}
static ParameterConversionConfiguration GetParameterConversionConfig()
{
// NOTE: The parameter conversion list is processed once per parameter.
// Parameter conversions will apply from most inside, to most outside.
// So to apply a conversion chain like
// string -> Type1 -> Type2
// we need to register in the (reverse) order
// Type1 -> Type2
// string -> Type1
//
// (If the registration were in the order
// string -> Type1
// Type1 -> Type2
// the parameter (starting as Type2) would not match the first conversion,
// then the second conversion (Type1 -> Type2) would be applied, and no more,
// leaving the parameter having Type1 (and probably not eligible for Excel registration.)
//
//
// Return conversions are also applied from most inside to most outside.
// So to apply a return conversion chain like
// Type1 -> Type2 -> string
// we need to register the ReturnConversions as
// Type1 -> Type2
// Type2 -> string
//
var paramConversionConfig = new ParameterConversionConfiguration()
// Register the Standard Parameter Conversions (with the optional switch on how to treat references to empty cells)
.AddParameterConversion(ParameterConversions.GetOptionalConversion(treatEmptyAsMissing: true))
// Register some type conversions (note the ordering discussed above)
.AddParameterConversion((TestType1 value) => new TestType2(value))
.AddParameterConversion((string value) => new TestType1(value))
// This is a conversion applied to the return value of the function
.AddReturnConversion((TestType1 value) => value.ToString())
.AddReturnConversion((Complex value) => new double[2] {value.Real, value.Imaginary})
// .AddParameterConversion((string value) => convert2(convert1(value)));
// This parameter conversion adds support for string[] parameters (by accepting object[] instead).
// It uses the TypeConversion utility class defined in ExcelDna.Registration to get an object->string
// conversion that is consist with Excel (in this case, Excel is called to do the conversion).
.AddParameterConversion((object[] inputs) => inputs.Select(TypeConversion.ConvertToString).ToArray())
// This is a pair of very generic conversions for Enum types
.AddReturnConversion((Enum value) => value.ToString(), handleSubTypes: true)
.AddParameterConversion(ParameterConversions.GetEnumStringConversion())
.AddParameterConversion((object[] input) => new Complex(TypeConversion.ConvertToDouble(input[0]), TypeConversion.ConvertToDouble(input[1])))
.AddNullableConversion(treatEmptyAsMissing: true, treatNAErrorAsMissing: true);
return paramConversionConfig;
}
internal static LambdaExpression GetReturnConversion(ParameterConversionConfiguration conversionConfig,
Type initialReturnType, ExcelReturnRegistration returnRegistration)
{
return ComposeLambdas(GetReturnConversions(conversionConfig, initialReturnType, returnRegistration));
}
internal static LambdaExpression GetParameterConversion(ParameterConversionConfiguration conversionConfig,
Type initialParamType, ExcelParameterRegistration paramRegistration)
{
return ComposeLambdas(GetParameterConversions(conversionConfig, initialParamType, paramRegistration));
}