asp导出excel源码(asp读取excel数据)
本文目录一览:
asp代码实现access数据导出到excel文件
asp代码实现access数据导出到excel文件:
一,下面是导出XLS格式
%
dimreferer
referer=Request.ServerVariables(HTTP_REFERER)
DimCName,action
action=request.Form(action)
CName=../../Excel/
dimdaytime
daytime=year(now())-month(now())-day(now())-hour(now())-Minute(now())
daytime=year(now())年month(now())月day(now())日hour(now())时
Setrs=Server.CreateObject(ADODB.Recordset)
--从数据库中把你想放到EXCEL中的数据查出来
sql=select*fromgbookorderbyiddesc
rs.Opensql,conn
ifrs.EOFandrs.BOFthen
response.writescriptalert(操作出错,下面是产生错误的可能原因:nn库里暂时没有数据,所以不能导出EXCEL文件!);location.href=referer;/script
response.end
else
dimfilename,fs,myfile,x
Setfs=server.CreateObject(scripting.filesystemobject)
--假设你想让生成的EXCEL文件做如下的存放
filename=Server.MapPath(CNamedaytime.xls)
--如果原来的EXCEL文件存在的话删除它
iffs.FileExists(filename)then
fs.DeleteFile(filename)
endif
--创建EXCEL文件
setmyfile=fs.CreateTextFile(filename,true)
dimstrLine,responsestr
strLine=
Foreachxinrs.fields
strLine=strLinex.namechr(9)
Next
--将表的列名先写入EXCEL
myfile.writelinestrLine
DowhileNotrs.EOF
strLine=
foreachxinrs.Fields
strLine=strLinex.valuechr(9)
next
--将表的数据写入EXCEL
myfile.writelinestrLine
rs.MoveNext
loop
rs.Close
setrs=nothing
conn.close
setconn=nothing
setmyfile=nothing
Setfs=Nothing
Response.WriteBRBRcenterb导出成功,请选择继续操作/b/center
Response.Writebr
response.Writetablewidth=90%cellspacing=1cellpadding=3align=center
Response.Writetralign=centertd
response.write(fontcolor=green/fontahref=CNameENames.xls下载/a)nbsp;nbsp;fontcolor=green/fontahref=referer返回/a
Response.Write/td/tr/table
endif
%
二,下面是导出CSV格式
%
dimreferer
referer=Request.ServerVariables(HTTP_REFERER)
DimCName,action
action=request.Form(action)
CName=../../Excel/
dimdaytime
daytime=year(now())-month(now())-day(now())-hour(now())-Minute(now())
daytime=year(now())年month(now())月day(now())日hour(now())时
Setrs=Server.CreateObject(ADODB.Recordset)
--从数据库中把你想放到EXCEL中的数据查出来
sql=select*fromgbookorderbyiddesc
rs.Opensql,conn
ifrs.EOFandrs.BOFthen
response.writescriptalert(操作出错,下面是产生错误的可能原因:nn库里暂时没有数据,所以不能导出EXCEL文件!);location.href=referer;/script
response.end
else
DimFs,Exc
DimExcelstring,ExcelHead,Excelstr,Excelstr2
DimScou_FileNamen,Scou_FieldList
DimDest_FileName,Dest_FieldList
Dest_FileName=CNamedaytime.csv
Excelstring=
ExcelHead=识别号,主题类型,公司名称,姓名,性别,职位,城市省份,城市,公司地址,邮编,邮件,电话,传真,手机,公司网址,详细内容,IP地址,留言时间
SetRs=Server.CreateObject(ADODB.Recordset)
Sql=Select*Fromgbook
Rs.OpenSql,Conn,1,3
DoWhileNotRs.Eof
Excelstr=Rs(ID),Rs(zhutiname),Rs(gsname),Rs(gsname),Rs(sex),Rs(zhiwei),Rs(Province),Rs(City),Rs(dizhi),Rs(youbian),Rs(email),Rs(tel),Rs(fax),Rs(tel2),Rs(homeurl),Rs(body),Rs(ip),Rs(addtime)
Excelstr2=Excelstr2Chr(13)Excelstr
Rs.Movenext
Loop
Excelstring=ExcelHeadExcelstr2
Rs.Close
asp导出数据到EXCEL表格
asp导出到excel
在开头加上这一句
Response.ContentType = "application/vnd.ms-excel"
如果导出结果为空白的,那就去掉它
一下是导出Excel代码
--------------------------------------------------------------------------------
%
set rs=server.createobject("adodb.recordset")
sql="select * from provinceinfo where 1=1"
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对象
%
因你数据量大 另提示EXCEL相关信息
Excel 07-2003一个工作表最多可有65536行,行用数字1—65536表示;最多可有256列,列用英文字母A—Z,AA—AZ,BA—BZ,……,IA—IV表示;一个工作簿中最多含有255个工作表,默认情况下是三个工作表;
Excel 2007及以后版本,一个工作表最多可有1048576行,16384列;
所以建议用2007以上版本的excel
如何从asp导出至Excel表格,要详细的源码
最简单的方法就是导出CSV文本文件了,每一行对应一条记录,可使用逗号分割
CSV文件我记得默认就是使用excel打开的
asp 把表格导出excel表源码
一、用Excel对象,但要有Excel模板。速度略慢
!--#include file="../conn.asp"--
%
dim s,sql,filename,fs,myfile,x
Set fs = server.CreateObject("scripting.filesystemobject")
'--假设你想让生成的EXCEL文件做如下的存放
filename = Server.MapPath("users.xls")
'--如果原来的EXCEL文件存在的话删除它
if fs.FileExists(filename) then
fs.DeleteFile(filename)
end if
'--创建EXCEL文件
set myfile = fs.CreateTextFile(filename,true)
strSql = "select djh,bmmc,jihua,mubiao from scheme "
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=""users.xls"" target=""_blank""下载/a!"
rstData.Close
set rstData = nothing
Conn.Close
Set Conn = nothing
%
二、生成假的Excel,文件本身的格式是TXT,但扩展名使用的是XLS,用Excel也可以打开。速度快
!--#include file="../conn.asp"--
%
Response.ContentType="application/vnd.ms-excel "
%
table border="1" style="border-collapse:collapse;"
tr align="center"
td编号/td
td日期/td
td部门/td
td工作计划/td
td工作目标/td
/tr
%
set rs=server.createobject("adodb.recordset")
sql="select djh,rq,bmmc,jihua,mubiao from scheme where bmmc='"session("bmmc")"'"
rs.open sql,conn,1,1
do while not rs.eof
%
tr
td%=rs("djh")%/td
td%=rs("rq")%/td
td%=rs("bmmc")%/td
td%=rs("jihua")%/td
td%=rs("mubiao")%/td
/tr
%
rs.movenext
loop
%
/table
%
rs.close
Conn.Close
Set Conn = nothing
%