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
Set Objexcelapp = CreateObject ("Excel.Application")
Objexcelapp.displayalerts = False does not show warning
ObjExcelApp.Application.Visible = False does not show interface
2. New Excel File
ObjExcelApp.WorkBooks.add
Set Objexcelbook = Objexcelapp.activeworkbook
Set objexcelsheets = Objexcelbook.worksheets
Set objexcelsheet = Objexcelbook.sheets (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 Excel File
Objexcelbook.saveas straddr & "\temp\table.xls"
5. Save Excel File
Objexcelbook.save (the author of the test saved successfully, the page error.) ）
6. Exit Excel operation
Objexcelapp.quit be sure to quit.
Set Objexcelapp = Nothing
ASP operation Excel to generate datasheet
1. Inserting data in a range
Objexcelsheet.range ("B3:k3"). Value = Array ("67", "87", "5", "9", "7", "45", "45", "54", "54", "10")
2. Insert data in a unit then cell
Objexcelsheet.cells (3,1). Value= "Internet Explorer"
3, change the color of cell text
Objexcelsheet.cells (3,1). Font.color=vbred
4. Draw a line around a cell
Objexcelsheet.cells (3.1). Borders (1). LineStyle =1
Objexcelsheet.cells (3.1). Borders (2). LineStyle =1
Objexcelsheet.cells (3.1). Borders (3). LineStyle =1
Objexcelsheet.cells (3.1). Borders (4). LineStyle =1
Area Middle Draw Line
Objexcelsheet.range ("A1:g7"). Borders (7). LineStyle =1
Objexcelsheet.range ("A1:g7"). Borders (8). LineStyle =1
8. Set the background color of the cell
Objexcelsheet.cells (3.1). Interior.colorindex=17
9. Merging cells
Objexcelsheet.range ("A1:g7"). Merge
10. Set the right and left alignment method
2 left 3 Middle 4 right
Objexcelsheet.range ("A1"). HorizontalAlignment = 2
11. Set up and down alignment method
2 is centered
Objexcelsheet.range ("A1"). VerticalAlignment = 2
Iv. ASP operation Excel generates chart diagram
1. Create chart diagram
ObjExcelApp.Charts.Add
2. Set Chart Chart type
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
ObjExcelApp.ActiveChart.HasTitle = True
ObjExcelApp.ActiveChart.ChartTitle.Text = "A Test Chart"
4, through the table data set graphics
ObjExcelApp.ActiveChart.SetSourceData Objexcelsheet.range ("A1:k5"), 1
5, directly set graphics data (recommended)
ObjExcelApp.ActiveChart.SeriesCollection.NewSeries
ObjExcelApp.ActiveChart.SeriesCollection (1). Name = "=" "333" ""
ObjExcelApp.ActiveChart.SeriesCollection (1). Values = "="
6. Binding Chart Diagram
ObjExcelApp.ActiveChart.Location 1
7, Display data table
ObjExcelApp.ActiveChart.HasDataTable = True
8, display the legend
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.
Add two points:
1, other Excel concrete operation can by record the macro to solve.
2, server-side open SQL Enterprise Manager will also cause problems.
Copy Code code as follows:
<%
Onerrorresumenextstraddr=server.mappath (".") Setobjexcelapp=createobject ("Excel.Application")
Objexcelapp.displayalerts=false
Objexcelapp.application.visible=false
ObjExcelApp.WorkBooks.Open (straddr& "\templet\null.xls")
Setobjexcelbook=objexcelapp.activeworkbook
Setobjexcelsheets=objexcelbook.worksheets
Setobjexcelsheet=objexcelbook.sheets (1) objexcelsheet.range ("B2:k2"). Value=array ("Week1", "Week2", "Week3", "Week4", "Week5", "Week6", "Week7",
"Week8", "Week9", "Week10")
Objexcelsheet.range ("B3:k3"). Value=array ("67", "87", "5", "9", "7", "45", "45", "54", "54", "10")
Objexcelsheet.range ("B4:k4"). Value=array ("10", "10", "8", "27", "33", "37", "50", "54", "10", "10")
Objexcelsheet.range ("B5:k5"). Value=array ("23", "3", "86", "64", "60", "18", "5", "1", "36", "80")
Objexcelsheet.cells (3,1). Value= "InternetExplorer"
Objexcelsheet.cells (4,1). Value= "Netscape"
Objexcelsheet.cells (5,1). Value= "Other" Objexcelsheet.range ("B2:k5"). Select
ObjExcelApp.Charts.Add
objexcelapp.activechart.charttype=97
Objexcelapp.activechart.barshape=3
Objexcelapp.activechart.hastitle=true
objexcelapp.activechart.charttitle.text=
"Visitorslogforeachweekshowninbrowserspercentage"
ObjExcelApp.ActiveChart.SetSourceDataobjExcelSheet.Range ("A1:k5"), 1
ObjExcelApp.ActiveChart.Location1
' Objexcelapp.activechart.hasdatatable=true
' Objexcelapp.activechart.datatable.showlegendkey=trueobjexcelbook.
saveasstraddr& "\temp\excel.xls" Objexcelapp.quit
Setobjexcelapp=nothing
%>
<! Doctypehtmlpublic "-//w3c//dtdhtml4.0transitional//en" >
<HTML>
<HEAD>
<TITLE>NewDocument</TITLE>
<metaname= "generator" content= "MicrosoftFrontPage5.0" >
<metaname= "Author" content= "" >
<metaname= "Keywords" content= "" >
<metaname= "Description" content= "" >
</HEAD>
<BODY>
</BODY>
</HTML>
