First off, if you can use the OpenXML library to open an read the spreadsheet, it is already stored as a zip package of XML files. You can prove this to yourself by changing the file extension to ".zip" and explore its contents.
You can use the "Imports System.IO.Packaging" namespace to open and retrieve these files. As I really have no idea what it is that you trying to extract, I will show you a simple method to list the contents and how to extract a given file as a String.
Create a new WinForm project. Add a project reference to "WindowsBase" and then paste this code to "Form1.vb". Modify the definition of "filepath" to point to one of your Excel spreadsheets and run the code. This will display a list of the contained files. Double-Click on a file name and a message box will displace its content.
Hopefully this will give you enough of a start to be able to extract what you are after.
For information on the Excel file format, see:
http://msdn.microsoft.com/en-us/library/office/aa338205%28v=office.12%29.aspx#office2007aboutnewfileformat_structureoftheofficexmlformats[
^]
' Add project ref: WindowsBase.dll (.Net Component)
Imports System.IO.Packaging
Public Class Form1
Private WithEvents DataGridView1 As New DataGridView With {.Dock = DockStyle.Fill, .Parent = Me, .RowHeadersVisible = False}
' replace filepath with the path to your XML spreadsheet
Private filepath As String = IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "Spreadsheets\Book1.xlsx")
Private paths As New List(Of PathItem) ' backing storage for Uri's in zippackage to display in datagridview
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
' fill paths list with the
Using zp As System.IO.Packaging.ZipPackage = DirectCast(ZipPackage.Open(filepath, IO.FileMode.Open), ZipPackage)
For Each pp As PackagePart In zp.GetParts
paths.Add(New PathItem() With {.Path = pp.Uri})
Next
zp.Close()
End Using
DataGridView1.DataSource = paths
DataGridView1.Columns("Path").ReadOnly = True
DataGridView1.Columns("Path").AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
End Sub
' helper class to display the package part paths in a datagridview
Private Class PathItem
Private _Path As Uri
Public Property Path() As Uri
Get
Return _Path
End Get
Set(ByVal value As Uri)
_Path = value
End Set
End Property 'Path
End Class 'PathItem
Private Sub DataGridView1_MouseDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles DataGridView1.MouseDoubleClick
Dim hti As DataGridView.HitTestInfo = DataGridView1.HitTest(e.X, e.Y)
If hti.RowIndex > -1 Then
Dim pp_path As Uri = CType(DataGridView1(hti.ColumnIndex, hti.RowIndex).Value, Uri)
Using zp As ZipPackage = DirectCast(Package.Open(filepath, IO.FileMode.Open), ZipPackage)
' use GetXML to retrieve the the packagepart content
' for this demo, put it in a messagebox
MsgBox(GetXML(zp.GetPart(pp_path)))
End Using
End If
End Sub
Private Function GetXML(ByVal pp As PackagePart) As String
Using sr As New IO.StreamReader(pp.GetStream(IO.FileMode.Open, IO.FileAccess.Read))
GetXML = sr.ReadToEnd()
sr.Close()
End Using
End Function
End Class