📄 asp导出excel数据的四种方法 .htm
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<!-- saved from url=(0055)http://www.51base.com/article/view_article.asp?id=25949 -->
<HTML lang=zh-cn><HEAD><TITLE>NB联盟</TITLE>
<META content="text/html; charset=gb2312" http-equiv=Content-Type><LINK
href="NB联盟8.files/style.css" rel=stylesheet>
<META content="MSHTML 5.00.2614.3500" name=GENERATOR></HEAD>
<BODY><!-- start page body -->
<TABLE align=center border=0 cellPadding=0 cellSpacing=0 class=td id=position
width=773>
<TBODY>
<TR>
<TD class=shadow colSpan=2 height=20 width=262> <A
href="http://www.51base.com/"><IMG border=0
src="NB联盟8.files/logo1.gif"></A> </TD>
<TD align=right width=505>
<P align=left></P></TD></TR></TBODY></TABLE>
<TABLE align=center border=0 cellPadding=0 cellSpacing=0 id=main width=770>
<TBODY>
<TR>
<TD class=tdlbr vAlign=top>
<TABLE align=center border=0 cellPadding=0 cellSpacing=0 id=welcome
style="TABLE-LAYOUT: fixed" width="98%">
<TBODY>
<TR>
<TD style="TABLE-LAYOUT: fixed; WORD-BREAK: break-all"
vAlign=top><FONT color=red><B>文章标题</B></FONT><FONT color=red><B>:
ASP导出Excel数据的四种方法 </B></FONT>
<HR noShade SIZE=1>
<B>一、使用OWC</B><BR><BR> 什么是OWC？<BR><BR> OWC是Office Web
Compent的缩写，即Microsoft的Office
Web组件，它为在Web中绘制图形提供了灵活的同时也是最基本的机制。在一个intranet环境中，如果可以假设客户机上存在特定的浏览器和一些功能强大的软件（如IE5和Office
2000），那么就有能力利用Office
Web组件提供一个交互式图形开发环境。这种模式下，客户端工作站将在整个任务中分担很大的比重。<BR><BR><BR>
<TABLE bgColor=#fcdec2 width="100%">
<TBODY>
<TR>
<TD>＜%Option Explicit<BR>Class ExcelGen<BR>Private
objSpreadsheet<BR>Private iColOffset<BR><BR>Private
iRowOffset<BR>Sub Class_Initialize()<BR>Set objSpreadsheet =
Server.CreateObject("OWC.Spreadsheet")<BR>iRowOffset =
2<BR>iColOffset = 2<BR>End Sub<BR><BR>Sub
Class_Terminate()<BR>Set objSpreadsheet = Nothing 'Clean
up<BR>End Sub<BR><BR>Public Property Let
ColumnOffset(iColOff)<BR>If iColOff ＞ 0 then<BR>iColOffset =
iColOff<BR>Else<BR>iColOffset = 2<BR>End If<BR>End
Property<BR><BR>Public Property Let RowOffset(iRowOff)<BR>If
iRowOff ＞ 0 then<BR>iRowOffset = iRowOff<BR>Else<BR>iRowOffset
= 2<BR>End If<BR>End Property Sub
GenerateWorksheet(objRS)<BR>'Populates the Excel worksheet
based on a Recordset's contents<BR>'Start by displaying the
titles<BR>If objRS.EOF then Exit Sub<BR>Dim objField, iCol,
iRow<BR>iCol = iColOffset<BR>iRow = iRowOffset<BR>For Each
objField in objRS.Fields<BR>objSpreadsheet.Cells(iRow,
iCol).Value =
objField.Name<BR>objSpreadsheet.Columns(iCol).AutoFitColumns<BR>'设置Excel表里的字体<BR>objSpreadsheet.Cells(iRow,
iCol).Font.Bold = True<BR>objSpreadsheet.Cells(iRow,
iCol).Font.Italic = False<BR>objSpreadsheet.Cells(iRow,
iCol).Font.Size = 10<BR>objSpreadsheet.Cells(iRow,
iCol).Halignment = 2 '居中<BR>iCol = iCol + 1<BR>Next
'objField<BR>'Display all of the data<BR>Do While Not
objRS.EOF<BR>iRow = iRow + 1<BR>iCol = iColOffset<BR>For Each
objField in objRS.Fields<BR>If IsNull(objField.Value)
then<BR>objSpreadsheet.Cells(iRow, iCol).Value =
""<BR>Else<BR>objSpreadsheet.Cells(iRow, iCol).Value =
objField.Value<BR>objSpreadsheet.Columns(iCol).AutoFitColumns<BR>objSpreadsheet.Cells(iRow,
iCol).Font.Bold = False<BR>objSpreadsheet.Cells(iRow,
iCol).Font.Italic = False<BR>objSpreadsheet.Cells(iRow,
iCol).Font.Size = 10<BR>End If<BR>iCol = iCol + 1<BR>Next
'objField<BR>objRS.MoveNext<BR>Loop<BR>End Sub Function
SaveWorksheet(strFileName)<BR><BR>'Save the worksheet to a
specified filename<BR>On Error Resume Next<BR>Call
objSpreadsheet.ActiveSheet.Export(strFileName,
0)<BR>SaveWorksheet = (Err.Number = 0)<BR>End Function<BR>End
Class<BR><BR>Dim objRS<BR>Set objRS =
Server.CreateObject("ADODB.Recordset")<BR>objRS.Open "SELECT *
FROM xxxx", "Provider=SQLOLEDB.1;Persist
Security<BR><BR>Info=True;User ID=xxxx;Password=xxxx;Initial
Catalog=xxxx;Data source=xxxx;"<BR>Dim SaveName<BR>SaveName =
Request.Cookies("savename")("name")<BR>Dim objExcel<BR>Dim
ExcelPath<BR>ExcelPath = "Excel\" & SaveName &
".xls"<BR>Set objExcel = New ExcelGen<BR>objExcel.RowOffset =
1<BR>objExcel.ColumnOffset =
1<BR>objExcel.GenerateWorksheet(objRS)<BR>If
objExcel.SaveWorksheet(Server.MapPath(ExcelPath))
then<BR>'Response.Write "＜html＞＜body bgcolor='gainsboro'
text='#000000'＞已保存为Excel文件.<BR><BR>＜a href='" &
server.URLEncode(ExcelPath) &
"'＞下载＜/a＞"<BR>Else<BR>Response.Write "在保存过程中有错误!"<BR>End
If<BR>Set objExcel = Nothing<BR>objRS.Close<BR>Set objRS =
Nothing<BR>%＞</TD></TR></TBODY></TABLE><SPAN
class=f14> <B>二、用Excel的Application组件在客户端导出到Excel或Word</B><BR><BR> 注意：两个函数中的“data“是网页中要导出的table的
id<BR><BR>
<TABLE bgColor=#fcdec2 width="100%">
<TBODY>
<TR>
<TD>＜input type="hidden" name="out_word"
onclick="vbscript:buildDoc" value="导出到word"
class="notPrint"＞<BR>＜input type="hidden" name="out_excel"
onclick="AutomateExcel();" value="导出到excel"
class="notPrint"＞</TD></TR></TBODY></TABLE><BR> 导出到Excel代码<BR><BR>
<TABLE bgColor=#fcdec2 width="100%">
<TBODY>
<TR>
<TD>＜SCRIPT LANGUAGE="javascript"＞<BR>＜!--<BR>function
AutomateExcel()<BR>{<BR>// Start Excel and get Application
object.<BR>var oXL = new
ActiveXObject("Excel.Application");<BR>// Get a new
workbook.<BR>var oWB = oXL.Workbooks.Add();<BR>var oSheet =
oWB.ActiveSheet;<BR>var table = document.all.data;<BR>var hang
= table.rows.length;<BR><BR>var lie =
table.rows(0).cells.length;<BR><BR>// Add table headers going
cell by cell.<BR>for (i=0;i＜hang;i++)<BR>{<BR>for
(j=0;j＜lie;j++)<BR>{<BR>oSheet.Cells(i+1,j+1).value =
table.rows(i).cells(j).innerText;<BR>}<BR><BR>}<BR>oXL.Visible
= true;<BR>oXL.UserControl =
true;<BR>}<BR>//--＞<BR>＜/SCRIPT＞</TD></TR></TBODY></TABLE><BR> 导出到Word代码<BR><BR>
<TABLE bgColor=#fcdec2 width="100%">
<TBODY>
<TR>
<TD>＜script language="vbscript"＞<BR>Sub buildDoc<BR>set table
= document.all.data<BR>row = table.rows.length<BR>column =
table.rows(1).cells.length<BR><BR>Set objWordDoc =
CreateObject("Word.Document")<BR><BR>objWordDoc.Application.Documents.Add
theTemplate,
False<BR>objWordDoc.Application.Visible=True<BR><BR>Dim
theArray(20,10000)<BR>for i=0 to row-1<BR>for j=0 to
column-1<BR>theArray(j+1,i+1) =
table.rows(i).cells(j).innerTEXT<BR>next<BR>next<BR>objWordDoc.Application.ActiveDocument.Paragraphs.Add.Range.InsertBefore("综合查询结果集")
//显示表格标题<BR><BR>objWordDoc.Application.ActiveDocument.Paragraphs.Add.Range.InsertBefore("")<BR>Set
rngPara =
objWordDoc.Application.ActiveDocument.Paragraphs(1).Range<BR>With
rngPara<BR>.Bold = True
//将标题设为粗体<BR>.ParagraphFormat.Alignment = 1
//将标题居中<BR>.Font.Name = "隶书" //设定标题字体<BR>.Font.Size = 18
//设定标题字体大小<BR>End With<BR>Set rngCurrent =
objWordDoc.Application.ActiveDocument.Paragraphs(3).Range<BR>Set
tabCurrent =
ObjWordDoc.Application.ActiveDocument.Tables.Add(rngCurrent,row,column)<BR><BR>for
i = 1 to
column<BR><BR>objWordDoc.Application.ActiveDocument.Tables(1).Rows(1).Cells(i).Range.InsertAfter
theArray(i,1)<BR>objWordDoc.Application.ActiveDocument.Tables(1).Rows(1).Cells(i).Range.ParagraphFormat.alignment=1<BR>next<BR>For
i =1 to column<BR>For j = 2 to
row<BR>objWordDoc.Application.ActiveDocument.Tables(1).Rows(j).Cells(i).Range.InsertAfter
theArray(i,j)<BR>objWordDoc.Application.ActiveDocument.Tables(1).Rows(j).Cells(i).Range.ParagraphFormat.alignment=1<BR>Next<BR>Next<BR><BR>End
Sub<BR>＜/SCRIPT＞</TD></TR></TBODY></TABLE><BR> <B>三、直接在IE中打开，再存为EXCEL文件</B><BR><BR> 把读出的数据用＜table＞格式，在网页中显示出来，同时，加上下一句即可把EXCEL表在客客户端显示。<BR><BR>
<TABLE bgColor=#fcdec2 width="100%">
<TBODY>
<TR>
<TD>＜%response.ContentType
="application/vnd.ms-excel"%＞</TD></TR></TBODY></TABLE><BR> 注意：显示的页面中，只把＜table＞输出，最好不要输出其他表格以外的信息。<BR><BR> <B>四、导出以半角逗号隔开的csv</B><BR><BR> 用fso方法生成文本文件的方法，生成一个扩展名为csv文件。此文件，一行即为数据表的一行。生成数据表字段用半角逗号隔开。（有关fso生成文本文件的方法，在此就不做介绍了）<BR><BR> CSV文件介绍
（逗号分隔文件）<BR><BR> 选择该项系统将创建一个可供下载的CSV 文件；
CSV是最通用的一种文件格式，它可以非常容易地被导入各种PC表格及数据库中。<BR><BR> 请注意即使选择表格作为输出格式，仍然可以将结果下载CSV文件。在表格输出屏幕的底部，显示有
"CSV
文件"选项，点击它即可下载该文件。<BR><BR> 如果您把浏览器配置为将您的电子表格软件与文本（TXT）/逗号分隔文件（CSV）
相关联，当您下载该文件时，该文件将自动打开。下载下来后，如果本地已安装EXCEL，点击此文件，即可自动用EXCEL软件打开此文件。<BR></SPAN>
<P></P></TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE><BR>
<TABLE align=center border=0 cellPadding=2 cellSpacing=0 id=footer width=770>
<TBODY>
<TR>
<TD align=middle class=tdt>
<P align=center></P></TD></TR></TBODY></TABLE><BR></BODY></HTML>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -