Open XML File Format Code Snippets for Visual Studio 2005

System.IO.Packagingのクラスを使った2007 OfficeのOpen XML File Formatためのコードスニペットがリリースされています。

Download 2007 Office System Sample: Open XML File Format Code Snippets for Visual Studio 2005 from Official Microsoft Download Center

ためしに１つ、「XLGetSheetInfo.VB.snippet」を挿入してみると下記のようなコードが追加されました。

Imports System.IO
Imports System.IO.Packaging
Imports System.Xml

Public Structure SheetInfo
    Public SheetName As String
    Public SheetType As String
 
    Public Sub New(ByVal SheetName As String, ByVal SheetType As String)
        Me.SheetName = SheetName
        Me.SheetType = SheetType
    End Sub
End Structure
 
Public Function XLGetSheetInfo(ByVal fileName As String) As List(Of SheetInfo)
    ' Return a generic list containing info about all the sheets.       
    Const documentRelationshipType As String = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument"
 
    ' Fill this collection with a list of all the sheets
    Dim sheets As New List(Of SheetInfo)
 
    Using xlPackage As Package = Package.Open(fileName, FileMode.Open, FileAccess.Read)
        ' Get the main document part (workbook.xml).
        For Each relationship As PackageRelationship In xlPackage.GetRelationshipsByType(documentRelationshipType)
            ' There should only be one document part in the package.
            Dim documentUri As Uri = PackUriHelper.ResolvePartUri(New Uri("/", UriKind.Relative), relationship.TargetUri)
            Dim documentPart As PackagePart = xlPackage.GetPart(documentUri)
 
            ' Load the contents of the workbook, which is all you
            ' need to retrieve the names and types of the sheets:
            Dim doc As XmlDocument = New XmlDocument()
            doc.Load(documentPart.GetStream())
 
            ' Create a NamespaceManager to handle the default namespace,
            ' and create a prefix for the default namespace:
            Dim nsManager As New XmlNamespaceManager(doc.NameTable)
            nsManager.AddNamespace("default", doc.DocumentElement.NamespaceURI)
 
            ' Loop through all the nodes, retrieving the information
            ' about each sheet:
            For Each node As XmlNode In doc.SelectNodes("//default:sheets/default:sheet", nsManager)
                Dim sheetName As String = String.Empty
                Dim sheetType As String = "worksheet"
 
                sheetName = node.Attributes("name").Value
                Dim typeAttr As XmlAttribute = node.Attributes("type")
                If typeAttr IsNot Nothing Then
                    sheetType = typeAttr.Value
                End If
                sheets.Add(New SheetInfo(sheetName, sheetType))
            Next
 
            ' There's only one document part.
            Exit For
        Next
    End Using
    Return sheets
    End
End Function

これを使うコードを下記のように少し書くだけで、

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    For Each sampleSheetInfo As SheetInfo In XLGetSheetInfo("C:\Book1.xlsx")
        ListBox1.Items.Add(sampleSheetInfo.SheetName & ", " & sampleSheetInfo.SheetType)
    Next
End Sub

簡単にxlsxファイルのシート名などを取得することができました。



System.IO.Packagingについてはまったくチェックしていませんでしたが、このコードスニペットのリリースされたことで手軽に試すことができそうです。