ASP remote users can print Excel files

Source: Internet
Author: User

ASP remote users can print Excel files

 

When developing an ASP Website, you sometimes need to call the MSSQL database data on the client for printing. If the called data volume is small, you can use FileSystemObject on the client to generate file objects, I will not go into details here. If you need to call a large amount of data, you can instantiate RDS in the client script. dataSpace (Remote Data Service) object, and use a remote provider to access the MSSQL database through the ASP Website (set to access the database only through RDS default handler or custom Business Objects, to ensure database security), and then instantiate excel on the client. application object. Write the data in the dataset into Excel and save or print the data. The Code is as follows:

<HTML>

<Head>

<Meta content = "text/html; charset = gb2312" http-equiv = Content-Type>

<Title> client workbook printing </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">

Client workbook Printing

</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 is the IP address of the web server

Strcn = "provider = MS remote; Remote Server = http: // 192.168.0.1; handler = msdfmap. Handler; Data Source = pubsdatabase;" '192. 168.0.1 is the Web Server IP Address

Strsql = "getalljobs"

Set rs = DF. Query (strcn, strsql)

Set xlapp = Createobject ("Excel. application") 'Note: 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 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>

You can also instantiate RDS datacontrol by modifying the above Code:

Set RDS = Createobject ("RDS. DataSpace ")

Set df = RDS. Createobject ("rdsserver. DataFactory", "http: // 192.168.0.1") '192. 168.0.1 is the IP address of the web server

Strcn = "provider = MS remote; Remote Server = http: // 192.168.0.1; handler = msdfmap. Handler; Data Source = pubsdatabase;" '192. 168.0.1 is the Web Server IP Address

Strsql = "getalljobs"

Set rs = DF. Query (strcn, strsql)

To:

Set Dc = Createobject ("RDS. datacontrol ")

DC. executeoptions = 1' is set to synchronous execution, which can simplify the next step of code

DC. fetchoptions = 1

With DC

. Server = "http: // 192.168.0.1"

. Handler = "msdfmap. Handler"

. Connect = "Data Source = pubsdatabase ;"

. SQL = "getalljobs"

. Refresh

End

Set rs = Dc. recordset

Modify the msdfmap file. INI (if it is in Win98, C:/Windows/msdfmap. INI; if it is in Win2000, D:/winnt/msdfmap. ini. 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 the Registry HKEY_LOCAL_MACHINE/system/CurrentControlSet/services/w3svc/parameters/adclaunch. If no rdsserver. DataFactory exists, add it. In this example, use RDS default handler to access the database. If you do not access the database through RDS handler, modify the Registry HKEY_LOCAL_MACHINE/software/Microsoft/DataFactory/handlerinfo and set handlerrequired = 1 to handlerrequired = 0. Please note that if you do not access the database through RDS handler or custom business objects, it will bring security risks to the database, therefore, the author strongly recommends that you access the database only through RDS handler or custom business objects.

The following code uses VB to compile a custom business object:

'Compile ActiveX DLL, name: rsget. dll, including rsreturn class, 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 &"'"

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, adshorttext

Set returnrs = rs

Exit Function

Ehgetrecordset:

Err. Raise err. Number, Err. Source, Err. Description

End Function

Set rsget. copy DLL to C:/Windows or D:/WINNT, start/run, enter regsvr32.exe C:/Windows/rsget.dllor regsvr32.exe D:/winnt/rsget. DLL, click OK to register as a web server component, and add rsget in the Registry HKEY_LOCAL_MACHINE/system/CurrentControlSet/services/w3svc/parameters/adclaunch. rsreturn.

If you use a custom business object, modify the above ASP file code:

Set RDS = Createobject ("RDS. DataSpace ")

Set df = RDS. Createobject ("rdsserver. DataFactory", "http: // 192.168.0.1") '192. 168.0.1 is the IP address of the web server

Strcn = "provider = MS remote; Remote Server = http: // 192.168.0.1; handler = msdfmap. Handler; Data Source = pubsdatabase;" '192. 168.0.1 is the Web Server IP Address

Strsql = "getalljobs"

Set rs = DF. Query (strcn, strsql)

Changed:

Set RDS = Createobject ("RDS. DataSpace ")

Set df = RDS. Createobject ("rsget. rsreturn", "http: // 192.168.0.1 ")

Set rs = DF. returnrs ("pubsdatabase", "getpubsbyid ('2', '10 ')")

In addition, the following configuration is required on the browser:

Open Control Panel-> Internet Options-> Security-> Custom Level-> initialize and Run ActiveX controls that are not marked as secure-> enable

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.