Asp导出数据库数据到Excel

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/u010393758/article/details/50423648

最近做ASP,需要导出数据库数据到Excel中,结果卡住好久没有进展。

今天终于解决了,总结下过程:

1.整体代码

<span style="font-size:18px;"><%
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 = "实习编号" 
          ExcelBook.WorkSheets(1).cells(2,5).value = "结果" 
          cnt = 3 

            Dim rs ,strSql ,Num ,pageNum ,page ,loopNum
            strSql = "SELECT UserID, RealName FROM TUser WHERE ClassID = " & ClassID
            Set rs = Server.CreateObject("ADODB.Recordset")
            rs.open strSql,conn,1,1

          Response.Write rs("UserID")
            while not rs.EOF
              Dim rss1, strsqls1
              Set rss1 = Server.CreateObject("ADODB.RecordSet")
              strSqls1 = "Select * from TReport Where UserID='" & rs("UserID") & "'"
              rss1.open strSqls1, conn, 1, 1

              'Response.Write rss1("ProblemID")             
              do while not rss1.eof 
              ExcelBook.WorkSheets(1).cells(cnt,1).value = rs("UserID") 
              ExcelBook.WorkSheets(1).cells(cnt,2).value = rs("RealName") 
              ExcelBook.WorkSheets(1).cells(cnt,3).value = rss1("ProblemID") 
              ExcelBook.WorkSheets(1).cells(cnt,4).value = rss1("Subject")
              ExcelBook.WorkSheets(1).cells(cnt,5).value = rss1("Result")
              'Response.Write rss1("UserID")&" "
              rss1.movenext 
              cnt = cint(cnt) + 1 
              loop 
              
            rs.movenext
            wend
            rs.close
            Set rs=Nothing
        'daytime=year(now())&"-"&month(now())&"-"&day(now())&"-"&hour(now())&"-"&Minute(now()) 
        Excelbook.SaveAs "F:\hello.xls"
        ExcelApp.Application.Quit
        Set ExcelApp = Nothing
%></span>

2.就在创建ExcelApp的语句这,只要一执行,就运行超时,出不来正确结果。中间翻遍各种博客解答,调过Excel Application组件、设置过IIS权限,都没有什么效果,最终看到一篇帖子,完美的解决了我的问题。附链接如下:asp中把数据库内容导出到excel
感谢分享。

3.解决方法:

管理工具->组件服务->计算机->我的电脑->DCOM配置

(也可以通过运行->DCOMCNFG来启动组件服务配置

选择Microsoft Excel应用程序->属性

标识:选择交互式用户

安全:三个选项都选为自定义,加入Everyone账户,赋予权限

常规:身份验证级别:无

展开阅读全文

没有更多推荐了,返回首页