asp中把数据导出为excel的2种方法

待我称王封你为后i 2021-11-04 16:52 300阅读 0赞

我们在做项目的时候经常要将数据库的数据导出到excel中，很多asp用户并不知道怎么写。  
这里明凯总结了两种方法来导出excel，希望能帮到大家。  
方法一：用excel组件

< %
    set rs=server.createobject("adodb.recordset")
    sql="select * from mkusers"
    rs.open sql,objconn,1,1
    Set ExcelApp =CreateObject("Excel.Application")
    ExcelApp.Application.Visible = True
    Set ExcelBook = ExcelApp.Workbooks.Add
    ExcelBook.WorkSheets(1).cells(1,1).value ="用户表"
    ExcelBook.WorkSheets(1).cells(2,1).value = "用户编号"
    ExcelBook.WorkSheets(1).cells(2,2).value = "登陆名"
    ExcelBook.WorkSheets(1).cells(2,3).value = "真实姓名"
    ExcelBook.WorkSheets(1).cells(2,4).value = "密码"
    cnt =3 
    do while not rs.eof 
    ExcelBook.WorkSheets(1).cells(cnt,1).value = rs("provinceid") 
    ExcelBook.WorkSheets(1).cells(cnt,2).value = rs("province") 
    ExcelBook.WorkSheets(1).cells(cnt,3).value = rs("flag") 
    ExcelBook.WorkSheets(1).cells(cnt,4).value = rs("id")
    rs.movenext 
    cnt = cint(cnt) + 1 
    loop 
    Excelbook.SaveAs "d:\yourfile.xls"  '这个是数据导出完毕以后在D盘存成文件
    ExcelApp.Application.Quit   '导出以后退出Excel
    Set ExcelApp = Nothing   '注销Excel对象
    %>< %
    set rs=server.createobject("adodb.recordset")
    sql="select * from mkusers"
    rs.open sql,objconn,1,1
    Set ExcelApp =CreateObject("Excel.Application")
    ExcelApp.Application.Visible = True
    Set ExcelBook = ExcelApp.Workbooks.Add
    ExcelBook.WorkSheets(1).cells(1,1).value ="用户表"
    ExcelBook.WorkSheets(1).cells(2,1).value = "用户编号"
    ExcelBook.WorkSheets(1).cells(2,2).value = "登陆名"
    ExcelBook.WorkSheets(1).cells(2,3).value = "真实姓名"
    ExcelBook.WorkSheets(1).cells(2,4).value = "密码"
    cnt =3 
    do while not rs.eof 
    ExcelBook.WorkSheets(1).cells(cnt,1).value = rs("provinceid") 
    ExcelBook.WorkSheets(1).cells(cnt,2).value = rs("province") 
    ExcelBook.WorkSheets(1).cells(cnt,3).value = rs("flag") 
    ExcelBook.WorkSheets(1).cells(cnt,4).value = rs("id")
    rs.movenext 
    cnt = cint(cnt) + 1 
    loop 
    Excelbook.SaveAs "d:\yourfile.xls"  '这个是数据导出完毕以后在D盘存成文件
    ExcelApp.Application.Quit   '导出以后退出Excel
    Set ExcelApp = Nothing   '注销Excel对象
    %>

方法二：使用文件组件

< % 
    dim s,sql,filename,fs,myfile,x 
      
    Set fs = server.CreateObject("scripting.filesystemobject") 
    '--假设你想让生成的EXCEL文件做如下的存放 
    filename = Server.MapPath("order.xls") 
    '--如果原来的EXCEL文件存在的话删除它 
    if fs.FileExists(filename) then 
       fs.DeleteFile(filename) 
    end  if 
    '--创建EXCEL文件 
    set myfile = fs.CreateTextFile(filename,true) 
      
      
    StartTime = Request("StartTime")
    EndTime = Request("EndTime")
    StartEndTime = "AddTime between #"& StartTime &" 00:00:00# and #"& EndTime &" 23:59:59#"
    strSql = "select * from mksuers "
    Set rstData =conn.execute(strSql)
    if not rstData.EOF and not rstData.BOF then 
      
       dim  trLine,responsestr 
       strLine=""
       For each x in rstData.fields 
         strLine = strLine & x.name & chr(9) 
       Next
      
    '--将表的列名先写入EXCEL 
       myfile.writeline strLine 
      
       Do while Not rstData.EOF 
         strLine=""
      
         for each x in rstData.Fields 
           strLine = strLine & x.value &  chr(9) 
         next 
         myfile.writeline  strLine 
      
         rstData.MoveNext 
       loop 
      
    end if 
    Response.Write  "生成EXCEL文件成功，点击<a href="/" rel="external nofollow" order.xls"" target=""_blank"">下载！"
    rstData.Close 
    set rstData = nothing
    Conn.Close
    Set Conn = nothing
    %>

可以看出，第一种方法是直接导出的是excel文件，而第二张方法是到处的是文本文件，只不过后缀名改成了xls。  
然后看起来就是excel了。  
经过对比第一种方法的效率没有第二种方法的效率高，而且也不好控制。  
推荐大家使用第二种写文件的方法来进行excel操作。  
![在这里插入图片描述][aHR0cHM6Ly9vc2NpbWcub3NjaGluYS5uZXQvb3NjbmV0LzZmZThkNGE3YzMwMjBjMGFjYmU2ZTQxNDI3NWE4MTYyNDEwLmpwZw]

[aHR0cHM6Ly9vc2NpbWcub3NjaGluYS5uZXQvb3NjbmV0LzZmZThkNGE3YzMwMjBjMGFjYmU2ZTQxNDI3NWE4MTYyNDEwLmpwZw]: /images/20211104/ec0053eace074140b1f65f0ac62f9294.png