asp表格导出EXCEL方法修改_第1页
asp表格导出EXCEL方法修改_第2页
asp表格导出EXCEL方法修改_第3页
已阅读5页，还剩6页未读， 继续免费阅读

下载本文档

版权说明：本文档由用户提供并上传，收益归属内容提供方，若内容存在侵权，请进行举报或认领

文档简介

1、asp中表格导出到EXCEL勺方法、第一种表格导出到 Word代码、导出到 EXCEL弋码<input type="hidden" name="out_word" onclick="vbscript:buildDoc"value=" 导出到 word" class="notPrint"><input type="hidden" name="out_excel" onclick="AutomateExcel();" va

2、lue=" 导出 到 excel" class="notPrint"><title> 浏览器表格导出到 Excel代码</title><input type="button" name="out_word" onclick="vbscript:buildDoc"value=" 导出到 word" class="notPrint"><input type="button" name=&quo

3、t;out_word1" onclick="javascript:AutomateExcel() "value=" 导出到 excel" class="notPrint"><table id="data" width="200" border="1"><tr><td>11</td><td>11</td></tr><tr><td>22</td>

4、<td>22</td></tr><tr><td>33</td><td>33</td></tr><tr><td>44</td><td>44</td></tr></table><SCRIPT LANGUAGE="JavaScript"><!-function AutomateExcel()/ Start Excel and get Application object.v

5、ar 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 (

6、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>导出到 Word 代码<script language="vbscript">Sub buildDocset table = row = column = table.rows(1).cells.lengthSet objWordDoc = CreateObject("Wo

7、rd.Document")&#39;objWordDoc.Application.Documents.Add theTemplate, False objWordDoc.Application.Visible=TrueDim theArray(20,10000)for i=0 to row-1for j=0 to column-1theArray(j+1,i+1) = table.rows(i).cells(j).innerTEXTnextnextobjWordDoc.Application.ActiveDocument.Paragraphs.Add.Range.Insert

8、Before(" 合查询结果集 ") / 显示表格标题 objWordDoc.Application.ActiveDocument.Paragraphs.Add.Range.InsertBefore("") Set rngPara = objWordDoc.Application.ActiveDocument.Paragraphs(1).Range With rngPara.Bold = True / 将标题设为粗体 .ParagraphFormat.Alignment = 1 /将标题居中.Font.Name = " 隶书 " /

9、设定标题字体 .Font.Size = 18 /设定标题字体大小End WithSet rngCurrent = objWordDoc.Application.ActiveDocument.Paragraphs(3).RangeSet tabCurrent ObjWordDoc.Application.ActiveDocument.Tables.Add(rngCurrent,row,column) for i = 1 to column objWordDoc.Application.ActiveDocument.Tables(1).Rows(1).Cells(i).Range.Inse rtA

10、fter theArray(i,1) objWordDoc.Application.ActiveDocument.Tables(1).Rows(1).Cells(i).Range.Para graphFormat.alignment=1nextFor i =1 to columnFor j = 2 to row objWordDoc.Application.ActiveDocument.Tables(1).Rows(j).Cells(i).Range.Inse rtAfter theArray(i,j) objWordDoc.Application.ActiveDocument.Tables(

11、1).Rows(j).Cells(i).Range.Para graphFormat.alignment=1NextNextEnd Sub </SCRIPT>二、第二种如何在 asp 脚本里做个按钮可以将数据导出到 excel 表里导出到方法一<input type="hidden" name="out_excel" onclick="AutomateExcel();" value="excel" class="notPrint"><title> 浏览器表格导

12、出到 Excel</title>II<input type="button" name="out_word1" onclick="java script:AutomateExcel() value=" 导出到 excel" class="notPrint"><table id="data" width="200" border="1"><tr><td>11</td><t

13、d>11</td></tr><tr><td>22</td><td>22</td></tr><tr><td>33</td><td>33</td></tr><tr><td>44 </td><td>44</td></tr></table><SCRIPT LANGUAGE="JavaScript"><!-fun

14、ction 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;/ A

15、dd 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>方法二<% LANGUAGE="VBSCRIPT" %><%option explicit%><%><!-#include

16、file="conn.asp"-><HTML><HEAD><meta http-equiv="Content-Type" content="text/html; charset=gb2312"><TITLE>生成 EXCEL文件 </TITLE></HEAD><body><%dim rs,sql,filename,fs,myfile,x,linkSet fs = server.CreateObject("scripting.fil

17、esystemobject")filename = "f:online.xls"if fs.FileExists(filename) then fs.DeleteFile(filename) end ifset myfile = fs.CreateTextFile(filename,true)Set rs = Server.CreateObject("ADODB.Recordset") sql = "select * from table" rs.Open sql,conn if rs.EOF and rs.BOF then

18、 else dim strLine,responsestr strLine=""For each x in rs.fieldsstrLine= strLine & & chr(9)Next myfile.writeline strLine Do while Not rs.EOF strLine="" for each x in rs.Fields strLine= strLine & x.value & chr(9) next myfile.writeline strLine rs.MoveNext loop

19、 end if rs.Close set rs = nothing conn.close set conn = nothing set myfile = nothing Set fs=Nothing %></BODY></HTML>/是从数据库里直接读出来再转到EXCEL中.要在页面上显示稍改下就成如何关闭 excel 进程dim excelappset excelapp=createobject("excel.application") excelapp.quit&#39; 必备，建议上面加上 on error resume next

20、 防止未知错误不能执行到 此行set excelapp = nothing三、强人介绍的四种方法一、使用 OWC十么是 OWGDW（是 Office Web Compent 的缩写，即 Microsoft 的 Office Web 组件，它为在 Web中绘制图形提供了灵活的同时也是最基本的机制。在一个intranet 环境中，如果可以假设客户机上存在特定的浏览器和一些功能强大的软件（如IE5 和 Office2000），那么就有能力利用 Office Web 组件提供一个交互式图形开发环境。这种模式下， 客户端工作站将在整个任务中分担很大的比重。v %Optio n ExplicitClass

21、 ExcelGenPrivate objSpreadsheetPrivate iColOffsetPrivate iRowOffsetSub Class_Initialize()Set objSpreadsheet = Server.CreateObject("OWC.Spreadsheet") iRowOffset = 2iColOffset = 2End SubSub Class_Terminate()Set objSpreadsheet = Nothing &#39;Clean upEnd SubPublic Property Let ColumnOffset

22、(iColOff)If iColOff > 0 the niColOffset = iColOffElse iColOffset = 2End IfEnd PropertyPublic Property Let RowOffset(iRowOff)If iRowOff > 0 the n iRowOffset = iRowOffElseiRowOffset = 2End IfEnd Property Sub GenerateWorksheet(objRS)&#39;Populates the Excel worksheet based on a Recordset&

23、#39;s contents &#39;Start by displaying the titlesIf objRS.EOF then Exit Sub Dim objField, iCol, iRow iCol = iColOffset iRow = iRowOffsetFor Each objField in objRS.Fields objSpreadsheet.Cells(iRow, iCol).Value = objField.Name objSpreadsheet.Columns(iCol).AutoFitColumns &#39; 设置 Excel 表里的字体 o

24、bjSpreadsheet.Cells(iRow, iCol).Font.Bold = True objSpreadsheet.Cells(iRow, iCol).Font.Italic = False objSpreadsheet.Cells(iRow, iCol).Font.Size = 10 objSpreadsheet.Cells(iRow, iCol).Halignment = 2 &#39;居中iCol = iCol + 1Next &#39;objField &#39;Display all of the data Do While Not objRS.E

25、OF iRow = iRow + 1 iCol = iColOffsetFor Each objField in objRS.Fields If IsNull(objField.Value) then objSpreadsheet.Cells(iRow, iCol).Value = "" Else objSpreadsheet.Cells(iRow, iCol).Value = objField.Value objSpreadsheet.Columns(iCol).AutoFitColumns objSpreadsheet.Cells(iRow, iCol).Font.Bo

26、ld = False objSpreadsheet.Cells(iRow, iCol).Font.Italic = False objSpreadsheet.Cells(iRow, iCol).Font.Size = 10End If iCol = iCol + 1Next &#39;objFieldobjRS.MoveNextLoopEnd Sub Fu nction SaveWorksheet(strFileName)&#39;Save the worksheet to a specified file nameOn Error Resume NextCall objSpr

27、eadsheet.ActiveSheet.Export(strFileName, 0)SaveWorksheet = (Err.Number = 0)End FunctionEnd ClassDim objRSSet objRS = Server.CreateObject("ADODB.Recordset")objRS.Ope n "SELECT * FROM xxxx", "rovider=SQLOLEDB.1In fo=True;User ID=xxxx曲 assword=xxxx;l nitial Catalog=xxxx;Data so

28、urce=xxxx;"Dim SaveNameSaveName = Request.Cookies("save name")(" name")Dim objExcelDim ExcelPathExcelPath = "Excel" & SaveName & ".xls"Set objExcel = New ExcelGe nobjExcel.RowOffset = 1objExcel.Colu mnOffset = 1objExcel.Ge nerateWorksheet(objRS)bg

29、color= &#39;gai nsboro &#39;>下载v /a >"If objExcel.SaveWorksheet(Server.MapPath(ExcelPath) then &#39;Resp on se.Write" v html > v bodytext=&#39;#000000&#39; >已保存为 Excel 文件.v a href= &#39;" & server.URLE ncode(ExcelPath) & "& #39; Els

30、eResp on se.Write " 在保存过程中有错误 !"End IfSet objExcel = Nothi ngobjRS.CloseSet objRS = Noth ing%>二、用 Excel 的 Application 组件在客户端导出到 Excel 或 Word 注意：两个函数中的“ data “是网页中要导出的 table 的 id导出到导出到v in put type="hidde n" n ame="out_word" on click="vbscript:buildDoc" valu

31、e=" word" class="notPrint" >v input type="hidden" name="out_excel" onclick="AutomateExcel();" value=" excel" class="notPrint" >导出到 Excel 代码v SCRIPT LANGUAGE="javascript" >v !-function AutomateExcel()/ Start Exce

32、l 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 ce

33、ll.for (i=0;iv hang;i+)for (j=0;jv lie;j+)oSheet.Cells(i+1,j+1).value = table.rows(i).cells(j).innerText;oXL.Visible = true;oXL.UserControl = true;/- >v /SCRIPT >导出到 Word 代码v script language="vbscript" >Sub buildDoc set table = row = column = table.rows(1).cells.lengthSet objWordD

34、oc = CreateObject("Word.Document") objWordDoc.Application.Documents.Add theTemplate, False objWordDoc.Application.Visible=TrueDim theArray(20,10000)for i=0 to row-1for j=0 to column-1 theArray(j+1,i+1) = table.rows(i).cells(j).innerTEXT next next综合查objWordDoc.Application.ActiveDocument.Par

35、agraphs.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 WithSet rngCur

人人文库> 全部分类> 教育资料 > 课件下载

温馨提示

  • 1. 本站所有资源如无特殊说明，都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
  • 2. 本站的文档不包含任何第三方提供的附件图纸等，如果需要附件，请联系上传者。文件的所有权益归上传用户所有。
  • 3. 本站RAR压缩包中若带图纸，网页内容里面会有图纸预览，若没有图纸预览就没有图纸。
  • 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
  • 5. 人人文库网仅提供信息存储空间，仅对用户上传内容的表现方式做保护处理，对用户上传分享的文档内容本身不做任何修改或编辑，并不能对任何下载内容负责。
  • 6. 下载文件中如有侵权或不适当内容，请与我们联系，我们立即纠正。
  • 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。

最新文档

评论

0/150

提交评论