VB.net Help - Importing .xls data into a listbox

[Chris Bell]

Ok i am new to using VB.net, so as you can guess my skills are very limited.

So heres the problem. What i am wanting to do is import data from a .xls (excel file) and display that data in rows within a listbox. I have the open file box already working, i just cant seem to figure out how to get the data within the .xls file to display within the listbox. If it helps here is my code so far:

' Created by SharpDevelop.
' User: Chris
' Date: 4/16/2008
' Time: 8:32 PM
' 
' To change this template use Tools | Options | Coding | Edit Standard Headers.
'
Public Partial Class MainForm
	Public Sub New()
		' The Me.InitializeComponent call is required for Windows Forms designer support.
		Me.InitializeComponent()

		'
		' TODO : Add constructor code after InitializeComponents
		'
	End Sub

	Sub Button1Click(ByVal sender As Object, ByVal e As EventArgs)
		openmunro()
	End Sub

	Private Sub openmunro()
		'creates a variable that can be used to read from a file
		Dim openMunroD As IO.StreamReader
		'dimensions a variable that lets the user open a file dialog box to open a file of the users choice
		Dim openfiledialog As New OpenFileDialog
		'gives the dialog box a title
		openfiledialog.Title = "Open Munro Data"
		Try
			'sets the initial directory to look at
			openfiledialog.InitialDirectory = Application.StartupPath
			'limits the file types that are displayed
			openfiledialog.Filter = "Excel Files (*.xls)|*.xls"
			'shows the dialog
			openfiledialog.ShowDialog()
			Catch
			MsgBox("The file was either not found or you did not open the file")
		End Try
	End Sub
End Class

Any help will be greatly appreciated :)

Many Thanks

[Antaris Veteran]

You can read Excel files through the standard OLEDB layer. What you need to do, is setup a connection using ADO.NET, and pass in a specific connection string:

Provider=Microsoft.Jet.OleDb.4.0;Data Source=<path/to/file.xls>;Extended Properties=Excel 8.0;

There are a few things you need to do to your spreadsheet though, you need to create a named range which would become something akin to a virtual sql table. The top row on your spreadsheet needs to contain the column names, and you will then select all the cells that will make up your virtual table, and select Insert > Name > Define. The name you give this range will become the name of your virtual table.

Back in ADO.NET, you can then use standard SQL statements to manipulate the data.

SELECT * FROM People WHERE Age <= 50

Hope that helps!