dear vb.net developers,
i am post the very simple to import data from excel to sql server.
first you need to import the following files.
step---1
Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.IO
step--2
declare following variables.
Dim rs As DataSet
Dim cmd As SqlCommand = myconnection.CreateCommand()
Dim myTrans As SqlTransaction
Dim xlApp As New Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As New Excel.Worksheet
dim path as string
step --3
store the path of excel file in variable path
like :-
path=C:\myfile.xls
xlWorkBook = xlApp.Workbooks.Open(path)
xlWorkSheet = xlWorkBook.Sheets.Item(1)
Me.ListView1.Items.Clear()
Me.ListView1.Columns.Clear()
With Me.ListView1
.Items.Clear()
.Columns.Add("a", 70)
.Columns.Add("b", 300)
.Columns.Add("c", 100)
.Columns.Add("d", 150)
.Columns.Add("e", 150)
End With
'item 1 is sheet one
'like the same pattern you can select your excel sheet.
first of you need to store value in list view or datagrid its not necessary.
method to store value in list view.
you can start loop from 0 to n value.
Private Sub button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles button1.Click
For i = 0 To 9000
If CStr(xlWorkSheet.Cells(i + 3, 1).value) = "" And CStr(xlWorkSheet.Cells(i + 3, 2).value) = "" And CStr(xlWorkSheet.Cells(i + 3, 3).value) = "" Then
i = 9000
Else
With Me.ListView1
.Items.Add(xlWorkSheet.Cells(i + 3, 1).value)
.Items(i).SubItems.Add(xlWorkSheet.Cells(i + 3, 2).value)
.Items(i).SubItems.Add(xlWorkSheet.Cells(i + 3, 3).value)
If CStr(xlWorkSheet.Cells(i + 3, 6).value) = "" Then
.Items(i).SubItems.Add("")
Else
.Items(i).SubItems.Add(Trim$(Replace(xlWorkSheet.Cells(i + 3, 6).value, "'", " ")))
End If
If CStr(xlWorkSheet.Cells(i + 4, 7).value) = "" Then
.Items(i).SubItems.Add("")
Else
.Items(i).SubItems.Add(Trim$(Replace(xlWorkSheet.Cells(i + 3, 7).value, "'", " ")))
End If
End With
End If
Next
'''''' this is used to dispose excel file which one you import.
releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet)
'myconnection is your connection which one you used in your project.
If myconnection.State = ConnectionState.Closed Then
myconnection.Open()
myTrans = myconnection.BeginTransaction(IsolationLevel.ReadCommitted, "MultipleTransaction")
cmd_new.Connection = myconnection
cmd_new.Transaction = myTrans
End If
For i = 0 To Me.ListView1.Items.Count - 1
cmd.CommandText = ("insert into table_name(a,b,c,d,e) values(@a,@b,@c,@d,@e")
cmd.Parameters.Add("@a", SqlDbType.bigint).Value = Me.ListView1.Items(i).SubItems(0).Text
cmd.Parameters.Add("@b", SqlDbType.varchar,100).Value = Me.ListView1.Items(i).SubItems(1).Text
cmd.Parameters.Add("@c", SqlDbType.datetime).Value = Me.ListView1.Items(i).SubItems(2).Text
cmd.Parameters.Add("@d", SqlDbType.varchar,100).Value = Me.ListView1.Items(i).SubItems(3).Text
cmd.Parameters.Add("@e", SqlDbType.decimal,19,2).Value = Me.ListView1.Items(i).SubItems(4).Text
cmd.ExecuteNonQuery()
'this is used to clear declare parameter in cmd test file so that we can used it again and again within the loop
cmd.Parameters.Clear()
next
myTrans.Commit()
end sub
' procedure to dispose excel file.
Private Sub releaseObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
Finally
GC.Collect()
End Try
End Sub
'this is the simple procedure to store value in data base from excel file.
'any wants any code for vb.net then write me i will try to resolve your problem.
