2 ways to export data to excel in ASP _asp programming

When we do the project, we often export the database data to Excel, and many ASP users don't know how to write it.

Here Minkai summed up two ways to export Excel, I hope to help you.

Method One: Use the Excel component

<% set Rs=server.createobject ("Adodb.recordset") sql= "select * from Mkusers" Rs.Open sql,objconn,1,1 set Excelapp =Cr
Eateobject ("Excel.Application") ExcelApp.Application.Visible = True Set Excelbook = ExcelApp.Workbooks.Add 
Excelbook.worksheets (1). Cells (1,1). Value = "User table" Excelbook.worksheets (1). Cells (2,1). Value = "User ID" 
Excelbook.worksheets (1). Cells (2,2). Value = "Login Name" Excelbook.worksheets (1). Cells (2,3). Value = "real name"  Excelbook.worksheets (1). Cells (2,4). Value = "Password" cnt =3 do, not rs.eof excelbook.worksheets (1). Cells (cnt,1). Value = RS ("Provinceid") excelbook.worksheets (1). Cells (cnt,2). Value = rs ("province") excelbook.worksheets (1). Cells (cnt,3). Value = rs ("flag") excelbook.worksheets (1). Cells (cnt,4). Value = RS ("id") Rs.movenext cnt = CInt (CNT) + 1 loop excelboo K.saveas "D:\yourfile.xls" is the data exported after the D inventory into a file ExcelApp.Application.Quit ' export exit Excel Set Excelapp = Nothing ' logoff Excel Object%><% Set Rs=server.createobject ("Adodb.recordset") sql= "select * from Mkusers"Rs.Open sql,objconn,1,1 set Excelapp =createobject (" Excel.Application ") ExcelApp.Application.Visible = True Set Excelbook = ExcelApp.Workbooks.Add excelbook.worksheets (1). Cells (1,1). Value = "User table" Excelbook.worksheets (1). Cells ( 
2, 1). Value = "User Number" Excelbook.worksheets (1). Cells (2,2). Value = "Login Name" Excelbook.worksheets (1). Cells (2,3). Value = "real name" Excelbook.worksheets (1). Cells (2,4). Value = "Password" cnt =3 do, not rs.eof excelbook.worksheets (1). Cells (cnt,1). Value = RS ("Provinceid") excelbook.worksheets (1). Cells (cnt,2). Value = rs ("province") excelbook.worksheets (1). Cells (cnt,3) . Value = rs ("flag") excelbook.worksheets (1). Cells (cnt,4). Value = RS ("id") Rs.movenext cnt = CInt (CNT) + 1 loop Excelbo Ok. 
SaveAs "D:\yourfile.xls" is the data exported after the D inventory into a file ExcelApp.Application.Quit ' export exit Excel Set Excelapp = Nothing ' Logoff Excel object
 %>

Method Two: Using File components

<% Dim S,sql,filename,fs,myfile,x Set fs = server. CreateObject ("Scripting.FileSystemObject") '--suppose you want the generated Excel file to be stored as follows filename = Server.MapPath ("Order.xls") '-- If the original Excel file exists, delete it if FS. FileExists (filename) then fs. DeleteFile (filename) End If '--Create Excel file Set MyFile = fs. CreateTextFile (filename,true) starttime = Request ("StartTime") Endtime = Request ("Endtime") Startendtime = "Addtime B" Etween # "& StartTime &" 00:00:00# and # "& Endtime &" 23:59:59# "strSQL =" SELECT * from Mksuers "Set rstd  ATA =conn.execute (strSQL) if not rstdata.eof and not Rstdata.bof then Dim trline,responsestr strline= "" for Each x in Rstdata.fields strLine = strLine & X.name & Chr (9) Next '--writes the table's column name first to Excel Myfile.writel  Ine strLine do and not rstdata.eof strline= ' for each x in Rstdata.fields strLine = StrLine & X.value & Chr (9) Next MyFile.WriteLine strLine Rstdata.movenext
   Loop End If Response.Write "Generate Excel file successfully, click <a href="/"rel=" external nofollow "Order.xls" "target=" "_blank" "" Download
 "Rstdata.close Set rstdata = Nothing Conn.close set Conn = Nothing%>

It can be seen that the first method is directly exported is an Excel file, and the second method is everywhere is a text file, but the suffix name changed to XLS.

And then it looks like it's Excel.

The efficiency of the first method is not as high as that of the second method, and it is difficult to control.

It is recommended that you use the second method of writing files for Excel operations.