Custom Function

FlexSheet allows user to add custom functions by the addFunction method. FlexSheet will parse the cell reference parameter such as 'A1' or 'A1:B2' to a a two-dimensional value range for the custom function.

flexSheet.addFunction('customSumProduct', (...params: any[][][]) => { let result = 0, range1 = params[0], range2 = params[1]; if (range1.length > 0 && range1.length === range2.length && range1[0].length === range2[0].length) { for (let i = 0; i < range1.length; i++) { for (let j = 0; j < range1[0].length; j++) { result += range1[i][j] * range2[i][j]; } } } return result; }, 'Custom SumProduct Function', 2, 2);
FlexSheet also allows user to process non-supported functions met in cell expressions by the unknownFuntion event. This event will pass the 'UnKnownFunctionEventArgs' to customer. This 'UnKnownFunctionEventArgs' provides the function name and the evaluated values list of the parameters. Customer can set the 'value' field of 'UnKnownFunctionEventArgs' to customize the miss formula result. Otherwise the missed function will return the default error message: 'The function "funcName" has not supported in FlexSheet yet.'.

flexSheet.unknownFunction.addHandler((sender: any, e: wjcGridSheet.UnknownFunctionEventArgs) => { let result = ''; if (e.params) { for (let i = 0; i < e.params.length; i++) { result += e.params[i]; } } e.value = result; });