Summary of ASP operation Excel technology
Table of contents
1. Environment configuration
2. Basic operations of ASP on Excel
3. ASP operates Excel to generate data tables
4. ASP operates Excel to generate Charts
5. Server-side Excel file browsing, downloading, and deletion solutions
6. Appendix
text
1. Environment configuration
Regarding the server-side environment configuration, judging from the reference materials, all Microsoft series configurations should be fine, namely:
1. Win9x+PWS+Office
2. Win2000 Professional+PWS+Office
3． Win2000 Server+IIS+Office
At present, the environment where the author has successfully tested is the latter two. There are no special requirements for the Office version. Considering the uncertainty of client configuration and low compatibility, it is recommended that the server-side Office version should not be too high to prevent the client from failing to display correctly after downloading.
Two more serendipitous discoveries about server-side environment configuration are:
1. The author's development machine was originally equipped with Kingsoft's WPS2002, but there was always a problem with Excel object creation. After uninstalling WPS2002, the error disappeared.
2. The author likes to use FrontPage when developing ASP code. It turns out that if FrontPage is opened (server side), object creation is unstable, sometimes successful and sometimes unsuccessful. After extensive investigation, we found that if the Office series software is run on the server side, it is difficult to successfully create Excel objects.
Another thing that must be set on the server side is the operation permission of the COM component. Type DCOMCNFG on the command line to enter the COM component configuration interface, select Microsoft Excel and click the Properties button, select Customize for all three options, and add Everyone to all permissions during editing. Restart the server after saving.
There is nothing special about the environment configuration of the client. As long as Office and IE are installed, any universal version seems to be fine.
2. Basic operations of ASP on Excel
1. Create Excel object
set objExcelApp = CreateObject(Excel.Application)
objExcelApp.DisplayAlerts = false does not display warnings
objExcelApp.Application.Visible = false does not display the interface
2. Create a 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 as
objExcelBook.SaveAs strAddr & /Temp/Table.xls
5. Save the Excel file
objExcelBook.Save (The author successfully saved when testing, but the page reported an error.)
6. Exit Excel operation
objExcelApp.Quit must exit
set objExcelApp = Nothing
3. ASP operates Excel to generate data tables
1. Insert data in a 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 thick line on the left side of the cell
5. Draw a thick line on the right side of the cell
6. Draw thick lines on the top of the cells
7. Draw thick lines under the cells
8. Set the background color of the cell
9. Merge cells
10. Insert row
11. Insert column
4. ASP operates Excel to generate Charts
1. Create Chart 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 graphics through table data
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. Bind Chart
objExcelApp.ActiveChart.Location 1
7. Display data table
objExcelApp.ActiveChart.HasDataTable = True
8. Display legend
objExcelApp.ActiveChart.DataTable.ShowLegendKey = True
5. Server-side Excel file browsing, downloading, and deletion solutions
There are many solutions for browsing, including Location.href=, Navigate, and Response.Redirect. It is recommended to use the client method because it gives the server more time to generate Excel files.
The implementation of downloading is a little more troublesome. It is a better solution to download components from ready-made servers on the Internet or to customize and develop a component yourself. Another method is to operate the Excel component on the client, and the client operates the server-side Excel file and saves it to the client. This method requires the client to open the operation permission of the unsafe ActiveX control. Considering the trouble of notifying each customer to set the server as a trusted site, it is recommended to use the first method to save trouble.
The deletion plan consists of three parts:
A: Excel files generated by the same user use the same file name. The file name can be composed of a user ID number or Session ID number that ensures non-duplicate strings. This way the new file will automatically overwrite the previous file when it is generated.
B: When the Session_onEnd event is set in the Global.asa file to fire, delete this user's Excel temporary file.
C: When the Application_onStart event is set in the Global.asa file to fire, delete all files in the temporary directory.
Note: Recommended directory structure/Src code directory/Templet template directory/Temp temporary storage directory
6. Appendix
The dead process of Excel when an error occurs is a headache. Adding On Error Resume Next before each file will help improve this situation, because it will insist on executing to Application.Quit regardless of whether the file generates an error, ensuring that no dead process is left after each program execution.