1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45
| Imports excel = Microsoft.Office.Interop.Excel
Private dr As New DataSet
Private Connection As New OleDbConnection
Private objexcel As New excel.Application
Private xlWorkBooks As excel.Workbooks
Private xlBook As excel.Workbook
Private xlworksheet As excel.Worksheet
Private Sub Initdb(ByVal path As String)
Try
Connection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.12.0;" + path & ";Extended Properties=""Excel 12.0;HDR= YES""")
Connection.Open()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
Private Function GetDataFromExcel(ByVal path As String) As DataSet
Dim sql As String
Dim ObjetCommand As New OleDbCommand
Dim da As OleDb.OleDbDataAdapter
Dim ds As New DataSet
Dim sheetname As String
xlWorkBooks = objexcel.Workbooks
xlBook = xlWorkBooks.Open(path)
xlworksheet = CType(xlBook.ActiveSheet, excel.Worksheet)
sheetname = xlworksheet.Name
sql = "select * from [ sheetname] "
Try
ObjetCommand = New OleDbCommand(sql)
ObjetCommand.Connection() = Connection
da = New OleDbDataAdapter(ObjetCommand)
Connection.Close()
da.Fill(ds, "Table")
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Return ds
End Function
Private Sub OpenFileDialog1_FileOk(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles OpenFileDialog1.FileOk
Initdb(DirectCast(sender, OpenFileDialog).FileName)
dr = GetDataFromExcel(DirectCast(sender, OpenFileDialog).FileName)
End Sub |
Partager