We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

CalculateFormula not completing the calculation correctly, until file is opened via Excel

Hi,
i have an excel file with complex calculations.
I have observed an issue whereby applying some input cell values and then calling CalculateFormula does not yield a correct result. when i save the file using Aspose.Cells and then open it via Excel, the output cell displays the correct & completed calculation. it looks like the output value read immediately after CalculateFormula is only a partial calculation. I would like Aspose to complete the calculation and make the output cell immediately available to read its value.
Here is the sample excel file.
Sample.zip (885.4 KB)

and here is the code to reproduce. i have tried many variations on calcstacksize and precisionstrategy, without observing any difference in the output value (always incorrect). using Aspose.Cells 19.8.0.0.
Many thanks for any assistance.

static void Main(string[] args)
{
    string filepath = @"D:\sample.xlsx";
    Console.WriteLine("Opening file: " + filepath);
    Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(filepath);
    var ws = wb.Worksheets["Inputs"];

    int[] inputValues = new int[] { 0, 6, 9, 90, 9, 6, 6, 6, 6, 6, 4, 0 };

    Console.WriteLine("Applying input values");
    // apply some input values
    for (int col = 1; col <= 12; col++)
        ws.Cells[213, col].Value = inputValues[col - 1];

    Console.WriteLine("Performing Calculation");
            
    // perform the calculation
    wb.CalculateFormula(new CalculationOptions()
    {
        Recursive = true,
        IgnoreError = true,
        PrecisionStrategy = CalculationPrecisionStrategy.None
        // none of the stack size variations made any difference in my testing, for all 3 types of precision strategy
    });

            
    // note the calculated values
    ws = wb.Worksheets["Diet"];
    double outputValue = ws.Cells[9, (int)Cols.O].DoubleValue;
    Console.WriteLine("Fresh Weight % Cow Intake Grass (O:10): " + outputValue);
    Console.WriteLine("Press any key to open the excel file to observe the value in Diet worksheet, cell O:10, changing to 92%");
    Console.ReadKey();
            
    //Save the output file
    string savePath = @"d:\sample_output.xlsx";
    wb.Save(savePath);
    wb.Dispose();

    // open the output file in Excel and observe that the calculated values change after you open the file
    Process.Start(savePath);
}

@timmer3,
Could you please mention the value of “Cols” so that we can reproduce the problem and provide our feedback after analysis. Currently value can not be set to “outputValue”.