ASP operation Excel technology summary
table of Contents
First, environmental configuration
Second, ASP's basic operation of Excel
Third, ASP operation Excel generates data sheet
Fourth, ASP operation Excel generates chart
V. Server EXCEL file browsing, download, delete scheme
6. Appendix
text
First, environmental configuration
The server-side environment configuration looks on the reference, the Microsoft series should be all line, namely:
1. Win9X PWS Office
2. Win2000 Professional PWS Office
3. Win2000 Server IIS Office
At present, the author's successful environment is the last two. The version of Office does not have special requirements, take into account the uncertainty and compatibility features of client configuration, and it is recommended that the server-side Office version should not be too high to prevent the client from being downloaded after downloading.
There are two accidental discovery of server-side environment configurations:
1. The author develops the machine's original WPS2002, and the results of the Excel object have always been issued, and after uninstalling the WPS2002, the error disappears.
2. The author develops ASP code likes to use FrontPage, and the results are found to find that if the FrontPage is open (server-side), the object creates unstable phenomenon, and it is time to succeed. After the expansion, found that the Office series software is running in the server side, and the creation of the Excel object is difficult to succeed.
The server side must also set is the operation permission of the COM component. Type "DCMCNFG" on the command line, enter the COM component configuration interface. After selecting Microsoft Excel, click the Properties button to select a custom, and the EVERYONE is added to all permissions. Restart the server after saving.
The client's environmental configuration did not find anything particularly parallel, as long as office and IE can be installed, the version is generally like.
Second, ASP's basic operation of Excel
1. Create an Excel object
Set objexcelapp = createObject ("excel.application")
Objexcelapp.displayalerts = false does not display a warning
Objexcelapp.Application.visible = false does not display interface
2, create an Excel file
Objexcelapp.workbooks.add
Set ObjexcelBook = Objexcelapp.activeWorkbook
Set objexcelsheets = ObjexcelBook.Worksheets
Set objexcelsheet = ObjexcelBook.sheets (1)
3, read 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
Objexcelbook.save (the author is saved, the page is wrong.) 6, exit Excel operation
Objexcelapp.quit must exit
Set objexcelapp = Nothing
Third, ASP operation Excel generates data sheet
1, insert data within one range
Objexcelsheet.Range ("B3: K3"). Value = Array ("67", "87", "5", "9", "7", "45", "45", "54", "54" , "10")
2, insert data in one unit
Objexcelsheet.cells (3, 1) .value = "Internet Explorer"
3, select a range
4, the thick line of the cell
5, the unit grown on the right side of the coarse line
6, the thick line of painting on the cell
7, drawing thick lines at the lower cell
8, cell setting background color
9, combined unit
10, insert
11, insert column
Fourth, ASP operation Excel generates chart
1, create a chart map
Objexcelapp.Charts.add
2, set the type of Chart map
Objexcelapp.activechart.ChartType = 97
Note: Two-dimensional line diagram, 4; two-dimensional cake, 5; two-dimensional column, 51
3, set the chart figure title
Objexcelapp.activechart.hastitle = true
Objexcelapp.activechart.charttitle.text = "a test chart"
4, set graphics through table data
Objexcelapp.activechart.setsourceData Objexcelsheet.Range ("A1: K5"), 1
5, set the graphic data directly (recommended)
Objexcelapp.activechart.seriescollection.newseries
Objexcelapp.activechart.seriescollection (1) .Name = "=" "333" ""
Objexcelapp.activechart.seriescollection (1) .values = "= {1,4,5,6,2}"
6, binding Chart chart
Objexcelapp.activechart.Location 1
7, display data table
Objexcelapp.activeChart.hasDataTable = TRUE
8, display legend
Objexcelapp.activechart.DataTable.showlegendKey = True
V. Server EXCEL file browsing, download, delete scheme
Browse the solution, "location.href =", "navigate", "response.redirect" can be implemented, recommended for client-side methods, because more time to generate an Excel file for more time.
The implementation of the download is troublesome. Downloading components on an online homeD server or custom development a component is a better solution. Another method is to operate the Excel component at the client, and save the server-side EXCEL file to the client. This approach requires the client to open an operation permission of unsafe ActiveX controls, considering that each customer sets the server set to trusted sites, it is more expensive to use the first method.
The deletion scheme consists of three parts:
A: The Excel file generated by the same user is confident that the string is confident by the same file name, the file name. This automatically overrides the previous file when the new file is generated. B: Set the SESSION_ONEND event in the global.asa file to delete the user's Excel temporary file.
C: When setting the Application_onstart event in the global.asa file, delete all the files in the temporary directory.
Note: Recommended Directory Structure / SRC Code / Templet Template Directory / TEMP Temporary Directory
6. Appendix
At the time of error, the death process of Excel appeared was a very headache. Plus "On Error Resume next" before each file will help improve this, because it will execute it to "Application.quit" regardless of whether the file generates an error, guarantees that each program is not left process.
Supplement two points:
1. Other Excel specific operations can be solved by recording macros.
2, 2, the server-side open SQL Enterprise Manager will also generate problems.
Seven, example
<%
ON Error ResMe next
straddr = Server.mappath (".")
Set objexcelapp = createObject ("excel.application")
Objexcelapp.displayAlerts = false
Objexcelapp.Application.visible = false
Objexcelapp.workbooks.open (straddr & "/templet/null.xls")
Set ObjexcelBook = Objexcelapp.activeWorkbook
Set objexcelsheets = ObjexcelBook.Worksheets
Set objexcelsheet = ObjexcelBook.sheets (1)
Objexcelsheet.Range ("B2: K2"). Value = Array ("Week1", "Week2", "Week3", "Week4", "Week5", "WEEK6", "Week7", "Week", "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 = "Internet Explorer"
Objexcelsheet.cells (4, 1) .value = "Netscape"
Objexcelsheet.cells (5, 1) .value = "ER"
Objexcelsheet.Range ("B2: K5"). SELECT
Objexcelapp.Charts.add
Objexcelapp.activechart.ChartType = 97
Objexcelapp.activeChart.barshape = 3
Objexcelapp.activechart.hastitle = true
Objexcelapp.activechart.charttitle.text = "Visitors Log for Each Week Shown in Browsers Percentage"
Objexcelapp.activechart.setsourceData Objexcelsheet.Range ("A1: K5"), 1
Objexcelapp.activechart.Location 1
'Objexcelapp.activeChart.hasDataTable = TRUE
'Objexcelapp.ActiveChart.DataTable.ShowleGendKey = TRUE
Objexcelbook.saveas straddr & "/temp/excel.xls"
Objexcelapp.quit
Set objexcelapp = Nothing
%>
3C
// DTD HTML 4.0 Transitional // En ">
