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 ("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& "\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")
' XLS file with full network address, this file has been formatted and printed and stored on the server
Set Objexcelbook=objexcelapp.activeworkbook
Set Objexcelsheets=objexcelbook.worksheets
Set Objexcelsheet=objexcelbook.sheets (1)
' = = = = = Here is a statement that fills out data for an Excel cell, and if you extract the data from the database, the statements are 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 ("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 preceding code
' Objexcelapp.quit
' Set objexcelapp=nothing
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.
At the same time the Objexcelapp object also has the printing setup and enters the print preview interface and so on the method, may refer to the related Excel related material.
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
