asp导出excel文件大家有什么简便点的操作方法
发布网友
发布时间：2022-04-20 21:16
共1个回答
热心网友
时间：2023-09-14 13:34
一 使用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=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 = 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 ActiveSheet; var table = document all data; var hang = table rows length;
var lie = table rows( ) cells length;
// Add table headers going cell by cell for (i= ;i＜hang;i++) { for (j= ;j＜lie;j++) { oSheet Cells(i+ j+ ) value = table rows(i) cells(j) innerText; }
} oXL Visible = true; oXL 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 文件 选项 点击它即可下载该文件
lishixin/Article/program/net/201311/15808