ASP将Excel导入到Access数据库的方法实例

Excel中的数据导入到Access中，前提是在Access中的表已经建好。dimconndimconn2setconn=CreateObject("ADODB.Connection")conn.Open"Provider=Microsoft.Jet.OLEDB.4.0;JetOLEDB:DatabasePassword=;DataSource=c:\book1.mdb"setconn2=CreateObject("ADODB.Connection")conn2.Open"Provider=Microsoft.Jet.OLEDB.4.0;JetOLEDB:DatabasePassword=;Extendedproperties=Excel5.0;DataSource=c:\book1.xls"sql="select*FROM[Sheet1$]"setrs=conn2.execute(sql)whilenotrs.eofsql="insertintoxxx([a],[bb],[c],[d])values('"&fixsql(rs(0))&",'"&fixsql(rs(1))&",'"&fixsql(rs(2))&"','"&fixsql(rs(3))&"')"conn.execute(sql)rs.movenextwendconn.closesetconn=nothingconn2.closesetconn2=nothingfunctionfixsql(str)dimnewstrnewstr=strifisnull(newstr)thennewstr=""elsenewstr=replace(newstr,"”',"”')endiffixsql=newstrendfunction导入到SqlServer数据库中时，如果Excel文件和数据库不在同一台服务器上时，请参考上面的代码。在同一机器上可以参考下面代码(不需要先把表建表，程序会自己动建表，AExcel中的第一行数据做为表的字段名)dimconnsetconn=CreateObject("ADODB.Connection")conn.Open("driver={SQLServer};server=localhost;uid=sa;pwd=sa;database=hwtemp;")sql="select*intonewtableFROMOpenDataSource('Microsoft.Jet.OLEDB.4.0','DataSource=""c:\book1.xls"";UserID=Admin;Password=;Extendedproperties=Excel5.0')...[Sheet1$]IIconn.execute(sql)conn.closesetconn=nothing以下代码优点，不用打开EXCEL进程，不会出现EXCEL进程无法结束而死机缺点:可能会出现导入空格的现像.<%subdatalntoSqlServer_ceritificate(strFileName,strSheetName,myConn)'定义dimmyConnectiondimstrNamedimrsXsl,rsSqldimstr_Xsl,str_SqldimmyConn_Xsldimcmddimi,jdimmaxIdstrName=strFileNamesetmyConnection=server.createobject("adodb.connection")setrsXsl=Server.Createobject("ADODB.Recordset")setrsSql=Server.CreateObject("ADODB.Recordset")setcmd=Server.CreateObject("ADODB.Command")cmd.ActiveConnection=myConnmyConn_Xsl="Provider=Microsoft.Jet.OLEDB.4.0;DataSource="&strName&";ExtendedProperties=Excel8.0"'myConn_Xsl="Provider=Microsoft.Jet.OLEDB.4.0;DataSource="&Application("ASP_Directory")&"chapter05\database\"&strName&";ExtendedProperties=Excel8.0"'打开连接myConnection.openmyConn_Xsl'打开表str_Xsl="select*from["&strSheetName&"$]"rsXsl.openstr_Xsl,myConnection,1,1j=1DoWhilenotrsXsI.eof'取出最大值str_SqI="seIectMax(id)asmaxldfromnew"rsSql.openstr_Sql,myConn,1,3IfNotrsSql.EofThenIfnotisNull(rsSql("maxld"))Thenmaxld=Clng(rsSql("maxld"))+1ElsemaxId=1EndifelsemaxId=1EndifrsSql.close'〃关闭对象'加入数据库strqbsr=""yssr=""str_Sql="insertintonewvalues(”&maxld&","&rsXsl(1)&"T"&rsXsl(2)&"T"&rsXsl(3)&"T"&rsXsl(4)&"T"&rsXsl(5)&"T"&rsXsl(6)&"T"&rsXsl(7)&"T"&rsXsl(8)&"T"&rsXsl(9)&"T"&rsXsl(10)&"T"&rsXsl(11)&"T"&rsXsl(12)&"',"&rsXsl(13)&"T"&rsXsl(14)&"T"&rsXsl(15)&"T"&rsXsl(16)&"T"&strqbsr&"T"&yssr&"')"cmd.CommandText=str_Sqlcmd.Execute()j=j+1rsXsl.movenextLoopresponse.write共导入vfontcolor='red'>"&j-1&"v/font涤记录<br>"response.write"vahref=javascript:history.back()确定v/a>"setrsXsl=nothingsetrsSql=nothingsetmyconnection=nothingsetcmd=nothingendsubfile1="”'&request.form("filename2")&"”'strtj=mid(request.form("filename2"),instrrev(file1,"\"),(instrrev(file1,".")-instrrev(file1,"\")-1))iffile1=""thenresponse.write"请选择您要导入的Excel表!vpx/p>"%>vahref=javascript:history.back()>上一页v/a><%response.endendif'respons.write"”‘&