asp中把数据导出为excel的2种方法
来源:爱站网时间:2020-02-28编辑:网友分享
asp.net数据库导出excel表,要完成实例代码的导出,可以使用相应的数据库连接,下面爱站技术频道小编来给大家介绍asp中把数据导出为excel的2种方法吧!
asp.net数据库导出excel表,要完成实例代码的导出,可以使用相应的数据库连接,下面爱站技术频道小编来给大家介绍asp中把数据导出为excel的2种方法吧!
方法一:用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。
通过爱站技术频道介绍的asp中把数据导出为excel的2种方法,相信您已经了解了编程的世界,大家是否掌握了呢?
下一篇:ASP获取新增记录ID值的方法