1. Introduction Report printing is usually an important module in the management information system, and Excel has been widely used in report printing due to its powerful functions, flexible application, and strong versatility.
The initial management information system was basically developed using the client/server (C/S) model. However, with the widespread application of WWW, the current management information system has gradually begun to shift from the C/S model to the browser/server (B /S) mode change. The B/S mode has many features that the traditional C/S mode cannot match, such as being more open, independent of software and hardware, convenient for application expansion and system maintenance and upgrades, etc. It has now become the preferred computing mode for enterprise networks. It was originally used in C/S Many software under the Internet have begun to be 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 under C/S has become a difficulty under B/S. This article gives a better general method based on the experience summarized in actual projects, taking ASP as an example.
2. Function implementation To illustrate the problem, here is an example. The system platform is Windows 2000+SQL Server 2000+IIS 5.0+ASP 3. The report uses Excel. It is required to generate a report of book sales statistics according to the given report format and be able to print it.
2.1 Production of Excel report template First, make an Excel template (that is, the form of the report to be printed) according to the given report format. Of course, the data generated from dynamic statistics in the database should be left blank. This report is first drawn in Excel, then saved as a template, and stored here, here as testbook1.xlt.
2.2 Generation and printing of Excel reports The Application component of Excel is used here, which is installed into the system when Excel is installed. Our operations are also targeted at this component.
(1) Create Excel.Application object
set objExcel=CreateObject("Excel.Application")
(2) Open the Excel template
objExcel.Workbooks.Open(server.mappath("test")&"book1.xlt") 'Open Excel template
objExcel.Sheets(1).select 'Select the work page
set sheetActive=objExcel.ActiveWorkbook.ActiveSheet
(3) Excel’s regular adding operations such as sheetActive.range("g4").value=date() 'What is added here is time, of course it can also be any data you specify.
(4) Add records in the database in Excel. It is assumed that there is already a data set adoRset, which stores statistical data generated by Sql operations.
num=7 'Start from the seventh row of Excel
do until adoRset.EOF 'Loop until the data in the data set is written
strRange="d"&num&":f"&num 'Set the unit area to fill in the content
sheetActive.range(strRange).font.size=10 'Set font size
sheetActive.range(strRange).WrapText=false 'Set text wrap
sheetActive.range(strRange).ShrinkToFit=true 'Set 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 data set into the corresponding unit
num=num+1
adoRset.MoveNext
loop
(5) Saving and processing of Excel temporary report files. In actual operation, it should be noted that each time a user prints a report, a temporary Excel file is used instead of rigidly specifying the file name, because if a fixed file name is used, only the first Once the generation is successful, subsequent operations will fail because a file with the same name already exists. So we need to generate a temporary and non-repeating file name every time. Here we can use the custom getTemporaryFile() function to generate it, and then store it in the variable filename, and use the variable filepos to represent the paths of these temporary files.
In addition, if these temporary files are not processed, they will become file garbage over time. Therefore, when each user submits an Excel report print request, all previously generated temporary print files in the temporary directory must be deleted first.
The main code for processing temporary files is as follows:
function getTemporaryFile(myFileSystem)
dimtempFile,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 to store temporary printing files
fileName=getTemporaryFile(myFs) 'Get a temporary file name
myFs.DeleteFile filePos&"*.xls" 'Delete all temporary print files originally generated in this directory
set myFs=nothing
The saving code for Excel temporary files is:
objExcel.ActiveWorkbook.saveas filePos&filename
(6) Exit the Excel application
objExcel.quit
set objExcel=Nothing
(7) Printing of Excel report The previous steps have generated the Excel report. For printing in the next step, there are two strategies:
Option 1: Provide the link to the temporary file of the Excel report generated above to the user. The user can directly click to open the Excel report in the browser and print it through the browser's print function. They can also right-click and save it locally for printing, etc. deal with.
Option 2: After generating the Excel report, load it directly into the browser on the client side. Of course, when it is not fully loaded, it should prompt "Loading, please wait" and other words.
2.3 System configuration and precautions Although the above code is very simple, errors often occur if not used properly in practice, so the system configuration and precautions mentioned below are very critical.
(1) Be sure to ensure the correctness of the above code input, otherwise once the operation error occurs, the Excel object will remain in the memory and will be difficult to eliminate, causing the next call to be extremely slow and generate a Windows error that the memory cannot be read or written. The solution at this time is to log out of the current user. If that doesn't work, you can only reset.
(2) Be sure to set the permissions of the asp file responsible for the printing function. The method is: In IIS management, select the asp file, right-click and select "Properties"/"File Security"/"Anonymous Access and Authentication Control". Here, IIS defaults to anonymous access, and you should select authenticated access (here is the basic authentication Both methods can be used and integrated Windows authentication, but the former is not secure enough), this is extremely important, otherwise errors will occur in the application.
(3) Sometimes the report is divided into multiple pages, and we want each page to have the same header. We require the header to be automatically printed on each page, which can be set in the Excel template. The method is as follows: Select the menu "File"/"Page Setup"/"Worksheet", and then enter the number of rows in your header in the "Top Title Row" (for example, if the header is rows 1-3, fill in: $1:$3 ).
3． In summary, we have given an example of generating and printing EXCEL reports in B/S mode written in ASP, which has been well applied in practice. Facts have also proved that although the code of this example is not difficult to write, you must pay attention to the system configuration. This is also the experience gained after countless failures.