sp 导出excel ,导入excel到sql 速度极快[br][br][br]asp 导出excel ,导入excel到sql 速度极快[br][br]导出excel [br] <% [br][br] dim s,sql,filename,fs,myfile,x [br][br] set fs = server.createobject("scripting.filesystemobject") [br] '--假设你想让生成的excel文件做如下的存放 [br] filename = server.mappath("order.xls") [br] '--如果原来的excel文件存在的话删除它 [br] if fs.fileexists(filename) then [br] fs.deletefile(filename) [br] end if [br] '--创建excel文件 [br] set myfile = fs.createtextfile(filename,true) [br][br] 'set rs = server.createobject("adodb.recordset") [br] '--从数据库中把你想放到excel中的数据查出来 [br] 'sql = "select * from tb_execl order by id desc" [br] 'rs.open sql,conn [br] starttime = request("starttime")[br] endtime = request("endtime")[br] startendtime = "addtime between #"& starttime &" 00:00:00# and #"& endtime [br] &" 23:59:59#"[br] strsql = "select * from ljphs200 "[br] set rstdata =conn.execute(strsql)[br] if not rstdata.eof and not rstdata.bof then [br][br] dim trline,responsestr [br] strline="" [br] for each x in rstdata.fields [br] strline = strline & x.name & chr(9) [br] next [br][br] '--将表的列名先写入excel [br] myfile.writeline strline [br][br] do while not rstdata.eof [br] strline="" [br][br] for each x in rstdata.fields [br] strline = strline & x.value & chr(9) [br] next [br] myfile.writeline strline [br][br] rstdata.movenext [br] loop [br][br] end if [br] response.write "生成excel文件成功，点击下载！"[br] rstdata.close [br] set rstdata = nothing[br] conn.close[br] set conn = nothing[br] %> [br] exce导入到sql[br] <%[br] dim patnnow[br] patnnow="uploadfile/ultrawebgrid1.xls"[br] '=====================asp读取excel注事项===========================[br] 'i)将excel97或excel2000生成的xls文件（book）看成一个数据库，其中的每一个工作表(sheet)看成数据库表[br] 'ii)ado假设excel中的第一行为字段名.所以你定义的范围中必须要包括第一行的内容[br] 'iii)excel中的行标题（即字段名）不能够包含数字. excel的驱动在遇到这种问题时就会出错的。例如你的行标题名为“f1”[br] 'iiii)如果你的excel电子表格中某一列同时包含了文本和数字的话,那么excel的odbc驱动将不能够正常, 处理这一行的数据类型，[br] '你必须要保证该列的数据类型一致[br] '===========================================================[br] dim connxls,driver,dbpath,rs[br] ' 建立connection对象[br] set connxls = server.createobject("adodb.connection")[br] driver = "driver={microsoft excel driver (*.xls)};"[br] dbpath = "dbq=" & server.mappath(patnnow)[br] '调用open 方法打开数据库[br] connxls.open driver & dbpath[br] 'dsn连接方式[br] 'connxls.open "dsn=test"[br] '注意 表名一定要以下边这种格试 "[表名$]" 书写[br] sql="select * from [sheet1$] "[br] set rs=connxls.execute(sql)[br] if rs.eof and rs.bof then[br] response.write "

没有找到您需要的数据!!"[br] else[br] do while not rs.eof[br] conn.execute"insert into yufufei [br] (haoma,yuenow,yuenowtime,yueold,yueoldtime,state) values [br] ('"&rs("号码")&"','"&rs("当前余额(分)")&"','"&rs("当前余额时间")&"','"&rs("上次余额(分)")&"','"&rs("上次余额时间")&"','"&rs("当前状态")&"')"[br] 'response.write rs("接入号")&"
"[br] rs.movenext[br] loop[br] end if[br] rs.close[br] set rs=nothing[br] connxls.close[br] set connxls=nothing[br] 'response.write "成功！"[br][br] conn.close[br] set conn=nothing[br][br] %>