Web data statistics, reports, and printing with ASP

Source: Internet
Author: User

This article describes a basic idea and implementation method for implementing Web data statistics and reports using ASP, and provides a solution for cleverly calling word printing reports.

Key words: Asp, data statistics, reports, printing, word

 

1. Introduction

With the rapid development of Internet, more and more business application systems are developed based on Web, such as office automation, E-Commerce and Management Information System (MIS. These web service application systems often involve data statistics, reports, and printing. ASP has great advantages in implementing dynamic interaction and generating dynamic pages, but it has encountered a lot of trouble in processing complex data statistics, reports, and printing. This article describes a basic idea and implementation method for implementing Web data statistics and reports using ASP, and provides a solution for cleverly calling word printing reports.

 

2. Application Instances

Assume that a company uses the Network MIS system to manage its employees, which will inevitably involve statistics of employees in various departments. For simplicity, assume that the final statistical report is as follows:

 

Company employee statistical table

Department
Total
Employee name

Marketing Department
2
Zhang San

 

Li Si

R & D department
3
Wang Wu

 

Zhao Liu

 

Liu Qi

Total employees
5

 

 

Compared with the traditional stand-alone MIS system, the following problems may occur when using ASP to achieve the above data statistics, reports, and printing in the Network MIS system:

(1) data statistics should be classified by department, and a specific list of employees of each department should be recorded.

(2) When generating a report, you need to dynamically plot several rows and several columns of tables as required, and keep the blank at appropriate places.

(3) Table printing can be simply printed on the webpage, but the effect is not good and it is not easy to control.

 

3. Implementation Methods

Web-based business applications generally adopt a three-tier structure. The client is a common web browser. The application layer of intermediate business logic is stored on the Web server, and the database interfaces on the Web server access the background database. The process of Web data statistics, reports, and printing using ASP:

Browser

Html

VBScript

Statistics

Reports

Word

Object

Web Server

Business Logic implementation

Database

Access interface

Quantity

Data

Library

Word local printing

 

 

 

 

 

(1) the client sends a data statistics request to the web server.

(2) The web server executes SQL statements, obtains statistical data from the background database, and dynamically generates reports on the page.

(3) run the script on the client and use the VBScript function Createobject to create an instance of the Document Object of the word locally, that is, create a Word document on the client and set the table attributes, then, enter the report data on the page into the local Word Table, save the document, and execute local word printing.

 

4. Implementation Process Analysis

(1) database connection

First, create a database (data. mdb). The simple structure of the employee data table (Personnel) is as follows:

Personnel: department, text; Name, text;

Create an ODBC Data Source (DSN) and use DSN to point to the ODBC database.

(2) program code analysis (only the statistical report program Tongji. asp is analyzed here)

<%

SQL = "select Department, count (department) from personnel group by Department" // collect statistics by department

Set CNN = server. Createobject ("ADODB. Connection") // connect to the database

CNN. Open "data"

Set rs = server. Createobject ("ADODB. recordset ")

Rs. cursortype = 3

Rs. locktype = 3

Rs. Open SQL, CNN

If Rs. EOF then // end if no record exists

Response. End

End if

%>

<HTML>

// The following table titles and output headers are displayed:

<P align = "center"> <B> <font size = "4"> company employee statistical table </font> </B> </P>

<Div align = "center">

<Table id = "data" border = "1" width = "606" Height = "53" cellpadding = "0" cellspacing = "0" style = "border-collapse: collapse "bordercolor =" #111111 "> // Note: The table ID is data.

<Tr>

<TD width = "93" Height = "24">

<P align = "center"> <font size = "2"> Department </font> </TD>

<TD width = "78" Height = "24">

<P align = "center"> <font size = "2"> total </font> </TD>

& Lt; TD width = "413" Height = "24" & gt;

<P align = "center"> <font size = "2"> employee name </font> </TD>

</Tr>

 

<%

HJ = 0 // set the variable, the total number of initial values is 0

// The following while loop completes statistics and reports by department in sequence

While (not Rs. EOF)

Departmenttmp = RS ("Department") // set the temporary variable and save the name of the current department.

Sqlstr = "select * from personnel where Department = '" & departmenttmp & "'" // locate all employees in the current Department

Set conn = server. Createobject ("ADODB. Connection") // connect to the database again

Conn. Open "data"

Set RSS = server. Createobject ("ADODB. recordset ")

RSS. cursortype = 3

RSS. locktype = 3

RSS. Open sqlstr, Conn

Number = 0 // set the variable. The total number of current departments is initially 0.

// The following while loop calculates the total number of current departments

While (not RSS. EOF)

RSS. movenext

Number = Number + 1

Wend

HJ = HJ + number // The total number is the sum of the number of people in each department

RSS. movefirst

Flag = 0 // set the variable. Flag is used to determine whether the current Department appears for the first time. The initial value is 0.

// The following while loop outputs the statistics of the current Department and the employee list

While (not RSS. EOF) %>

<Tr>

<% IF (flag = 0) Then %>

// If the flag is 0, this indicates that this department is the first time to appear. The table shows the Department name and total number of employees.

<TD width = "93" Height = "24"> <p align = "center"> <font size = 2> <% = departmenttmp %> </font> </TD>

<TD width = "78" Height = "24"> <p align = "center"> <font size = 2> <% = Number %> </font> </TD>

<% Else %>

// If the flag is not 0, it indicates that this department does not appear for the first time. The table should be blank here.

<TD width = "93" Height = "24"> <p align = "center"> & nbsp; </TD>

<TD width = "78" Height = "24"> <p align = "center"> & nbsp; </TD>

<% End if %>

// No matter whether the Department appears for the first time or not, the employee name is output here

<TD width = "413" Height = "24"> <p align = "center"> <font size = 2> <% = RSS ("name ") %> </font> </TD>

<% RSS. movenext // current Department employee record pointer RSS points to next employee record

Flag = Flag + 1 // flag plus 1

Wend %>

</Tr>

<% Rs. movenext // department record pointer Rs points to the next department record

Wend %>

<Tr>

// Total number of output persons in the last row of the table

<TD width = "93" Height = "24"> <p align = "center"> <font size = 2> total employees </font> </TD>

<TD width = "78" Height = "24"> <p align = "center"> <font size = 2> <% = HJ %> </font> </TD>

<TD width = "413" Height = "24"> <p align = "center"> <font size = 2> & nbsp; </font> </TD>

</Tr>

</Table>

</Div>

<Input type = button onclick = "VBScript: builddoc" value = "print"> // click the "print" button and call the VBScript function builddoc to generate a local Word file for local printing.

</Html>

// The following VBScript code implements the builddoc Function

<Script language = "VBScript">

Sub builddoc

Set table = Document. All. Data // assign the structure and data of table data in the HTML document to table

Row = table. Rows. Length // row indicates the number of rows in the table.

Column = table. Rows (1). cells. Length // colnum indicates the number of columns in the table.

Set objworddoc = Createobject ("word. Document") // create an object for word. Document

Dim thearray (10000) // defines an array variable to store data in the table. 10 indicates the number of virtual columns and indicates the number of virtual rows.

// The following two-layer for loop assigns the plain text data in the HTML document table to the array

For I = 0 to row-1

For J = 0 to column-1

Thearray (J + 1, I + 1) = table. Rows (I). cells (j). innertext

Next

Next

 

Objworddoc. application. activedocument. Paragraphs. Add. range. insertbefore ("company employee statistical table") // display the table title

Objworddoc. application. activedocument. Paragraphs. Add. range. insertbefore ("") // output the title and press enter to wrap it

 

Set rngpara = objworddoc. application. activedocument. Paragraphs (1). Range

// Set the title attribute in the following with code snippet

With rngpara

. Bold = true // set the title to bold

. Paragraphformat. Alignment = 1 // center the title

. Font. Name = "Arial" // set the title Font

. Font. size = 12 // set the title Font Size

End

 

Set rngcurrent = objworddoc. application. activedocument. Paragraphs (3). Range

Set tabcurrent = objworddoc. application. activedocument. Tables. Add (rngcurrent, row, column)

// The following for loop output Header

For I = 1 to Column

Objworddoc. application. activedocument. Tables (1). Rows (1). cells (I). range. insertafter thearray (I, 1)

Objworddoc. application. activedocument. Tables (1). Rows (1). cells (I). range. paragraphformat. Alignment = 1

Next

// The actual content of the following two-layer for loop output table

For I = 1 to Column

For J = 2 to row

Objworddoc. application. activedocument. Tables (1). Rows (j). cells (I). range. insertafter thearray (I, j)

Objworddoc. application. activedocument. Tables (1). Rows (j). cells (I). range. paragraphformat. Alignment = 1

Next

Next

Objworddoc. application. activedocument. saveas

End sub

</SCRIPT>

 

5. Conclusion

The above programs run in Windows2000 and iis5.0, and the database uses Access2000. Before printing, set the security level of the browser to low or enable the ActiveX control at the intermediate security level. The advantage of this method is that it is very simple to implement statistical reports, and it is very convenient to call word printing, and table attributes can be set according to user requirements. The disadvantage is that browser security settings need to be adjusted, and does not support printing images and special fonts. Microsoft wordsoftware must be installed on the client.

 
 

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.