ASP網(wǎng)站遠(yuǎn)程客戶完成EXCEL打印技巧
發(fā)表時間：2023-08-13 來源：明輝站整理相關(guān)軟件相關(guān)文章人氣：
[摘要]在進(jìn)行ASP網(wǎng)站開發(fā)時，有時需在客戶端調(diào)用MSSQL數(shù)據(jù)庫的數(shù)據(jù)進(jìn)行打印，若調(diào)用數(shù)據(jù)量小，可以通過在客戶端運(yùn)用FileSystemObject生成文件對象的方法實(shí)現(xiàn)打印，這里不再贅述。若需調(diào)用大量數(shù)...
在進(jìn)行ASP網(wǎng)站開發(fā)時，有時需在客戶端調(diào)用MSSQL數(shù)據(jù)庫的數(shù)據(jù)進(jìn)行打印，若調(diào)用數(shù)據(jù)量小，可以通過在客戶端運(yùn)用FileSystemObject生成文件對象的方法實(shí)現(xiàn)打印，這里不再贅述。若需調(diào)用大量數(shù)據(jù)，可在客戶端腳本中實(shí)例化RDS.DataSpace（Remote Data Service）對象,并采用遠(yuǎn)程提供程序通過ASP網(wǎng)站訪問MSSQL數(shù)據(jù)庫（設(shè)置成只能通過RDS Default Handler或自定義商業(yè)對象才能訪問數(shù)據(jù)庫，可保證數(shù)據(jù)庫的安全），再在客戶端實(shí)例化EXCEL.APPLICATION對象，把數(shù)據(jù)集中的數(shù)據(jù)寫入EXCEL中，再進(jìn)行保存或打印。代碼如下：
<html>
<head>
<META content="text/html; charset=gb2312" http-equiv=Content-Type>
<title>客戶端電子表格打印</title>
</head>
<body bgColor=skyblue topMargin=5 leftMargin="20" oncontextmenu="return false" rightMargin=0 bottomMargin="0">
<div align="center"><center>
<table border="1" bgcolor="#ffe4b5" style="HEIGHT: 1px; TOP: 0px" bordercolor="#0000ff">
<tr>
<td align="middle" bgcolor="#ffffff" bordercolor="#000080">
<font color="#000080" size="3">
客戶端電子表格打印
</font>
</td>
</tr>
</table>
</div>
<form name="myform">
<DIV align=left>
<input type="button" value="Excel Report" name="report" language="vbscript" onclick="fun_excel()" style="HEIGHT: 32px; WIDTH: 90px">
</div>
</form>
</body>
</html>
<script language="vbscript">
sub fun_excel()
Dim rds,rs,df
dim strCn,strSQL,StrRs
Dim xlApp, xlBook, xlSheet1
set rds = CreateObject("RDS.DataSpace")
Set df = rds.CreateObject("RDSServer.DataFactory","http://192.168.0.1") '192.168.0.1 為WEB服務(wù)器IP地址
strcn="provider=ms remote;remote server=http://192.168.0.1;handler=msdfmap.handler;data source=pubsdatabase;" '192.168.0.1 為WEB服務(wù)器IP地址
strsql= "getalljobs"
Set rs = df.Query(strCn, strSQL)
Set xlApp = CreateObject("EXCEL.APPLICATION") '注意不是：Server.CreateObject("EXCEL.APPLICATION")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet1 = xlBook.Worksheets(1)
xlSheet1.cells(1,1).value ="職務(wù)表"
xlSheet1.range("A1:D1").merge
xlSheet1.cells(2,1).value = "job_id"
xlSheet1.cells(2,2).value = "job_desc"
xlSheet1.cells(2,3).value = "max_lvl"
xlSheet1.cells(2,4).value = "min_lvl"
cnt =3
do while not rs.eof
xlSheet1.cells(cnt,1).value = rs("job_id")
xlSheet1.cells(cnt,2).value = rs("job_desc")
xlSheet1.cells(cnt,3).value = rs("max_lvl")
xlSheet1.cells(cnt,4).value = rs("min_lvl")
rs.movenext
cnt = cint(cnt) + 1
loop
xlSheet1.Application.Visible = True
end sub
</script>
也可以實(shí)例化RDS DataControl，只需把以上部分代碼進(jìn)行修改：
set rds = CreateObject("RDS.DataSpace")
Set df = rds.CreateObject("RDSServer.DataFactory","http://192.168.0.1") '192.168.0.1 為WEB服務(wù)器IP地址
strcn="provider=ms remote;remote server=http://192.168.0.1;handler=msdfmap.handler;data source=pubsdatabase;" '192.168.0.1 為WEB服務(wù)器IP地址
strsql= "getalljobs"
Set rs = df.Query(strCn, strSQL)
修改為：
set DC = createobject("RDS.DataControl")
dc.ExecuteOptions =1 '設(shè)置成同步執(zhí)行，可以簡化下步代碼
dc.FetchOptions = 1
With dc
.Server = "http://192.168.0.1"
.Handler = "MSDFMAP.Handler"
.Connect = "Data Source=pubsdatabase;"
.Sql = "getalljobs"
.Refresh
End With
set rs= dc.Recordset
修改文件MSDFMAP.INI（若在WIN98，C：\windows\msdfmap.ini；若在WIN2000,D:\winnt\msdfmap.ini；若在WIN2000 SERVER，D：\winnts\msdfmap.ini）。
[sql getalljobs]
Sql="SELECT * FROM jobs"
[connect pubsDatabase]
Access=Readonly
Connect="provider=sqloledb;data source=sql server;initial catalog=pubs;UID=userid;PWD=password"
打開注冊表HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\ Services\W3SVC\Parameters\ADCLaunch 若無 RDSServer.Datafactory，請?zhí)砑�。本例使用RDS Default Handler訪問數(shù)據(jù)庫，若不通過RDS Handler訪問數(shù)據(jù)庫，修改注冊表HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\DataFactory\ HandlerInfo 將HandlerRequired=1 設(shè)置成HandlerRequired =0。請注意，若不通過RDS Handler或自定義商業(yè)對象訪問數(shù)據(jù)庫，將對數(shù)據(jù)庫帶來安全隱患，所以作者極力推薦采用只能通過RDS Handler或自定義商業(yè)對象才能訪問數(shù)據(jù)庫的方式。
下面用VB編寫一個自定義商業(yè)對象，代碼如下：
'編寫ActiveX DLL，名稱：rsget.dll,包含類rsreturn，方法returnrs
Public Function ReturnRs(strDB As Variant, strSQL As Variant) As ADODB.Recordset
'Returns an ADODB recordset.
On Error GoTo ehGetRecordset
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Select Case strDB
Case "ydjjspdatabase"
strDB = "ydjjsp"
Case "pubsdatabase"
strDB = "pubs"
End Select
If strSQL = "getallbuy" Then
strSQL = "select * from buyuser"
GoTo nextstep
End If
If Left(strSQL, InStr(strSQL, "(") - 1) = "getpubsbyid" Then
If InStr(strSQL, ",") <= 0 Then
Dim str As String
str = Mid(strSQL, InStr(strSQL, "(") + 2, InStr(strSQL, ")") - InStr(strSQL, "(") - 3)
strSQL = "select * from jobs where job_id='" & str & "'"
Else
Dim strstart, strend As String
strstart = Mid(strSQL, InStr(strSQL, "(") + 2, InStr(strSQL, ",") - InStr(strSQL, "(") - 3)
strend = Mid(strSQL, InStr(strSQL, ",") + 2, InStr(strSQL, ")") - InStr(strSQL, ",") - 3)
strSQL = "select * from jobs where job_id>='" & strstart & "' and job_id<='" & strend & "'"
End If
End If
nextstep:
Dim strConnect As String
strConnect = "Provider=SQLOLEDB;Server=ddk;uid=ydj;pwd=ydj; Database=" & strDB & ";"
cn.Open strConnect
rs.CursorLocation = adUseClient
rs.Open strSQL, cn, adOpenStatic, adLockOptimistic, adCmdText
Set ReturnRs = rs
Exit Function
ehGetRecordset:
Err.Raise Err.Number, Err.Source, Err.Description
End Function
把rsget.dll復(fù)制到C:\WINDOWS或D:\WINNT,開始\運(yùn)行，輸入Regsvr32.exe c:\windows\rsget.dll或Regsvr32.exe d:\winnt\rsget.dll，按確定按鈕，注冊成WEB服務(wù)器組件，并在注冊表HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\ Services\W3SVC\Parameters\ADCLaunch 添加rsget.rsreturn。
若使用自定義商業(yè)對象，修改上面的ASP文件代碼：
set rds = CreateObject("RDS.DataSpace")
Set df = rds.CreateObject("RDSServer.DataFactory","http://192.168.0.1") '192.168.0.1 為WEB服務(wù)器IP地址
strcn="provider=ms remote;remote server=http://192.168.0.1;handler=msdfmap.handler;data source=pubsdatabase;" '192.168.0.1 為WEB服務(wù)器IP地址
strsql= "getalljobs"
Set rs = df.Query(strCn, strSQL)
改為：
set rds = CreateObject("RDS.DataSpace")
Set df = rds.CreateObject("rsget.rsreturn","http://192.168.0.1")
set rs=df.returnrs("pubsdatabase","getpubsbyid('2','10')")
另外在瀏覽器端需做如下配置：
打開控制面板->INTERNET選項(xiàng)->安全性->自定義級別-> 對沒有標(biāo)記為安全的ActiveX控件進(jìn)行初始化和腳本運(yùn)行->開啟
作者姓名：游大軍
聯(lián)系地址：遼寧省凌源鋼鐵集團(tuán)有限責(zé)任公司生產(chǎn)安全部
郵編：122504
email地址：ydj1922@sohu.com
