¶ I. Custom Function Conventions
You must follow the conventions for custom functions in this control to use them properly.
The conventions are as follows:
- The custom class name must match the function name. (Required)
- The method name in the class must match the function name, and this method is the entry point of the function. (Required)
- The value of
namemust match the name used in the spreadsheet. (Required) - It is recommended to build the function as a static function.
¶ II. Custom UDF Function
In this section, we have created a UDF (User-Defined Function) for Excel. This function simply returns the fixed value "Function UDF return value." The code is as follows:
// Define the UDF class
export default class UDF {
// Define a static private variable #instance
static #instance: UDF | null = null;
// Define class properties
public name!: string;
// Return value type
public returnValueType: string = 'S'; // Text
// public returnValueType: string = 'N'; // Number
// Constructor
constructor() {
if (!UDF.#instance) {
UDF.#instance = this;
this.name = 'udf';// Consistent with the formula name used in the spreadsheet
this.returnValueType = 'S'; // Text
}
}
// Static method to get the singleton instance
public static getInstance(): UDF {
if (!this.#instance) {
this.#instance = new UDF();
}
return this.#instance;
}
// Define the UDF method (parameters and return values are discussed in Section 5)
public UDF(pram: any, sheetname: string, workbook: any, formula: any): string {
console.log(pram.toString());
return 'Function UDF return value';
}
}
HTML implementation:
// Define the UDF class
class UDF {
// Define a static private variable instance
static instance = null;
// Define class properties
name;
returnValueType;
// Constructor
constructor() {
if (!UDF.instance) {
UDF.instance = this;
this.name = 'udf'; // Consistent with the formula name used in the spreadsheet
this.returnValueType = 'S'; // Text
}
return UDF.instance;
}
// Static method to get the singleton instance
static getInstance() {
if (!this.instance) {
this.instance = new UDF();
}
return this.instance;
}
// Define the UDF method (parameters and return values are discussed in Section 5)
UDF(pram, sheetname, workbook, formula) {
console.log(pram.toString());
return 'Function UDF return value';
}
}
¶ III. Registering Custom Functions
¶ 3.1 Importing Files
Import the edited formula class file using import or other methods.
import UDF from './udf'
¶ 3.2 Registration
After importing the custom class file, you can register the function using the workbook.AddUserDefineFunction(...) method. The code is as follows:
let workbook = wsheet.Workbook();
workbook.AddUserDefineFunction(UDF);
¶ IV. Using in the Spreadsheet
In this example, the formula for cell A1 is set as follows:
/**
* Step 1: Get the workbook
*/
let workbook = wsheet.Workbook();
/**
* Step 2: Register the custom function
*/
workbook.AddUserDefineFunction(UDF);
let activeSheet = wsheet.ActiveSheet();
/**
* Use udf
*/
activeSheet.SetCellValue('A1', '=udf()');
/**
* Step 3: Redraw the table
*/
activeSheet.setColWidth(1, 160);
activeSheet.WorkFormula(); // Rebuild formulas
activeSheet.cacl(); // Calculate formulas
wsheet.BuildSheet();
wsheet.Draw();
The result is as shown in the figure below:
¶ V. Parameters and Return Values
¶ 5.1 Parameters
The parameters of the called function are as follows:
| Parameter Name | Meaning |
|---|---|
| valueParms | The content within the function parentheses. For example, the custom udf() above has no parameters. This parameter can be a number, amount, string, cell address, etc., depending on the use case. The value is passed as an array. |
| sheetName | The sheet name of the formula |
| workbook | The workbook object, convenient for function use |
For example, passing the amount 10 to the function, as shown in Section 5.3, the value is obtained.
/**
* Use udf
*/
activeSheet.SetCellValue('A1', '=udf(10)');
¶ 5.2 Return Values
Return values can be distinguished between single values and arrays, as shown in the table:
| Return Value Type | Meaning |
|---|---|
| Single Value | Can be a number, amount, string, etc., for example: UDF returns a fixed string value |
| Array | Use a two-dimensional array to return the Array. The first dimension is the row, and the second dimension is the column, containing Cell objects |
¶ 5.3 Example of Returning an Array
In this section, we have modified the UDF function above to return a 5x5 array. The code is as follows:
public UDF(pram: any, sheetname: string, workbook: any, range: any): [] {
//console.log(pram.toString());
// Get the parameter
console.log(pram[0]); // This will print the value 10
let rowVale = [];
for (let i = 0; i < 5; i++) {
let colValues = [];
for (let c = 0; c < 5; c++) {
let cell = new WebSheet.Model.Cell();
cell.value = 'row=' + (i + 1) + ' col=' + (c + 1);
colValues.push(cell);
}
rowVale.push(colValues);
}
return rowVale;
}
The result is as shown in the figure below:
