EXCEL中数据导入到SQL SERVER中(要完不成任务了,急!)
各位大侠 如何把EXCEL中数据导入到SQL SERVER中?
搞了两天了 还是没有搞出来?有没有那位以前做过,给个例子或是思路!小弟不胜感激.
就只能给这么多了,好像没有分了.
[解决办法]
導入DataGridview中
Try
Dim MyOleDbCn As New System.Data.OleDb.OleDbConnection
Dim FileName As String = " "
Dim OpenFileDialog As New OpenFileDialog
OpenFileDialog.Title = "打開 "
OpenFileDialog.Filter = ".xls|*.xls "
OpenFileDialog.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.MyDocuments
If (OpenFileDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK) Then
FileName = OpenFileDialog.FileName
End If
If FileName = " " Then
Exit Sub
End If
'If Dir(FileName) <> " " Then
' Kill(FileName)
'End If
MyOleDbCn.ConnectionString = "Provider=Microsoft.Jet.OleDb.4.0; " & _
"Data Source= " & FileName & "; " & _
"Extended ProPerties= " "Excel 8.0;HDR=Yes;IMEX=1 " " "
MyOleDbCn.Close()
MyOleDbCn.Open()
Dim ComStr As String
ComStr = "SELECT * FROM [Sheet 1$] "
Dim adapter As New OleDb.OleDbDataAdapter(ComStr, MyOleDbCn)
dt.Clear()
adapter.Fill(dt)
dt.AcceptChanges()
Me.DataGridView1.DataSource = dt
MyOleDbCn.Close()
Catch ex As Exception
MsgBox(Err.Description, MsgBoxStyle.Critical, "System Error! ")
End Try
寫入數據庫
Try
SqlCnn.Close()
If Me.ComboBox1.Text.Trim = " " Then
MsgBox( "請選擇要保存的表，不能選錯! ", MsgBoxStyle.Critical, "System Error! ")
Exit Sub
End If
Dim sqlstr As String = "select * from tl " '& Me.ComboBox1.Text.Trim
If Me.ComboBox1.Text.Trim = "領料 " Then
Dim i As Integer
Dim str As String = " "
If dt.Rows.Count - 1 > = 0 Then
For i = 0 To dt.Rows.Count - 1
str = " "
str = "insert into tl(TLF026,TLF027,TLF036,TLF037,TLF06,TLF10,CCC23,TLF11,TLF13,TLF902,[IMA02||IMA021]) values( ' " & _
IIf(dt.Rows(i).Item(1).ToString = " ", " ", dt.Rows(i).Item(1)) & " ', ' " & _
IIf(dt.Rows(i).Item(2).ToString = " ", " ", dt.Rows(i).Item(2)) & " ', ' " & _
IIf(dt.Rows(i).Item(3).ToString = " ", " ", dt.Rows(i).Item(3)) & " ', ' " & _
IIf(dt.Rows(i).Item(4).ToString = " ", " ", dt.Rows(i).Item(4)) & " ', ' " & _
IIf(dt.Rows(i).Item(5).ToString = " ", " ", dt.Rows(i).Item(5)) & " ', ' " & _
IIf(dt.Rows(i).Item(6).ToString = " ", " ", dt.Rows(i).Item(6)) & " ', ' " & _
IIf(dt.Rows(i).Item(7).ToString = " ", " ", dt.Rows(i).Item(7)) & " ', ' " & _
IIf(dt.Rows(i).Item(8).ToString = " ", " ", dt.Rows(i).Item(8)) & " ', ' " & _
IIf(dt.Rows(i).Item(9).ToString = " ", " ", dt.Rows(i).Item(9)) & " ', ' " & _
IIf(dt.Rows(i).Item(10).ToString = " ", " ", dt.Rows(i).Item(10)) & " ', ' " & _
IIf(dt.Rows(i).Item(11).ToString = " ", " ", Replace(dt.Rows(i).Item(11), " ' ", " ")) & " ') "
'IIf(dt.Rows(i).Item(12).ToString = " ", " ", dt.Rows(i).Item(12)) & " ') "
ExecuteSql(str)
Next
MsgBox( "數據保存成功! ", MsgBoxStyle.Information, "System Info! ")
SqlCnn.Close()
End If
End If
Catch ex As Exception
SqlCnn.Close()
MsgBox(Err.Description, MsgBoxStyle.Critical, "System Error! ")
End Try
[解决办法]
从Excel里读到DataTable里，for一下，动态拼一个sql，一行一行的导进去
