ASP导出Excel数据的四种方法

　　一、使用owc

　　什么是owc？

　　owc是office web compent的缩写，即microsoft的office web，它为在web中绘制图形提供了灵活的同时也是最基本的机制。在一个intranet环境中，如果可以假设客户机上存在特定的和一些功能强大的软件（如ie5和office 2000），那么就有能力利用office web组件提供一个交互式图形开发环境。这种模式下，客户端工作站将在整个任务中分担很大的比重。

 

＜%option explicit
class excelgen
private ob(sun企业级应用的首选)readsheet
private icoloffset

private irowoffset
sub class_initialize()
set objsp(sun企业级应用的首选)readsheet = server.createobject("owc.spreadsheet")
irowoffset = 2
icoloffset = 2
end sub

sub class_terminate()
set objsp(sun企业级应用的首选)readsheet = nothing clean up
end sub

public property let columnoffset(icoloff)
if icoloff ＞ 0 then
icoloffset = icoloff
else
icoloffset = 2
end if
end property

public property let rowoffset(irowoff)
if irowoff ＞ 0 then
irowoffset = irowoff
else
irowoffset = 2
end if
end property sub generateworksheet(objrs)
populates the excel worksheet based on a recordsets contents
start by displaying the titles
if objrs.eof then exit sub
dim objfield, icol, irow
icol = icoloffset
irow = irowoffset
for each objfield in objrs.fields
objsp(sun企业级应用的首选)readsheet.cells(irow, icol).value = objfield.name
objsp(sun企业级应用的首选)readsheet.columns(icol).autofitcolumns
设置excel表里的字体
objsp(sun企业级应用的首选)readsheet.cells(irow, icol).font.bold = true
objsp(sun企业级应用的首选)readsheet.cells(irow, icol).font.italic = false
objsp(sun企业级应用的首选)readsheet.cells(irow, icol).font.size = 10
objsp(sun企业级应用的首选)readsheet.cells(irow, icol).halignment = 2 居中
icol = icol + 1
next objfield
display all of the data
do while not objrs.eof
irow = irow + 1
icol = icoloffset
for each objfield in objrs.fields
if isnull(objfield.value) then
objsp(sun企业级应用的首选)readsheet.cells(irow, icol).value = ""
else
objsp(sun企业级应用的首选)readsheet.cells(irow, icol).value = objfield.value
objsp(sun企业级应用的首选)readsheet.columns(icol).autofitcolumns
objsp(sun企业级应用的首选)readsheet.cells(irow, icol).font.bold = false
objsp(sun企业级应用的首选)readsheet.cells(irow, icol).font.italic = false
objsp(sun企业级应用的首选)readsheet.cells(irow, icol).font.size = 10
end if
icol = icol + 1
next objfield
objrs.movenext
loop
end sub function saveworksheet(strfilename)

save the worksheet to a specified filename
on error resume next
call objsp(sun企业级应用的首选)readsheet.activesheet.export(strfilename, 0)
saveworksheet = (err.number = 0)
end function
end class

dim objrs
set objrs = server.createobject("adodb.recordset")
objrs.open "select * from xxxx", "provider=sqloledb.1;persist security

info=true;user id=xxxx;password=xxxx;initial catalog=xxxx;data source=xxxx;"
dim savename
savename = request.cookies("savename")("name")
dim objexcel
dim excelpath
excelpath = "excel" & savename & ".xls"
set objexcel = new excelgen
objexcel.rowoffset = 1
objexcel.columnoffset = 1
objexcel.generateworksheet(objrs)
if objexcel.saveworksheet(server.mappath(excelpath)) then
response.write "＜html＞＜body bgcolor=gainsboro text=#000000＞已保存为excel文件.

＜a href=" & server.urlencode(excelpath) & "＞下载＜/a＞"
else
response.write "在保存过程中有错误!"
end if
set objexcel = nothing
objrs.close