Table of contents 1. Environment configuration 2. ASP basic operations on Excel 3. ASP operates Excel to generate data table 4. ASP operates Excel to generate Chart diagram 5. Server-side Excel file browsing, downloading and deletion solutions 6. Appendix
text 1. Environment configuration server Judging from the reference materials for the terminal environment configuration, 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 Custom for all three radio options. During editing, add Everyone to all permissions. After saving, restart the server.
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 & "TempletTable.xls")
set objExcelBook = objExcelApp.ActiveWorkBook
set objExcelSheets = objExcelBook.Worksheets
set objExcelSheet = objExcelBook.Sheets(1)
4. Save Excel file as
objExcelBook.SaveAs strAddr & "TempTable.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 table
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 Chart chart
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 = "={1,4,5,6,2}"
6. Bind Chart
objExcelApp.ActiveChart.Location 1
7. Display data table
objExcelApp.ActiveChart.HasDataTable = True
8. Display legend
objExcelApp.ActiveChart.DataTable.ShowLegendKey = True
5. There are many solutions for browsing, downloading, and deleting Excel files on the server side. "Location.href=", "Navigate", and "Response.Redirect" can all be implemented. It is recommended to use the client The reason is to give the server more time to generate the Excel file.
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-duplication of 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: It is recommended that the directory structureSrc code directoryTemplet template directoryTemp temporary storage directory
6. The dead process of Excel when an error occurs in the appendix is a very troublesome thing. 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 each program is executed without leaving a dead end. process.