How to get the updated value of a forumula

Question

Hi!

I'm currently looking trying to get a updated value of a formula that I have in a Excel sheet when I change a value in a cell Via the WPF application.

The thing is I have a very complicated excel sheet and a lot of reference.

So to give some information I have 2 worksheet

=>Input

=>Calcs

In the Input C21 is the value that I want to change via the Wpf.

In the Input O11 is the result of CalcC18(formula that is on another worksheet)

When I change the C21 manually in the Excel the result in O11 change.

When I change the C21 in the Wpf and I reopen my Excel, I can see that the C21 value is changed, but the updated calculation was not done.

I try to EnableSheetCalculation, to manually Calculate with the Calculate() function on both worksheet. Nothing work. I can't get the excel to recalculate the formula.

Thanks

Ramya Sivakumar · Answer

Hi JeanChristophe ,Greetings from Syncfusion.We can reproduce the reported formula value issue at our end
and validate it currently. We will share the validation details in 2 business
days, on April
19, 2022.Regards,Ramya.

Konduru Keerthi Konduru Ravichandra Raju · Answer

Hi JeanChristophe,We appreciate your patience.We have validated the query and found that it is a usage
level issue. When the cell value of a cell referred to a formula is changed,
then Calculate method of the worksheet should be called to recalculate the
updated formulas. Please find the code snippet below.Code snippet:

//Create an instance of ExcelEngine
  using (ExcelEngine excelEngine = new ExcelEngine())
  {
      IApplication application =
  excelEngine.Excel;
      application.DefaultVersion =
  ExcelVersion.Xlsx;
   
      //Create a workbook
      IWorkbook workbook =
  application.Workbooks.Open("../../Sample.xlsx");
   
      IWorksheet worksheet1 =
  workbook.Worksheets[0];
      IWorksheet worksheet2 =
  workbook.Worksheets[1];
   
      worksheet1.EnableSheetCalculations();
      worksheet1.Calculate();
      worksheet2.EnableSheetCalculations();
      worksheet2.Calculate();
   
      string value1 =
  worksheet1["B2"].Value;
      string value2 =
  worksheet1["B3"].Value;
   
      string formula =
  worksheet2["B4"].Formula;
      double formulavalue =
  worksheet2["B4"].FormulaNumberValue;

worksheet1.Range["B3"].Value
  = TextBox1.Text;
      worksheet2.Calculate();
      double newformulavalue =
  worksheet2["B4"].FormulaNumberValue;
   
      //Save the Excel document
      workbook.SaveAs("../../Output.xlsx");
  }

Kindly try the suggestion and let us know if the issue is
resolved.Regards,Keerthi.