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
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
