VSTO WEBBROWSER

Imports Microsoft.Office.Tools.Ribbon
Imports System.Data.SqlClient
Public Class MENU
DELAY MODULE
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=xxx;”}
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=xxx;”}
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

Private Sub Button3_Click(sender As Object, e As RibbonControlEventArgs) Handles Button3.Click
‘Excel Defination
Dim NativeWorksheet As Microsoft.Office.Interop.Excel.Worksheet = Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet
Dim Excel_Range As Excel.Range

Dim frm As New Form1
frm.WebBrowser1.ScriptErrorsSuppressed = True
frm.WebBrowser1.Navigate(“http://news.baidu.com/”)
Do While frm.WebBrowser1.ReadyState <> 4
Delay1(0.5)
Loop
‘MsgBox(“loaded”)
‘Delay1(10)
Dim rows As Integer = 2
Dim get_item_span1 As System.Windows.Forms.HtmlElementCollection = frm.WebBrowser1.Document.GetElementsByTagName(“a”)
For Each get_item_span1_ As System.Windows.Forms.HtmlElement In get_item_span1
If get_item_span1_.GetAttribute(“classname”) = “a3” Then
Excel_Range = NativeWorksheet.Cells(rows, 2)
Excel_Range.Value = get_item_span1_.GetAttribute(“innerText”)
rows += 1
End If
Next
End Sub

Private Sub Button4_Click(sender As Object, e As RibbonControlEventArgs) Handles Button4.Click
‘Excel Defination
Dim NativeWorksheet As Microsoft.Office.Interop.Excel.Worksheet = Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet
Dim Excel_Range As Excel.Range
Dim client As System.Net.WebClient = New System.Net.WebClient
Dim html As String = client.DownloadString(“http://www.nasdaq.com/zh/symbol/acn”)
Dim t1 = InStr(html, “qwidget-dollar”)
html = Mid(html, t1 + 16, html.Length)
t1 = InStr(html, “<“)
html = Mid(html, 1, t1 – 1)
‘MsgBox(html)
Excel_Range = NativeWorksheet.Cells(2, 2)
Excel_Range.Value = html

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 *