一、使用OWC
什么是OWC?
OWC是Office Web Compent的缩写,即Microsoft的Office Web组件,它为在Web中绘制图形提供了灵活的同时也是最基本的机制。在一个intranet环境中,如果可以假设客户机上存在特定的浏览器和一些功能强大的软件(如IE5和Office 2000),那么就有能力利用Office Web组件提供一个交互式图形开发环境。这种模式下,客户端工作站将在整个任务中分担很大的比重。
1
<%
Option
Explicit
2
Class ExcelGen
3
Private
objSpreadsheet
4
Private
iColOffset
5

6
Private
iRowOffset
7
Sub
Class_Initialize()
8
Set
objSpreadsheet
=
Server.
CreateObject
(
"
OWC.Spreadsheet
"
)
9
iRowOffset
=
2
10
iColOffset
=
2
11
End Sub
12

13
Sub
Class_Terminate()
14
Set
objSpreadsheet
=
Nothing
'
Clean up
15
End Sub
16

17
Public
Property
Let
ColumnOffset(iColOff)
18
If
iColOff >
0
then
19
iColOffset
=
iColOff
20
Else
21
iColOffset
=
2
22
End
If
23
End Property
24

25
Public
Property
Let
RowOffset(iRowOff)
26
If
iRowOff >
0
then
27
iRowOffset
=
iRowOff
28
Else
29
iRowOffset
=
2
30
End
If
31
End Property
Sub
GenerateWorksheet(objRS)
32
'
Populates the Excel worksheet based on a Recordset's contents
33
'
Start by displaying the titles
34
If
objRS.EOF
then
Exit
Sub
35
Dim
objField, iCol, iRow
36
iCol
=
iColOffset
37
iRow
=
iRowOffset
38
For
Each
objField in objRS.Fields
39
objSpreadsheet.Cells(iRow, iCol).Value
=
objField.Name
40
objSpreadsheet.Columns(iCol).AutoFitColumns
41
'
设置Excel表里的字体
42
objSpreadsheet.Cells(iRow, iCol).Font.Bold
=
True
43
objSpreadsheet.Cells(iRow, iCol).Font.Italic
=
False
44
objSpreadsheet.Cells(iRow, iCol).Font.Size
=
10
45
objSpreadsheet.Cells(iRow, iCol).Halignment
=
2
'
居中
46
iCol
=
iCol
+
1
47
Next
'
objField
48
'
Display all of the data
49
Do
While
Not
objRS.EOF
50
iRow
=
iRow
+
1
51
iCol
=
iColOffset
52
For
Each
objField in objRS.Fields
53
If
IsNull
(objField.Value)
then
54
objSpreadsheet.Cells(iRow, iCol).Value
=
""
55
Else
56
objSpreadsheet.Cells(iRow, iCol).Value
=
objField.Value
57
objSpreadsheet.Columns(iCol).AutoFitColumns
58
objSpreadsheet.Cells(iRow, iCol).Font.Bold
=
False
59
objSpreadsheet.Cells(iRow, iCol).Font.Italic
=
False
60
objSpreadsheet.Cells(iRow, iCol).Font.Size
=
10
61
End
If
62
iCol
=
iCol
+
1
63
Next
'
objField
64
objRS.MoveNext
65
Loop
66
End Sub
Function
SaveWorksheet(strFileName)
67

68
'
Save the worksheet to a specified filename
69
On
Error
Resume
Next
70
Call
objSpreadsheet.ActiveSheet.Export(strFileName,
0
)
71
SaveWorksheet
=
(Err.Number
=
0
)
72
End Function
73
End
Class
74

75
Dim
objRS
76
Set
objRS
=
Server.
CreateObject
(
"
ADODB.Recordset
"
)
77
objRS.Open
"
SELECT * FROM xxxx
"
,
"
Provider=SQLOLEDB.1;Persist Security
78
79
Info
=
True
;User ID
=
xxxx;Password
=
xxxx;Initial Catalog
=
xxxx;Data source
=
xxxx;
"
80
Dim
SaveName
81
SaveName
=
Request.Cookies(
"
savename
"
)(
"
name
"
)
82
Dim
objExcel
83
Dim
ExcelPath
84
ExcelPath
=
"
Excel\
"
&
SaveName
&
"
.xls
"
85
Set
objExcel
=
New
ExcelGen
86
objExcel.RowOffset
=
1
87
objExcel.ColumnOffset
=
1
88
objExcel.GenerateWorksheet(objRS)
89
If
objExcel.SaveWorksheet(Server.MapPath(ExcelPath))
then
90
'
Response.Write "<html><body bgcolor='gainsboro' text='#000000'>已保存为Excel文件.
91
92
<a href
=
'
" & server.URLEncode(ExcelPath) & "'>下载</a>"
93
Else
94
Response.Write
"
在保存过程中有错误!
"
95
End
If
96
Set
objExcel
=
Nothing
97
objRS.Close
98
Set
objRS
=
Nothing
99
%>
100

101
二、用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">
<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(
0
).cells.length;

//
Add table headers going cell by cell.
for
(i
=
0
;i<hang;i
++
)
{
for
(j
=
0
;j<lie;j
++
)
{
oSheet.Cells(i
+
1
,j
+
1
).value
=
table.rows(i).cells(j).innerText;
}

}
oXL.Visible
=
true
;
oXL.UserControl
=
true
;
}
//
-->
<
/
SCRIPT>
2. 导出到Excel代码
<script language="javascript">
function exportExcel(atblData)
{
if (typeof(EXPORT_OBJECT)!="object")
{
document.body.insertAdjacentHTML("afterBegin","<OBJECT style='display:none' classid=clsid:0002E510-0000-0000-C000-000000000046 id=EXPORT_OBJECT></Object>");
}
with (EXPORT_OBJECT){
DataType = "HTMLData";
HTMLData =atblData.outerHTML;
try{
ActiveSheet.Export("C:\\sortTEL.xls",0);
alert('导出EXCEL文档完毕');
}
catch (e)
{
alert('导出Excel表失败,请确定已安装Excel2000(或更高版本),并且没打开同名xls文件');
}
}
}
</script>
<center><input type="button" value="导出以上数据为Excel文档" onclick="exportExcel(tblData)"></center>
导出到Word代码
<script language
=
"
vbscript
"
>
Sub buildDoc
set table
=
document.all.data
row
=
table.rows.length
column
=
table.rows(
1
).cells.length

Set objWordDoc
=
CreateObject(
"
Word.Document
"
)

objWordDoc.Application.Documents.Add theTemplate, False
objWordDoc.Application.Visible
=
True

Dim theArray(
20
,
10000
)
for
i
=
0
to row
-
1
for
j
=
0
to column
-
1
theArray(j
+
1
,i
+
1
)
=
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(
1
).Range
With rngPara
.Bold
=
True
//
将标题设为粗体
.ParagraphFormat.Alignment
=
1
//
将标题居中
.Font.Name
=
"
隶书
"
//
设定标题字体
.Font.Size
=
18
//
设定标题字体大小
End With
Set rngCurrent
=
objWordDoc.Application.ActiveDocument.Paragraphs(
3
).Range
Set tabCurrent
=
ObjWordDoc.Application.ActiveDocument.Tables.Add(rngCurrent,row,column)

for
i
=
1
to column

objWordDoc.Application.ActiveDocument.Tables(
1
).Rows(
1
).Cells(i).Range.InsertAfter theArray(i,
1
)
objWordDoc.Application.ActiveDocument.Tables(
1
).Rows(
1
).Cells(i).Range.ParagraphFormat.alignment
=
1
next
For i
=
1
to column
For j
=
2
to row
objWordDoc.Application.ActiveDocument.Tables(
1
).Rows(j).Cells(i).Range.InsertAfter theArray(i,j)
objWordDoc.Application.ActiveDocument.Tables(
1
).Rows(j).Cells(i).Range.ParagraphFormat.alignment
=
1
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 文件"选项,点击它即可下载该文件。
如果您把浏览器配置为将您的电子表格软件与文本(TXT)/逗号分隔文件(CSV) 相关联,当您下载该文件时,该文件将自动打开。下载下来后,如果本地已安装EXCEL,点击此文件,即可自动用EXCEL软件打开此文件。