Get cell formula arguments as a list

Get cell formula arguments as a list

Posted by: kim.dok on 3 January 2023, 1:00 pm EST

• 

  kim.dok

  • Post Options:
  • Link
    Copy

  Posted 3 January 2023, 1:00 pm EST

  Hi, how can we get formula parameter arguments as string list?
  
  for example if I have the formula: SUM(A1,AVERAGE(1,SUM(18,B1),C3),3)
  
  we want to get the arguments as array of string: [“A1”, “AVERAGE(1,SUM(18,B1),C3)”, “3”]
  
  we saw you have the function functionToExpression which is returning an object that contains the arguments but not as string.

  can we have some guidance on how to achieve the required outcome?

  Thanks.

• 

  avinash.pathak

  

  • Post Options:
  • Link
    Copy

  Posted 4 January 2023, 2:14 am EST

  Hi,

  We are sorry but there is no such method available to provide the plane argument string but functionToExpression returns the arguments object which has a property called type which is nothing but an ExpressionType enum. We could use it and create the arguments string. Please refer to the following code snippet which creates an argument string using ExpressionType number, function, and reference.

  For the list of Expression Types you may refer to the following API:

  Expression Type: https://www.grapecity.com/spreadjs/api/enums/GC.Spread.CalcEngine.ExpressionType

  
  let str = "SUM(A1,AVERAGE(1,SUM(18,B1),C3),3)";
  var expression = GC.Spread.Sheets.CalcEngine.formulaToExpression(
    spread.getActiveSheet(),
    str,
    0,
    0
  );
  let res = getStringFromArgs(expression.arguments);
  
  function getStringFromArgs(argument) {
    let res = [];
    argument.forEach((arg) => {
      let { type } = arg;
      let sheet = spread.getActiveSheet();
      switch (type) {
        case GC.Spread.CalcEngine.ExpressionType.function:
          res.push(
            arg.functionName + "(" + getStringFromArgs(arg.arguments) + ")"
          );
          break;
        case GC.Spread.CalcEngine.ExpressionType.number:
          res.push(arg.originalValue);
          break;
        case GC.Spread.CalcEngine.ExpressionType.reference:
          let str = GC.Spread.Sheets.CalcEngine.rangeToFormula(
            new GC.Spread.Sheets.CellRange(sheet, arg.row, arg.column, 1, 1),
            0,
            0,
            true
          );
          res.push(str.replace("$", ""));
          break;
        default:
          break;
      }
    });
    return res;
  }

  Regards,
  
  Avinash