Private Sub browseXLfile_Click(sender As Object, e As EventArgs) Handles browseXLfile.Click
Dim ofd As New OpenFileDialog
If ofd.ShowDialog() <> System.Windows.Forms.DialogResult.OK Then Exit Sub
Dim nme As String = ofd.FileName
Dim safename As String = ofd.SafeFileName
safename = safename.Substring(0, safename.LastIndexOf("."))
Import(nme, dgv, safename)
End Sub
Public Shared Function Import(ByVal FileName As String, ByVal dgv As DataGridView, ByVal safefilename As String) As Boolean
Try
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim DtSet As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=Excel 8.0;")
MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)
MyCommand.TableMappings.Add("Table", safefilename)
DtSet = New System.Data.DataSet
MyCommand.Fill(DtSet)
dgv.DataSource = DtSet.Tables(0)
MyConnection.Close()
Dim expr As String = "SELECT * FROM [Sheet1$]"
Dim SQLconn As New SqlConnection()
Dim ConnString As String = "Data Source=SUN-50\SQLEXPRESS;Initial Catalog=SunEducation;Persist Security Info=True;User ID=yourusername;Password=yourpassword"
Dim objCmdSelect As OleDbCommand = New OleDbCommand(expr, MyConnection)
Dim objDR As OleDbDataReader
SQLconn.ConnectionString = ConnString
Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(ConnString)
bulkCopy.DestinationTableName = safefilename
Try
MyConnection.Open()
objDR = objCmdSelect.ExecuteReader
bulkCopy.WriteToServer(objDR)
objDR.Close()
SQLconn.Close()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Using
Return True
Catch ex As Exception
Return False
End Try
End Function