none
List links in Excel file RRS feed

  • Question

  • I was wondering if anyone could point me in the direction of a VB.NET sample, to list the external links in a Excel file using the Open XML SDK?
    • Edited by DJNidum Tuesday, August 2, 2016 7:55 AM
    Monday, August 1, 2016 7:06 PM

Answers

  • Hi, DJNidum

     

    >> list the external links in a Excel file using the Open XML SDK

     

    Try the code below.

    Imports DocumentFormat.OpenXml.Packaging
    Module Module1
        Sub Main()
            Dim fileName As String = "C:\Users\celeste\Desktop\Book1.xlsx"
            For Each s As String In ListLinks(fileName)
                Console.WriteLine(s)
            Next
            Console.ReadKey()
        End Sub
        Public Function ListLinks(ByVal fileName As String) As IEnumerable
            Dim ret As List(Of String) = New List(Of String)
            Using document As SpreadsheetDocument = SpreadsheetDocument.Open(fileName, False)
                Dim wbPart As WorkbookPart = document.WorkbookPart
                Dim exwbPart As ExternalWorkbookPart = wbPart.ExternalWorkbookParts.FirstOrDefault()
                For Each exwbPart In wbPart.ExternalWorkbookParts
                    For Each relation As ExternalRelationship In exwbPart.ExternalRelationships
                        ret.Add(relation.Uri.OriginalString)
                    Next
                Next
            End Using
            Return ret
        End Function
    End Module



    • Edited by Chenchen LiModerator Thursday, August 4, 2016 2:07 AM insert code block
    • Marked as answer by DJNidum Thursday, August 4, 2016 10:03 AM
    Thursday, August 4, 2016 2:06 AM
    Moderator

All replies

  • Hi, DJNidum

     

    >> list the external links in a Excel file using the Open XML SDK

     

    Try the code below.

    Imports DocumentFormat.OpenXml.Packaging
    Module Module1
        Sub Main()
            Dim fileName As String = "C:\Users\celeste\Desktop\Book1.xlsx"
            For Each s As String In ListLinks(fileName)
                Console.WriteLine(s)
            Next
            Console.ReadKey()
        End Sub
        Public Function ListLinks(ByVal fileName As String) As IEnumerable
            Dim ret As List(Of String) = New List(Of String)
            Using document As SpreadsheetDocument = SpreadsheetDocument.Open(fileName, False)
                Dim wbPart As WorkbookPart = document.WorkbookPart
                Dim exwbPart As ExternalWorkbookPart = wbPart.ExternalWorkbookParts.FirstOrDefault()
                For Each exwbPart In wbPart.ExternalWorkbookParts
                    For Each relation As ExternalRelationship In exwbPart.ExternalRelationships
                        ret.Add(relation.Uri.OriginalString)
                    Next
                Next
            End Using
            Return ret
        End Function
    End Module



    • Edited by Chenchen LiModerator Thursday, August 4, 2016 2:07 AM insert code block
    • Marked as answer by DJNidum Thursday, August 4, 2016 10:03 AM
    Thursday, August 4, 2016 2:06 AM
    Moderator
  • Yes that worked, exactly what I was looking for. thank you
    Thursday, August 4, 2016 10:04 AM