Forums Home / Spread / SpreadJS
Posted by: thanh.minh.le on 27 March 2023, 2:59 am EST
thanh.minh.le
Posted 27 March 2023, 2:59 am EST
Hi,
For example, I’m using SUM(B7:B9), then I select row 7 and insert a new row. Now it’s become SUM(B8:B10).
I know this is the default design and it’s like Excel, but is there a way to make the ranges of the function automatically update when a row is inserted?
Like when I insert a new row, the formula will be updated like this: SUM(B7:B10)
Regards,
Thanh
avinash.pathak
Posted 28 March 2023, 7:39 am EST
Hello,
Please note that when you insert a row at the first row of a cell range referenced by a formula, then reference is not modified in the formula to include the added row. This behavior is by design and aligns with the behavior of MS Excel.
You can override the “gc.spread.contextMenu.insertRows” command of spreadjs to manually update the reference inside the formula. This command is executed when a row is added from context menu “Insert” option.
First, you need to get the formula using sheet.getFormula() method and then get the cell ranges from the formula using GC.Spread.Sheets.CalcEngine.formulaToRanges() method. You need to modify the ranges as per the rows added and then convert the range to reference string using GC.Spread.Sheets.CalcEngine.rangeToFormula() method. And finally, set the formula with modified cell references using sheet.setFormula() method.
Please refer to the attached sample for further understanding.
Sample: https://jscodemine.grapecity.com/share/VD8QcBgoKkqxgfIqkVuuNw/?defaultOpen={“OpenedFileName”%3A[“%2Findex.html”%2C"%2Fsrc%2Fapp.js"]%2C"ActiveFile"%3A"%2Fsrc%2Fapp.js"}
Please don’t hesitate to ask if you face any issue.
Doc reference
GC.Spread.Sheets.CalcEngine.rangeToFormula(): https://www.grapecity.com/spreadjs/api/v15/modules/GC.Spread.Sheets.CalcEngine#rangetoformula
GC.Spread.Sheets.CalcEngine.formulaToRanges(): https://www.grapecity.com/spreadjs/api/v15/modules/GC.Spread.Sheets.CalcEngine#formulatoranges
regards,
Avinash
nhan.thanh.nguyen
Posted 29 March 2023, 4:10 am EST
Hi,
I cannot override insertRows command on spreadJs 14 version 14.0.4.
Could you please help me on this version?
let command = commandManager[‘gc.spread.contextMenu.insertRows’];
// command is null => execute error
let oldExecute = command.execute;
Thanks,
Nhan Nguyen
avinash.pathak
Posted 30 March 2023, 7:23 am EST
Hello,
In SpreadJS v14, context menu commands such as ‘gc.spread.contextMenu.insertRows’ are not added to command manager by default. They are added when the context menu is opened for the first time. That is why you are getting the error while trying to override the ‘gc.spread.contextMenu.insertRows’ command because it is not available on command manager.
Alternatively, you can create a custom command which inserts the rows in the sheet and also modifies the reference of a formula. You can register the custom command using commandManager.register() method.
You also need to modify the “Insert” option of the context menu to execute the custom command instead of default command. For this, you can get the menuData items using spread.contextMenu.menuData property and update the menu data item for “Inset” option of context menu.
Please refer to the attached sample in Spreadjs v14.0.4
Sample: https://codesandbox.io/s/solution-forked-mcb4dn?file=/src/index.js
Please let us know if you still face any difficulties.
Doc reference
GC.Spread.Sheets.CalcEngine.rangeToFormula(): https://www.grapecity.com/spreadjs/api/v15/modules/GC.Spread.Sheets.CalcEngine#rangetoformula
GC.Spread.Sheets.CalcEngine.formulaToRanges(): https://www.grapecity.com/spreadjs/api/v15/modules/GC.Spread.Sheets.CalcEngine#formulatoranges
commandManager.register():https://www.grapecity.com/spreadjs/api/classes/GC.Spread.Commands.CommandManager#register
contextMenu.menuData:https://www.grapecity.com/spreadjs/api/v16/classes/GC.Spread.Sheets.ContextMenu.ContextMenu#menudata
regards,
Avinash
