Imports System.Data.OleDb
Imports System.Data.SqlClient
Public Class frm_Main
Private Sub frm_Main_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim excelDataTable As New DataTable
excelDataTable = ImportExcelToDataTable("Provider=Microsoft.ACE.OLEDB.12.0ata Source=.\spread.xls;Extended Properties=""Excel 12.0;HDR=No;IMEX=1""")
grdview_Excel.DataSource = excelDataTable
End Sub
Private Function ImportExcelToDataTable(excelConnString As String) As DataTable
ImportExcelToDataTable = New DataTable
Dim excelConn As OleDbConnection = New OleDbConnection(excelConnString)
Dim oldbDataAdaptor As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM [Sheet1$]", excelConn)
excelConn.Open()
oldbDataAdaptor.Fill(ImportExcelToDataTable)
excelConn.Close()
End Function
Private Sub importDataToSQL(excelDataTabe As DataTable)
Dim ConnectionString As String = "Data Source=sqlServer;Initial Catalog=HUB;User ID=USENAME;Password=PASSWORD providerName=System.Data.SqlClient"
Dim sql As String = ""
Dim dbConn As SqlConnection
dbConn = New SqlConnection(ConnectionString)
dbConn.Open()
For Each row In excelDataTabe.Rows
'F1 and F2 just happen to be the column names that we got from excel. This can be seen in the Datagrid that is on the form
SQL = "INSERT INTO TABLE VALUES('" & row.Item("F1") & "'"
Dim sqlcommand As SqlCommand = New SqlCommand(SQL, dbConn)
sqlcommand.CommandText = SQL
sqlcommand.ExecuteNonQuery()
Next
dbConn.Close()
End Sub
End Class
Copyright © 2024, NextGenUpdate.
All Rights Reserved.