vb.net中如何将excel导入到SQL Server

[wwb198686]

vb.net中如何将excel导入到SQL Server中去，能有代码最好！

谢谢各位大侠帮忙。

[wuyq11]

Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filePath & ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'"
Using OleConn As New OleDbConnection(strConn)
OleConn.Open()
Dim sql As [String] = "SELECT * FROM [Sheet1$]"
Dim OleDaExcel As New OleDbDataAdapter(sql, OleConn)
Dim ds As New DataSet()
OleDaExcel.Fill(ds)
OleConn.Close()
End Using

[wwb198686]

[Quote=引用 1 楼 wuyq11 的回复:]
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filePath & ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'"
Using OleConn As New OleDbConnection(strConn)
OleCo……
[/Quote]
代码我加进去，有问题，不能到进去啊！

[思考]

这个很复杂吗？
不过是读取excel，再写到sql server嘛

[lzg19832721]

我最近写了一个专业方面的小软件，就是查询方面遇到点困难，因为我只想检索数字，只要输入一个数字，和数据库里面的数据进行对比计算，只要误差在一个范围内就显示结果，请问这个代码怎么写啊。我是新手，望大侠能提供帮助，不胜感激！！

[qshurufa]

[Quote=引用 1 楼 wuyq11 的回复:]
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filePath & ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'"
Using OleConn As New OleDbConnection(strConn)
OleCo……
[/Quote]

可以把数据读到DataSet，把DataSet插入SQL Server就可以了，例如循环Insert

[wwb198686]

If mydataset.Tables(0).Rows.Count > 0 Then
这一句出错了，{"无法找到表 0。"}
怎么修改。

[nices0028]

Dim fileName As String
fileName = Me.OpenFileDialog1.FileName
'建立EXCEL连接，读入数据
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & fileName & "';Extended Properties=Excel 8.0;"
'Dim myDataset As New DataSet

Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn)

Dim mydataset As DataSet = New DataSet
Try
da.Fill(mydataset)
Me.DataGridView1.DataSource = mydataset.Tables(0)
Catch ex As Exception

End Try
If mydataset.Tables(0).Rows.Count > 0 Then
Dim strSql As String '////////////// imports///////////////
Dim CnnStr As String = "server=localhost;database=AAA;user id=sa;password=AAA"
Dim conn As SqlClient.SqlConnection = New SqlConnection(CnnStr)
conn.Open()
Dim myCmd As SqlCommand


Dim i As Integer
For i = 0 To mydataset.Tables(0).Rows.Count - 1 Step i + 1
strSql = "Insert Into ......"
Try
myCmd = New SqlCommand(strSql, conn)
myCmd.ExecuteNonQuery()
Catch
End Try
Next
MsgBox("导入保存成功！")
End If
End If

[nices0028]

Dim fileName As String
  fileName = Me.OpenFileDialog1.FileName
  '建立EXCEL连接，读入数据  
  Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & fileName & "';Extended Properties=Excel 8.0;"
  'Dim myDataset As New DataSet  

  Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn)

  Dim mydataset As DataSet = New DataSet
  Try
  da.Fill(mydataset)
  Me.DataGridView1.DataSource = mydataset.Tables(0)
  Catch ex As Exception

  End Try
  If mydataset.Tables(0).Rows.Count > 0 Then
  Dim strSql As String '////////////// imports///////////////
  Dim CnnStr As String = "server=localhost;database=AAA;user id=sa;password=AAA"
  Dim conn As SqlClient.SqlConnection = New SqlConnection(CnnStr)
  conn.Open()
  Dim myCmd As SqlCommand


  Dim i As Integer
  For i = 0 To mydataset.Tables(0).Rows.Count - 1 Step i + 1
  strSql = "Insert Into ......"
  Try
  myCmd = New SqlCommand(strSql, conn)
  myCmd.ExecuteNonQuery()
  Catch
  End Try
  Next
  MsgBox("导入保存成功！")
  End If
  End If

[Forrest23]

[Quote=引用 7 楼 nices0028 的回复:]
VB.NET code
Dim fileName As String
fileName = Me.OpenFileDialog1.FileName
'建立EXCEL连接，读入数据
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & fileName & "';E……
[/Quote]

就是这样了