一使用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軟件打開此文件
From:http://tw.wingwit.com/Article/program/net/201311/15808.html
