IDC资讯中心
clsexport2excel.asp
<%
类开始
class clsexport2excel
声明常量、变量
private strfilepath,strtitle,strsql,strfield,strrows,strcols
private strcn,strhtml,strpath
private objdbcn,objrs
private objxlsapp,objxlsworkbook,objxlsworksheet
private arrfield
初始化类
private sub class_initialize()
strcn = "driver={sql server};server=liuhq;uid=sa;pwd=sa;database=ms"
set objdbcn = server.createobject("adodb.connection")
objdbcn.open strcn
strfilepath = ".\"
strtitle = "查询结果"
strrows = 2
strcols = 1
end sub
销毁类
private sub class_terminate()
end sub
属性filepath
public property let filepath(value)
strfilepath = value
end property
public property get filepath()
filepath = strfilepath
end property
属性title
public property let title(value)
strtitle = value
end property
public property get title()
title = strtitle
end property
属性sql
public property let sql(value)
strsql = value
end property
public property get sql()
sql = strsql
end property
属性field
public property let field(value)
strfield = value
end property
public property get field()
field = strfield
end property
属性rows
public property let rows(value)
strrows = value
end property
public property get rows()
rows = strrows
end property
属性cols
public property let cols(value)
strcols = value
end property
public property get cols()
cols = strcols
end property
public function export2excel()
if strsql = "" or strfield = "" then
response.write "参数设置错误，请与管理员联系！谢谢"
response.end
end if
if right(strfilepath,1) = "/" or right(strfilepath,1) = "\" then
strfilepath = left(strfilepath,len(strfilepath)-1)
end if
if instr("/",strfilepath) > 0 then
strfilepath = replace(strfilepath,"/","\")
end if
strfilepath = strfilepath & "\"
set objfso = createobject("scripting.filesystemobject")
if objfso.folderexists(server.mappath(strfilepath)) = false then
objfso.createfolder(server.mappath(strfilepath))
end if
strfilename = strfilepath & cstr(createfilename()) & ".xls"
set objrs = server.createobject("adodb.recordset")
objrs.open strsql,objdbcn,3,3
if objrs.recordcount <= 0 then
strhtml = "暂时没有任何合适的数据导出，如有疑问，请与管理员联系！抱歉"
else
set objxlsapp = server.createobject("excel.application")
objxlsapp.visible = false
objxlsapp.workbooks.add
set objxlsworkbook = objxlsapp.activeworkbook
set objxlsworksheet = objxlsworkbook.worksheets(1)
objxlsworksheet.cells(1,1).value = strtitle
arrfield = split(strfield,"||")
for f = 0 to ubound(arrfield)
objxlsworksheet.cells(2,f+1).value = arrfield(f)
next
for c = 1 to objrs.recordcount
for f = 0 to objrs.fields.count – 1
身份证号码特殊处理
if objrs.fields(f).name = "pm_field_41325" or objrs.fields(f).name = "cardid" then
objxlsworksheet.cells(c+2,f+1).value = "" & objrs.fields(f).value
就业特殊处理
elseif objrs.fields(f).name = "jiuye" then
select case objrs.fields(f).value
case 1
objxlsworksheet.cells(c+2,f+1).value = "是"
case 0
objxlsworksheet.cells(c+2,f+1).value = "否"
case -1
objxlsworksheet.cells(c+2,f+1).value = "(未知)"
end select
else
objxlsworksheet.cells(c+2,f+1).value = objrs.fields(f).value
end if
next
objrs.movenext
next
objxlsworksheet.saveas server.mappath(strfilename)
strhtml = "excel文件已经导出成功，您可以<a href=" & strfilename & " target=_blank>打开</a>文件并将文件另存到本地目录中！"
objxlsapp.quit
set objxlsworksheet = nothing
set objxlsworkbook = nothing
set objxlsapp = nothing
end if
objrs.close
set objrs = nothing
if err > 0 then
strhtml = "excel文件导出时出现意外错误，请<a href=# onclick=window.history.back();>返回</a>，如有疑问，请与管理员联系！抱歉"
end if
export2excel = strhtml
end function
函数
public function createfilename()
fname=now
fname=replace(fname,":","")
fname=replace(fname,"-","")
fname=replace(fname," ","")
createfilename=fname
end function
public function debug(varstr)
response.write varstr
response.end
end function
类结束
end class
%>
tesp.asp
<%@language="vbscript" codepage="936"%>
<!–#include file="clsexport2excel.asp"–>
<!doctype html public "-//w3c//dtd html 4.01 transitional//en" "http://www.w3.org/tr/html4/loose.dtd">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=gb2312">
<title>无标题文档</title>
</head>
<body>
<%
set newexcel = new clsexport2excel
newexcel.filepath = "../excel/"
newexcel.sql = "select name,cardid from usrpopulation"
newexcel.title = "基本人口信息"
newexcel.field = "姓名||身份证号||"
response.write newexcel.export2excel()
%>
</body>
</html>
