求通过ASP将EXEL导入SQL SERVER的代码

[cain2001]

求通过ASP将EXEL导入SQL SERVER的代码.
最好附说明.
谢谢

[mymyal123]

将SQL导入到EXCEL和一将EXCEL的数据导入到SQL中


Set objConn=server.CreateObject("adodb.connection")
objConn.ConnectionTimeout = 60
objConn.CommandTimeout = 60
objConn.CursorLocation = 3
objConn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID = sa;Password=sa;Initial Catalog=" & SqlName & ";Data Source=" & SqlIp & ""

Set objConn1 = Server.CreateObject("ADODB.Connection")
objConn1.Provider = "Microsoft.Jet.OLEDB.4.0 "
objConn1.ConnectionString = "Data Source=" & strPath & ";" & "Extended Properties=Excel 8.0;"
objConn1.Open

Set objRs=server.CreateObject("ADODB.Recordset")
objRs.Open "select * from sheet1",objConn,1,3

Set objRs1=server.CreateObject("ADODB.Recordset")
objRs1.Open "select * from [Sheet1$]",objConn1,1,3

Dim i
Dim strValue'存放内容
Dim strTitle'存放表头
Dim strSql

If Not objRs.EOF Then

While Not objRs.EOF
strTitle = objRs1(ExcelArrange).Name
For i = ExcelArrange + 1 To objRs1.Fields.Count - 1
strTitle = strTitle & "," & objRs1(i).Name
Next

strValue = "'" & objRs(0).Value & "'"
For i = 1 To objRs.Fields.Count - 1
strValue = strValue & ",'" & objRs(i).Value & "'"
Next

strSql = "insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source=" & strPath1 & ";Extended properties=Excel 8.0')...sheet1$ (" & strTitle & ") values (" & strValue & ")"
Response.Write strSql
objConn.Execute strSql
objRs.MoveNext
i=i+1
Wend

End If




Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Provider = "Microsoft.Jet.OLEDB.4.0 "
objConn.ConnectionString = "Data Source=" & strPath & ";" & "Extended Properties=Excel 8.0;"
objConn.Open

Set objConn1=server.CreateObject("adodb.connection")
objConn1.ConnectionTimeout = 60
objConn1.CommandTimeout = 60
objConn1.CursorLocation = 3
objConn1.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID = sa;Password=sa;Initial Catalog=" & SqlName & ";Data Source=" & SqlIp & ""

Set objRs=server.CreateObject("ADODB.Recordset")
objRs.Open "select * from [Sheet1$]",objConn,1,3

Set objRs1=server.CreateObject("ADODB.Recordset")
objRs1.Open "select * from sheet1",objConn1,1,3

Dim i
Dim strValue'存放内容
Dim strTitle'存放表头
Dim strSql

If Not objRs.EOF Then

While Not objRs.EOF
strTitle = objRs1(0).Name
For i = 1 To objRs1.Fields.Count - 1
strTitle = strTitle & "," & objRs1(i).Name
Next

strValue = "'" & objRs(ExcelArrange).Value & "'"
For i = ExcelArrange + 1 To objRs.Fields.Count - 1
strValue = strValue & ",'" & objRs(i).Value & "'"
Next

strSql="insert into sheet1 (" & strTitle & ") values (" & strValue & ")"
'Response.Write strSql
objConn1.Execute strSql
objRs.MoveNext
i=i+1
Wend

End If

[underone]

<%
Dim strPath '路径
strPath = Server.MapPath("book1.xls")
Dim ExcelArrange 'Execl从第几列开始选择数据
ExcelArrange = 0

Set objConn1 = Server.CreateObject("ADODB.Connection")
objConn1.Provider = "Microsoft.Jet.OLEDB.4.0 "
objConn1.ConnectionString = "Data Source=" & strPath & ";" & "Extended Properties=Excel 8.0;"
objConn1.Open

Set objConn=server.CreateObject("adodb.connection")
objConn.ConnectionTimeout = 60
objConn.CommandTimeout = 60
objConn.CursorLocation = 3
objConn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID = sa;Password=sa;Initial Catalog=XX;Data Source=192.168.1.77"

Set objRs1 = server.CreateObject("ADODB.Recordset")
objRs1.Open "select * from [Sheet1$]",objConn1,1,3

Set objRs = server.CreateObject("ADODB.Recordset")
objRs.Open "select * from sheet1",objConn,1,3

Dim i
Dim strValue 'Excel内容
Dim strTitle 'Sql表头
Dim strSql

If Not objRs1.EOF Then

While Not objRs1.EOF
strTitle = objRs(0).Name
For i = 1 To objRs.Fields.Count - 1
strTitle = strTitle & "," & objRs(i).Name
Next

strValue = "'" & objRs1(ExcelArrange).Value & "'"
For i = ExcelArrange + 1 To objRs1.Fields.Count - 1
strValue = strValue & ",'" & objRs1(i).Value & "'"
Next

strSql="insert into sheet1 (" & strTitle & ") values (" & strValue & ")"
'Response.Write strSql
objConn.Execute strSql
objRs1.MoveNext
i=i+1
Wend

End If

objRs.Close
objRs1.Close
%>