通过asp把excel数据导入mssql数据库成功

1、upload.asp










    
          
  





2、readxls.asp

http://www.w3.org/1999/xhtml">






<%
session.CodePage=936
Server.ScriptTimeOut=600000
set upload=new upload_5xsoft
set file=upload.file("file1")
if file.fileSize>120000 then
%>

<% end if
if file.fileSize>0 then
    filename=year(now)&month(now)&day(now)&hour(now)&minute(now)&second(now)
    filename=filename+"."
    filenameend=file.filename
    filenameend=split(filenameend,".")
    if filenameend(1)="xls" then
        filename=filename&filenameend(1)
        file.saveAs Server.mappath("uploadfile/"&filename)
    else
        response.write "数据格式不对！"
        response.write "返回"
  response.end()
    end if
    set file=nothing
else
        response.write "文件不能为空！"
        response.write "返回"
  response.end()
End if
set upload=nothing

'上传XLS文件结束，下面从上传的XLS文件中读取数据写入到SQL数据库
   strAddr=server.MapPath("uploadfile/"&filename)
   set excelconn=server.createobject("adodb.connection")
   excelconn.open "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = "+strAddr+";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'"
sql="select * from [Sheet1$]"
set rs=server.CreateObject("adodb.recordset")
rs.open sql,excelconn,1,1
if not(rs.bof and rs.eof) then
  rs.movenext
  do while not rs.eof
    dim ConnStr
    set conn=server.createobject("adodb.connection")
    ConnStr = "driver={SQL Server};server=192.168.100.8;uid=sa;pwd=bsdsz2008;database=shizhu_2009"
    conn.open ConnStr
    sql1="select * from lizi"
    set rs1=server.CreateObject("adodb.recordset")  
    rs1.open sql1,conn,1,3
    rs1.addnew
       rs1("name")=rs(0)
       rs1("sex")=rs(1)
       rs1("shengri")=rs(2)
   rs1.update
   rs1.close
   rs.movenext
  loop
end if
rs.close()  

set rs=nothing
set rs1=nothing

excelconn.Close()  
set excelconn=nothing

conn.close()
set conn=nothing
%>




3、上传组件 upload.inc

过程描述   上传文件---读excel文件--写入数据库

点击下载此文件

上一篇: ASP页面实现Excel数据批量导入MSSQL问题
下一篇: Asp将查询结果导出到excel里
文章来自: 本站原创
引用通告: 查看所有引用 | 我要引用此文章
Tags:
相关日志: