Hi There,
I am using Aspose.Cells.GridJs latest version (2024.2.0).
I have an excel file with 2 custom functions: =@DR.GET(bla) & @DR.GET(Value). One of the cell’s formula in the excel file is an operation of the 2: =@DR.GET(bla)+@DR.GET(Value). Its result is 262.
When I send an update (i.e. gridJsWorkbook.UpdateCell) to a cell that is not related to the custom functions and their calculation, I get a result to update the cell result with custom function operation to 524.
I have attached the following under grid-js.zip:
file-grid-minmize.xlsx - the excel input file.
input.json - file holding the content of UpdateCell input.
output.json - file holding the content of UpdateCell output.
grid-js-code.zip - holding 3 classes for implementing GridJs SDK.
Use GridJsHandler.UpdateCell(file-grid-minmize.xlsx, input.json, output.json) to reproduce the issue.
grid-js.zip (10.6 KB)
Please advise,
Shlomi
@shlomi.z ，
We are evaluating your case, we will get back once there are some updates.
@shlomi.z,
Thanks for the sample project with resource files.
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSGRIDJS-1016,CELLSNET-55145
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
@shlomi.z
We find the behavior is ok. After loading the file, the C4 value is 262. It does not trigger the formula calculation. And later, after updating the D1 value to 0.6, we will recalculate the formula, so C4 formula is（=@DR.GET(bla)+@DR.GET(Value)）and is recalculated. @DR.GET both return 262, so C4 now becomes 262+262=524. Or you can write code like this, thus the C4 value will be 262 always:
public override void Calculate(GridCalculationData data)
{
if(data.Value==null)
{
data.CalculatedValue = "Missing";
}
else
{//here is the tigger ,if we throw exception it will just keep the cell origin value
throw new Exception();
}
//var newValue = data.Value ?? "Missing";
//data.CalculatedValue = newValue;
}
@peter.zhou,
According to the file, the value of =@DR.GET(bla)+@DR.GET(Value) is 262. It does not mean that DR.GET(bla) value is 262 nor DR.GET(Value) value is 262. In addition, throwing exception if data.Value is not null will cause problems for our customers. The example that I showed is small, and we cannot allow not calculating values for custom function.
I would think of that like a math equation: if x+y = 262 then we cannot deduce the values of x nor y. But if we’ll have an equation like 100 + x + y, we can deduce that it equals 362.
I hope I explained myself correctly.
@shlomi.z
Yes, if DR.GET(bla)+DR.GET(Value) is 262, 100+DR.GET(bla)+DR.GET(Value) will become 362. However, when some referenced data was changed and re-calculation is required for those formulas, it is impossible for us to know whether DR.GET(bla) and DR.GET(Value) should be re-calculated or not. So we have to send custom functions to user’s implementation, because only user(and the implementation) knows how to calculate those custom functions.
If there are some specific patterns for those expressions, such as this one in example: DR.GET(bla)+DR.GET(Value), we think you may put it into another custom function to hold and control the calculated value. For example:
cell.Formula="=MYHOLDER(DR.GET(bla)+DR.GET(Value))";
Then you can keep or change the value of expression DR.GET(bla)+DR.GET(Value) while calcuating MYHOLDER function in you implementation.
I am trying to understand from your explanation what is the difference between the behavior on GridJS and Excel.Cells for Desktop version.
We’ve been using Aspose for Excel.Cells for a few years now and such calculations (arithmetic operations between custom functions) were/are working perfectly.
This is causing a big issue with some of the customers - their Excel files build with DR.GET custom functions are not working on GridJS.
@eyalmolad
GridJs focus on the UI operation and just wrap/use the cells api.
GridCalculationData just encapsulate CalculationData.
GridCalculationData.CalculatedValue just use CalculationData.CalculatedValue
GridCalculationData.Value use CalculationData.Cell.Value
the basic logic is same inside actually.
Can you show your code example which uses cells api:
(AbstractCalculationEngine,CalculationData …etc)
directly for this file that working perfectly.
according to the json and file provided above ,we can simulate the process.
the update operation in code logic from GridJs ui to cells api is:
internal class GridCustomCalculationCELLSGRIDJS_1016 : AbstractCalculationEngine
{
private MyCalculationCELLSGRIDJS_1016 _engine;
internal MyCalculationCELLSGRIDJS_1016 CalculateEngin
{
get { return _engine; }
}
public GridCustomCalculationCELLSGRIDJS_1016(MyCalculationCELLSGRIDJS_1016 engine)
{
_engine = engine;
}
//then cells calculate formula will call this method during calculating
public override void Calculate(CalculationData data)
{
_engine.Calculate(data);
}
}
class MyCalculationCELLSGRIDJS_1016 : AbstractCalculationEngine
{
public override void Calculate(CalculationData data)
{ Console.WriteLine("Calculate Param info:text:"+data.GetParamText(0) +",value:"+ data.GetParamValue(0)+",data.Cell:"+data.Cell.Name+" cell value:"+data.Cell.Value);
var newValue = data.Cell.Value ?? "Missing";
data.CalculatedValue = newValue;
}
}
private static void testcalculateengineCELLSGRIDJS_1016()
{
CalculationOptions coptions = new CalculationOptions();
GridCustomCalculationCELLSGRIDJS_1016 gcustomengine = new GridCustomCalculationCELLSGRIDJS_1016(new MyCalculationCELLSGRIDJS_1016());
coptions.CustomEngine = gcustomengine;
Workbook wb = new Workbook(@"D:\codebase\customerissue\wb\tempfromdownload\file-grid-minmize.xlsx");
if (wb.Settings.FormulaSettings.CalculateOnOpen)
{
wb.CalculateFormula(coptions);
}
Console.WriteLine("after file loaded c4 actual :"+wb.Worksheets["Sheet1"].Cells["C4"].Value+" shall be :262");
wb.Worksheets["Sheet1"].Cells["D1"].PutValue(0.6f);
wb.CalculateFormula(coptions);
Console.WriteLine("after do update at D1 and recalculate for the whole workbook,the c4 actual :"+wb.Worksheets["Sheet1"].Cells["C4"].Value + "expected to be 262 or 262+262");
}