将Excel中的数据导入至MsSQLSERVER中(示例)

@#/*************************************************************************/
@#/* ＥＸＣＥＬ数据证书导入程序 　　　　　　　　　　　*/
@#/* 2003-6-13 yinxiang www.ykce.com 　　　　　　　　　　　*/
@#/*************************************************************************/
@#/* dataIntoSqlServer_ceritificate(strFileName,strSheetName,myConn,strKind) */
@#/* 参数说明： */
@#/* strFileName --XLS文件名 */
@#/* strSheetName--标签名 */
@#/* myConn--外连接 */
@#/* strKind--专业代码 */
@#/************************************************************************/
sub dataIntoSqlServer_ceritificate(strFileName,strSheetName,myConn,strKind)
@#定义
dim myConnection
dim strName
dim rsXsl,rsSql
dim str_Xsl,str_Sql
dim myConn_Xsl
dim cmd
dim i,j
dim strKmId @#科目ID号
dim maxId
dim maxOrderId
dim maxKm
dim str_Date
dim str_Kind

strName=strFileName
set myConnection=server.createobject("adodb.connection")
set rsXsl=Server.Createobject("ADODB.Recordset")
set rsSql=Server.CreateObject("ADODB.Recordset")
set cmd=Server.CreateObject("ADODB.Command")
set cmd.ActiveConnection=myConn

@#证书种类
str_Kind=split(strKind,"-")

@#加入上传日期时间
str_Date=FormatDateTime(Date(),2)& " " & Time()
myConn_Xsl="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strName& ";Extended Properties=Excel 8.0"
@#打开连接
myconnection.open myConn_Xsl

@#打开表
str_Xsl="select * from ["& strSheetName &"$]"
rsXsl.open str_Xsl,myconnection,1,1

@#//姓名,身份证号码,证书号码,签发日期,有效日期
j=1
Do While not rsXsl.eof

@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#
@#取出最大值
str_Sql="select Max(id) as maxId from ceritificate"
rsSql.open str_Sql,myConn,1,3
If Not rsSql.Eof Then
If not isNull(rsSql("maxId")) Then
maxId=Clng(rsSql("maxId"))+1
Else
maxId=1
End if
else
maxId=1
End if
rsSql.close@#//关闭对象
@#加入成绩单
str_Sql=" insert into ceritificate values("&maxId&",@#"&rsXsl(0)&"@#,@#"&rsXsl(1)&"@#,@#"&rsXsl(2)&"@#,@#"& str_Kind(0) & "@#,@#" & rsXsl(3)& "@#,@#"&rsXsl(4) &"@#,@#" & str_Date &"@#)"
cmd.CommandText=str_Sql
cmd.Execute()
@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#
j=j+1
rsXsl.movenext
Loop

response.write "<font color=@#red@#>" & str_Kind(1) & "</font>证书导入成功.<br>"
response.write "共导入<font color=@#red@#>" & j & "</font>条证书信息.<br>"
response.write "<a href=# onclick=@#self.close();@#>关闭窗口</a>"
set rsXsl=nothing
set rsSql=nothing
set myconnection=nothing
set cmd=nothing
end sub

代码说明：
　　1）上列代码是将EXCEL中的数据信息导入至SQLSERVER中，strKind参数是指证书的种类；
2） 链接Excel字符串：Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strName& ";Extended Properties=Excel 8.0"
3) str_Xsl="select * from ["& strSheetName &"$]"这条语句是确定是Excel哪一个表签，即表

原文转自：http://www.ltesting.net