One way to take advantage of Excel in ASP is to link an Excel file as a database, and then do something similar to an Access database operation. But this method is not always useful, and Excel is not a relational database. For a fixed format, there are complex cell merges in this format, border line style, there are patterns, there are formulas between the cells and so on, I think the most easy to understand is to open a template in the background of the file has been set up, and then insert the data where needed, save, output ...
The method mentioned here is to create an Excel object directly, which makes it easier to perform various operations on the Excel document in the background.
The point that the server side must also set is the operation permissions of the COM component. Type "DCOMCNFG" at the command line, then go to the COM component Configuration interface, select MicrosoftExcel, click the Properties button, and select all three single options to customize, and edit everyone to join all permissions. Restart the server when the save is complete.
It's important. No other computers will be able to complete the creation of Excel objects without setting them up.
But there is also a drawback to this approach, in my actual operation, if there is already open Excel file on the server, and then execute the same file, there will be an error. I can't figure out why. There may be some places that are not set up.
In addition, the instance code in the article referenced above is not fully formatted, many of these lines and the space format is not accurate, if the code integrity, as long as the copy code, you can run successfully, and then slowly research changes, it is easy to get started. The revised code is as follows (delete the part of the diagram in which it was drawn):
Program code:
<% 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") Objexcelsheet.range ("B3:k3"). Value=array ("The", "" "," 5 "," 9 "," 7 "," the "," the "," Si "," si "," ten ") Objexcelsheet.range (" B4:k4 "). Value=array ("Ten", "Ten", "8", "" "," "," "", "", "", "" Ten "," Ten ") Objexcelsheet.range (" B5:k5 "). Value=array ("A", "3", "The" "," "," "," "," "5", "1", "the", "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& "\temp\excel.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 the operation of the Excel file needs to output the file, the actual get, with ASP redirect to Excel method, sometimes in IE directly open, sometimes pop-up "Download open save" window. If you need to open it directly in IE, do not put the FSO in the Excel file, Then output in IE.
Program code:
<%
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 Excel or Access programs, the client commits the build Excel.Application object unsuccessfully. In another case, when a client submits a request and does not end, the request submitted by another client cannot succeed! There may be other solutions to the problem, but at least it is unstable.
There are some successful examples of our department's internal network, dug up to see, originally is in the client to establish excel.application. This way, the server side will not conflict. For clients, because they are running on a local area network, the client IE Security level can be set low, and the associated ActiveX is allowed to run ( is in IE tool options to all kinds of things related to security are set to allow, the specific which is not to be investigated, even if there is no setting, IE will also pop-up warning window: "Is it allowed to run ActiveX?"
The code implemented is the same as the previous log, as follows:
Program code:
<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 full network address, this file has been formatted and printed, saved on the server set Objexcelbook=objexcelapp.activeworkbook Set Objexcelsheets=objexcelbook.worksheets Set objExcelSheet= Objexcelbook.sheets (1) ' = = = = = = = = Here is the statement that fills in the data for the Excel cell, if the data is extracted from the database, the statements can be generated by the background program, as shown in asp: ' such as: 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 ("The", "" "," 5 "," 9 "," 7 "," the "," the "," Si "," si "," ten ") Objexcelsheet.range (" B4:k4 "). Value=array ("Ten", "Ten", "8", "" "," "," "", "", "", "" Ten "," Ten ") Objexcelsheet.range (" B5:k5 "). Value=array ("A", "3", "The" "," "," "," "," "5", "1", "the", "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 preceding code
' Objexcelapp.quit
As a cancellation, because you do not close excel.applicaition here, or Excel shuts down when the data is filled out. The Excel file opened on the client needs to be modified or printed by the customer.
The above is the use of ASP output Excel file all the process, I hope to help you learn.
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
