How to use ASP to generate an Excel report

Here is an example to illustrate the problem. The system platform is Windows 2000 + SQL Server 2000 + IIS 5.0 + Asp 3, and the report adopts excel. The report must generate the book sales statistics report according to the given report format and print it.

Function implementation
Here is an example to illustrate the problem. The system platform is Windows 2000 + SQL Server 2000 + IIS 5.0 + Asp 3, and the report adopts excel. The report must generate the book sales statistics report according to the given report format and print it.
1. Preface
Report printing is usually an important module in the management information system. Excel has been widely used in report printing because of its powerful functions, flexible application, and versatility.
Initially, the Management Information System was developed in the Client/Server (C/S) mode. However, with the wide application of WWW, the current management information system has gradually changed from the C/S mode to the browser/server (B/S) mode. The B/S mode has many characteristics that are not comparable to the traditional C/S mode, such as being more open, unrelated to software and hardware, application expansion, and easy system maintenance and upgrade, at present, it has become the preferred computing mode for enterprise networks. Many software in C/S have been transplanted to B/S mode. Due to the particularity of the B/S mode, the Excel report printing function that is relatively easy to implement in C/S has become a challenge in B/S. Based on the experience summarized in the actual project, this article takes ASP as an example and provides a better general method.
2.1 create an Excel Report Template
First, create an Excel template (The table of the report to be printed) based on the given report format. Of course, the data generated by dynamic statistics in the database must be left blank. This report is first drawn in Excel, then saved as a template and stored in \ test \ book1.xlt.
2.2 generate and print an Excel report
The Excel application component is used here, which is installed in the system when Excel is installed. Our operations are also for this component.
(1) Create an Excel. Application Object
Set objexcel = Createobject ("Excel. application ")
(2) open an Excel template
Objexcel. workbooks. Open (server. mappath ("\ test") & "\ book1.xlt") 'open an Excel template
Objexcel. Sheets (1). Select 'select the work page
Set sheetactive = objexcel. activeworkbook. activesheet
(3) General addition of Excel
For example, sheetactive. Range ("G4"). value = Date () 'is added with time. Of course, it can be any data you specify.
(4) add records in the database in Excel
Assume that an adorset dataset already exists, which stores statistics generated by SQL operations.
Num = 7' starting from the seventh row of Excel
Do until adorset. EOF until data in the dataset is written
Strrange = "D" & num & ": F" & num' specifies the unit area to be filled in.
Sheetactive. Range (strrange). Font. size = 10' set the font size
Sheetactive. Range (strrange). wraptext = false' sets the text rewind.
Sheetactive. Range (strrange). shrinktofit = true': determines whether to automatically adapt to the table unit size.
Sheetactive. range (strrange ). value = array (adorset ("bookid"), adorset ("bookname"), adorset ("author") 'Fill in the data in the dataset to the corresponding unit
Num = num + 1
Adorset. movenext
Loop
(5) Saving and processing Excel temporary report files
In actual operation, you should note that each time a user prints a report, a temporary Excel file is used, rather than a hard file name, because if a fixed file name is used, only the first generation is successful, all subsequent operations will fail because a file with the same name already exists. Therefore, we need to generate a temporary and non-repeated file name each time. Here we can use the custom gettemporaryfile () function to generate the file and store it in the filename variable, use the filepos variable to indicate the paths of these temporary files.
In addition, if these temporary files are not processed, they will become file spam over time. Therefore, when each user submits an Excel report printing request, he must first delete all the original temporary printed files in the temporary directory.
Temporary File Processing Code As follows:
Function gettemporaryfile (myfilesystem)
Dim tempfile, dotpos
Tempfile = myfilesystem. gettempname
Dotpos = instr (1, tempfile ,".")
Gettemporaryfile = mid (tempfile, 1, dotpos) & "xls"
End Function
Set myfs = Createobject ("scripting. FileSystemObject ")
Filepos = server. mappath ("\ test") & "\ TMP \" 'temporary directory for storing printed temporary files
Filename = gettemporaryfile (myfs) 'get a temporary file name
Myfs. deletefile filepos & "*. xls" 'Delete all the temporary print files generated in this directory
Set myfs = nothing
Save the temporary Excel file as follows:
Objexcel. activeworkbook. saveas filepos & filename
(6) Exit the Excel application
Objexcel. Quit
Set objexcel = nothing
(7) Printing EXCEL reports
An Excel report has been generated in the previous step. The following two policies can be used to print the report in the next step:
Solution 1: Provide the link to the temporary Excel report file generated above. You can click to open the Excel report in the browser and print it using the print function of the browser, you can also right-click and save it locally before printing and other related processing.
Solution 2: After an Excel report is generated, it is directly loaded to the browser on the client. Of course, when not fully loaded, the prompt "loading, please wait" should be displayed.
2.3 system configuration and precautions
Although the above code is very simple, errors often occur due to improper application. Therefore, the system configuration and precautions described below are critical.
(1) ensure the correctness of the above Code Input. Otherwise, the EXCEL object will be stuck in the memory and difficult to eliminate in case of running errors, resulting in a slow speed during the next call, and generate a Windows Error where the memory cannot be read or written. The solution is to log out of the current user. If not, you can only reset it.
(2) You must set the asp File Permission for the printing function. In IIS management, right-click the ASP file and choose "properties"/"file security"/"Anonymous Access and authentication control". in this case, IIS uses anonymous access by default and authentication access should be selected (both basic authentication and Windows authentication are supported, but the former is not secure). This is extremely important, otherwise, an error occurs in the application.
(3) Sometimes a report can be divided into multiple pages, and we want each page to have the same header. the header must be automatically printed on each page. You can set it in an Excel template. The method is as follows: Select "file"/"page settings"/"worksheet" from the menu, and enter the number of rows in your header in "Top heading line" (for example, if the header is 1-3 rows, enter: $1: $3 ).
3. Summary
The preceding example shows how to generate and print an Excel report in B/S mode written in ASP. It has been well applied in practice. It turns out that the code in this example is not difficult to write, but you must pay attention to the system configuration. This is also the experience gained after countless failures.

-