将实时数据从Excel导入到SQL Server 2008
问题描述：
亲爱的所有人，
我有以下查询:
1.我有一张excel表格，它为我提供了实时变化的股票报价.
2.我想在价格变化时将此数据作为&导入到sql server或任何临时存储中.
3.我想根据价格的变化生成警报.
那么你们中有人可以建议我这样做吗?
谢谢与问候.
Vinay D Sarmalkar
Dear All,
I have a following query:
1.I have a excel sheet which gives me live quotes of stock prices which changes second by second.
2.I want to import this data into sql server or any temporary storage as & when the price get change.
3.I want to generate alerts based on the change in the price.
So can anyone of you suggest me a way to do this?
Thanks & Regards.
Vinay D Sarmalkar
答
在此代码中，首先将数据导入到datagridview中，然后将其保存在sql数据库中. 其中Test.Xlsx是Excel工作表，而Excel是数据库表
导入System.Data.SqlClient
公共课程表格1
私有子Button1_Click(ByVal发送者为System.Object，ByVal e为System.EventArgs)处理Button1.Click
将MyConnection变暗为System.Data.OleDb.OleDbConnection
昏暗的DtSet作为System.Data.DataSet
将MyCommand变暗为System.Data.OleDb.OleDbDataAdapter
fBrowse作为新的OpenFileDialog
使用fBrowse
.Filter ="Excel文件(* .xlsx)| * .xlsx |所有文件(*.*)| *.*"
.FilterIndex = 1
.Title =从Excel文件导入数据"
结尾为
如果fBrowse.ShowDialog()= Windows.Forms.DialogResult.OK然后
昏暗的fname作为字符串
fname = fBrowse.FileName
MyConnection =新的System.Data.OleDb.OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0; Data Source ="&fname&"'';&" Extended Properties = Excel 8.0;)
MyCommand = New System.Data.OleDb.OleDbDataAdapter(从* Sheet1
In this code firstly import your data into datagridview then it saves in sql database.try it
Where Test.Xlsx Is excel sheet and Excel is DataBase Table
Imports System.Data.SqlClient
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim DtSet As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
Dim fBrowse As New OpenFileDialog
With fBrowse
.Filter = "Excel files(*.xlsx)|*.xlsx|All files (*.*)|*.*"
.FilterIndex = 1
.Title = "Import data from Excel file"
End With
If fBrowse.ShowDialog() = Windows.Forms.DialogResult.OK Then
Dim fname As String
fname = fBrowse.FileName
MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=''" & fname & " ''; " & "Extended Properties=Excel 8.0;")
MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1
"中选择*，MyConnection)
MyCommand.TableMappings.Add("Table"，"Test")
DtSet =新的System.Data.DataSet
MyCommand.Fill(DtSet)
DataGridView1.DataSource = DtSet.Tables(0)
MyConnection.Close()
对于DtSet.Tables(0).Rows中每个DataRow的Drr
Execute_Local("INSERT INTO Excel(Name，Designation，Salary)VALUES("&Drr(0).ToString&"''，''&Drr(1).ToString&"''，''&Drr (2).ToString和'')")
下一个
MsgBox(成功保存")
如果
结束
结束子
结束类
", MyConnection)
MyCommand.TableMappings.Add("Table", "Test")
DtSet = New System.Data.DataSet
MyCommand.Fill(DtSet)
DataGridView1.DataSource = DtSet.Tables(0)
MyConnection.Close()
For Each Drr As DataRow In DtSet.Tables(0).Rows
Execute_Local("INSERT INTO Excel(Name, Designation, Salary) VALUES (''" & Drr(0).ToString & "'',''" & Drr(1).ToString & "'',''" & Drr(2).ToString & "'')")
Next
MsgBox("Successfully Saved")
End If
End Sub
End Class