System platform is Windows 2000 SQL Server 2000 IIS 5.0 ASP
Function: Requires report sales statistics in the given report format, and can be printed.
Production of Excel Report Templates:
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 is drawn in Excel, then saved as template, stored here, here for /test/book1.xls.
Generation and printing of Excel report
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
SheetAtAtAtive.Range (strRrange) .Value = array (AdorSet ("BookId"), AdorSet ("BookName"), AdorSet ("Author")) Fill in the data in the data set to the corresponding unit
Num = NUM 1
Adornt.Movenext
loop
Save and handling 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, dotpostempfile = 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
Exit Excel application
Objexcel.quit
Set objexcel = Nothing
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.
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 ).
