ASP Excel Technical Summary)

ASP Excel Technical Summary)



Directory

I. Environment Configuration

Ii. Basic ASP operations on Excel

3. Generate a data table using ASP Excel

Iv. Generate a chart using ASP Excel

V. Solutions for viewing, downloading and deleting Excel files on the server

Vi. Appendix

text
1. Environment configuration
server-side environment configuration from the perspective of reference materials, Microsoft series configuration should be all done, that is:
1. win9x + PWS + Office
2. win2000 professional + PWS + Office
3. win2000 Server + IIS + Office
currently, the latter two environments are successfully tested by the author. There are no special requirements for the Office version. Considering the uncertainty and compatibility of the client configuration, it is recommended that the Office version on the server end be too high to prevent the client from being correctly displayed after download.
there are two accidental discoveries in server environment configuration:
1. the author developed wps2002 with Kingsoft installed on the machine. As a result, the Excel Object creation always encountered a problem. After wps2002 is uninstalled, the error disappears.
2. I developed ASP Code and liked to use FrontPage. It turns out that if the FrontPage is opened (on the server side), the object creation is unstable, sometimes success or failure. After the extension test, we found that if the Office series software runs on the server side, it is difficult to create an Excel object.
the operation permission of the COM component must also be set on the server side. Type "dcomcnfg" in the command line to go to the COM component configuration page. select Microsoft Excel and click the Properties button. All three single options are customized. In the editing window, add everyone to all permissions. Restart the server after saving the file.
nothing special is found in the environment configuration of the client. As long as the office and IE are installed, the general version is acceptable.

2. Basic Excel operations by ASP
1. Create an Excel Object
set objexcelapp = Createobject ("Excel. application ")
objexcelapp. displayalerts = false: no warning is displayed.
objexcelapp. application. visible = false: the page is not displayed.
2. Create an Excel file.
objexcelapp. workbooks. add
set objexcelbook = objexcelapp. activeworkbook
set objexcelsheets = objexcelbook. worksheets
set objexcelsheet = objexcelbook. she ETS (1)
3. Read existing Excel files
straddr = server. mappath (". ")
objexcelapp. workbooks. open (straddr & "\ templet \ table.xls")
set objexcelbook = objexcelapp. activeworkbook
set objexcelsheets = objexcelbook. worksheets
set objexcelsheet = objexcelbook. sheets (1)
4. save an Excel file
objexcelbook. saveas straddr & "\ temp \ table.xls"
5. Save the Excel file
objexcelboo K. Save (saved successfully during the test. An error is reported on the page .)
6. Exit Excel
objexcelapp. Quit must be exited
set objexcelapp = nothing

3. Create a data table using ASP Excel
1. Insert data within a specified range
objexcelsheet. range ("B3: K3 "). value = array ("67", "87", "5", "9", "7", "45", "45", "54", "54 ", "10")
2. Insert data into a cell
objexcelsheet. cells (3, 1 ). value = "Internet Explorer"
3. Select a range
4. Draw a rough line on the left of the cell
5. Draw a line on the right of the cell. rough lines
6. Draw a thick line on the cell
7. Draw a thick line on the bottom of the cell
8. Set the background color of the cell.

9. Merge cells
10. Insert rows
11. Insert columns

4. Create a chart using ASP Excel
1. Create a chart
objexcelapp. charts. add
2. Set the chart type
objexcelapp. activechart. charttype = 97
Note: Two-dimensional line chart, 4; two-dimensional pie chart, 5; two-dimensional column chart, 51
3. Set the chart title
objexcelapp. activechart. hastitle = true
objexcelapp. activechart. charttitle. TEXT = "A test chart"
4. Set a chart using table data
objexcelapp. activechart. setsourcedata objexcelsheet. range ("A1: K5"), 1
5. directly set graphic data (recommended)
objexcelapp. activechart. seriescollection. newseries
objexcelapp. activechart. seriescollection (1 ). name = "=" "333"
objexcelapp. activechart. seriescollection (1 ). values = "= {, 2}"
6. Bind a chart
objexcelapp. activechart. location 1
7. display data tables
objexcelapp. activechart. hasdatatable = true
8. Display legend
objexcelapp. activechart. datatable. showlegendkey = true

V. Solutions for viewing, downloading and deleting Excel files on the server

There are many solutions for browsing, "location. href = "," navigate "," Response. redirect "can be implemented. We recommend that you use the client method, because it gives the server more time to generate an Excel file.

The download implementation requires some trouble. It is a good solution to download components from a ready-made server on the Internet or develop a component by yourself. Another method is to operate the Excel component on the client, and save the Excel file on the client to the client. This method requires the client to enable the operation permission of the insecure ActiveX Control. Considering the trouble of notifying each customer to set the server as a trusted site, we recommend that you use the first method to save trouble.

The deletion scheme consists of three parts:

A: an Excel file generated by the same user is composed of the same file name. The file name can contain user ID or sessionid to ensure that strings are not repeated. In this way, the previous file is automatically overwritten when a new file is generated.

B: When the session_onend event is set to be triggered in the global. Asa file, delete the Excel temporary file of this user.

C: delete all files under the temporary directory when the application_onstart event is set in the global. Asa file.

Note: It is recommended that the directory structure \ SRC code directory \ templet template directory \ Temp temporary directory

Vi. Appendix

When an error occurs, it is a headache to see an Excel dead process. Adding "on error resume next" to each file will help improve this situation, because it will always execute "application. Quit" regardless of whether the file produces errors.ProgramNo dead process is left after execution