asp表格導(dǎo)出EXCEL方法修改_第1頁(yè)
asp表格導(dǎo)出EXCEL方法修改_第2頁(yè)
asp表格導(dǎo)出EXCEL方法修改_第3頁(yè)
已閱讀5頁(yè)，還剩6頁(yè)未讀， 繼續(xù)免費(fèi)閱讀

下載本文檔

版權(quán)說(shuō)明：本文檔由用戶提供并上傳，收益歸屬內(nèi)容提供方，若內(nèi)容存在侵權(quán)，請(qǐng)進(jìn)行舉報(bào)或認(rèn)領(lǐng)

文檔簡(jiǎn)介

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

2、lue=" 導(dǎo)出 到 excel" class="notPrint"><title> 瀏覽器表格導(dǎo)出到 Excel代碼</title><input type="button" name="out_word" onclick="vbscript:buildDoc"value=" 導(dǎo)出到 word" class="notPrint"><input type="button" name=&quo

3、t;out_word1" onclick="javascript:AutomateExcel() "value=" 導(dǎo)出到 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>導(dǎo)出到 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(" 合查詢結(jié)果集 ") / 顯示表格標(biāo)題 objWordDoc.Application.ActiveDocument.Paragraphs.Add.Range.InsertBefore("") Set rngPara = objWordDoc.Application.ActiveDocument.Paragraphs(1).Range With rngPara.Bold = True / 將標(biāo)題設(shè)為粗體 .ParagraphFormat.Alignment = 1 /將標(biāo)題居中.Font.Name = " 隸書 " /

9、設(shè)定標(biāo)題字體 .Font.Size = 18 /設(shè)定標(biāo)題字體大小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 腳本里做個(gè)按鈕可以將數(shù)據(jù)導(dǎo)出到 excel 表里導(dǎo)出到方法一<input type="hidden" name="out_excel" onclick="AutomateExcel();" value="excel" class="notPrint"><title> 瀏覽器表格導(dǎo)

12、出到 Excel</title>II<input type="button" name="out_word1" onclick="java script:AutomateExcel() value=" 導(dǎo)出到 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>/是從數(shù)據(jù)庫(kù)里直接讀出來(lái)再轉(zhuǎn)到EXCEL中.要在頁(yè)面上顯示稍改下就成如何關(guān)閉 excel 進(jìn)程dim excelappset excelapp=createobject("excel.application") excelapp.quit&#39; 必備，建議上面加上 on error resume next

20、 防止未知錯(cuò)誤不能執(zhí)行到 此行set excelapp = nothing三、強(qiáng)人介紹的四種方法一、使用 OWC十么是 OWGDW（是 Office Web Compent 的縮寫，即 Microsoft 的 Office Web 組件，它為在 Web中繪制圖形提供了靈活的同時(shí)也是最基本的機(jī)制。在一個(gè)intranet 環(huán)境中，如果可以假設(shè)客戶機(jī)上存在特定的瀏覽器和一些功能強(qiáng)大的軟件（如IE5 和 Office2000），那么就有能力利用 Office Web 組件提供一個(gè)交互式圖形開發(fā)環(huán)境。這種模式下， 客戶端工作站將在整個(gè)任務(wù)中分擔(dān)很大的比重。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; 設(shè)置 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 " 在保存過(guò)程中有錯(cuò)誤 !"End IfSet objExcel = Nothi ngobjRS.CloseSet objRS = Noth ing%>二、用 Excel 的 Application 組件在客戶端導(dǎo)出到 Excel 或 Word 注意：兩個(gè)函數(shù)中的“ data “是網(wǎng)頁(yè)中要導(dǎo)出的 table 的 id導(dǎo)出到導(dǎo)出到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" >導(dǎo)出到 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 >導(dǎo)出到 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(" 詢結(jié)果集 ") / 顯示表格標(biāo)題objWordDoc.Application.ActiveDocument.Paragraphs.Add.Range.InsertBefore("") Set rngPara = objWordDoc.Application.ActiveDocument.Paragraphs(1).Range With rngPara.Bold = True /將標(biāo)題設(shè)為粗體.ParagraphFormat.Alignment = 1 /將標(biāo)題居中.Font.Name = " 隸書 " / 設(shè)定標(biāo)題字體 .Font.Size = 18 /設(shè)定標(biāo)題字體大小End WithSet rngCur

溫馨提示

  • 1. 本站所有資源如無(wú)特殊說(shuō)明，都需要本地電腦安裝OFFICE2007和PDF閱讀器。圖紙軟件為CAD,CAXA,PROE,UG,SolidWorks等.壓縮文件請(qǐng)下載最新的WinRAR軟件解壓。
  • 2. 本站的文檔不包含任何第三方提供的附件圖紙等，如果需要附件，請(qǐng)聯(lián)系上傳者。文件的所有權(quán)益歸上傳用戶所有。
  • 3. 本站RAR壓縮包中若帶圖紙，網(wǎng)頁(yè)內(nèi)容里面會(huì)有圖紙預(yù)覽，若沒有圖紙預(yù)覽就沒有圖紙。
  • 4. 未經(jīng)權(quán)益所有人同意不得將文件中的內(nèi)容挪作商業(yè)或盈利用途。
  • 5. 人人文庫(kù)網(wǎng)僅提供信息存儲(chǔ)空間，僅對(duì)用戶上傳內(nèi)容的表現(xiàn)方式做保護(hù)處理，對(duì)用戶上傳分享的文檔內(nèi)容本身不做任何修改或編輯，并不能對(duì)任何下載內(nèi)容負(fù)責(zé)。
  • 6. 下載文件中如有侵權(quán)或不適當(dāng)內(nèi)容，請(qǐng)與我們聯(lián)系，我們立即糾正。
  • 7. 本站不保證下載資源的準(zhǔn)確性、安全性和完整性, 同時(shí)也不承擔(dān)用戶因使用這些下載資源對(duì)自己和他人造成任何形式的傷害或損失。

最新文檔

評(píng)論

0/150

提交評(píng)論