This project has moved. For the latest updates, please go here.

External links

Feb 2, 2012 at 10:19 AM

Hi,

I'd like to enumerate all external links (links to other Excel worksbooks). Is it possible?

thanks,
Francois

Coordinator
Feb 6, 2012 at 9:52 PM

No, external links is currently very poorly supported by the library, but I hope I can improve it sometime in the future.

Jan

Jun 13, 2012 at 9:01 AM

Even though they aren't supported in the meantime, could you perhaps give me some pointers on how to manage (edit) the external links by working directly with the Package object and the internal .xml files? I've already successfully enumerated the links, but I'm unable to change them.

Aug 16, 2012 at 6:05 PM

igitur, how did you enumerate the links? I am very interested in being able to do this as well. Cheers!

Aug 17, 2012 at 9:13 AM
Edited Aug 17, 2012 at 9:19 AM

Probably not the most elegant way, but here's how I do it:

using (ExcelPackage excelPackage = new ExcelPackage(InputFileInfo))
{
    //The links to external files are contained in separate .xml files in the Excel zip package
    //There is a specific metadata xml file which has references to the .xml files with the actual links.
    //So the process is: Open the metadata xml file, find the .xml files, open the .xml files, parse the actual link to the external Excel files.

    //The external links' xml files (also metadata?) are referenced by '/xl/_rels/workbook.xml.rels' in the zip package
    var workbookLinksPart = excelPackage.Package.GetParts().Where(p => p.ContentType.Equals("application/vnd.openxmlformats-package.relationships+xml") && p.Uri.ToString().Equals("/xl/_rels/workbook.xml.rels")).First();

    //Open the stream to that part
    XDocument workbookXmlDoc = XDocument.Load(workbookLinksPart.GetStream(FileMode.Open, FileAccess.Read));

    //Enumerate the links xml files in that part
    var externalLinksXML = from r in workbookXmlDoc.Descendants()
                        where r.Name.LocalName.Equals("Relationship") &&
                        r.Attribute("Type").Value.Equals("http://schemas.openxmlformats.org/officeDocument/2006/relationships/externalLink")
                        select (string)r.Attribute("Target");

 

Originally I thought that would give me the list of links, but I found that many of the links were not used, and in Excel didn't even display if you click "Edit links". I have no idea why they are there, but I consider them 'orphaned links'. To check whether a link is used, adds a whole level of complexity and slows down the process, but I haven't found a way around it.  So, continuing... 

 

    foreach (var linkXML in externalLinksXML)
    {
        // Grab linkId from target link string
        int linkId = int.Parse(Regex.Match(linkXML, @"externalLinks/externalLink(\d+)\.xml").Groups[1].Captures[0].Value);

        // Use the linkId only to check whether the link is actually used. I'll document this method later.
        if (IsLinkIdUsed(excelPackage, linkId))
        {
            string linkrel = linkXML.Replace("externalLinks/", "externalLinks/_rels/") + ".rels";
            var linkPart = excelPackage.Package.GetParts().Where(p => p.ContentType.Equals("application/vnd.openxmlformats-package.relationships+xml") && p.Uri.ToString().Contains(linkrel)).First();

            var stream = linkPart.GetStream(FileMode.Open, FileAccess.ReadWrite);
            XDocument xmlDoc = XDocument.Load(stream);

            // usually there is only one relationship (external link) in that file, but I make provision in case there are more than one
            var relationships = from r in xmlDoc.Descendants()
                          where r.Name.LocalName.Equals("Relationship")
                          select r;

            foreach (var relationship in relationships)
            {
                // This is what we're after!
                string target = relationship.Attribute("Target").Value;

                // In my case, I needed an absolute URL, not relative as most of the values would be. You can skip this part if you want to.
                Uri targetUri;
                string filePath;
                if (Uri.TryCreate(target, UriKind.RelativeOrAbsolute, out targetUri) && targetUri.IsAbsoluteUri)
                    filePath = targetUri.LocalPath;
                else
                    filePath = target;

                filePath = ResolvePath(doc, filePath);
            }
        }
    }
}

 

 

The helper methods are (written in a huge hurry, so don't judge):

 

private bool IsLinkIdUsed(ExcelPackage excelPackage, int linkId)
{
    bool isUsed = false;

    var sheetParts = excelPackage.Package.GetParts().Where(p => p.ContentType.Equals("application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml") && p.Uri.ToString().StartsWith("/xl/worksheets/sheet"));
    foreach (var sheetPart in sheetParts)
    {
        XDocument sheetXmlDoc = XDocument.Load(sheetPart.GetStream(FileMode.Open, FileAccess.Read));

        var referencesToLink = from r in sheetXmlDoc.Descendants()
                            where r.Name.LocalName.Equals("f") && r.Value.Contains(string.Format("[{0}]", linkId))
                            select r;

        isUsed = isUsed || referencesToLink.Count() > 0;
        if (isUsed) return true;

    }

    return isUsed;
}
private static string ResolvePath(OfficeDocument doc, string filePath)
{
    filePath = filePath.Replace("%20", " ");
    if (filePath.StartsWith("/") || !filePath.StartsWith("\\") && !filePath.Contains(':'))
    {
        //relative link
        if (filePath.StartsWith("/"))
            filePath = new FileInfo(doc.FullPath).Directory.Root.FullName + filePath.Substring(1).Replace('/', '\\');
        else
            filePath = Path.Combine(new FileInfo(doc.FullPath).DirectoryName, filePath.Replace('/', '\\'));
    }
    return filePath;
}

Editor
Aug 17, 2012 at 12:30 PM
Edited Aug 17, 2012 at 2:17 PM

nice work igitur! Dont be shy! When someone has pride of their code, they waste a lot of time coding hehehe

Oct 6, 2014 at 7:36 PM
Hi igitur,

I'm interested interested in this issue. Can you provide a complete code?

Thanks in advance,
surika