Forums Home / Spread / SpreadJS
Posted by: Fabrice.Mainguene on 29 November 2023, 8:09 pm EST
Fabrice.Mainguene
Posted 29 November 2023, 8:09 pm EST
Hi,
I use a lot of custom functions in my documents and it appears that the FileLoaded event occurs before the end of the calculation of all my functions.
Is there another way to know when my document and my functions are fully calculated?
ankit.kumar
Posted 30 November 2023, 9:35 pm EST
Hi,
From what I gather, you’re indicating that the fileLoaded event takes place prior to the completion of the calculation involving many custom functions.
However, I have been unsuccessful in reproducing this behavior. On my end, the fileLoaded event is triggered after the calculation of custom functions. I have provided a sample for your reference. In the sample, initially save the .sjs file with default settings. Subsequently, open the .sjs file with default settings, enable full calculation, and observe(in console) that the fileLoaded event is called after the custom formulas’ calculation .
Can you share a sample demonstrating the behavior you’ve observed, or could you modify an provided sample to replicate the issue? So that we can assist you better.
Best regards,
Ankit
Fabrice.Mainguene
Posted 1 December 2023, 1:20 am EST - Updated 1 December 2023, 1:23 am EST
Hi,
Here an example:
var GetNumberFromServer = function () {
};
GetNumberFromServer.prototype = new GC.Spread.CalcEngine.Functions.AsyncFunction("GETNUMBERFROMSERVER", 1, 2);
GetNumberFromServer.prototype.evaluate = function (context, arg1, arg2) {
setTimeout(function () {
var value = Math.random() + 1;
context.setAsyncResult(value);
}, 500);
};
GC.Spread.CalcEngine.Functions.defineGlobalCustomFunction("GETNUMBERFROMSERVER", new GetNumberFromServer());
let designer = GC.Spread.Sheets.Designer.findControl(document.getElementById("gc-designer-container"));
designer.bind(GC.Spread.Sheets.Designer.Events.FileLoaded, (event, data)=>{
console.log("file has loaded");
console.log("E19="+activeSheet.getCell(18,4).value());
});and I join the document to load as attachment spreadjs (2).zip
When I test this code, I get E19=0 because my function has not finished to calculate.
It is a problem when I want to automatically open a document and export it in pdf or excel format because some cells are wrong.
ankit.kumar
Posted 4 December 2023, 3:12 pm EST
Hi,
There is no API or event available for detecting the calculation completion of all custom asynchronous functions.
However, if you still wish to achieve this, you can utilize the “rangeChanged” event which give changedCells information where formula evaluation happened.
There is two possible solutions with information of cells where formulas have used:
Note: Each approach requires information of cells where formulas have applied.
References:
RangedChanged: https://developer.mescius.com/spreadjs/api/classes/GC.Spread.Sheets.Events#rangechanged
Best regards,
Ankit
Fabrice.Mainguene
Posted 4 December 2023, 8:01 pm EST
HI,
Thanks for the answer, but unfortunately the documents are made by my customer so I have any information on where the formulas have been applied.
Have you a feature to manage this event in your backlog?
ankit.kumar
Posted 6 December 2023, 5:17 am EST
Hi,
There no direct feature available for this.
But you can achieve you requirements by following below steps:
spread.fromJSON(data,{doNotRecalculateAfterLoad: true});cellReferences = getMapFormulaReferencesOfCellsForAsync(spread);
function getMapFormulaReferencesOfCellsForAsync( spread){
const info = {};
spread.sheets.forEach( sheet => {
const rows = sheet.getRowCount();
const cols = sheet.getColumnCount();
for( let i = 0; i < rows; i++){
for(let j = 0; j < cols; j++){
const formulaInfo = sheet.getFormulaInformation(i,j);
if(formulaInfo.hasFormula){
const functionName = formulaInfo.formula.slice(0,formulaInfo.formula.indexOf("("));
const customfunction = GC.Spread.CalcEngine.Functions.findGlobalFunction(functionName)
if(customfunction instanceof GC.Spread.CalcEngine.Functions.AsyncFunction){
if(!info[sheet.name()]){
info[sheet.name()] = {};
}
if(!info[sheet.name()][i]){
info[sheet.name()][i] = new Set();
}
info[sheet.name()][i].add(j);
}
}
}
}
})
return info;
}spread.sheets.forEach( sheet => sheet.recalcAll());let flag = true;
designer.getWorkbook().bind(GC.Spread.Sheets.Events.RangeChanged, (e, info) => {
// when function evalution happen
if(cellReferences && info.action == 6){
for( let i = 0; i < info.changedCells.length; i++){
if(cellReferences[info.sheetName] && cellReferences[info.sheetName][info.changedCells[i].row] && cellReferences[info.sheetName][info.changedCells[i].row].has(info.changedCells[i].col)){
cellReferences[info.sheetName][info.changedCells[i].row].delete(info.changedCells[i].col);
if(!cellReferences[info.sheetName][info.changedCells[i].row].size){
delete cellReferences[info.sheetName][info.changedCells[i].row];
}
if(!Object.keys(cellReferences[info.sheetName]).length){
delete cellReferences[info.sheetName];
}
}
if(isAllCalculationDone(cellReferences)){
console.log("All calculation done and now exporting!!")
if(flag){
cellReferences = getMapFormulaReferencesOfCellsForAsync(spread);
flag = false;
return;
}
spread.export(function(blob) { saveAs(blob, "export.xlsx"); },function(){}, {includeFormulas: false});
flag = true;
cellReferences = undefined;
break;
}else{
console.log("Calculation are not done")
}
}
}
});Note: When you recalculate all sheets, the rangeChanged event will trigger for each sheet, even if the calculation of asynchronous results is not set. It is crucial to handle this case. I’ve addressed this scenario in the provided sample for your reference.
I have attached a sample to refer.
Best regards,
Ankit
