I'm trying to update an Excel file with extenal references. But I get NaN after calling
Calculate() on the worksheet. Here is my code:
var excelApplication = _serviceProvider.GetRequiredService<IApplication>();
using var s = File.Open(@"c:\somefile.xlsm", FileMode.Open); var workbook = excelApplication.Workbooks.Open(s); var sheet = workbook.Worksheets; var num = sheet["A1"].FormulaNumberValue; Debug.WriteLine(num.ToString("N0")); // => 10000 (value of A1 is based on external workbook) sheet.Calculate(); num = sheet["A1"].FormulaNumberValue; Debug.WriteLine(num.ToString("N0")); //=> NaN
How do I update the values for the external workbooks?
The external workbook also has an external reference:
somefile.xlsm => somefile2.xlsm => somefile3.xlsm
Is it possible to reflect changes made in somefile3.xlsm in somefile.xlsm using Syncfusion.XlsIO ?
Sorry for the delay in getting back to you.
Sorry to inform you that Syncfusion XlsIO supports only a single step of external reference. That is changes made in somefile3 can only be reflected in somefile2. This is the limitation of XlsIO.
We found a bug in this process and logged a defect report as CalculatedValue of a formula with external workbook reference is returned improperly. We will include the fix for this issue in our weekly NuGet release scheduled for July 25th, 2023. You can track the status of this defect report through below feedback link.
Disclaimer: The inclusion of this solution in the weekly release may change due to other factors including but not limited to QA checks and works reprioritization.
We don't have a weekly NuGet release this week, however, we generated a custom NuGet for this issue. We will include the fix for this issue in our upcoming weekly NuGet release scheduled for August 1st week.
Mohamed Yusuf Khan.
We have included the fix to resolve the issue CalculatedValue of a formula with external workbook reference is returned improperly in our weekly NuGet release version 22.2.8
Kindly upgrade your Syncfusion packages to this new 22.2.8 version and let us know if the issue is resolved.
Latest NuGet Package: https://www.nuget.org/packages/Syncfusion.XlsIO.Net.Core/22.2.8
Mohamed Yusuf Khan.