Excel export using ASP

Excel export using ASP

<%

Dim S, SQL, filename, FS, myfile, X

Set FS = server. Createobject ("scripting. FileSystemObject ")
'-- Suppose you want to store the generated Excel file 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 an Excel file
Set myfile = FS. createtextfile (filename, true)

'Set rs = server. Createobject ("ADODB. recordset ")
'-- Check the data you want to put in Excel from the database.
'SQL = "select * From tb_execl order by ID DESC"
'Rs. Open SQL, Conn
Starttime = request ("starttime ")
Endtime = request ("endtime ")
Startendtime = "addtime between #" & starttime & "00:00:00 # And #" & endtime & "23:59:59 #"

Strsql = "select * From ljphs200"
Set rstdata into conn.exe cute (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

'-- Write the column name of the table to excel first
Myfile. writeline strline

Do while 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 "the Excel file is generated successfully. Click <a href ="/"order.xls" "target =" _ blank ""> download </a>! "

Rstdata. Close
Set rstdata = nothing
Conn. Close
Set conn = nothing
%>

Import exce to SQL
<%
Dim patnnow
Patnnow = "uploadfile/ultrawebgrid1.xls"

'================================== ASP read Excel note ====================== ====================
'I) Think Of The xls file (book) generated by excel97 or excel2000 as a database, and each Worksheet (sheet) in it as a database table.
Ii) ADO assumes the name of the first row in Excel. Therefore, you must include the content of the first row in the defined range.
'III) the row title (field name) in Excel cannot contain numbers. The Excel driver may encounter errors in this case. For example, your line title is "f1"
'Iiii) If a column in your Excel worksheet contains both text and numbers, the ODBC driver of Excel cannot process the data type of this row,
'You must ensure that the data type of this column is consistent
'================================================ ================================

Dim connxls, driver, dbpath, RS
'Create a connection object
Set connxls = server. Createobject ("ADODB. Connection ")
Driver = "driver = {Microsoft Excel Driver (*. xls )};"
Dbpath = "DBQ =" & server. mappath (patnnow)
'Call the Open Method to open the database
Connxls. Open driver & dbpath

'Dsn connection method
'Connxls. Open "DSN = test"
'Note that the table name must be written in the following format: "[Table Name $ ]"
SQL = "select * from [sheet1 $]"
Set rs = connxls. Execute (SQL)

If Rs. EOF and Rs. bof then
Response. Write "<br> no data is found !! "
Else
Do while not Rs. EOF
Conn.exe cute "insert into yufufei (haoma, yuenow, yuenowtime, yueold, yueoldtime, state) values ('" & RS ("Number ")&"', '"& RS (" Current Balance (points) ") &"', '"& RS (" current balance time ")&"', '"& RS (" last balance (minute) ") &"', '"& RS (" last balance time ")&"', '"& RS (" Current status ")&"')"
'Response. Write RS ("Access Number") & "<br>"
Rs. movenext
Loop
End if

Rs. Close
Set rs = nothing
Connxls. Close
Set connxls = nothing
'Response. Write "successful! "

Conn. Close
Set conn = nothing

%>

# ASP Column