现在的导入语句:
Select * Into ExcelTemp1 From OPENROWSET
('MICROSOFT.JET.OLEDB.4.0','Excel 8.0;IMEX=1;HDR=YES;DATABASE="+filepath+"',sheet1$)
____________________________________________________
Dim sFile As String
Dim i As Integer
Dim s, ss As String
Dim sAddFields, sUpdateFields, sWhereFields As String
Dim nRow, nCol As Integer
Dim sErr As String
Dim DS As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim AddSQL, AddSQLfields, AddSQLvalues, UpdateSQL, WhereSQL, DeleteSQL As String
Dim sTmp() As String
'----------------------------------------]
On Error GoTo err1
If Me.ddTableList.SelectedValue = "Null" Then
Response.Write("<script language='javascript'>alert('请选择要导入数据表名!');</script>")
Exit Sub
End If
sFile = File1.PostedFile.FileName
If sFile = "" Then
Response.Write("<script language='javascript'>alert('请选择要导入数据的Excel配置文件!');</script>")
Exit Sub
End If
sFile = Server.MapPath("..") & "\ExcelData\" & Right(File1.PostedFile.FileName, Len(File1.PostedFile.FileName) - InStrRev(File1.PostedFile.FileName, "\"))
File1.PostedFile.SaveAs(sFile)
For i = 0 To Me.ltAdd.Items.Count - 1
If Me.ltAdd.Items(i).Selected Then
sAddFields = sAddFields & Left(ltAdd.Items(i).Text, InStr(ltAdd.Items(i).Text, "(") - 1) & ","
End If
Next
If Len(sAddFields) > 0 Then sAddFields = Left(sAddFields, Len(sAddFields) - 1)
For i = 0 To Me.ltUpdate.Items.Count - 1
If Me.ltUpdate.Items(i).Selected Then
sUpdateFields = sUpdateFields & Left(ltUpdate.Items(i).Text, InStr(ltUpdate.Items(i).Text, "(") - 1) & ","
End If
Next
If Len(sUpdateFields) > 0 Then sUpdateFields = Left(sUpdateFields, Len(sUpdateFields) - 1)
For i = 0 To Me.ltWhere.Items.Count - 1
If Me.ltWhere.Items(i).Selected Then
sWhereFields = sWhereFields & Left(ltWhere.Items(i).Text, InStr(ltWhere.Items(i).Text, "(") - 1) & ","
End If
Next
If Len(sWhereFields) > 0 Then
sWhereFields = Left(sWhereFields, Len(sWhereFields) - 1)
Else
Response.Write("<script language='javascript'>alert('请选择关键字条件字段!');</script>")
Exit Sub
End If
'----------------------------------------
MyConnection = New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=" & sFile & "; " & _
"Extended Properties=Excel 8.0;")
MyCommand = New System.Data.OleDb.OleDbDataAdapter( _
"select * from [sheet1$]", MyConnection)
DS = New System.Data.DataSet
MyCommand.Fill(DS)
sErr = ""
For nRow = 0 To DS.Tables(0).Rows.Count - 1
AddSQLfields = ""
AddSQLvalues = ""
AddSQL = ""
UpdateSQL = ""
DeleteSQL = ""
WhereSQL = ""
'获得插入语句
If Trim(sAddFields) = "" Then
AddSQL = ""
Else
sTmp = Split(sAddFields, ",")
For i = 0 To sTmp.Length - 1
AddSQLfields = AddSQLfields & sTmp(i) & ","
AddSQLvalues = AddSQLvalues & "'" & DS.Tables(0).Rows(nRow).Item(sTmp(i)) & "',"
Next
AddSQLfields = Left(AddSQLfields, Len(AddSQLfields) - 1)
AddSQLvalues = Left(AddSQLvalues, Len(AddSQLvalues) - 1)
AddSQL = "Insert into " & Me.ddTableList.SelectedValue & " (" & AddSQLfields & ") values (" & AddSQLvalues & ")"
End If
'获得条件语句
If Trim(sWhereFields) = "" Then
WhereSQL = ""
DeleteSQL = ""
Else
sTmp = Split(sWhereFields, ",")
For i = 0 To sTmp.Length - 1
WhereSQL = WhereSQL & sTmp(i) & "='" & DS.Tables(0).Rows(nRow).Item(sTmp(i)) & "' and "
Next
WhereSQL = Left(WhereSQL, Len(WhereSQL) - 5)
DeleteSQL = "Delete " & Me.ddTableList.SelectedValue & " Where " & WhereSQL
End If
'获得更新语句
If Trim(sUpdateFields) = "" Then
UpdateSQL = ""
Else
sTmp = Split(sUpdateFields, ",")
For i = 0 To sTmp.Length - 1
UpdateSQL = UpdateSQL & sTmp(i) & "='" & DS.Tables(0).Rows(nRow).Item(sTmp(i)) & "',"
Next
UpdateSQL = Left(UpdateSQL, Len(UpdateSQL) - 1)
UpdateSQL = "update " & Me.ddTableList.SelectedValue & " set " & UpdateSQL
UpdateSQL = UpdateSQL & " Where " & WhereSQL
End If
If WhereSQL = "" Then Exit For
s = ExistData(WhereSQL) '判断Excel记录是否在SQL数据库中存在
Select Case Val(s)
Case 0 '不存在
If AddSQL = "" Or WhereSQL = "" Then
Exit Select
ElseIf (Not R1.Checked) And (Not R2.Checked) And (R3.Checked) Then
Exit Select
End If
ss = ExecSQL(AddSQL)
If ss <> "" Then
sErr = sErr & "行 " & nRow & " : " & ss & Chr(10)
End If
Case 1 '存在
If WhereSQL = "" Then
Exit For
Else
If R1.Checked And (Not R2.Checked) And (Not R3.Checked) Then '保留原来数据
ElseIf (Not R1.Checked) And (R2.Checked) And (Not R3.Checked) Then '覆盖原来数据
If UpdateSQL = "" Then Exit Select
ss = ExecSQL(UpdateSQL)
If ss <> "" Then
sErr = sErr & "行 " & nRow & " : " & ss & Chr(10)
End If
ElseIf (Not R1.Checked) And (Not R2.Checked) And (R3.Checked) Then '删除原来数据
If DeleteSQL = "" Then Exit Select
ss = ExecSQL(DeleteSQL)
If ss <> "" Then
sErr = sErr & "行 " & nRow & " : " & ss & Chr(10)
End If
Else
End If
End If
Case 2 '出错
sErr = sErr & "行 " & nRow & " : " & s & Chr(10)
End Select