Can SpreadSheet implement calculation formulas like those in Sample1.xlsx?

[lixin]

Hi,Please help me.
The Left function does not exist in Supported Formulas?
Can the Formulas of A1, B1, C1 in Sample1.xlsx be implemented?

A1：=IF(C1="","",IF(OR(LEFT(E1,1)={"5","8","9"}),"定番",("シーズン")))

B1：=IF(C1="","",SWITCH(MID(E1,3,1),"2","PY","3","PF","4","PN","0","PA","その他"))

C1：=IF(D1="","",VALUE(LEFT(D1,2)))

[lixin]

Reference Excel Sample1_5a3e670c.zip

Attachment: Sample1_9b852267.zip

[Janakiraman Sakthivel]

Hi lixin,

We have validated your reported requirement and kindly inform you that, unfortunately, we currently don’t have support for the LEFT, SWITCH, and VALUE formulas in our Spreadsheet.

However, in our spreadsheet, we provide the addCustomFunction method to add custom functions based on your specific needs at the sample level. By using this method, we can add those formulas as custom functions to our Spreadsheet component.

Please refer to the links below for more information regarding this function.

Documentation link: https://ej2.syncfusion.com/documentation/spreadsheet/formulas#create-user-defined-functions--custom-functions

API link: https://ej2.syncfusion.com/documentation/api/spreadsheet/#addcustomfunction

For your convenience, we have prepared a sample in which we have added custom functions for the LEFT, SWITCH, and VALUE formulas. Additionally, since you have used the MID function in your shared Excel file, we have also included a custom function for the MID formula. We have attached the code snippet and sample below for your reference.

Sample: https://stackblitz.com/edit/93cbnc-weugzh?file=default-data.json,index.ts

CODE SNIPPET:

created: (): void => {

        //Applies cell formatting

        spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center', verticalAlign: 'middle' }, 'A1');

 

        // Added custom functions for the LEFT, SWITCH, VALUE, and MID formulas.

        spreadsheet.addCustomFunction(leftFormulaHandler, 'LEFT');

        spreadsheet.addCustomFunction(midFormulaHandler, 'MID');

        spreadsheet.addCustomFunction(switchFormulaHandler, 'SWITCH');

        spreadsheet.addCustomFunction(valueFormulaHandler, 'VALUE');

 

        spreadsheet.updateCell({ formula: '=LEFT(A2,4)', format: getFormatFromType('Text') }, 'B2');

        spreadsheet.updateCell({ formula: '=LEFT(A3,3)', format: getFormatFromType('Text') }, 'B3');

 

        spreadsheet.updateCell({ formula: '=MID(A2,3, 4)', format: getFormatFromType('Text') }, 'C2');

        spreadsheet.updateCell({ formula: '=MID(A3,3)', format: getFormatFromType('Text') }, 'C3');

 

        spreadsheet.updateCell({ formula: '=SWITCH(A4,1,"January",2,"February",3,"March","No Match")' }, 'B4');

        spreadsheet.updateCell({ formula: '=SWITCH(A5,1,"January",2,"February",3,"March","No Match")' }, 'B5');

        spreadsheet.updateCell({ formula: '=SWITCH(A6,10,10,100,100,1000,1000,0)' }, 'B6');

 

        spreadsheet.updateCell({ formula: '=VALUE(A7)' }, 'B7');

        spreadsheet.updateCell({ formula: '=VALUE(A8)' }, 'B8');

        spreadsheet.updateCell({ formula: '=VALUE(A9)' }, 'B9');

        spreadsheet.updateCell({ formula: '=VALUE(A10)' }, 'B10');

        spreadsheet.updateCell({ formula: '=VALUE(A11)' }, 'B11');

    }

});

 

function leftFormulaHandler(text: string, numChars: number): string {

    return text.slice(0, numChars);

};

 

function midFormulaHandler(text: string, startNum: number, length: number): string {

    startNum--;

    return text.substr(startNum, length);

};

 

function switchFormulaHandler(...args: string[]): string {

    let expression: string = args[0];

    const cases: string[] = args.slice(1);

    //// To handle double quotes

    if (/^".*"$/.test(expression)) {

        expression = expression.replace(/"/g, '');

    }

    for (let i = 0; i < cases.length - 1; i += 2) {

        let value: string = cases[i];

        if (/^".*"$/.test(value)) {

            value = value.replace(/"/g, '');

        }

        if (expression == value) {

            return cases[i + 1].replace(/"/g, '');

        }

    }

    // Default case

    return cases[cases.length - 1].replace(/"/g, '');

}

 

function valueFormulaHandler(text: any): number | string {

    const parsedNumber: string = parseFloat(text);

    return isNaN(parsedNumber) ? "#VALUE!" : parsedNumber;

}

 

Please find the API links below.

https://ej2.syncfusion.com/documentation/api/spreadsheet/#created

https://ej2.syncfusion.com/documentation/api/spreadsheet/#updatecell

In addition, we have noted that in the formula of the A1 cell, you have used the OR function to check whether the number 1 matches any of the values in the array {"5","8","9"}. Unfortunately, we currently don't have support for this in our spreadsheet. However, we have already confirmed this as an improvement and logged it as a feature. It will be available in one of our upcoming releases. You can communicate and track the status using the link below from our feedback portal.

Feedback link for tracking purposes: https://www.syncfusion.com/feedback/20494/need-to-provide-array-formulas-support-in-spreadsheet


In the shared sample above, if you load your Excel file, you will notice that the functions LEFT, SWITCH, and VALUE work properly, and the formula results update correctly in cells B1 and C1, just as in MS Excel. However, the value in cell A1 is mismatched due to the array collection used in the OR function, which we mentioned above.

List of supported formulas: https://ej2.syncfusion.com/documentation/spreadsheet/formulas#supported-formulas

Additionally, we have planned to include these functions in our spreadsheet and have confirmed this as an improvement. We have logged it as a feature, and it will be available in one of our upcoming releases. You can communicate and track the status of this feature using the link below from our feedback portal.

Feedback links for tracking purposes:
https://www.syncfusion.com/feedback/42091/need-to-include-the-listed-unsupported-formula-into-spreadsheet-component

https://www.syncfusion.com/feedback/12372/left-indirect-formula-support-in-spreadsheet

At the planning stage for every release cycle, we review all the open features once again and finalize features for implementation based on specific parameters including product vision, technological feasibility, and customer interest. Once we have anything definite to share about these features implementation, we will move the feedback to scheduled status with the tentative release timeline. We appreciate your patience until then.