'Imports Statements
Imports System
Imports System.Data
Imports System.Data.OleDb
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
' The declaration lines should be self explanatory
Dim daExcelData As New OleDbDataAdapter
Dim dtFirstTable As New DataTable
Dim drSchema As DataRow
Dim dtSchema As DataTable
Dim strForTextBox As String
Dim strTableName As String
Dim strSelect As String = "SELECT * FROM [{0}]"
Dim fDlg As New OpenFileDialog
' Clear the current contents of the datagrid
DataGrid1.DataSource = Nothing
' Set the filter for the file dialog to show only spreadsheet files,
' could be modified to include csv files also, but I don't need it.
fDlg.Filter = "Excel Spreadsheets (*.xls)|*.xls"
' Show the file open dialog
fDlg.ShowDialog()
' Make sure the user didn't click cancel...
If fDlg.FileName.Length > 0 Then
' Declare the connection string, every section of this line is critical or the spreadsheet won't open
Dim cnExcelData As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & fDlg.FileName & _
";Extended Properties=Excel 8.0;")
' I hope this is self explanatory
cnExcelData.Open()
' Read the database schema into a DataTable
dtSchema = cnExcelData.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
' Get the first row in the table which will equate to the first sheet in the workbook
drSchema = dtSchema.Rows(0)
' Get the name of the first sheet
strTableName = drSchema("TABLE_NAME")
' create a command object to select the data from the first sheet in the workbook
Dim cmd As New OleDbCommand(String.Format(strSelect, strTableName))
' I hope the following lines are self explanatory
cmd.Connection = cnExcelData
daExcelData.SelectCommand = cmd
daExcelData.Fill(dtFirstTable)
DataGrid1.DataSource = dtFirstTable
cnExcelData.Close()
cnExcelData.Dispose()
End If
End Sub