ExternalLinks XLSX

Aspose.Cells Product Family
1

Hi


There appears to be an issue with ExternalLinks from Excel 2007 files. It works as expected with Excel 2003, reading Excel 2007(xlsx) files however ExternalLinks always is always empty. Would greatly appreciate if someone could take a look into this. Using release 4.9.1.0

Code is simply

Workbook workbook = new Workbook();
workbook.Open(@“C:\HelloWorld.xlsx”);
ExternalLinks references = workbook.Worksheets.ExternalLinks;

Thanks
Kevin


2

Hi,

Please use Worksheet.Hyperlinks instead for your requirement. It will give you all types of hyperlinks whether they are external or internal.

e.g

Workbook workbook = new Workbook();
workbook.Open(“e:\test\Book1.xlsx”);

//Get the hyperlinks from the first worksheet.
Hyperlinks hlinks = workbook.Worksheets[0].Hyperlinks;
MessageBox.Show(hlinks.Count.ToString());
MessageBox.Show(hlinks[0].Address);

//…



Thank you.


3

I’m not sure we’re talking about the same thing. An external reference is a reference to an external spreadsheet eg. =‘C:\SomeFolder[HelloWorlsd.xls]Sheet1’!$A$1 would allow me to use/show the value from A1 Sheet1 HelloWorld.xls in another spreadsheet. A hyperlink on the other hand would just open up the linked file/url , assuming Aspose follows the same naming convention of Excel.


Regardless I’ve tested the above code and it doesn’t pick up references to other spreadsheets (Which I wouldn’t expect it too).
4

Hi,

Sorry for the confusion.

Please use Workbook.HasExternalLinks() method to check whether a workbook has some external references. Also, you may use Cell.ContainsExternalLink boolean attribute to check if a cell has external link reference in the formula. See the sample code below:

Workbook workbook = new Workbook();
workbook.Open(“e:\test\ExtBook2.xlsx”);
bool checklinks = workbook.HasExernalLinks();
MessageBox.Show("External Links: " + checklinks.ToString());

//Get the first worksheet.
Worksheet worksheet = workbook.Worksheets[0];

// workbook.CalculateFormula();

int cnt = 0;
for (int i = 0; i <= worksheet.Cells.MaxDataRow; i++)
{
for (int j = 0; j <= worksheet.Cells.MaxDataColumn; j++)
{
if (worksheet.Cells[i,j].ContainsExternalLink)
{
cnt++;
MessageBox.Show(worksheet.Cells[i,j].Name);
MessageBox.Show(worksheet.Cells[i,j].Formula);
MessageBox.Show(worksheet.Cells[i, j].StringValue);

}
}

}

MessageBox.Show("total external links in sheet1: " + cnt);




Thank you.

5

Hi, thanks for the reply. I won't be in the office till Monday and don't have access to our Aspose license to test this, I'd have major concerns regarding performance with this approach, especially in extremely large spreadsheets. I've seen slow down already when working through a large cell range using MaxDataRow and MaxDataColumn (which is understandable). Are there no intentions to fix the ExternalLinks property for xlsx workbooks?

6

Hi,

I have logged your request into our issue tracking system with an id: CELLSNET-17060. We will analyze/investigate if we can enhance the ExternalLinks API for XLSX files.

We will get back to you soon.

Thank you.

7

Thanks, in the mean time the workaround I've been using is below, in case anyone else has run into this issue...

Workbook wb = new Workbook();
wb.Open(@"C:\Test\LinkTest.xlsx");
int counter = 0;

if (wb.HasExernalLinks())
{
foreach (Worksheet worksheet in wb.Worksheets)
{
Cell linkCell = null;
do
{
// Within an ExternalLink the spreadsheet named is wrapped around [spreadsheetname.xlsx]
// I'm not aware of another valid use of [] within an Excel formula
linkCell = worksheet.Cells.FindFormulaContains("[", linkCell);
if (linkCell != null)
{

if (linkCell.ContainsExternalLink)

{

counter++;

}

}

} while (linkCell != null);

}

}



It offers reasonable performance (~10 seconds on a sheet with 2, 525, 202 cells
though this was on a fairly beefy system). I haven’t had a chance to profile it
properly against manually iterating through the MaxDataRow and MaxDataColumn,
though this assumes FindFormulaContains gives better performance (which I
believe it does).



The code needs to be tested more but should offer some assistance to people
with this issue. It works on assumption that a formula with an ExternalLink
will contain a square bracket (which it must), this alone is not sufficient as
it can return some false positives so we still need to verify using the
ContainsExternalLink property.


8

Hi again, now I’ve got a strange bug. When I use the Formula property for a cell that contains a link in a XLSX file, the formula returns


“=SUM([1]Sheet1!$A$2:$A$6)”

It should be

"=SUM(’[Range1.xlsx]Sheet1’!$A$2:$A$6)"

I’d hazard a guess that this is something to do with why your ExternalLinks property always returns an ExternalLinks of count 0 for XLSX. I can see the the correct value in the non public porperities for the cell but sadly can’t access it.

Would appreciate a fix as soon as possible

Thanks
9

It’d be great if I could get some idea if this intended or is indeed a bug as it’s a blocker for us.


Thanks

10

Hi,

We will get back to you soon.

Thank you.

11

Hi,

Please try the attached version.We have supported ExternalLinks property for xlsx workbooks. You may use Worksheets.ExternalLinks attribute and Aspose.Cells.ExternalLink API.


Thank you
12

While the fix addresses the issue with ExternalLinks, Formula still returns the incorrect formula value in xlsx.

13

Hi,

Yes, the formula (the formulas having external links/references) issue is not resolved yet. We will fix it and let you know about it soon.

Thank you.

14

I’ve also noticed that the Datasource is now different between the XLS and XLSX APIs. In the XLS version the datasource is only the spreadsheet name with extension, whereas the datasource for XLSX also contains a pseudo file path (minus the drive letter)


So in XLS we get

"Source.xls"

but in XLSX we get

"/Folder/SubFolder/Source.xlsx"

Also I noticed that the first ExternalLink within the XLS API is always blank, so if a workbook contains only 1 Externallink the ExternalLinks property will have a count of 2. Not a huge issue just odd that it happens within the XLS and XLSX. I’m not sure whether this occurred before the fix, again not an issue for us.

Thanks for the assitance
15

Any update on this, this leaves some of our stories in limbo without this functionality

16

Hi,

We will get back to you soon.

Thanks for being patient!

17

Hi,

Please try the attached version.We have fixed your issue.
For the issue of directory, if the file and external link
are in the same directory, MS Excel 2003 does not preserve the directory.<o:p></o:p>

But, MS Excel 2007 always stores the full directory. So you
have to check the file path to get the file name.

Thank you.

18

Yeah cool seems to work fine. My code already handles the missing file location in Office 2003

19

The issues you have found earlier (filed as CELLSNET-17060) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.

While trying to keep the API as straightforward and clear as possible, we have decided to recognize and honor the common development practices of the platform; we have re-arranged API Structure/ Namespaces.

With this release, we have reorganized the API classes for Aspose.Cells component. This change has some major aspects that we follow. We have added new namespaces. The entire API (classes, interfaces, enumerations, structures etc.) were previously located in the Aspose.Cells namespace. Now, certain sets of API have been moved to their relative namespaces, which make the relationship of classes (with their members) and namespaces clear and simplified. It is to be noted here, we have not renamed existing API (classes, enumerations etc.) but, I am afraid you still need to make certain adjustments in your existing projects accordingly.

For complete reference, please see the product's API Reference.