ASP Web site remote client to achieve Excel printing function

Source: Internet
Author: User
Tags goto ini connect mssql query
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



Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.