External links in formulas in v7.2

Aspose.Cells Product Family
1

Hello,

I have taken over maintenance of a project using Aspose.Cells 7.2 and am looking to implement new functionality but am not sure if I need to upgrade or not. I have been told by the previous maintainer that this version most likely does not deal with formulas containing external links (to cells in other Excel files) correctly when opening a file for reading. It’s quite difficult to look through the many changelogs so I am hoping to find an answer here. Is it true that between v7.2 and the current version the support for formulas with external links has been added or fixed?

Thanks.

2

@esnoek,

Well, yes, in older version this feature (evaluating formulas referencing external links) might not be supported or might have bugs. We always recommend our users to upgrade to and use latest versions of the APIs. Over the years, we have enhanced the existing features and included new features to make the product more robust and feature rich APIs. You should upgarde to and try our latest version, the reasons are as follow:

  1. The formula calculation engine is being continuously enhanced with each and every release of the product. So, it is best to use latest APIs set of the component.
  2. If a user finds any issue in the latest version/fix, we will try to fix it and provide the fixed version. The fixes are created based on latest APIs set only.
3

Okay fair enough.

So I just did a quick test with an evaluation version of the latest version. My result:

File A -> File B
Referencing a cell in File B from File A works and if I call UpdateLinkedDataSource(null) on File A it will fetch the latest data from File B.

File A -> File B -> File C
Referencing a cell in in File B which references a cell in File C from File A. If I change the value in File C and call UpdateLinkedDataSource(null) on File A, File B will not get an updated value from File C so the value returned from File A is outdated.

How should I handle this situation?

4

@esnoek,

For second scenario/case, please see the sample code for your reference and change your code accordingly (if appropriate):
e.g
Sample code:

File A -> File B -> File C

//Load File3
//and change some data inside it
Workbook wbData = new Workbook("File3.xlsx");
Worksheet wsData = wbData.Worksheets[0];
wsData.Cells["A3"].PutValue(44);

//Load File2
Workbook wb1 = new Workbook("File2.xlsx");
//Link your workbook with data workbook
wb1.UpdateLinkedDataSource(new Workbook[] { wbData});
//Calculate the formulas
wb1.CalculateFormula(); 

//Load File1
Workbook workbook = new Workbook("File1.xlsx");
//Link your workbook with second workbook
workbook.UpdateLinkedDataSource(new Workbook[] { wb1});

//Calculate the formulas
workbook.CalculateFormula(); 

//..........
//Your code goes here.
//.......

If you still could not figure it out, kindly do provide your template files and paste your sample code (runnable), we will check it soon.