My team is trying to design a new tool using a Spreadsheet UI that will leverage our users' familiarity with Excel to allow them to view and manipulate some of the data we store for them. One feature we will need to make this work is a way to create a custom function. For example, if a user types "=CUSTOMER(smith, john)" we could have it call a method like "string LookupCustomer(string lastname, string firstname)" in the code behind that returns customer ID matching the input parameters. Ideally we'd also like the UI to provide a similar amount of suggestion/auto complete complexity as regular old Excel.
While looking at off the shelf solutions, it wasn't immediately obvious if XamSpreadsheet has this degree of customization. How feasible would this type of extension be?
Hello J. Moran,
Custom function support for the Infragistics Excel Engine and the XamSpreadsheet does exist. In order to do this, you need to create a class that derives from the abstract Infragistics.Documents.Excel.CaclEngine.ExcelCalcFunction class. This abstract class exposes a MinArgs, MaxArgs, and Name property, representing the number of arguments and the name of the function, respectively, along with an Evaluate method that will fire when the method evaluates.
Once you have created your function, you need to pass it to the RegisterUserDefinedFunction method of your Workbook to use it.
Regarding the auto-complete and suggestion functionality, this is not currently supported in the XamSpreadsheet. If you would like to see it supported, I would recommend suggesting a new product idea for this at our WPF Ideas Site. This will place you in direct communication with our product management teams who plan and prioritize upcoming features and development based on community and user feedback.
Please let me know if you have any other questions or concerns on this matter.
Hi Andrew,
Thanks for your response.
Would you be able to provide a simple best practices implementation of such a function.
I can make things work for simple types but we are concerned we are not handling complicated reference scenarios. Here is a simple UDF we wrote to convert a string toupper(). We couldn't find enough documentation around the usage of the classes involved.
public class FooUpper : Infragistics.Documents.Excel.CalcEngine.ExcelCalcFunction { public override string Name => "FooUpper";
public override int MinArgs => 1;
public override int MaxArgs => 1;
protected override ExcelCalcValue Evaluate(ExcelCalcNumberStack numberStack, int argumentCount) { if(argumentCount < MinArgs && argumentCount > MaxArgs) return new ExcelCalcValue(new ExcelCalcErrorValue(ExcelCalcErrorCode.Name)); while (numberStack.Count() > 0) { var val = numberStack.Pop(); return val.IsString ? new ExcelCalcValue(val.ToString().ToUpper()) : new ExcelCalcValue(new ExcelCalcErrorValue(ExcelCalcErrorCode.Value)); } return new ExcelCalcValue(new ExcelCalcErrorValue(ExcelCalcErrorCode.Name)); } }
Thanks,
Dev B