1 Introduction
Report printing is usually an important module in the management information system, and Excel has gained a wide range of applications in report printing with its strength, flexible application, flexible, and versatility.
The initial management information system is basically developed by client / server (C / S) mode, but with wwww applications, current management information systems have gradually begun to browser / servers from C / S mode (B / S) mode transition. The B / S mode has a lot of characteristics that have not been in traditional C / S mode, such as more open, unrelated to hardware and software, convenient application expansion and system maintenance upgrade, etc. Many of the software will begin to be ported to B / S mode. Due to the particularity of the B / S mode, the relatively more easy implementation of the Excel report printing function under C / S is a difficult point under B / S. In accordance with the experience in the actual project, this paper takes ASP as an example, gives a better general method.
2. Functional implementation
To illustrate the problem, one example is given here. The system platform is Windows 2000 SQL Server 2000 IIS 5.0 ASP 3. The report uses Excel, requiring report sales statistics to generate book sales statistics in a given report format, and can be printed.
2.1 Production of Excel Report Template
First, according to a given report format, the Excel template is created (the table of the report to be printed), and of course, the data that needs to be generated from the database in the database is blank. This report draws well in Excel, then saved as template, stored, here for /test/book1.xlt.
2.2 Generation and Printing of Excel Reports
This uses Excel Application Components, which is installed in the system when installing Excel. Our operations are also directed to this component.
(1) Establish an Excel.Application object
Set objexcel = createObject ("excel.application")
(2) Open Excel Template
Objexcel.Workbooks.open (Server.MAppath ("/ Test") & "/ book1.xlt") "Opens Excel Template
Objexcel.sheets (1) .select 'Checking Works page
Set sheetactive = Objexcel.activeworkbook.activesheet
(3) EXCEL's regular addition operation
For example sheetAtAtive.Range ("G4"). Value = Date () 'Here added time, of course, you can also be any data you specified.
(4) Record in the database in Excel
Here, it is assumed that there is a data set AdorSet that stores statistics generated by the SQL operation.
Num = 7 'starting from the eleventh row of Excel
DO Until AdorSet.eof 'circulating until data in the data set
StrRrange = "D" & NUM & ": f" & num 'Setting the unit area to fill in the content
SheetAtArtive.Range (StrRange) .Font.size = 10 'Setting the font size
SheetAtactive.Range (StrRange) .wraptext = false 'Set text back
SheetAtAtactive.Range (StrRange) .shrinktofit = true 'Set whether to automatically adapt to the table unit size
SheetAtAtArtive.Range (StrRange) .Value = array (AdorSet ("BookId"), AdorSet ("BookName")
Adornt.Movenext
loop
(5) Saving and processing of Excel temporary report files
In actual operation, you should pay attention to a temporary Excel file every time a user is printed, not a hard specified file name, because if you use a fixed file name, only the first generation is successful, followed by the following operations Because there is already the same name file, it will fail. So we need a temporary and non-repetitive file name each time, here you can use the custom getTemPoraryFile () function to generate, and then store the path of these temporary files in the variable filepos.
In addition, if these temporary files do not process, it will become a file garbage, so you should first delete all the original temporary print files under the temporary directory when submitting an Excel report print request.
The main code of the temporary document is as follows:
Function GetTempoRyfile (MyFileSystem)
Dim Tempfile, Dotpos
TempFile = myfilesystem.getTempname
Dotpos = INSTR (1, Tempfile, ".")
GetTemporaryFile = MID (Tempfile, 1, Dotpos) & "XLS"
END FUNCTION
Set myfs = creteObject ("scripting.filesystemobject")
Filepos = server.mappath ("/ test") & "/ tmp /" "To store the temporary directory of printing temporary files
Filename = getTempoRoyalfile (myfs) 'get a temporary file name
Myfs.Deletefile filepos & "*. xls" 'Deletes all original printed files under this directory
SET myfs = Nothing
The save code of the Excel temporary file is:
Objexcel.Activeworkbook.saveas filepos & filename
(6) Exit Excel application
Objexcel.quit
Set objexcel = Nothing
(7) Print of Excel report
The front step has generated an Excel report, and the next step is printed, and there are two strategies:
Scenary 1: Provides the EXCEL report temporary file link to the above, users can directly click on the Excel report in the browser and print through the browser's print function, or click the right click and then save it. deal with.
Solution 2: After generating an Excel report, load it directly to the browser directly, and of course, when not fully loaded, you should prompt the words that is loaded, please wait.
2.3 System configuration and precautions
Although the above code is simple, the actual application does not often have an error, so the system configuration and precautions you want to talk about are critical.
(1) Thousands must ensure the correctness of the above code input, otherwise the Excel object will remain in memory, which is difficult to eliminate, causing the next call to slow down, and generate a Windows error that memory is not readily writable. The solution at this time is to log out of the current user. If you can't still do it, you can only reset.
(2) Be sure to set the permissions of the ASP file responsible for printing functions. The method is: In IIS management, select the ASP file, right click and then select "Properties" / "File Security" / "Anonymous Access and Verification Control", here IIS is anonymous to access, you should choose to verify access (basic verification here) And integrated Windows verification is available, but the former is not safe enough), this is not important, otherwise it will be wrong. (3) Sometimes the report is divided into multi-pages, and we hope that each page has the same head, requires automatic printing per page per page, and can be set in the Excel template. The method is as follows: Select the menu "File" / "page setting" / "Worksheet", then enter the number of rows you headed in "Top Title" (such as: Header 1-3) Fill in: $ 1: $ 3 ).
3. to sum up
We have given an example of the generation and printing of the Excel report in B / S mode written by ASP, has been well applying in the actual actual. The fact also proves that although the code of this example is not difficult to write, it must pay attention to the configuration of the system, which is also experienced after countless failure.
