导入EXCEL 和TXT(源自镜子)

下面这段代码可以生成Excel文件，已调试
<!--#include file="inc/conn.asp"-->
<%
dim strLine,responsestr
strLine=""
Set fso = server.CreateObject("scripting.filesystemobject")
'--创建EXCEL文件
set myfile = fso.CreateTextFile(Server.MapPath("download.xls"),true)

Set Objrs=strDcndb_Login.ExeCute("select * from data_administrator")
if not Objrs.EOF then
'--将表的列名先写入EXCEL 按数据库表名生成
For each x in Objrs.fields
strLine = strLine & x.name & chr(9)
Next
'--将表的列名先写入EXCEL 按自己设定的表名生成
strLine="国企业(网络公司_05_12)"
myfile.writeline strLine
myfile.writeline
strLine=" 编号"& chr(9) &" 姓名"& chr(9) &" 日期"& chr(9) &" 星期"& chr(9) &" 班次"& chr(9) &" 班类型"& chr(9) &" 上下班"& chr(9) &" 打卡时间"& chr(9) &" 说明"& chr(9) &" 备注"

myfile.writeline strLine
Do while Not Objrs.EOF
strLine=""
for each x in Objrs.Fields
strLine = strLine &" "& x.value & chr(9)
next
'--将表的数据写入EXCEL
myfile.writeline strLine
Objrs.MoveNext
loop
else
response.write "无数据"
end if
Objrs.Close
set Objrs = nothing
Response.ContentType="application/x-msdownload"
Response.Redirect "download.xls"
%>

 

 

生成txt文件源代码（已经验证可用）：
<!--#include file="inc/conn.asp"-->
<%
dim rs,sql,filename,fso,myfile,x

Set fso = server.CreateObject("scripting.filesystemobject")
filename = Server.MapPath("download.txt")
if fso.FileExists(filename) then
fso.DeleteFile(filename)
end if
'--创建txt文件
set myfile = fso.CreateTextFile(filename,true)

Set rs = Server.CreateObject("ADODB.Recordset")
sql = "select * from userfile"
rs.Open sql,conn,1,1
if rs.EOF and rs.BOF then
response.write "无数据"
else
dim strLine,responsestr
strLine=""
'--将表的列名先写入txt
For each x in rs.fields
strLine = strLine & x.name & chr(9)
Next
myfile.writeline strLine
Do while Not rs.EOF
strLine=""
for each x in rs.Fields
strLine = strLine & x.value & chr(9)
next
'--将表的数据写入txt
myfile.writeline strLine
rs.MoveNext
loop
end if
rs.Close
set rs = nothing
Response.ContentType="application/x-msdownload"
Response.Redirect "download.txt"

 

'===============
'ASP读取Excel数据Dim cn,oConn,connstr
'打开XLS.
Set cn = Server.CreateObject("ADODB.Connection")
cn.Provider = "Microsoft.Jet.OLEDB.4.0 "
cn.ConnectionString = "Data Source=" & Server.MapPath("22.xls") & ";" & _
"Extended Properties=Excel 8.0;"
cn.Open
'打开MDB.

'读取数据.注意Excel的字段名要是英文，不是会错误
set rsRead=server.CreateObject("ADODB.Recordset")
rsRead.Open "select * from [Sheet4$]",cn,1,1
do until rsRead.EOF
'写入数据库.
strDcndb.execute "insert into data_work(user_name,date_IN,M_week,M_work,M_work_type,card_time,M_explains,M_desc)values('"& rsRead.Fields(" user_name")&"','" &rsRead.Fields(" date_IN") & "','" &rsRead.Fields(" Week") & "','" &rsRead.Fields(" work") & "','" &rsRead.Fields(" work_type") & "','" &rsRead.Fields(" card_time") & "','" &rsRead.Fields(" Explains") & "','" &rsRead.Fields(" desc") & "')"

rsRead.MoveNext
loop
%>