private static IScript <Globals> CreateScript(XlScriptOptions xlScriptOptions)
{
var options = CreateScriptingOptionsFrom(xlScriptOptions);
var script = m_ScriptFactory.Create <Globals>(m_GlobalsFactory, options);
return(script);
}
public static object OptionsCreate(
[ExcelArgument(Description = "Name of the object handle to be created")] string HandleName,
[ExcelArgument(Description = "Return type of the script. Object by default.")] string ReturnType = "object",
[ExcelArgument(Description = "Hosting type for script execution. Possible values: Shared = Same for all scripts (default). Individual = Seperate AppDomain for each script. Global = Same AppDomain as ExcelScript-Addin.")] string HostingType = "Shared"
)
{
Type _ReturnType = ToType(ReturnType);
ScriptingAbstractions.HostingType _HostingType = ToHostingType(HostingType);
var options = new XlScriptOptions(_ReturnType, _HostingType);
return(AddOrUpdateInStoreOrThrow <XlScriptOptions>(HandleName, options, () => TryDispose(options)));
}
private static ScriptingOptions CreateScriptingOptionsFrom(XlScriptOptions xlScriptOptions)
{
var options = new ScriptingOptions();
foreach (var reference in GetScriptDefaultReferences())
{
options.References.Add(reference);
}
foreach (var import in GetScriptDefaultImports())
{
options.Imports.Add(import);
}
options.HostingType = xlScriptOptions.HostingType;
return(options);
}
public static object Create(
[ExcelArgument("Name for a handle under which the script object will be stored in memory")] string HandleName,
[ExcelArgument("The code for this script. Multiple lines are allowed.")] string[] code,
[ExcelArgument("A handle to script option definitions as created by ExcelScript.Options.Create. Can be left blank to use defaults.")] string OptionsHandle = "",
[ExcelArgument("An arbitrary number of handles to parameter definitions (as created by ExcelScript.Parameters.Create), which define input parameters for the script")] params string[] ParameterHandles)
{
if (code == null)
{
throw new ArgumentNullException(nameof(code));
}
string _code = String.Join(Environment.NewLine, code);
// Get Options
XlScriptOptions xlScriptOptions = (String.IsNullOrEmpty(OptionsHandle)) ? XlScriptOptions.Default : GetFromStoreOrThrow <XlScriptOptions>(OptionsHandle);
// Create Script object
var script = CreateScript(xlScriptOptions);
script.Code = _code;
script.ReturnType = xlScriptOptions.ReturnType;
foreach (var parameterHandle in ParameterHandles)
{
var parameterHandleName = HandleNames.GetNameFrom(parameterHandle);
IParameter storedParameter = GetFromStoreOrThrow <IParameter>(parameterHandleName);
script.Parameters.Add(storedParameter);
}
IStoredObject <IScript> storedScript;
if (m_ObjectStore.GetByName <IScript>(HandleName, out storedScript) && storedScript.Object.GetHashCode() == script.GetHashCode())
{
return(HandleNames.ToHandle(storedScript));
}
else
{
return(AddOrUpdateInStoreOrThrow <IScript>(HandleName, script, () => TryDispose(script)));
}
}
public static object Parse(
[ExcelArgument("The code for this script. Multiple lines are allowed.")] string[] code,
[ExcelArgument("A handle to script option definitions as created by ExcelScript.Options.Create. Can be left blank to use defaults.")] string OptionsHandle = "",
[ExcelArgument("Name for a handle under which the script object will be stored in memory, Can be ommitted, in which case the function name will be used.")] string HandleName = "")
{
if (code == null)
{
throw new ArgumentNullException(nameof(code));
}
string _code = String.Join(Environment.NewLine, code);
// CAREFUL: This method may be run in a different AppDomain! Don't reference anything from outside of the delegate
Func <MethodInfo[], MethodInfo> EntryMethodSelector = (methods) =>
{
var _DEBUG_APPD = AppDomain.CurrentDomain.Id;
MethodInfo result = null;
// When only one method is available, we'll just return that one...
if (methods.Count() == 1)
{
result = methods.Single();
}
else
{
// Otherwise, we'll try to find a (one) function marked with an [ExcelFunction] attribute
var markedMethods = methods
.Where(x => x.GetCustomAttribute <ExcelFunctionAttribute>() != null);
if (markedMethods.Count() == 0)
{
throw new ArgumentException($"Your script defines {methods.Count()} methods; please mark one of them with the [ExcelFunction] attribute, to mark which function shall be used as an entry point to your script.");
}
if (markedMethods.Count() > 1)
{
throw new ArgumentException($"Please only mark one function with an [ExcelFunction] attribute; you have currently marked {markedMethods.Count()} functions: " + String.Join(", ", markedMethods.Select(x => x.Name)));
}
result = markedMethods.Single();
}
return(result);
};
// CAREFUL: This method may be run in a different AppDomain! Don't reference anything from outside of the delegate
Func <MethodInfo, IParameter[]> EntryMethodParameterFactory = (method =>
{
return(method
.GetParameters()
.Select(x =>
{
var excelArgumentAttribute = x.GetCustomAttribute <ExcelArgumentAttribute>();
return new Parameter
{
Name = (excelArgumentAttribute == null || String.IsNullOrWhiteSpace(excelArgumentAttribute.Name)) ? x.Name : excelArgumentAttribute.Name,
Type = x.ParameterType,
IsOptional = x.IsOptional,
DefaultValue = x.DefaultValue,
Description = (excelArgumentAttribute == null || String.IsNullOrWhiteSpace(excelArgumentAttribute.Description)) ? String.Empty : excelArgumentAttribute.Description
};
})
.ToArray());
});
// Create Script object
XlScriptOptions xlScriptOptions = (String.IsNullOrEmpty(OptionsHandle)) ? XlScriptOptions.Default : GetFromStoreOrThrow <XlScriptOptions>(OptionsHandle);
var options = CreateScriptingOptionsFrom(xlScriptOptions);
options.References.Add(typeof(ExcelFunctionAttribute).Assembly);
options.Imports.Add(typeof(ExcelFunctionAttribute).Namespace);
var parseResult = m_ScriptFactory.ParseFromAsync <Globals>(m_GlobalsFactory, options, _code, EntryMethodSelector, EntryMethodParameterFactory).Result;
var script = parseResult.Script;
script.ReturnType = xlScriptOptions.ReturnType;
if (String.IsNullOrWhiteSpace(HandleName))
{
HandleName = parseResult.EntryMethodName;
}
IStoredObject <IScript> storedScript;
if (m_ObjectStore.GetByName <IScript>(HandleName, out storedScript) && storedScript.Object.GetHashCode() == script.GetHashCode())
{
return(HandleNames.ToHandle(storedScript));
}
else
{
return(AddOrUpdateInStoreOrThrow <IScript>(HandleName, script, () => TryDispose(script)));
}
}
public static object FormatCode(
[ExcelArgument(AllowReference = true, Description = "A cell reference to the range containing C# code")] object excelRef)
{
Excel.Range cellRef = ToRange((ExcelReference)excelRef);
if (cellRef == null)
{
throw new ArgumentException("Input was not an excel reference", nameof(excelRef));
}
if (cellRef.Columns.Count != 1)
{
throw new ArgumentException("The range must have exactly one column", nameof(excelRef));
}
IEnumerable <string> codeLines = cellRef.Cells.Select(x => Convert.ToString(x.Value));
// Remove all empty lines at the beginning and end
codeLines = codeLines.SkipWhile(x => String.IsNullOrWhiteSpace(x))
.Reverse()
.SkipWhile(x => String.IsNullOrWhiteSpace(x))
.Reverse();
var code = String.Join(Environment.NewLine, codeLines);
XlScriptOptions xlScriptOptions = XlScriptOptions.Default;
var options = CreateScriptingOptionsFrom(xlScriptOptions);
var formattedCode = Script <Globals> .GetFormattedCodeAsync(code, options).Result;
if (formattedCode.TextLines.Count > cellRef.Rows.Count)
{
throw new ArgumentException($"The formatted result has {formattedCode.TextLines.Count} lines, but the input range has only {cellRef.Rows.Count}; please expant your input range.", nameof(excelRef));
}
for (int i = 0; i < cellRef.Rows.Count; i++)
{
Excel.Range target = cellRef[i + 1, 1];
if (i >= formattedCode.TextLines.Count)
{
// The target range is bigger than what we need; just clear out the unused cells
target.ClearContents();
}
else
{
var line = formattedCode.TextLines.ElementAt(i);
target.Value = line.Text;
foreach (var linePart in line.Parts)
{
var characters = target.Characters(linePart.LineSpan.Start + 1, linePart.LineSpan.Length);
var textColor = linePart.TextFormat.TextColor;
var excelColor = Color.FromArgb(textColor.B, textColor.G, textColor.R); // excel uses BGR format, so we convert RGB to BGR here
characters.Font.Color = excelColor.ToArgb();
}
}
}
return($"({DateTime.Now.ToShortTimeString()}) Formatted");
}
