Support for formulas with external links within INDIRECT functions?

Aspose.Cells Product Family
1

I am currently evaluating Aspose.Cells due to some limitation in another library, however I am finding somethings that work in the other library difficult to achieve here as well.

Specifically, I am unable to calculate workbook formulas that have external references, and those references are via INDIRECT functions e.g. =INDIRECT("[B.xlsx]Sheet1!MyNumber1")

Here is a simplified example, loading workbooks A.xlsx and B.xlsx, where A.xlsx has a reference to B.xlsx:

var workbookA = new Workbook(streamA);
workbookA.FileName = "A.xlsx"

var workbookB = new Workbook(streamB);
workbookB.FileName = "B.xlsx"

var externalLinks = new[]{workbookB};
workbookA.UpdateLinkedDataSource(externalLinks);

workbookA.CalculateFormula(new CalculationOptions());

If the workbook contains a formula such as: =SUM([B.XLSX]Sheet1!MyNumber1,2)
it works, formulas resolve and I can see the external link were found during load

var externalLinks = workbookA.Worksheets.ExternalLinks;

If the workbook contains a formula such as: =INDIRECT("[B.xlsx]Sheet1!MyNumber1")
it will not work, the formulas will not resolve. If there are no other external references, links are not found. I have tried calling:

foreach (var externalWorkbook in externalWorkbooks)
{
	workbookA.Worksheets.ExternalLinks.Add(externalWorkbook.FileName, externalWorkbook.Worksheets.Select(x => x.Name).ToArray());
}

I have also tried to look for some solutions in setting properties, but there is limited access e.g. ExternalLink.IsReferred or Cell.ContainsExternalLink do not have setters.

Is this at all viable in Aspose.Cells?

Thanks

2

@rscottqdev,

Could you please zip and attach A.xlsx and B.xlsx files. We will check your issue soon.

3

Thank you for the quick response. Please see the attached example.

Indirect_Example.zip (13.9 KB)

4

@rscottqdev,

Thanks for the sample files.

We found the issue as you mentioned after initial testing. We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-52771

You can obtain Paid Support services if you need support on a priority basis, along with the direct access to our Paid Support management team.

1 Like
5

@rscottqdev,

This is to inform you that your issue has been resolved. The fix will be included in our upcoming release (Aspose.Cells v23.3) that we plan to release in the first half of March 2023. You will be notified when the next version is released.

1 Like
6

The issues you have found earlier (filed as CELLSNET-52771) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi