2 ways to export data to excel in asp

When we are working on projects, we often export the database data to excel, and many Asp users do not know how to write it.

Here Mingkai summarizes two methods to export excel, hoping it can help everyone.

Method 1: Use excel component

< %
set rs=("")
sql="select * from mkusers"
 sql,objconn,1,1
Set ExcelApp =CreateObject("")
 = True
Set ExcelBook = 
(1).cells(1,1).value ="User Table" 
(1).cells(2,1).value = "User Number" 
(1).cells(2,2).value = "Login Name" 
(1).cells(2,3).value = "Real Name" 
(1).cells(2,4).value = "password" 
cnt =3 
do while not  
(1).cells(cnt,1).value = rs("provinceid") 
(1).cells(cnt,2).value = rs("province") 
(1).cells(cnt,3).value = rs("flag") 
(1).cells(cnt,4).value = rs("id")
 
cnt = cint(cnt) + 1 
loop 
 "d:\"  'This is stored in D into a file after the data is exported
    'Exit after exportExcel
Set ExcelApp = Nothing   'Log outExcelObject
%>< %
set rs=("")
sql="select * from mkusers"
 sql,objconn,1,1
Set ExcelApp =CreateObject("")
 = True
Set ExcelBook = 
(1).cells(1,1).value ="User Table" 
(1).cells(2,1).value = "User Number" 
(1).cells(2,2).value = "Login Name" 
(1).cells(2,3).value = "Real Name" 
(1).cells(2,4).value = "password" 
cnt =3 
do while not  
(1).cells(cnt,1).value = rs("provinceid") 
(1).cells(cnt,2).value = rs("province") 
(1).cells(cnt,3).value = rs("flag") 
(1).cells(cnt,4).value = rs("id")
 
cnt = cint(cnt) + 1 
loop 
 "d:\"  'This is stored in D into a file after the data is exported
    'Exit after exportExcel
Set ExcelApp = Nothing   'Log outExcelObject
%>

Method 2: Use file components

< % 
dim s,sql,filename,fs,myfile,x 
 
Set fs = ("") 
'--Suppose you want to generateEXCELThe files are stored as follows 
filename = ("") 
'--If the originalEXCELIf the file exists, delete it 
if (filename) then 
   (filename) 
end  if 
'--createEXCELdocument 
set myfile = (filename,true) 
 
 
StartTime = Request("StartTime")
EndTime = Request("EndTime")
StartEndTime = "AddTime between #"& StartTime &" 00:00:00# and #"& EndTime &" 23:59:59#"
strSql = "select * from mksuers "
Set rstData =(strSql)
if not  and not  then 
 
   dim  trLine,responsestr 
   strLine="" 
   For each x in  
     strLine = strLine &  & chr(9) 
   Next 
 
'--Write the column names of the table firstEXCEL 
    strLine 
 
   Do while Not  
     strLine="" 
 
     for each x in  
       strLine = strLine &  &  chr(9) 
     next 
       strLine 
 
      
   loop 
 
end if 
  "generateEXCELdocument成功，Click<a href="/" rel="external nofollow" "" target=""_blank"">Download!"
 
set rstData = nothing

Set Conn = nothing
%>

It can be seen that the first method is to directly export the excel file, while the second method is to everywhere with text files, except that the suffix name is changed to xls.

Then it looks like excel.

After comparison, the efficiency of the first method is not as efficient as the second method, and it is not easy to control.

It is recommended that you use the second method of writing files to perform excel operations.