📄 用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=40280 -->
<HTML lang=zh-cn><HEAD><TITLE>NB联盟</TITLE>
<META content="text/html; charset=gb2312" http-equiv=Content-Type><LINK
href="NB联盟28.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联盟28.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>
<BR>
<P>作者：tonny 出自：伟网动力 <A
href="http://www.weiw.com/">http://www.weiw.com/</A><BR>如需转载，请写明出处。</P>
<P>作者序：这类知识点，在网上有很多，在此本人只是做一个总结。</P>
<P>一、用OWC<BR>什么是OWC？ <BR> OWC是Office Web
Compent的缩写，即Microsoft的Office Web组件，它为在Web中绘制图形提供</P>
<P>了灵活的同时也是最基本的机制。在一个intranet环境中，如果可以假设客户机上存在特定的浏览器和一</P>
<P>些功能强大的软件（如IE5和Office 2000），那么就有能力利用Office Web组件提供一个交互式图形开</P>
<P>发环境。这种模式下，客户端工作站将在整个任务中分担很大的比重。
<BR>有关的详细介绍也可在本站找到。<BR><%Option Explicit<BR>Class
ExcelGen<BR>Private objSpreadsheet</P>
<P>Private iColOffset</P>
<P>Private iRowOffset<BR>Sub Class_Initialize()<BR>Set
objSpreadsheet =
Server.CreateObject("OWC.Spreadsheet")<BR>iRowOffset =
2<BR>iColOffset = 2<BR>End Sub</P>
<P>Sub Class_Terminate()<BR>Set objSpreadsheet = Nothing 'Clean
up<BR>End Sub</P>
<P>Public Property Let ColumnOffset(iColOff)<BR>If iColOff > 0
then<BR>iColOffset = iColOff<BR>Else<BR>iColOffset = 2<BR>End
If<BR>End Property</P>
<P>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)</P>
<P>'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</P>
<P>Dim objRS<BR>Set objRS =
Server.CreateObject("ADODB.Recordset")<BR>objRS.Open "SELECT * FROM
xxxx", "Provider=SQLOLEDB.1;Persist Security </P>
<P>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文件. </P>
<P><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>%></P>
<P><BR>二、用Excel的Application组件在客户端导出到Excel或Word<BR>注意：两个函数中的“data“是网页中要导出的table的
id<BR><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">
</P>
<P>导出到Excel代码<BR><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;</P>
<P>var lie = table.rows(0).cells.length; </P>
<P>// 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>}</P>
<P>}<BR>oXL.Visible = true;<BR>oXL.UserControl =
true;<BR>}<BR>//--><BR></SCRIPT><BR>导出到Word代码<BR><script
language="vbscript"><BR>Sub buildDoc<BR>set table =
document.all.data<BR>row = table.rows.length<BR>column =
table.rows(1).cells.length</P>
<P>Set objWordDoc = CreateObject("Word.Document")</P>
<P>objWordDoc.Application.Documents.Add theTemplate,
False<BR>objWordDoc.Application.Visible=True</P>
<P>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("综合查询结果集")
//显示表格标题</P>
<P>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)</P>
<P>for i = 1 to column </P>
<P>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</P>
<P>End Sub<BR></SCRIPT> </P>
<P><BR>三、直接在IE中打开，再存为EXCEL文件。<BR>把读出的数据用<table>格式，在网页中显示出来，同时，加上下一句即可把EXCEL表在客客户端显示。<BR><%response.ContentType
="application/vnd.ms-excel"%><BR>注意：显示的页面中，只把<table>输出，最好不要输出其他表格以外的信息。</P>
<P><BR>四、导出以半角逗号隔开的csv<BR>用fso方法生成文本文件的方法，生成一个扩展名为csv文件。此文件，一行即为数据表的一行。生成数据</P>
<P>表字段用半角逗号隔开。<BR>有关fso生成文本文件的方法，在此就不做介绍了。相关文档，可本站找到。</P>
<P>CSV文件介绍 （逗号分隔文件） <BR>选择该项系统将创建一个可供下载的CSV 文件；
CSV是最通用的一种文件格式，它可以非常容易地被导入各种PC表格及数据库中。<BR>请注意即使选择表格作为输出格式，仍然可以将结果下载CSV文件。在表格输出屏幕的底部，显示有
"CSV 文件"选项，点击它即可下载该文件。<BR>如果您把浏览器配置为将您的电子表格软件与文本（TXT）/逗号分隔文件（CSV）
相关联，当您下载该文件时，该文件将自动打开。下载下来后，如果本地已安装EXCEL，点击此文件，即可自动用EXCEL软件打开此文件。<BR></P><BR></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 + -