ASP operation Excel Technology Summary _asp Foundation

Directory

First, the Environment configuration

The basic operation of ASP to Excel

ASP operation Excel to generate datasheet

Iv. ASP operation Excel generates chart diagram

Five, server-side Excel file browsing, downloading, deletion scheme

VI. Appendix

Body

First, the Environment configuration

Server-side environment configuration from the resources, the Microsoft series should be configured in the line, namely:

1. Win9x+pws+office

2. Win2000 Professional+pws+office

3. Win2000 Server+iis+office

At present, the author tested the successful environment is the latter two. There are no special requirements for the version of Office, considering the uncertainty and compatibility characteristics of the client configuration, it is recommended that the server-side Office version not be too high to prevent the client from downloading correctly.

Server-side environment configuration there are two other accidental findings:

1. The author developed the original WPS2002 on the machine, the results of Excel object creation always problems, uninstall WPS2002, the error disappears.

2. The author developed ASP code like using FrontPage, the results found that if FrontPage open (server side), object creation is unstable, sometimes successful sometimes unsuccessful. The extended survey found that the Office family of Software that runs on the server side makes it difficult to create Excel objects successfully.

The point that the server side must also set is the operation permissions of the COM component. Type "DCOMCNFG" at the command line, enter the COM component Configuration interface, select Microsoft Excel to click the Properties button, select all three single options to customize, and edit everyone to the all permissions. Restart the server when the save is complete.

The client's environment configuration did not find anything particularly fastidious, as long as the office and IE can be installed, the version of General can be.

The basic operation of ASP to Excel

1, the establishment of Excel objects

The following is a reference fragment: Set Objexcelapp = CreateObject ("Excel.Application") Objexcelapp.displayalerts = False does not show warning ObjExcelApp.Application.Visible = False does not show interface

2. New Excel File

The following is a reference fragment: ObjExcelApp.WorkBooks.add Set Objexcelbook = Objexcelapp.activeworkbook Set objexcelsheets = Objexcelbook.worksheets Set objexcelsheet = Objexcelbook.sheets (1)

3. Read existing Excel Files

The following is a reference fragment: 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 Excel File

The following is a reference fragment: Objexcelbook.saveas straddr & "\temp\table.xls"

5. Save Excel File

The following is a reference fragment: Objexcelbook.save (the author of the test saved successfully, the page error.) ）

6. Exit Excel operation

The following is a reference fragment: Objexcelapp.quit be sure to quit. Set Objexcelapp = Nothing

ASP operation Excel to generate datasheet

1. Inserting data in a range

The following is a reference fragment: Objexcelsheet.range ("B3:k3"). Value = Array ("67", "87", "5", "9", "7", "45", "45", "54", "54", "10")

2. Insert data in a unit then cell

The following is a reference fragment: Objexcelsheet.cells (3,1). Value= "Internet Explorer"

3. Select a range

4, the left side of the cell to draw thick strokes

5, the right side of the cell draw thick strokes

6, the upper edge of the cell draw thick strokes

7, the bottom of the cell draw thick strokes

8. Set the background color of the cell

9. Merging cells

10, insert Row

11. Insert Column

Iv. ASP operation Excel generates chart diagram

1. Create chart diagram

The following is a reference fragment: ObjExcelApp.Charts.Add

2. Set Chart Chart type

The following is a reference fragment: ObjExcelApp.ActiveChart.ChartType = 97

Note: two-dimensional line chart, 4 two-dimensional pie chart, 5 two-dimensional column chart, 51

3, set chart chart title

The following is a reference fragment: ObjExcelApp.ActiveChart.HasTitle = True ObjExcelApp.ActiveChart.ChartTitle.Text = "A Test Chart"

4, through the table data set graphics

The following is a reference fragment: ObjExcelApp.ActiveChart.SetSourceData Objexcelsheet.range ("A1:k5"), 1

5, directly set graphics data (recommended)

The following is a reference fragment: ObjExcelApp.ActiveChart.SeriesCollection.NewSeries ObjExcelApp.ActiveChart.SeriesCollection (1). Name = "=" "333" "" ObjExcelApp.ActiveChart.SeriesCollection (1). Values = "={1,4,5,6,2}"

6. Binding Chart Diagram

The following is a reference fragment: ObjExcelApp.ActiveChart.Location 1

7, Display data table

The following is a reference fragment: ObjExcelApp.ActiveChart.HasDataTable = True

8, display the legend

The following is a reference fragment: ObjExcelApp.ActiveChart.DataTable.ShowLegendKey = True

Five, server-side Excel file browsing, downloading, deletion scheme

Browse a lot of solutions, "location.href=", "Navigate", "Response.Redirect" can be implemented, recommended by the client method, the reason is to give the server more time to generate Excel files.

The implementation of the download will be a bit cumbersome. It is a good solution to use the online off-the-shelf server-side download components or develop a custom component yourself. Another approach is to operate the Excel component on the client side, which is saved to the client by the client Operation server-side Excel file. This approach requires clients to open the operational permissions of unsafe ActiveX controls, taking into account the difficulty of notifying each client to set up the server as a trusted site or the first method.

The deletion scheme consists of three parts:

A: The same user-generated Excel file uses the same file name, the filename can be used with the user ID number or SessionID number, and so is sure not to repeat the string composition. This automatically overwrites the previous file when a new file is generated.

B: When setting the Session_OnEnd event fire in the Global.asa file, delete the user's Excel staging file.

C: When setting the Application_OnStart event fire in the Global.asa file, delete all files in the staging directory.

Note: The proposed directory structure \SRC code directory \templet template directory \temp staging directory

VI. Appendix

When an error occurs, Excel's death process appears to be a headache. Adding "on Error Resume Next" to each file will help to improve this situation because it persists to "application.quit" regardless of whether the file is wrong or not, ensuring that the process is not left dead every time the program is executed.