One way to use excel in asp is to link the excel file as a database, and then the operation is similar to the access database operation. However, this method is not always useful because Excel is not a relational database. For a fixed format, which has complex cell merging, border line styles, patterns, formula relationships between cells, etc., I think the easiest way to understand is to open an existing setting in the background Create a template file, then insert data where needed, save, and output...
The method mentioned here is to directly create an excel object, which makes it easier to perform various operations on the excel document in the background.
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.
It is very important. If it is not set, other computers will not be able to complete the creation of excel objects.
However, this method also has a shortcoming. In my actual operation, if there is already an open excel file on the server, and then execute the same file, an error will occur. I still don’t understand why. Maybe there are some places that have not been set up.
In addition, the example code format in the article cited above is not complete, and many of the line breaks and spaces are not in accurate format. If the code is complete, just copy the code and it will run successfully, and then slowly research and modify it, and it will be easy to get started. Now the modified code is as follows (the part that draws the chart has been deleted):
<%
On Error Resume Next
strAddr=Server.MapPath(".")
set objExcelApp=CreateObject("Excel.Application")
objExcelApp.DisplayAlerts=false
objExcelApp.Application.Visible=false
objExcelApp.WorkBooks.Open(strAddr&"TempletNull.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 ")
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="Other"
objExcelSheet.Range("b2:k5").Select
SaveAs(strAddr&"TempExcel.xls")
objExcelApp.Quit
set objExcelApp=Nothing
%>
<!DOCTYPEHTMLPUBLIC"-//W3C//DTDHTML4.0Transitional//EN">
<HTML>
<HEAD>
<TITLE>NewDocument</TITLE>
<METANAME="Generator"CONTENT=" MicrosoftFrontPage5.0">
<METANAME="Author"CONTENT="">
<METANAME="Keywords"CONTENT="">
<METANAME="Description"CONTENT="">
</HEAD>
<BODY>
</BODY>
</HTML>
After operating the excel file, you need to output the file. To actually do it, use ASP's redirect to excel method. Sometimes it is opened directly in IE, and sometimes the "Download, Open, Save" window pops up. If you need to open it directly in IE, Just use FSO to load the excel file and then output it in IE.
<%
Dim Fso,FileExt,strFilePath,Mime
strFilePath = "f:aspxuexi.doc"
Set Fso=Server.CreateObject("Scripting.FileSystemObject")
FileExt = Fso.GetExtensionName(strFilePath)
Set fso=Nothing
Select Case FileExt
Case "doc"
Mime="Application/msword"
Case "xls"
Mime="Application/msexcel"
End Select
Call OutPut(strFilePath,Mime)
'####################################### ##############
Function OutPut(strFilePath,Mime)
Response.ContentType = Mime
Const adTypeBinary = 1
Set objStream = Server.CreateObject("ADODB.Stream")
objStream.Open
objStream.Type = adTypeBinary
objStream.LoadFromFile strFilePath
Response.BinaryWrite objStream.Read
objStream.Close
Set objStream = Nothing
End Function
'############################################### #####
%>
in practical applications. When the server is running the excel or ACCESS program, the client submits to create excel. The application object cannot succeed. In another situation, when a client submits a request, it does not end, and the request submitted by another client cannot succeed! There may be other solutions to this problem, but at least this is unstable.
There happened to be related successful examples on our department's intranet. When I dug them out, I found out that they were creating Excel on the client. application. This way, there will be no more conflicts on the server side. For the client, because it is running in the LAN, the client IE security level can be set low and the relevant active (Investigation), even if there is no setting, IE will pop up a warning window: "Are you allowed to run activeX?"
The implemented code is similar to the previous log, and is simply as follows:
<script language="vbscript">
set objExcelApp=CreateObject("Excel.Application")
objExcelApp.DisplayAlerts=true
objExcelApp.WorkBooks.Open(" http://XXX.XXX.XXX/XXX.xls ")
'The xls file of the complete network address. This file has been formatted and printed and saved on the server.
set objExcelBook=objExcelApp.ActiveWorkBook
set objExcelSheets=objExcelBook.Worksheets
set objExcelSheet=objExcelBook.Sheets(1)
'====Here are the statements for filling in data in excel cells. If data is extracted from the database, these statements can be generated by the background program. The columns in ASP are:
'For example: response.write "objExcelSheet.Range(""B2"").Value="""&rs("XXX")&""""
'or objExcelSheet.Range("B2").Value="<%=rs("XXX")%>"
objExcelSheet.Range("B2:k2").Value=Array("Week1","Week2"," Week3","Week4","Week5","Week6","Week7")
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="Other"
'==================
'objExcelApp.Quit
'set objExcelApp=Nothing
</script>
In the above code
'objExcelApp.Quit
'set objExcelApp=Nothing
is used as a cancellation, because do not close excel.applicaition here, otherwise excel will be closed after the data is filled in. At this time, the excel file opened on the client needs to be modified or printed by the client. At the same time, the objexcelapp object also has methods for printing settings and entering the print preview interface. Please refer to relevant excel related information.