Summary of WEB development technology using ASP to operate Excel

Article title: Summary of WEB development technology using ASP to operate Excel Technology. Linux is a technology channel of the IT lab in China. Includes basic categories such as desktop applications, Linux system management, kernel research, embedded systems, and open source.
　　 I. environment configuration
Server-side environment configuration from the reference data, Microsoft series configuration should be all done, that is:
　　
1. Win9x + PWS + Office
2. Win2000 Professional + PWS + Office
3. Win2000 Server + IIS + Office
　　
Currently, the test is successful in the latter two environments. There are no special requirements for the Office version. considering the uncertainty and compatibility of the client configuration, it is recommended that the Office version on the server end be too high to prevent the client from being correctly displayed after download.
Two accidental discoveries of server-side environment configuration are as follows:
　　
1. The author developed the WPS2002 with Kingsoft installed on the machine. as a result, the Excel Object creation problem always occurs. after WPS2002 is uninstalled, the error disappears.
　　
2. I developed ASP code and liked to use FrontPage. I found that if the FrontPage is opened (on the server side), the object creation is unstable, and sometimes it succeeds or fails. After the extension test, we found that if the Office Series software runs on the server side, it is difficult to create an Excel object.
　　
The operation permission of the COM component must also be set on the server side. Type "DCOMCNFG" in the command line to go to the COM component configuration interface. select Microsoft Excel and click the properties button. All three single options are customized. in the editing window, add Everyone to all permissions. Restart the server after saving the file.
Client environment configuration does not find anything special, as long as the Office and IE are installed, the general version seems to be OK.
　　
　　 II. basic ASP operations on Excel
1. create an Excel Object
Set objExcelApp = CreateObject ("Excel. Application ")
ObjExcelApp. DisplayAlerts = false no warning is displayed
ObjExcelApp. Application. Visible = false: the interface is not displayed.
2. create an 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 an Excel file
ObjExcelBook. SaveAs strAddr & "\ Temp \ Table.xls"
5. save Excel? N
ObjExcelBook. Save (saved successfully during the test. an error is reported on the page .)
6. exit the Excel operation.
ObjExcelApp. Quit must exit
Set objExcelApp = Nothing
　　
　　 3. generate a data table using ASP Excel
1. Insert data within 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 rough line on the left of the cell
5. draw a rough line on the right of the cell
6. draw a thick line above the cell
7. draw a rough line under the cell
8. set the background color for cells.
9. Merge cells
10. Insert rows
11. insert columns
　　
　　 IV. generate a Chart using ASP Excel
1. create a Chart
ObjExcelApp. Charts. Add
2. set Chart types
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. use table data to set images
ObjExcelApp. ActiveChart. SetSourceData objExcelSheet. Range ("A1: k5"), 1
5. directly set graphic data (recommended)
ObjExcelApp. ActiveChart. SeriesCollection. NewSeries
ObjExcelApp. ActiveChart. SeriesCollection (1). Name = "=" "333 """
ObjExcelApp. ActiveChart. SeriesCollection (1). Values = "= {1, 4, 5, 6, 2 }"
6. bind a Chart
ObjExcelApp. ActiveChart. Location 1
7. display data tables
ObjExcelApp. ActiveChart. HasDataTable = True
8. display legend
ObjExcelApp. ActiveChart. DataTable. ShowLegendKey = True
　　
　　 V. solutions for viewing, downloading and deleting Excel files on the server
There are many solutions for browsing, "Location. href = "," Navigate "," Response. redirect "can be implemented. we recommend that you use the client method, because it gives the server more time to generate an Excel? N.
　　
The download implementation requires some trouble. It is a good solution to download components from a ready-made server on the Internet or develop a component by yourself. Another method is to operate the Excel component on the client side, and save the Excel file on the client side to the client side. This method requires the client to enable the operation permission of the insecure ActiveX control. Considering the trouble of notifying each customer to set the server as a trusted site, we recommend that you use the first method to save trouble.
　　
The deletion scheme consists of three parts:
A: an Excel file generated by the same user is composed of the same file name. the file name can contain user ID or SessionID to ensure that strings are not repeated. In this way, the previous file is automatically overwritten when the new file is generated.
B: When the Session_onEnd event is set in the Global. asa file, the user's Excel temporary storage disk is deleted.
C: delete all files under the temporary directory when the Application_onStart event is set in the Global. asa file.
Note: it is recommended that the directory structure \ Src code directory \ Templet Template directory \ Temp temporary directory
　　
　　 VI. Appendix
When an error occurs, it is a headache to see an Excel dead process. Adding "On Error Resume Next" before each file will help improve this situation, because it will be executed to "Application. quit ensures that no dead processes are left after each program is executed.
Add two points:
　　
1. Other Excel operations can be solved by recording macros.
2. opening the SQL Enterprise Manager on the server also causes problems.
　　
　　 VII. example
<%
On Error Resume 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", "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 = "Internet Explorer"
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 = "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. able. ShowLegendKey = True
ObjExcelBook. SaveAs strAddr & "\ Temp \ Excel.xls"
ObjExcelApp. Quit
Set objExcelApp = Nothing
%>
　　
　　
　　
　　 New Document
　　
　　
　　
　　
　　
　　
　　
　　
I will try again here. Save the table on the webpage.
For example, table id = MYTABLE
　　
Sub btnExport_onclick ()
Dim objExcel
On error resume next
Set objExcel = CreateObject ("excel. application ")
With objExcel
. Visible = true
. Workbooks. add
. Sheets ("sheet1"). select
　　
End
M_row = "0"
　　
For a = 0 to document. all. mytable. rows. length-1
M_row = cstr (int (m_row) + 1)
For B = 0 to document. all. mytable. rows (a). cells. length-1
M_col = chr (asc ("A") + B)
Objexcel. range (m_col & m_row). select
M_value = document. all. mytable. rows (a). cells (B). innerText
Objexcel. activecell. value = cstr (m_value)
Next
Next
Objexcel. visible = true
Objexcel. range ("A1"). select
End sub