GridAbstractCalculationEngine Issues

[orik]

Hi,
I’m working with the latest version (#23) of Aspose.Cells & Aspose.Cells.GridJs
After we got the fix for the two tickets we had (Aspose.Cells v22.2 - fails to MergeExcelFileFromJson (GridJsWorkbook) & GridJS - need the option to set the ReCalculateOnOpen)

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):

    public override void Calculate(GridCalculationData data)
    {
        _logger.LogInformation($"CustomCalculationEngine.Calculate  Formula={data.Formula}, Value={data.Value}, StringValue={data.StringValue}");
       
        data.CalculatedValue = data.Value;
    }

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

[Amjad_Sahi]

@orik,

Thanks for the sample file.

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.

[orik]

@Amjad_Sahi - thank you for the quick response. We use version 22.3 also on the server otherwise, the custom calculation wouldn’t run…

As for the server details, we are using ASP.NET service with .NET Core 6.0, we create a docker that runs on Azure cloud as a kubernetes pod

[Amjad_Sahi]

@orik,

Thanks for providing us environment details.

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.

[peter.zhou]

@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.

[orik]

Hi @peter.zhou
Thanks you for your answer.

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.

Please advice,
Thanks,
Ori

[Amjad_Sahi]

@orik,

Thanks for sharing the details.

We will be looking into it and get back to you soon.

Sorry for any inconvenience caused!

[peter.zhou]

@orik,

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.

[orik]

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.

Thanks for your support!
Ori

[peter.zhou]

@orik

it probably somehow encapsulate it

it probably somehow encapsulate it ，
You are correct,
Please do it.we are waiting for the result.

[orik]

Hey @peter.zhou

I’ve run the tests on docker and works as expected.
Please see attached screenshot.

image.png (45.4 KB)

How can we proceed with our issue?

Thanks,
Ori

[Amjad_Sahi]

@orik,

Thanks for your feedback.

We will evaluate it and get back to you soon.

[peter.zhou]

@orik
when will this log trigger. what is your steps to make the issue happen ,
Per my obervation, when we open the file in GridJs,
A1 shows the expected value :9328.5677.
We just use the demo project https://github.com/aspose-cells/Aspose.Cells-for-.NET/tree/master/Examples_GridJs
and load the file .

[orik]

@peter.zhou

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…

As for my code,

Please see attached zip CustomCalculationEngine.zip (24.7 KB)
, it contains:

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

[peter.zhou]

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

[orik]

CustomCalc-NewExample.zip (12.0 KB)

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.

Please let me know if anything else is needed.

[Amjad_Sahi]

@orik,

Thanks for the Excel file.

We will evaluate your issue further and get back to you soon.

[peter.zhou]

@peter.zhou

 CalculationOptions opts = new CalculationOptions();

        opts.CustomEngine = new TestCustomCalculation();

        wbc.CalculateFormula(opts);

@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]

Hi @peter.zhou

Please find attached the logs with your code.
Please note that in order to have this code properly compiled, I had to modify this code (add “Cell.”):

_logger.LogInformation($“Test want ###cellsCustomCalculationEngineCells.Calculate Formula={data.Cell.Formula}, Value={data.Cell.Value}, StringValue={data.Cell.StringValue}”);

Thanks,
Ori
extract-2022-03-24T09_27_04.830Z.zip (12.1 KB)

[peter.zhou]

@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);