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] '--創(chuàng)建excel文件 [br] set myfile = fs.createtextfile(filename,true) [br][br] 'set rs = server.createobject("adodb.recordset") [br] '--從數(shù)據(jù)庫中把你想放到excel中的數(shù)據(jù)查出來 [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）看成一個數(shù)據(jù)庫，其中的每一個工作表(sheet)看成數(shù)據(jù)庫表[br] 'ii)ado假設excel中的第一行為字段名.所以你定義的范圍中必須要包括第一行的內(nèi)容[br] 'iii)excel中的行標題（即字段名）不能夠包含數(shù)字. excel的驅(qū)動在遇到這種問題時就會出錯的。例如你的行標題名為“f1”[br] 'iiii)如果你的excel電子表格中某一列同時包含了文本和數(shù)字的話,那么excel的odbc驅(qū)動將不能夠正常, 處理這一行的數(shù)據(jù)類型，[br] '你必須要保證該列的數(shù)據(jù)類型一致[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] '調(diào)用open 方法打開數(shù)據(jù)庫[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 "

沒有找到您需要的數(shù)據(jù)!!"[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("當前狀態(tài)")&"')"[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] %>