Calculate() does not work for all cells with formulas

Aspose.Cells Product Family
,
1

Dear Aspose Team,

There is a file with data in A1:K1 and custom formula A2:K2

Aspose does not call Calculate() from AbstractCalculationEngine for cells J2, K2 where there is formula.

public class SampleEngine : AbstractCalculationEngine
{
  // Called only for cells A2 to I2
  // Not called for J2, K2
  public override void Calculate(CalculationData data)
  {
     var val = data.Cell.Value;
     data.Cell.Formula = data.Cell.R1C1Formula = null;
     data.Cell.Value = val;
     data.CalculatedValue = data.Cell.Value;
  }
}

var opts = new CalculationOptions { CustomEngine = new SampleEngine() };
workbook.CalculateFormula(opts);

File: apsose-test.xlsx.zip (71.9 KB)
Version: Aspose.Cells 24.12.0/Aspose.Cell 25.2.0, .NET 7

2

@aleksandr.l

It seems that the Calculate() method from your custom calculation engine is not being invoked for the cells J2 and K2 in your Excel file. This could be due to several reasons, including the specific formulas used in those cells or how the calculation engine is set up.

Here are a few steps you can take to troubleshoot and potentially resolve the issue:

  1. Check Formulas: Ensure that the formulas in J2 and K2 are valid and recognized by the Aspose.Cells library. If the formulas are custom or not standard, they may not trigger the calculation engine.

  2. Debugging: You can add logging or debugging statements within your Calculate() method to see if it is being called for those specific cells. This will help you determine if the issue lies in the formula recognition or in the calculation logic itself.

  3. Custom Calculation Logic: If the formulas in J2 and K2 are standard but still not triggering the calculation, you might want to explicitly handle those cases in your custom engine. For example, you can check for specific formula patterns or names and ensure that your logic is applied to those cells.

  4. Example Implementation: Here’s a simplified example of how you might implement a custom calculation engine that handles specific formulas:

    public class CustomCalculationEngine : AbstractCalculationEngine
{
    public override void Calculate(CalculationData data)
    {
        // Example: Handle specific formulas
        if (data.Cell.Name == "J2" || data.Cell.Name == "K2")
        {
            // Custom logic for J2 and K2
            data.CalculatedValue = /* your calculation logic */;
        }
        else
        {
            // Default behavior for other cells
            base.Calculate(data);
        }
    }
}

var opts = new CalculationOptions { CustomEngine = new CustomCalculationEngine() };
workbook.CalculateFormula(opts);

  5. Version Compatibility: Ensure that you are using a compatible version of Aspose.Cells. Sometimes, issues can arise from version discrepancies, so it might be worth checking if there are any updates or patches available.

If these suggestions do not resolve the issue, consider sharing more details about the specific formulas in J2 and K2, as well as any error messages or unexpected behavior you are encountering.

Feel free to reach out if you need further assistance!

3

The answer did not help. There are no errors. Custom formulas are used in J2, K2

4

@aleksandr.l
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): CELLSNET-57952

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.

5

@aleksandr.l
It seems that setting formulas or value corrupted data of the cell.
If I commented setting, all cells are called:

public class SampleEngine : AbstractCalculationEngine
{
public override void Calculate(CalculationData data)
{
var val = data.Cell.Value;
Console.WriteLine(data.Cell.Name);
}
6

@aleksandr.l

It is not allowed to change the cells model directly while calculating formulas. Please see the api doc details of AbstractCalculationEngine:

Remarks
User should not modify any part of the Workbook directly in this implementation(except the calculated result of the custom function, which can be set by CalculationData.CalculatedValue property). Otherwise unexpected result or Exception may be caused. If user needs to change other data than calculated result in the implementation for some custom functions, for example, change cell’s formula, style, …etc., user should gather those data in this implementation and change them out of the scope of formula calculation.

7

Thanks, when I set CalculatedValue I set it but formula remains. I want only value in cell without formula. Could you please advise me how to do this?

8

@aleksandr.l

If you need to remove all formulas in the worksheet and replace formulas with their calculated values, you may try Cells.RemoveFormulas().

If you only want to remove some formulas that contain custom functions, you have to record those cells(such as creating one list in your custom engine) and replace the formula to value one by one after the formula calculation.