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

Response.Buffer = True

Response.ContentType = "application/vnd.ms-excel"

Response.AddHeader "content-disposition","inlinefilename = SAR_List("&fid&"_"&rid&").xls"

我是用这种方式保存为excel的，可以直接打开

---