excel| Printing in the development of ASP Web site, sometimes need to call the MSSQL database in the client data to print, if the amount of data is small, you can use FileSystemObject in the client to generate file objects to print, here no longer repeat. If you need to invoke large amounts of data, you can instantiate the Rds.dataspace (remote Data Service) object in client script and use the remote provider to access the MSSQL database via the ASP Web site (set to only pass the RDS Default Handler or custom business objects can access the database, ensure the security of the database, and then instantiate the Excel.Application object on the client, write the data in the dataset to Excel, and save or print. The code is as follows:
<meta content= "text/html; charset=gb2312 "http-equiv=content-type>
<title> Client spreadsheet printing </title>
<body bgcolor=skyblue topmargin=5 leftmargin= "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"
Client spreadsheet printing
</font>
</td>
</tr>
</table>
</div>
<form name= "MyForm"
<div align=left>
<input type= "button" value= "Excel", "name=", "language=" VBScript "style=" HEIGHT:32PX; width:90px ">
</div>
</form>
</body>
<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 for Web server IP address
strcn= "Provider=ms remote;remote server=http://192.168.0.1;handler=msdfmap.handler;data source=pubsdatabase;" ' 192.168.0.1 for Web server IP address
Strsql= "GetAllJobs"
Set rs = df. Query (STRCN, strSQL)
Set xlapp = CreateObject ("EXCEL. Application ") ' Note is not: Server.CreateObject (" EXCEL. ") Application ")
Set xlbook = XlApp.Workbooks.Add
Set XlSheet1 = xlbook.worksheets (1)
Xlsheet1.cells (1,1). Value = "Job table"
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 as 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>
You can also instantiate the RDS DataControl by simply modifying the code in the above sections:
Set rds = CreateObject ("RDS.") DataSpace ")
Set df = rds. CreateObject ("RDSServer.DataFactory", "http://192.168.0.1") ' 192.168.0.1 for Web server IP address
strcn= "Provider=ms remote;remote server=http://192.168.0.1;handler=msdfmap.handler;data source=pubsdatabase;" ' 192.168.0.1 for Web server IP address
Strsql= "GetAllJobs"
Set rs = df. Query (STRCN, strSQL)
Modified to:
Set DC = CreateObject ("RDS.") DataControl ")
dc. ExecuteOptions = 1 ' set to execute synchronously to simplify the next step code
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
Modify File Msdfmap.ini (if in Win98,c:\windows\msdfmap.ini; if in Win2000,d:\winnt\msdfmap.ini; if in 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 "
Open registry Hkey_local_machine\system\currentcontrolset\ Services\w3svc\parameters\adclaunch if no RDSServer.DataFactory, please Add. This example uses RDS Default handler to access the database, modifying the registry without accessing the database via RDS handler Hkey_local_machine\software\microsoft\datafactory\ Handlerinfo sets Handlerrequired=1 to handlerrequired = 0. Note that if you do not access the database through RDS handler or custom business objects, it poses a security risk to the database, so the authors strongly recommend a way to access the database only through RDS handler or custom business objects.
The following VB to write a custom business object, the code is as follows:
' Write ActiveX DLL, name: Rsget.dll, containing class Rsreturn, method 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 & "" "
E