This example was created just to show the issue at hand, but this does severely affect our application.
We have custom functions that pull data from separate sheets, which might be pointing to a cell that in itself is the same custom function that points at other custom functions. We have examples of this that goes up to 4-5 cell/functions deep.
The getCellValue() function equates the target cell’s function, so if the targeted cell has a function, it triggers off many times. For one cell on sheet 1, we might see 12 functions all have to evaluate themselves.
For initialization, we see a delay of 20+ seconds before the flexsheet opens, and this is on a powerful development machine. On our clients’ more conservative PCs, I would expect this to be even slower.
All that said, flexsheet runs these functions multiple times each, same as the “intended behavior” of the simplistic example I provided above. There must be a way to limit multiple refreshes.
While I am very much concerned with these functions running twice during normal use (post initialization), the flaw is exponentially more severe during the initial load as the sheet runs the functions several times over (for use in triggering events on cell changes based on SUMs/Functions):
https://www.grapecity.com/en/forums/wijmo/how-to-detect-cell-change-
// register all formulas in sheet init
registerAllFormulas(flexSheet){
flexSheet.beginUpdate();
const selectedIndex = flexSheet.selectedSheetIndex;
for(let i = 0; i < flexSheet.sheets.length; i++){
this.registerSheetFormulas(flexSheet, flexSheet.sheets[i]);
}
flexSheet.selectedSheetIndex = selectedIndex;
flexSheet.endUpdate();
}
// register formulas in single sheet
registerSheetFormulas(flexSheet, sheet){
flexSheet.selectedSheetIndex = flexSheet.sheets.indexOf(sheet);
for(let i = 0; i < flexSheet.rows.length; i++){
for(let j = 0; j < flexSheet.columns.length; j++){
const cellData = flexSheet.getCellData(i,j,false);
if(cellData && cellData.toString().startsWith('=')){
flexSheet._calcEngine.evaluate(cellData);
this.addRemoveExpDependencies(cellData, flexSheet, sheet.name.toLowerCase(), i, j);
}
}
}
}
// add/remove all dependencies a cell requires based on expresion in cell
addRemoveExpDependencies(exp:string, flexSheet, sheetName, row, col, shouldRemove?){
let expr;
try{
expr = flexSheet._calcEngine._checkCache(exp);
}catch(err){
return;
}
if(typeof expr === 'string'){
return;
}
const dependentCell = sheetName+'__!'+row+'__!'+col;
this.addRemoveExp(expr, flexSheet.selectedSheet.name.toLowerCase(), dependentCell.toLowerCase(), shouldRemove);
}
// add/remove all dependencies a cell requires based on expresion in cell
addRemoveExp(exp, sheetName, dependentCell, shouldRemove?){
if(exp.cells) {
this.addRemoveCells(exp.cells, exp.sheetRef || sheetName, dependentCell, shouldRemove);
return;
} else if (exp._params) {
this.addRemoveParams(exp._params, sheetName, dependentCell, shouldRemove);
return;
}
if (exp._leftExpr) {
this.addRemoveExp(exp._leftExpr, sheetName, dependentCell, shouldRemove);
}
if (exp._rightExpr) {
this.addRemoveExp(exp._rightExpr, sheetName, dependentCell, shouldRemove);
}
}
// add/remove dependencies on a function param
addRemoveParams(params, sheetName, dependentCell, shouldRemove?){
for(let i = 0; i < params.length; i++){
if (params[i].cells && sheetName) {
this.addRemoveCells(params[i].cells, params[i].sheetRef || sheetName, dependentCell, shouldRemove);
}
}
}
addRemoveCells(cells , sheetName, dependentCell, shouldRemove?){
for(let i = cells.topRow; i <= cells.bottomRow; i++){
for(let j = cells.leftCol; j <= cells.rightCol; j++){
const dependencyCell = sheetName+'__!'+i+'__!'+j;
if (!this.cellsDependencyMonitor[dependencyCell]) {
this.cellsDependencyMonitor[dependencyCell] = [];
}
const tempIndex = this.cellsDependencyMonitor[dependencyCell].indexOf(dependentCell);
if (!shouldRemove && tempIndex < 0) {
this.cellsDependencyMonitor[dependencyCell].push(dependentCell);
} else if (shouldRemove && tempIndex >= 0){
this.cellsDependencyMonitor[dependencyCell].splice(tempIndex, 1);
}
}
}
}
