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.