ASP and Excel are combined to generate the code for data tables and charts. Friends in need can refer to it. 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 Chart chart
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 Custom for all three 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 & /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. Change cell text color
objExcelSheet.Cells(3,1).Font.Color=vbred
4. Draw lines around the cells
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
Draw lines in the middle of the area
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. Merge cells
objExcelSheet.Range(A1:G7).Merge
10. Set left and right alignment
2 left 3 middle 4 right
objExcelSheet.Range(A1).HorizontalAlignment = 2
11. Set the top and bottom alignment
2 is centered
objExcelSheet.Range(A1)..VerticalAlignment = 2
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-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: 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 persist in executing Application.Quit regardless of whether the file generates an error, ensuring that no dead process is left after each program execution.
Two additional points:
1. Other specific Excel operations can be solved by recording macros.
2. Opening SQL Enterprise Manager on the server side will also cause problems.
Copy the 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=OtherobjExcelSheet.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.xlsobjExcelApp.Quit
setobjExcelApp=Nothing
%>
<!DOCTYPEHTMLPUBLIC-//W3C//DTDHTML4.0Transitional//EN>
<HTML>
<HEAD>
<TITLE>NewDocument</TITLE>
<METANAME=GeneratorCONTENT=MicrosoftFrontPage5.0>
<METANAME=AuthorCONTENT=>
<METANAME=KeywordsCONTENT=>
<METANAME=DescriptionCONTENT=>
</HEAD>
<BODY>
</BODY>
</HTML>