Keep Formula When Pressing DEL Key

Keep Formula When Pressing DEL Key

Posted by: lucian.stana on 4 June 2025, 5:51 am EST

• 

  lucian.stana

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 4 June 2025, 5:51 am EST

  Hello,

  We have a special situation that we want to handle differently than what happens now. On a cell that has a formula that supports distribution, we want to keep the existing formula, replace the value with 0 and distribute that to the affected cells. What happens now is the content, including the formula, is deleted.
  
  Would this be possible without implementing an entirely custom logic for when the DEL key is pressed?

• 

  priyam.kushwaha

  

  • Post Options:
  • Link
    Copy

  Posted 5 June 2025, 1:51 am EST

  Hi,

  As per your requirement, we understand that it is required to fill the cell range with zero or retain the formula when a delete key is pressed on a cell containing the array formula that spills across multiple cells.

  It is possible by handling the delete key command of the Spread command manager: https://developer.mescius.com/spreadjs/api/classes/GC.Spread.Commands.CommandManager. Please refer to the code snippet below that illustrates the same:

  const oldDelKeyExecute = commandManager.clear.execute;
  const deleteCommand = {
    canUndo: true,
    execute: function (context, options, isUndo) {
      const Commands = GC.Spread.Sheets.Commands;
      options.cmd = "deleteDistFormula";
      if (isUndo) {
        Commands.undoTransaction(context, options);
        return true;
      } else {
        const sheet = context.getSheetFromName(options.sheetName);
        const activeColumn = sheet.getActiveColumnIndex();
        const activeRow = sheet.getActiveRowIndex();
        const formulaInformation = sheet.getFormulaInformation(activeRow, activeColumn);
        if(formulaInformation.formula) {
  	const baseRange = formulaInformation.baseRange;
  	if(baseRange.rowCount > 1 || baseRange.colCount > 1) {
            Commands.startTransaction(context, options);
         	  sheet.suspendPaint();
  	  const zeros = Array.from({ length: baseRange.rowCount }, () => new Array(baseRange.colCount).fill(0));
  	  sheet.setFormula(activeRow, activeColumn, "");
  	  sheet.setArray(activeRow, activeColumn, zeros);
  	  sheet.resumePaint();
            Commands.endTransaction(context, options);
  	} else {
  	  oldDelKeyExecute(context, options, isUndo);
  	}
        } else {
  	oldDelKeyExecute(context, options, isUndo);
        }
        return true;
      }
    }
  };
  commandManager.register("deleteDistFormula", deleteCommand);
  spread.commandManager().setShortcutKey("deleteDistFormula", GC.Spread.Commands.Key.del, false, false, false, false);

  In case it is required to keep the existing formula and not trigger the delete key action, the same can be achieved with the above code snippet by not setting the array of zeroes in the required cell range.

  You can further refer to the attached sample that uses the above code snippet and handles the delete operation (see below).

  Sample: https://jscodemine.mescius.io/share/uvKAiIUMIEGU0fmyx6logA/?defaultOpen={"OpenedFileName"%3A["%2Findex.html"%2C"%2Fapp.js"]%2C"ActiveFile"%3A"%2Fapp.js"}

  GIF:

  Please let us know if you require any further assistance.

  Regards,