We are facing an issue now when trying to use custom calculation with GridJs.
When We are trying to show our Excel(s) online, we use the Custom Calculation engine and rather than re-calculate our custom function, we’d like to set their existing “value” to the “calculated value”.
So the code looks like this (this is our implementation for GridAbstractCalculationEngine):
Running this code locally (unit testing), works as expected, we set the value and it see it.
But, when this code is running on the server, we get an empty values for data.Value and for data.StringValue we do get value data.Formula.
The log on the server look like that: CustomCalculationEngine.Calculate Formula==DR.GET(SumOfSales), Value=, StringValue=
The Excel we are trying to edit online is CustomCalcTest.zip (9.2 KB) - the issue occurs on the custom calculation of cell A1
Your support will be much appreciated!
Thanks,
Ori
Please make sure that you are using latest version (Aspose.Cells.GridJs 22.3 library) on the server. Also, give us environment details about the server, we will check it soon.
Please notice, we have logged a ticket with an id “CELLSGRIDJS-556” for your issue. We will investigate and look into the details of the issue to try to figure it out soon.
Once we have an update on it, we will let you know.
@orik
can you write a console application and run it in your docker server.
you need to use cells.dll for .netstandard
the code is below:
class TestCustomCalculation : AbstractCalculationEngine
{
//then cells calculate formula will call this method during calculating
public override void Calculate(CalculationData data)
{
Console.WriteLine(data.Cell.Formula+", data.Cell.Value:" + data.Cell.Value+",string value:"+data.Cell.StringValue);
data.CalculatedValue = data.Cell.Value;
}
}
//in main function:
//please use the correct file path in you enviroment
file = @“D:\codebase\customerissue\wb\tempfromdownload\CustomCalcTest.xlsx”;
Workbook wbc = null;
using (FileStream ms = new FileStream(file, FileMode.Open))
{
wbc = new Workbook(ms, new LoadOptions(LoadFormat.Xlsx));
}
CalculationOptions opts = new CalculationOptions();
opts.CustomEngine = new TestCustomCalculation();
wbc.CalculateFormula(opts);
//please use the correct file path in you enviroment
// string file2 = @"D:\codebase\customerissue\wb\tempfromdownload\testsave.xlsx";
using (MemoryStream ms = new MemoryStream())
{
wbc.Save(ms, SaveFormat.Xlsx);
wbc = new Workbook(ms,new LoadOptions(LoadFormat.Xlsx));
}
Console.WriteLine("after reload");
wbc.CalculateFormula(opts);
Console.WriteLine("after change value");
wbc.Worksheets[0].Cells["F1"].Formula = "=sin(1)";
wbc.CalculateFormula(opts);
run this console application and let’s see the output.
in our enviroment ,everything is ok.
I think we have some misunderstanding here, our issue is not with the AbstractCalculationEngine it is in the implementation of the GridAbstractCalculationEngine which was introduced in the last version…
So I’m not sure how will the testing you suggest will help us to debug it.
Actually, GridJs uses AbstractCalculationEngine, it uses Aspose.Cells library APIs basically. So, please let’s know what is your observation in your environment as we cannot reproduce this issue on our end. This will be helpful for us to investigate the issue.
Well, I can give it a try with the AbstractCalculationEngine test app as you suggested, we have some other implementation where we use it and it works for us. The question is whether you tried it with the GridAbstractCalculationEngine and worked for you as excepted?
BTW - The API is quite different as in the GridAbstractCalculationEngine there is no access to Cell object (it probably somehow encapsulate it).
Wouldn’t it be more useful to try figuring out the issue while we investigate the the GridAbstractCalculationEngine object, rather than testing the AbstractCalculationEngine which is mature and works pretty well as far as we know?
If performing the required test is what needed, to proceed we’ll obviously do it.
Thank you for the response.
I wonder how did you run the sample? I was able to run it locally and it worked as expected, as I originally wrote, the GridJS is working fine also when we run the tests locally…
ApiController.cs - Contains the relevant API we are using for opening the file (openFile+ Get Details) CustomCalculationEngine.cs - The implementation of the custom calc extract-2022-03-23T08_22_35.291Z.xlsx - The export of our application logs. the interesting part are cells B5 (the custom calc log) & B4 (Get details response) GetDetailsResponseFromBrowser.json - the response of get details from the browser (debug tools > Response)
Please let me know if anything else is needed
Thanks,
Ori
Through the json, we can see in sheet with name" Account type > Clasification in cells[0,4]" it set the formula DR.GET(SumOfSales) , let me call it testfile1
not the same file as you provided in the CustomCalcTest.xlsx ,in cells[0,0] it set the formula DR.GET(SumOfSales), let me call it testfile2
so can you try the console application against testfile1.
and also can you provide the detail log and json result based on GridJs open on testfile2
Hi - Sorry for online example, I’m using the attached Excel file.
It doesn’t really matter what file is being used we always fail with the getting the relevant info when we run it on the server.
@orik I’ve updated the ApiController.cs to add some debug info
Please build and run in your side. let me see the log thenApiController.zip (2.0 KB)
the log will contains lines starts with:"Test want "
@orik
that is correct,let 's check the log.
from the log we can see when new workbook from the incoming stream
for the cell E1
the cell.formula==DR.GET(SumOfSales),cell.stringvalue=,cell.value="
so it already has issue there.
from this log !!!
but when you run the console application to load the file directly, It seems ok,
So I doubt the result log from the console application run in your side ,
whether is the same file ??? ,Please use this file :CustomCalc-NewExample.xlsx
please run the console app and use the same excel file again.
the console app code :
//please use the correct file path in you enviroment
file = @“D:\codebase\customerissue\wb\tempfromdownload\CustomCalc-NewExample.xlsx”;
Workbook wbc = null;
using (FileStream ms = new FileStream(file, FileMode.Open))
{
wbc = new Workbook(ms, new LoadOptions(LoadFormat.Xlsx));
}
Cell ac = wbc.Worksheets[0].Cells["E1"];
Console.WriteLine("after new workbook ReCalculateOnOpen: " + wbc.Settings.ReCalculateOnOpen);
Console.WriteLine("after new workbook ac formula: " + ac.Formula+" ,ac.value:"+ac.Value+",ac.stringvalue:"+ac.StringValue);
CalculationOptions opts = new CalculationOptions();
opts.CustomEngine = new TestCustomCalculation();
wbc.CalculateFormula(opts);
//please use the correct file path in you enviroment
// string file2 = @"D:\codebase\customerissue\wb\tempfromdownload\testsave.xlsx";
using (MemoryStream ms = new MemoryStream())
{
wbc.Save(ms, SaveFormat.Xlsx);
wbc = new Workbook(ms,new LoadOptions(LoadFormat.Xlsx));
}
Console.WriteLine("after reload");
wbc.CalculateFormula(opts);
Console.WriteLine("after change value");
wbc.Worksheets[0].Cells["F1"].Formula = "=sin(1)";
wbc.CalculateFormula(opts);
