一使用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 = ServerCreateObject(OWCSpreadsheet)
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 Recordsets contents
Start by displaying the titles
If objRSEOF then Exit Sub
Dim objField iCol iRow
iCol = iColOffset
iRow = iRowOffset
For Each objField in objRSFields
objSpreadsheetCells(iRow iCol)Value = objFieldName
objSpreadsheetColumns(iCol)AutoFitColumns
设置Excel表里的字体
objSpreadsheetCells(iRow iCol)FontBold = True
objSpreadsheetCells(iRow iCol)FontItalic = False
objSpreadsheetCells(iRow iCol)FontSize =
objSpreadsheetCells(iRow iCol)Halignment = 居中
iCol = iCol +
Next objField
Display all of the data
Do While Not objRSEOF
iRow = iRow +
iCol = iColOffset
For Each objField in objRSFields
If IsNull(objFieldValue) then
objSpreadsheetCells(iRow iCol)Value =
Else
objSpreadsheetCells(iRow iCol)Value = objFieldValue
objSpreadsheetColumns(iCol)AutoFitColumns
objSpreadsheetCells(iRow iCol)FontBold = False
objSpreadsheetCells(iRow iCol)FontItalic = False
objSpreadsheetCells(iRow iCol)FontSize =
End If
iCol = iCol +
Next objField
objRSMoveNext
Loop
End Sub Function SaveWorksheet(strFileName)
Save the worksheet to a specified filename
On Error Resume Next
Call objSpreadsheetActiveSheetExport(strFileName )
SaveWorksheet = (ErrNumber = )
End Function
End Class
Dim objRS
Set objRS = ServerCreateObject(ADODBRecordset)
objRSOpen SELECT * FROM xxxx Provider=SQLOLEDB;Persist Security
Info=True;User ID=xxxx;Password=xxxx;Initial Catalog=xxxx;Data source=xxxx;
Dim SaveName
SaveName = RequestCookies(savename)(name)
Dim objExcel
Dim ExcelPath
ExcelPath = Excel\ & SaveName & xls
Set objExcel = New ExcelGen
objExcelRowOffset =
objExcelColumnOffset =
objExcelGenerateWorksheet(objRS)
If objExcelSaveWorksheet(ServerMapPath(ExcelPath)) then
ResponseWrite ＜html＞＜body bgcolor=gainsboro text=#＞已保存为Excel文件
＜a href= & serverURLEncode(ExcelPath) & ＞下载＜/a＞
Else
ResponseWrite 在保存过程中有错误!
End If
Set objExcel = Nothing
objRSClose
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(ExcelApplication);
// Get a new workbook
var oWB = oXLWorkbooksAdd();
var oSheet = oWBActiveSheet;
var table = documentalldata;
var hang = tablerowslength;
var lie = tablerows()cellslength;
// Add table headers going cell by cell
for (i=;i＜hang;i++)
{
for (j=;j＜lie;j++)
{
oSheetCells(i+j+)value = tablerows(i)cells(j)innerText;
}
}
oXLVisible = true;
oXLUserControl = true;
}
//＞
＜/SCRIPT＞
导出到Word代码
＜script language=vbscript＞
Sub buildDoc
set table = documentalldata
row = tablerowslength
column = tablerows()cellslength
Set objWordDoc = CreateObject(WordDocument)
objWordDocApplicationDocumentsAdd theTemplate False
objWordDocApplicationVisible=True
Dim theArray()
for i= to row
for j= to column
theArray(j+i+) = tablerows(i)cells(j)innerTEXT
next
next
objWordDocApplicationActiveDocumentParagraphsAddRangeInsertBefore(综合查询结果集) //显示表格标题
objWordDocApplicationActiveDocumentParagraphsAddRangeInsertBefore()
Set rngPara = objWordDocApplicationActiveDocumentParagraphs()Range
With rngPara
Bold = True //将标题设为粗体
ParagraphFormatAlignment = //将标题居中
FontName = 隶书 //设定标题字体
FontSize = //设定标题字体大小
End With
Set rngCurrent = objWordDocApplicationActiveDocumentParagraphs()Range
Set tabCurrent = ObjWordDocApplicationActiveDocumentTablesAdd(rngCurrentrowcolumn)
for i = to column
objWordDocApplicationActiveDocumentTables()Rows()Cells(i)RangeInsertAfter theArray(i)
objWordDocApplicationActiveDocumentTables()Rows()Cells(i)RangeParagraphFormatalignment=
next
For i = to column
For j = to row
objWordDocApplicationActiveDocumentTables()Rows(j)Cells(i)RangeInsertAfter theArray(ij)
objWordDocApplicationActiveDocumentTables()Rows(j)Cells(i)RangeParagraphFormatalignment=
Next
Next
End Sub
＜/SCRIPT＞
三直接在IE中打开再存为EXCEL文件
把读出的数据用＜table＞格式在网页中显示出来同时加上下一句即可把EXCEL表在客客户端显示
＜%responseContentType =application/vndmsexcel%＞
注意显示的页面中只把＜table＞输出最好不要输出其他表格以外的信息
四导出以半角逗号隔开的csv
用fso方法生成文本文件的方法生成一个扩展名为csv文件此文件一行即为数据表的一行生成数据表字段用半角逗号隔开（有关fso生成文本文件的方法在此就不做介绍了）
CSV文件介绍 （逗号分隔文件）
选择该项系统将创建一个可供下载的CSV 文件 CSV是最通用的一种文件格式它可以非常容易地被导入各种PC表格及数据库中
请注意即使选择表格作为输出格式仍然可以将结果下载CSV文件在表格输出屏幕的底部显示有 CSV 文件选项点击它即可下载该文件
如果您把浏览器配置为将您的电子表格软件与文本（TXT）/逗号分隔文件（CSV） 相关联当您下载该文件时该文件将自动打开下载下来后如果本地已安装EXCEL点击此文件即可自动用EXCEL软件打开此文件
