Cells lists external links even when they are deleted

Aspose.Cells Product Family
1

Hi,

with an application we analyze external links (joins to other workbooks) in MS Excel files and fix them if necessary using Aspose.Cells. Before fixing we report the results to the users to check them and to confirm our translation rules.

This works fine as long as these links really exist. Problem is that the Worksheets.ExternalLinkCollection contains even links which were deleted in Excel. As there is no property like ExternalLink.IsDeleted we can not filter the collection to report only really existing links.

Below the code snippet with which we gather the external links of a workbook:

’…
If doc.HasExernalLinks Then

Dim wss As WorksheetCollection = doc.Worksheets
Dim els As ExternalLinkCollection = wss.ExternalLinks
Dim IsDisabled As Boolean = False

For i As Integer = 0 To els.Count - 1

Dim el As ExternalLink = els(i)

ReDim Preserve ResultList(cc)
With ResultList(cc)
'…
.oldURL = el.DataSource
.newURL = “”
.PageId = -1
.Row = i
If Not el.DataSource.Contains("?") Then
.Ident = “#ExternalLink”
Else ‘Aspose.Cells Bug bei Umlauten! Ist inzwischen gefixt
.Ident = “#Translation disabled!”
IsDisabled = True
End If
End With
cc += 1

Next

If IsDisabled Then
dp.FileState = “#Translation disabled”
End If

End If
’…

Please find attached
- a sample Excel Workbook to demonstrate this behaviour (Mappe1.xls)
- a pic which shows that Excel doesn’t offer any external link to refresh (ExcelWorkbookLinksMappe1.jpg)
- a pic which shows a Notepad++ clipping where you can find the deleted link (ExcelNotepad.jpg)
- a pic with a clipping of the report containing the link which was deleted

Thanks for support and Regards
Thomas

2

Hi Thomas,


Thanks for providing us the template files.

After an initial test I noticed the issue a bit although we need further investigation for your issue. I have logged a ticket with an id: CELLSNET-28963. We will look into your issue soon.

Thank you.
3

Hi,

We have fixed this issue. Please download: Aspose.Cells for .NET v6.0.0.4.

We have added a new property ExternalLink.IsReferred.

When it returns false, it means this link is not referred by cell or defined name.

4

Hi,

I’ve checked the solution and it works fine with the sample file I provided to you.
Please find attached another file (from our cutomer) where this solution doesn’t work with. Original format was Excel 2003.

Thanks for your quick support and Regards
Thomas

5

Hi,

Thanks for your input and the problematic file.

I have reopened this issue. Hopefully it will be fixed soon and we will update you asap.

6

Hi,

The external link in the file is referred by defined names.

Please removed unused name before checking whether external link is referred.

C#


Workbook workbook = new Workbook(@“D:\FileTemp\TestNotReferredExternalLinks.xls”);

NameCollection names = workbook.Worksheets.Names;

ArrayList list = new ArrayList();

for (int i = 0; i < names.Count; i++)
{

Name name = names[i];

if (!name.IsReferred)
{

if(name.SheetIndex == 0)

list.Add(name.Text);

else

list.Add("’"+workbook.Worksheets[name.SheetIndex - 1].Name +"’"+name.Text);

}

}

string[] r = new string[list.Count];

for (int i = 0; i < list.Count; i++)
{
r[i] = (string)list[i];
}

names.Remove®;

ExternalLinkCollection links = workbook.Worksheets.ExternalLinks;

Console.WriteLine(links[0].IsReferred);