Custom Calculate not processing all CHOOSE formulas

Aspose.Cells Product Family
1

Hello,

I’m using a custom calc engine to find all of the CHOOSE formulas in a particular workbook. The Calculate override method is only being called for 2 of the 14 instances of the formula in the workbook. I’m having a heard time figuring out why it’s not being called for the other formulas.

ChooseExample.zip (7.6 KB)

This is my test code and the example file is attached.

        static void Main(string[] args)
    {

        var filename = @"ChooseExample.xlsx";
        using var fileStream = File.Open(filename, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);

        var workbook = new Workbook(fileStream);

        workbook.CalculateFormula(new CalculationOptions
        {
            IgnoreError = false,
            CustomEngine = new MyCustomEngine()
        });

        Console.In.ReadLine();
    }

    internal class MyCustomEngine : AbstractCalculationEngine
    {
        public override bool ProcessBuiltInFunctions { get; } = true;

        public override void Calculate(CalculationData data)
        {
            Console.Out.WriteLine($"{data.Cell.Name}{data.Cell.Formula}");
        }
    }

The output from this example displays:

A1=CHOOSE(2,"202000000001","USA")
A3=CHOOSE(2,"202000000002","EMEA")
A16=MADEUPFORMULA("test")

Where’s the formulas in cells A2 and A4 through A14?

Thanks,
-Andy

2

@weissa,
For IF function and CHOOSE function, there are special data structures designed by ms excel for performance consideration. Currently we calculate them according to the data structure too. If those functions in the formula data are organized as the special structure, we just use the built-in logic to fetch the result, without using normal calculation engines(includes custom engine). If you do need to use your custom engine for all those situations, please confirm and we will try to provide some option and do the required change for the calculation process.

3

@johnson.shi,

Thank you for the explanation. Now that I understand the behavior I can work with it. For this specific project I’ll be fine with searching for the formulas and parsing them, so no need to make any adjustments to the custom calc engine support for my needs.

-Andy

4

@weissa,

Thanks for your confirmation.

We will not embed any adjustments and retain existing mechanism for custom calculation engine.