<! -- # Include file = "conn. asp" -->
<%
################## Dynamically generate an Excel table, write the content together ######################
%>
<HTML>
<Head>
<Meta content = "text/html; charset = gb2312" http-equiv = "Content-Type">
<Title> Generate an Excel file using ASP (after debugging and modification, you can write the custom display content of the Excel title) </title>
</Head>
<Body>
<A href = "index. asp? Act = make "> Generate an Excel file </a>
<HR size = 1 align = left width = 300px>
<%
If request ("act") = "make" then
'Else
####################### Start the main program ############## #####
Dim SQL, filepath, FS, myfile, X, Link
Set FS = server. Createobject ("scripting. FileSystemObject ")
'-- Suppose you want to store the generated Excel file as follows:
Temp = server. mappath ("index. asp") 'to obtain the path of the index. asp homepage.
Path = left (temp, Len (temp)-9) 'to obtain the path string.
Filename = "books.xls" 'specifies the Excel file name.
Filepath = Path & filename 'to generate the Excel file name and path.
'-- If the original Excel file exists, delete it.
If fs. fileexists (filepath) then
FS. deletefile (filepath) 'deletes an existing file with the same name.
End if
'-- Create an Excel file
Set myfile = FS. createtextfile (filepath, true)
Set rs = server. Createobject ("ADODB. recordset ")
'-- Check the data you want to put in Excel from the database.
SQL = "select * from book order by ID"
Rs. Open SQL, Conn, 1, 3
Recnum = Rs. recordcount 'get the number of records.
If Rs. EOF and Rs. bof then
Else
############### ####
Dim strline, responsestr
Strline = ""
Fieldnum = 0
Myfile. writeline CHR (9) & CHR (9) & "Excel title" 'the Excel title can be defined here
For each X in RS. Fields
Strline = strline & X. Name & CHR (9) 'chr (9) refers to the horizontal tab.
Fieldnum = fieldnum + 1' to obtain the number of fields.
Next
'-- Write the column name of the table to excel first
Myfile. writeline strline
Do while not Rs. EOF
Strline = ""
W = 0' defines a variable. You can perform custom operations to determine the variable.
For each X in RS. Fields
If W = 0 then
Strline = strline & "custom text" & CHR (9)
Else
Strline = strline & X. Value & CHR (9)
End if
W = W + 1
Next
'-- Write table data to excel
Myfile. writeline strline
Rs. movenext
Loop
End if
Rs. Close
Set rs = nothing
Conn. Close
Set conn = nothing
Set myfile = nothing
Set FS = nothing
'Read write information. This operation is successful locally and the remote prompt does not have 'createobject' permission.
Set objexcelapp = Createobject ("Excel. application ")
Objexcelapp. displayalerts = false' no warning is displayed
Objexcelapp. application. Visible = false' the interface is not displayed.
Objexcelapp. workbooks. Open (filepath)
Set objexcelbook = objexcelapp. activeworkbook
Set objexcelsheets = objexcelbook. worksheets
Set objexcelsheet = objexcelbook. Sheets (1)
Response. Write "<Table border = '1' style = 'border-collapse: Collapse 'bordercolor = '#000000'>"
For I = 1 to recnum + 1
Response. Write "<tr>"
For j = 1 to fieldnum
If I = 1 then
Response. write "<TD bgcolor = #006699> <font color = # ffffff>" & objexcelsheet. cells (I, j ). value & CHR (9) & "</font> </TD>"
Else
Response. Write "<TD>" & objexcelsheet. cells (I, j). Value & CHR (9) & "</TD>"
End if
Next
Response. Write "</tr>"
Next
Response. Write "</table>"
Objexcelapp. Quit 'must exit
Set objexcelapp = nothing
Link = "Congratulations! The report is generated successfully! <A href = "& filename &"> open the Excel file: "& filename &" </a> & nbsp; <a href = 'javascript: void (0) 'onclick = 'javascript: window. close (); '> close & nbsp; close </a>"
Response. Write Link
End if
%>
</Body>
</Html>
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
