Home>Network programming tutorial>ASP tutorial

Explanation of using ASP to output excel file instance

Author：Eve Cole Update Time：2025-02-25 00:48:02

The main content of this article is to use ASP to output excel file examples. Interested friends can refer to it.

One way to use excel in Asp is to link the excel file as a database, and the operation is similar to the access database operation. However, this method is not always useful, and it should be that excel is not a relational database. For a fixed format, this format contains complex cell merging, border line styles, patterns, formula relationships between cells, etc. I think the easiest thing to understand is to open an existing setting in the background Good template file, then insert data where needed, save, output...

The method mentioned here is to directly create an excel object, which can perform various operations on the excel document more conveniently in the background.

The server side also needs to set the operation permissions of COM components. Type DCOMCNFG on the command line, and enter the COM component configuration interface. Select Microsoft Excel and click the Properties button. Select all three single options to customize. Add Everyone to all permissions during editing. Restart the server after saving.

It is very important. If you don't set it up, other computers will not be able to complete the creation of excel objects.

However, this method also has a drawback. In my actual operation, if there is an open excel file on the server and the same file is executed, an error will occur. I still don’t understand why. There may be some places that have not been set up properly.

In addition, the example code format in the article quoted above is not very complete, and many of the line breaks and spaces are inaccurate. If the code is complete, as long as the code is copied, it can run successfully, and then slowly research and modify it, it will be easy to get started. Now the modified code is as follows (the part where the chart is drawn has been deleted):

Program code:

  1. <%
  2. OnErrorResumeNext
  3. strAddr=Server.MapPath(.)
  4. setobjExcelApp=CreateObject(Excel.Application)
  5. objExcelApp.DisplayAlerts=false
  6. objExcelApp.Application.Visible=false
  7. objExcelApp.WorkBooks.Open(strAddr&/Templet/Null.xls)
  8. setobjExcelBook=objExcelApp.ActiveWorkBook
  9. setobjExcelSheets=objExcelBook.Worksheets
  10. setobjExcelSheet=objExcelBook.Sheets(1)
  12. objExcelSheet.Range(B2:k2).Value=Array(Week1,Week2,Week3,Week4,Week5,Week6,Week7)
  13. objExcelSheet.Range(B3:k3).Value=Array(67,87,5,9,7,45,45,54,54,10)
  14. objExcelSheet.Range(B4:k4).Value=Array(10,10,8,27,33,37,50,54,10,10)
  15. objExcelSheet.Range(B5:k5).Value=Array(23,3,86,64,60,18,5,1,36,80)
  16. objExcelSheet.Cells(3,1).Value=InternetExplorer
  17. objExcelSheet.Cells(4,1).Value=Netscape
  18. objExcelSheet.Cells(5,1).Value=Other
  19. objExcelSheet.Range(b2:k5).Select
  21. SaveAs(strAddr&/Temp/Excel.xls)
  22. objExcelApp.Quit
  23. setobjExcelApp=Nothing
  24. %>
  25. <!DOCTYPEHTMLPUBLIC-//W3C//DTDHTML4.0Transitional//EN>
  26. <HTML>
  27. <HEAD>
  28. <TITLE>NewDocument</TITLE>
  29. <METANAME=GeneratorCONTENT=MicrosoftFrontPage5.0>
  30. <METANAME=AuthorCONTENT=>
  31. <METANAME=KeywordsCONTENT=>
  32. <METANAME=DescriptionCONTENT=>
  33. </HEAD>
  34. <BODY>
  35. </BODY>
  36. </HTML>

After operating the excel file, you need to output the file. In fact, use ASP's redirect to the excel method. Sometimes it is opened directly in IE, and sometimes it is a pop-up download and open the save window. If you need to open directly in IE, don't put it Load the excel file with FSO and then output it in IE.

Program code:

  1. <%
  3. DimFso, FileExt, strFilePath, Mime
  4. strFilePath=f:/aspxuexi.doc
  5. SetFso=Server.CreateObject(Scripting.FileSystemObject)
  6. FileExt=Fso.GetExtensionName(strFilePath)
  7. Setfso=Nothing
  9. SelectCaseFileExt
  10. Casedoc
  11. Mime=Application/msword
  12. Casexls
  13. Mime=Application/msexcel
  14. EndSelect
  16. CallOutPut(strFilePath,Mime)
  18. '################################################# #####
  19. FunctionOutPut(strFilePath,Mime)
  20. Response.ContentType=Mime
  21. ConstadTypeBinary=1
  22. SetobjStream=Server.CreateObject(ADODB.Stream)
  23. objStream.Open
  24. objStream.Type=adTypeBinary
  25. objStream.LoadFromFilestrFilePath
  26. Response.BinaryWriteobjStream.Read
  27. objStream.Close
  28. SetobjStream=Nothing
  29. EndFunction
  30. '################################################# #####
  31. %>

In actual application, when the server is running an excel or ACCESS program, the client submits the establishment of the excel.application object without success. In another case, when one client submits the request and does not end, the other client submits the request. The request cannot be successful either! Maybe there are other solutions to this problem, but at least it is unstable.

There are related successful examples on the internal network of our department. I dug it out and found out that it was the client to establish excel.application. In this way, the server side will not conflict anymore. For the client, because it is running on the local area network, the client IE security level is You can set low and allow the running of relevant activeX (that is, set all kinds of security-related things to allow in the IE tool options, and which specific item is not investigated). Even if it is not set, IE will pop up a warning window: Is it? Allow activeX to run?

The code implemented is similar to the previous log, and the simplest one is as follows:

Program code:

  1. <scriptlanguage=vbscript>
  3. setobjExcelApp=CreateObject(Excel.Application)
  4. objExcelApp.DisplayAlerts=true
  6. objExcelApp.WorkBooks.Open(http://XXX.XXX.XXX/XXX.xls)
  7. 'The xls file with the complete network address, this file has been formatted and printed, and saved on the server
  8. setobjExcelBook=objExcelApp.ActiveWorkBook
  9. setobjExcelSheets=objExcelBook.Worksheets
  10. setobjExcelSheet=objExcelBook.Sheets(1)
  11. '==== Here are statements that fill in data on excel cells. If data is extracted from the database, these statements can be generated by the background program, and the columns in ASP are shown:
  12. 'For example: response.writeobjExcelSheet.Range(B2).Value=&rs(XXX)&
  13. 'or objExcelSheet.Range(B2).Value=<%=rs(XXX)%>
  15. objExcelSheet.Range(B2:k2).Value=Array(Week1,Week2,Week3,Week4,Week5,Week6,Week7)
  16. objExcelSheet.Range(B3:k3).Value=Array(67,87,5,9,7,45,45,54,54,10)
  17. objExcelSheet.Range(B4:k4).Value=Array(10,10,8,27,33,37,50,54,10,10)
  18. objExcelSheet.Range(B5:k5).Value=Array(23,3,86,64,60,18,5,1,36,80)
  19. objExcelSheet.Cells(3,1).Value=InternetExplorer
  20. objExcelSheet.Cells(4,1).Value=Netscape
  21. objExcelSheet.Cells(5,1).Value=Other
  22. '========================
  24. 'objExcelApp.Quit
  25. 'setobjExcelApp=Nothing
  27. </script>

In the above code

  1. 'objExcelApp.Quit
  2. 'setobjExcelApp=Nothing

As a cancellation, do not close excel.applicaition here, otherwise excel will be closed after the data is filled. At this time, the excel file opened on the client needs to be modified or printed by the client.

The above is the entire process of using ASP to output excel files. I hope it will be helpful to everyone's learning.

Related Articles