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 |
by: 发表于:2017-11-27 11:31:13 顶(0) | 踩(0) 回复
??
回复评论