Get Formula of a Cell

[Diego Matus]

I've made a simple application with a spreadsheet inside it. I can get the normal values of a cell with:

var cellHtml = this.spreadsheetObj.getCell(1,1);
var value = cellHtml.innerText;

However, I can't find a way to access the formula value of the cell (for example, "=SUM(A4:A7)"), is this functionality not available?

Thank you for your time.

[Sangeetha Priya Murugan]

Hi Diego,

Your requirement can be achievable by using the getCell and getRangeIndexes methods as shown below.

Code Block:

  getData() {

    let sheet = this.spreadsheetObj.getActiveSheet();

    var cell = sheet.activeCell;

    var cellIdx = getRangeIndexes(cell);

    // Get the cell model for active cell

    var cellObj = getCell(cellIdx[0], cellIdx[1], sheet);

    if (cellObj) {

      // Returns the cell formula

      console.log('formula:' + cellObj.formula);

      // Returns the cell formula result

      console.log('formula Result:' + cellObj.value);

    }

  }

For your reference, we have prepared the sample that returns the formula and its result in console window via button click event. Please find the link below.

Sample Link: https://stackblitz.com/edit/angular-guq2da?file=src%2Fapp.component.html,src%2Fapp.component.ts

[Diego Matus]

Thank you very much! It worked great!

One last thing, I tried altering the formula of the selected cell using the example mentioned above, but altering the formula in this way does not recalculate the value, is there any method I could call to recalculate the values for the cells in a sheet?

[Sangeetha Priya Murugan]

Hi Diego,

We have changed the formula value and the formula result calculated properly. So, before we proceed further, please explain how you alter the formula value in spreadsheet whether in UI level or programmatically. Share it in detail with your customization codes and video demonstration. If possible, please replicate your issue in the previously shared sample and send it back to us. Based on that we will be able to check and provide you with a better solution quickly.

[Diego Matus]

Hello!

I have altered the shared sample to add a new button, this button changes the formula of the A2 cell into a SUM of other cells, this does not change the cell's value, thank you for your help!

https://stackblitz.com/edit/angular-guq2da-lkets8?file=src%2Fapp.component.ts

[Sangeetha Priya Murugan]

Hi Diego,

Based on your provided codes you have directly update the formula value in cellModel and it will not reflect in UI. So, we suggest you to use updateCell method to update the formula values in the spreadsheet. And we have modified your provided sample based on our suggestion. Please find the link below.

Sample Link: https://stackblitz.com/edit/angular-guq2da-cgmzqh?file=src%2Fapp.component.ts

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