Imports
System.Data.OleDb
Public
Class
Form1
Private
_FilePath
As
String
=
""
Private
_DS
As
DataSet
Private
Const
_Connectstring =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<FilePath>;Extended Properties="
"Excel 8.0"
""
Private
Const
_SelectData =
"Select * from [<TableName>$]"
Private
Sub
TSBSelectFile_Click(
ByVal
sender
As
System.
Object
,
ByVal
e
As
System.EventArgs)
Handles
TSBSelectFile.Click
Me
.OFDExcel.FileName = _FilePath
If
Not
Me
.OFDExcel.ShowDialog(
Me
) = vbOK
Then
Exit
Sub
End
If
_FilePath =
Me
.OFDExcel.FileName
Me
.TSLFilePath.Text = _FilePath
Try
Using cn
As
OleDb.OleDbConnection =
New
OleDbConnection(_Connectstring.Replace(
"<FilePath>"
, _FilePath))
cn.Open()
_DS =
New
DataSet
Dim
tb
As
DataTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
Nothing
)
Me
.TSCBOSheetList.Items.Clear()
For
Each
r
As
DataRow
In
tb.Rows
If
r(
"TABLE_TYPE"
) =
"TABLE"
Then
Me
.TSCBOSheetList.Items.Add(r(
"TABLE_NAME"
).ToString.Replace(
"$"
,
""
))
End
If
Next
If
Me
.TSCBOSheetList.Items.Count > 0
Then
Me
.TSCBOSheetList.SelectedIndex = 0
End
If
End
Using
Catch
ex
As
Exception
MsgBox(ex.Message)
End
Try
End
Sub
Private
Sub
TSCBOSheetList_SelectedIndexChanged(
ByVal
sender
As
Object
,
ByVal
e
As
System.EventArgs)
Handles
TSCBOSheetList.SelectedIndexChanged
Try
Dim
TBName
As
String
=
Me
.TSCBOSheetList.SelectedItem.ToString
If
_DS.Tables.Contains(TBName)
Then
Me
.DataGridView1.DataSource = _DS.Tables(TBName)
Exit
Sub
End
If
Using cn
As
OleDb.OleDbConnection =
New
OleDbConnection(_Connectstring.Replace(
"<FilePath>"
, _FilePath))
cn.Open()
Dim
sql
As
String
= _SelectData.Replace(
"<TableName>"
, TBName)
Using ad
As
OleDbDataAdapter =
New
OleDbDataAdapter(sql, cn)
ad.Fill(_DS, TBName)
Me
.DataGridView1.DataSource = _DS.Tables(TBName)
End
Using
End
Using
Catch
ex
As
Exception
MsgBox(ex.Message)
End
Try
End
Sub
End
Class