ASP导出Excel数据的四种方法

ASP导出Excel数据的四种方法,第1张

 一 使用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 NET输出指定内容的WORD EXCEL TXT HTM等类型的文档很容易的 主要分为三步来完成

一 定义文档类型 字符编码

Response Clear()Response Buffer= true

Response Charset= utf

//下面这行很重要 attachment 参数表示作为附件下载 您可以改成 online在线打开

//filename=FileFlow xls 指定输出文件的名称 注意其扩展名和指定文件类型相符 可以为 doc xls txt

Response AppendHeader( Content Disposition attachmentfilename=FileFlow xls )

Response ContentEncoding=System Text Encoding GetEncoding( utf )

//Response ContentType指定文件类型 可以为application/ms excel application/ms word application/ms txt application/ms 或其他浏览器可直接支持文档 

Response ContentType = application/ms excel

this EnableViewState = false

二 定义一个输入流

System IO StringWriter oStringWriter = new System IO StringWriter()System Web UI HtmlTextWriter oHtmlTextWriter = new System Web UI HtmlTextWriter(oStringWriter)

三销早 将目标数据绑定到输入流输出   

this RenderControl(oHtmlTextWriter)//this 表示输出本页 你也可以绑定datagrid 或其他支持obj RenderControl()属性的控件 Response Write(oStringWriter ToString())

Response End()

四 这时如果发生 只能在执行 Render() 的过程中调用 RegisterForEventValidation 的错误提示

有两种方法可以解败友决

亏枯雀 修改web config(不推荐)<pages enableEventValidation = false ></pages>直接在导出Execl的页面修改 

lishixinzhi/Article/program/net/201311/14489


欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/yw/12391914.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2023-05-25
下一篇 2023-05-25

发表评论

登录后才能评论

评论列表(0条)

保存