ASP导出Excel数据的四种方法

 一 使用OWC

什么是OWC？

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

＜%Option Explicit Class ExcelGen Private objSpreadsheet Private iColOffset

Private iRowOffset Sub Class_Initialize() Set objSpreadsheet = Server CreateObject( OWC Spreadsheet ) iRowOffset = iColOffset = End Sub

Sub Class_Terminate() Set objSpreadsheet = Nothing Clean up End Sub

Public Property Let ColumnOffset(iColOff) If iColOff ＞ then iColOffset = iColOff Else iColOffset = End If End Property

Public Property Let RowOffset(iRowOff) If iRowOff ＞ then iRowOffset = iRowOff Else iRowOffset = End If End Property Sub GenerateWorksheet(objRS) Populates the Excel worksheet based on a Recordset s 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 objSpreadsheet Cells(iRow iCol) Value = objField Name objSpreadsheet Columns(iCol) AutoFitColumns 设置Excel表里的字体 objSpreadsheet Cells(iRow iCol) Font Bold = True objSpreadsheet Cells(iRow iCol) Font Italic = False objSpreadsheet Cells(iRow iCol) Font Size = objSpreadsheet Cells(iRow iCol) Halignment = 居中 iCol = iCol + Next objField Display all of the data Do While Not objRS EOF iRow = iRow + iCol = iColOffset For Each objField in objRS Fields If IsNull(objField Value) then objSpreadsheet Cells(iRow iCol) Value = Else objSpreadsheet Cells(iRow iCol) Value = objField Value objSpreadsheet Columns(iCol) AutoFitColumns objSpreadsheet Cells(iRow iCol) Font Bold = False objSpreadsheet Cells(iRow iCol) Font Italic = False objSpreadsheet Cells(iRow iCol) Font Size = End If iCol = iCol + Next objField objRS MoveNext Loop End Sub Function SaveWorksheet(strFileName)

Save the worksheet to a specified filename On Error Resume Next Call objSpreadsheet ActiveSheet Export(strFileName ) SaveWorksheet = (Err Number = ) End Function End Class

Dim objRS Set objRS = Server CreateObject( ADODB Recordset ) objRS Open SELECT * FROM xxxx Provider=SQLOLEDB Persist Security

Info=TrueUser ID=xxxxPassword=xxxxInitial Catalog=xxxxData source=xxxxDim SaveName SaveName = Request Cookies( savename )( name ) Dim objExcel Dim ExcelPath ExcelPath = Excel\ &SaveName &xls Set objExcel = New ExcelGen objExcel RowOffset = objExcel ColumnOffset = objExcel GenerateWorksheet(objRS) If objExcel SaveWorksheet(Server MapPath(ExcelPath)) then Response Write ＜＞＜body bgcolor= gain *** oro text= # ＞已保存为Excel文件

＜a href= &server URLEncode(ExcelPath) &＞下载＜/a＞ Else Response Write 在保存过程中有错误! End If Set objExcel = Nothing objRS Close Set objRS = Nothing %＞

二 用Excel的Application组件在客户端导出到Excel或Word

注意 两个函数中的 data 是网页中要导出的table的 id

＜input type= hidden name= out_word onclick= vbscript:buildDoc value= 导出到word class= notPrint ＞ ＜input type= hidden name= out_excel onclick= AutomateExcel()value= 导出到excel class= notPrint ＞ 

导出到Excel代码

＜SCRIPT LANGUAGE= javascript ＞ ＜! function AutomateExcel() { // Start Excel and get Application object var oXL = new ActiveXObject( Excel Application )// Get a new workbook var oWB = oXL Workbooks Add()var oSheet = oWB ActiveSheetvar table = document all datavar hang = table rows length

var lie = table rows( ) cells length

// Add table headers going cell by cell for (i= i＜hangi++) { for (j= j＜liej++) { oSheet Cells(i+ j+ ) value = table rows(i) cells(j) innerText}

} oXL Visible = trueoXL UserControl = true} // ＞ ＜/SCRIPT＞ 

导出到Word代码

＜script language= vbscript ＞ Sub buildDoc set table = document all data row = table rows length column = table rows( ) cells length

Set objWordDoc = CreateObject( Word Document )

objWordDoc Application Documents Add theTemplate False objWordDoc Application Visible=True

Dim theArray( ) for i= to row for j= to column theArray(j+ i+ ) = table rows(i) cells(j) innerTEXT next next objWordDoc Application ActiveDocument Paragraphs Add Range InsertBefore( 综合查询结果集 ) //显示表格标题

objWordDoc Application ActiveDocument Paragraphs Add Range InsertBefore( ) Set rngPara = objWordDoc Application ActiveDocument Paragraphs( ) Range With rngPara Bold = True //将标题设为粗体 ParagraphFormat Alignment = //将标题居中 Font Name = 隶书 //设定标题字体 Font Size = //设定标题字体大小 End With Set rngCurrent = objWordDoc Application ActiveDocument Paragraphs( ) Range Set tabCurrent = ObjWordDoc Application ActiveDocument Tables Add(rngCurrent row column)

for i = to column

objWordDoc Application ActiveDocument Tables( ) Rows( ) Cells(i) Range InsertAfter theArray(i ) objWordDoc Application ActiveDocument Tables( ) Rows( ) Cells(i) Range ParagraphFormat alignment= next For i = to column For j = to row objWordDoc Application ActiveDocument Tables( ) Rows(j) Cells(i) Range InsertAfter theArray(i j) objWordDoc Application ActiveDocument Tables( ) Rows(j) Cells(i) Range ParagraphFormat alignment= Next Next

End Sub ＜/SCRIPT＞ 

三 直接在IE中打开 再存为EXCEL文件

把读出的数据用＜table＞格式 在网页中显示出来 同时 加上下一句即可把EXCEL表在客客户端显示

＜%response ContentType = application/vnd ms excel %＞ 

注意 显示的页面中 只把＜table＞输出 最好不要输出其他表格以外的信息

四 导出以半角逗号隔开的csv

用fso方法生成文本文件的方法 生成一个扩展名为csv文件 此文件 一行即为数据表的一行 生成数据表字段用半角逗号隔开 （有关fso生成文本文件的方法 在此就不做介绍了）

CSV文件介绍 （逗号分隔文件）

选择该项系统将创建一个可供下载的CSV 文件 CSV是最通用的一种文件格式 它可以非常容易地被导入各种PC表格及数据库中

请注意即使选择表格作为输出格式 仍然可以将结果下载CSV文件 在表格输出屏幕的底部 显示有 CSV 文件 选项 点击它即可下载该文件

lishixinzhi/Article/program/net/201311/15808

ASP具备动态输出任一Office应用程序文件格式的功能 在开始编写代码之前 我们首先需要做的就是设置正确的文件类型 因为浏览器需要知道如何处理文件 第二步是编辑文件名称 我们可以使用HTML和CSS来创建Word文档或Excel文档的样式  下面这段例子代码可用于在线创建Word文档

以下是引用片段

<% Response ContentType = application/msword Response AddHeader Content Disposition attachmentfilename=NAME doc ???  response Write( Dotnetindex : <a href= visit/ >// dotnetindex >Visit Site</a><br>&vbnewline) response Write( <h >We can use HTML codes for word documents</h >) response Write ( <div style= padding: pxfont: px arial >CSS can be used tooo</span>) %>

下面这段例子代码可用于在线创建Excel文档

BORDER RIGHT: #cccccc px solidPADDING RIGHT: pxBORDER TOP: #cccccc px solidPADDING LEFT: pxBACKGROUND: #f f f PADDING BOTTOM: pxMARGIN: px pxBORDER LEFT: #cccccc px solidPADDING TOP: pxBORDER BOTTOM: #cccccc px solid ><% Response AddHeader Content Disposition attachmentfilename=members xls Response ContentType = application/vnd ms excel response write <table width= % border= >response write <tr>response write <th width= % ><b>Name</b></th>response write <th width= % ><b>Username</b></th>response write <th width= % ><b>Password</b></th>response write </tr>response write <tr>response write <td width= % >Scud Block</td>response write <td width= % >scud@gazatem </td>response write <td width= % >mypassword</td>response write </tr>response write </table>%>lishixinzhi/Article/program/net/201311/14969

可以生成，最简单的是生成CSV

<%

dim conn,strconn

strconn="driver={Microsoft Access driver (*.mdb)}dbq="&server.mappath("dataBase/mydatabase.mdb") '这里改

为你的数据库地址

set conn=server.CreateObject("adodb.connection")

conn.Open strconn

dim s,sql,filename,fs,myfile,x

Set fs = server.CreateObject("scripting.filesystemobject")

成不同文件名的EXCEL文件,只需要更改excel.xls文件名

filename = Server.MapPath("excel.xls")

'--如果原来的EXCEL文件存在的话删除它

if fs.FileExists(filename) then

fs.DeleteFile(filename)

end if

'--创建EXCEL文件

set myfile = fs.CreateTextFile(filename,true)

strSql = "select * from tabList"

Set rstData = DataToRsStatic(conn,strSql)

if not rstData.EOF and not rstData.BOF then

dim trLine,responsestr

strLine = "序 号" &chr(9) &"姓 名" &chr(9) &"电 话" &chr(9) &"Q Q" &chr(9) &"邮 箱"

&chr(9) &"地 址" &chr(9) &"生 日" &chr(9) &"备 注"

'--将表的列名先写入EXCEL

myfile.writeline strLine

Do while Not rstData.EOF

strLine=""

strLine = rstData("fid") & chr(9) &rstData("fName")& chr(9) &rstData("fTel") & chr(9) &

rstData("fQQ") &chr(9) &rstData("fEmail")&chr(9)&rstData("fAddress")& chr(9) &rstData("birthday")&

chr(9) &rstData("fNote") &chr(9)&IfSendStr '括号改为你的数据库字段

myfile.writeline strLine

rstData.MoveNext

loop

end if

Response.Charset="utf-8"

Response.Write "<br><br>生成EXCEL文件成功，点击<a href=""excel.xls"" target=""_blank"">下载</a>！"

rstData.Close

set rstData = nothing

Conn.Close

Set Conn = nothing

Function DataToRsStatic(Conn,strSql)

Dim RsStatic

Set DataToRsStatic = Nothing

If Conn Is Nothing Then

Exit Function

End If

Set RsStatic = CreateObject("ADODB.RecordSet")

RsStatic.CursorLocation = 3

RsStatic.Open strSql,Conn,3,3

If Err.Number <>0 Then

Exit Function

End If

Set DataToRsStatic = RsStatic

End Function

%>

---