'/*************************************************************************/ '/* EXCEL数据证书导入程序 */ '/* 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哪一个表签,即表

|