EXCEL GET FROM SQL AND SUBMIT TO SQL

Imports Microsoft.Office.Tools.Ribbon
Imports System.Data.SqlClient
Public Class MENU

Private Sub MENU_Load(ByVal sender As System.Object, ByVal e As RibbonUIEventArgs) Handles MyBase.Load

End Sub

Private Sub Button1_Click(sender As Object, e As RibbonControlEventArgs) Handles Button1.Click
‘Excel Defination
Dim NativeWorksheet As Microsoft.Office.Interop.Excel.Worksheet = Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet
Dim Excel_Range As Excel.Range
‘Data Source
Dim SQLCon As New SqlConnection With {.ConnectionString = “Server=foxtube.com.au;Database=e2s;User=sa;Pwd=xxxxxxxx;”}
Dim sqltxt As String = “select * from dbo.uuu”
Dim sqlcount As String = “select COUNT(*) from dbo.uuu”
Dim sqlcmd As New SqlCommand(sqltxt, SQLCon)
Dim sqlct As New SqlCommand(sqlcount, SQLCon)
Dim userReader As SqlDataReader
Dim sqlReader As SqlDataReader
‘Data Connection
SQLCon.Open()
sqlReader = sqlct.ExecuteReader
sqlReader.Read()
Dim Total_Rows As Integer = sqlReader.Item(0)
sqlReader.Close()
sqlct.Dispose()
userReader = sqlcmd.ExecuteReader
For rows As Integer = 1 To Total_Rows
userReader.Read()
Excel_Range = NativeWorksheet.Cells(rows, 1)
Excel_Range.Value = userReader.Item(0)
Excel_Range = NativeWorksheet.Cells(rows, 2)
Excel_Range.Value = userReader.Item(1)
Excel_Range = NativeWorksheet.Cells(rows, 3)
Excel_Range.Value = userReader.Item(2)
Excel_Range = NativeWorksheet.Cells(rows, 4)
Excel_Range.Value = userReader.Item(3)
Excel_Range = NativeWorksheet.Cells(rows, 5)
Excel_Range.Value = userReader.Item(4)
Next
userReader.Close()
sqlcmd.Dispose()
SQLCon.Close()
End Sub

Private Sub Button2_Click(sender As Object, e As RibbonControlEventArgs) Handles Button2.Click
‘Excel Defination
Dim NativeWorksheet As Microsoft.Office.Interop.Excel.Worksheet = Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet
Dim Excel_Range As Excel.Range
Dim rows_count_content As Integer = NativeWorksheet.Range(“A65536:E65536”).End(Excel.XlDirection.xlUp).Row
‘Into the Array
Dim Excel_Array(rows_count_content + 1, 5 + 1) As String
For i As Integer = 1 To rows_count_content
Excel_Range = NativeWorksheet.Cells(i, 1)
Excel_Array(i, 1) = Excel_Range.Value
Excel_Range = NativeWorksheet.Cells(i, 2)
Excel_Array(i, 2) = Excel_Range.Value
Excel_Range = NativeWorksheet.Cells(i, 3)
Excel_Array(i, 3) = Excel_Range.Value
Excel_Range = NativeWorksheet.Cells(i, 4)
Excel_Array(i, 4) = Excel_Range.Value
Excel_Range = NativeWorksheet.Cells(i, 5)
Excel_Array(i, 5) = Excel_Range.Value
Next

‘Data Source
Dim SQLCon As New SqlConnection With {.ConnectionString = “Server=foxtube.com.au;Database=e2s;User=sa;Pwd=xxxxxxx;”}
Dim sqlcount As String = “select COUNT(*) from dbo.ok”
Dim sqlct As New SqlCommand(sqlcount, SQLCon)
Dim sqlReader As SqlDataReader
‘Data Connection
SQLCon.Open()
sqlReader = sqlct.ExecuteReader
sqlReader.Read()
Dim Total_Rows As Integer = sqlReader.Item(0)
sqlReader.Close()
sqlct.Dispose()
For j As Integer = 1 To rows_count_content
Dim sqlinsert As String = “insert into dbo.ok values(‘” + Excel_Array(j, 1) + “‘,'” + Excel_Array(j, 2) + “‘,'” + Excel_Array(j, 3) + “‘,'” + Excel_Array(j, 4) + “‘,'” + Excel_Array(j, 5) + “‘);”
Dim sqlit As New SqlCommand(sqlinsert, SQLCon)
Dim sqliter As SqlDataReader
sqliter = sqlit.ExecuteReader
sqliter.Close()
sqlit.Dispose()
Next
SQLCon.Close()
End Sub
End Class

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *