ASP直接输出CSV格式文件
用法示例:
由于服务器限制,实际使用时请将查询语句s elect的空格删掉
export.asp
list.asp
数据库Access.mdb
1、如果字段里面带双引号需要用 chr(34) 包括起来
2、字段里面有html代码的话 需要用replace(str,vbcrlf,"") 替换掉 字段里面 vbcrlf 代码
由于服务器限制,实际使用时请将查询语句s elect的空格删掉
export.asp
程序代码
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<%
Response.Buffer = true
Response.Charset = "UTF-8"
Session.Codepage = 65001
Session.Timeout = 1440
Server.Scripttimeout = 9999
Dim DBName,Conn
DBName="Access.mdb" '定义数据库路径及名称
SET Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(DBName)
DIM ID,strSQL,strFields
DIM RST
DIM I
ID=request.querystring("id") '这里只做针对具体id进行csv输出,其他自行修改即可
if isnull(ID) or not isNumeric(ID) then ID=0 '对于id并未做严格检查,防止注入请自行过滤
strSQL="select ID,NAME,AGE FROM EMPLOYEES where id="&ID&"" 'strSQL 要导出的SQL查询语句
strFields="编号,姓名,年龄" 'strFields 字段名称列表,如果为空字符,则使用SQL语句中的字段名
strSQL=replace(strSQL,"%","%") '解决%号丢失问题, 参数中把%变成全角的,不然全丢失
strSQL=replace(strSQL,"#","#") '解决#号丢失问题
'Response.Write(strSQL &VBCRLF)
SET RST = CONN.EXECUTE(strSQL)
IF RST.EOF THEN
Response.Write("没有可导出数据!")
response.end
Else
Session.Codepage = 936
Response.AddHeader "Content-Disposition","attachment;filename=exportdata.csv;" '这里自定义导出的文件名
Response.ContentType = "application/octet-stream"
IF LEN(strFields)>0 THEN
Response.Write replace(strFields,"ID","id")
ELSE
FOR I =0 TO RST.FIELDS.COUNT-1
'strFields=strFields &RST.FIELDS(I).NAME & ","
'以ID(大写)内容开头的文件
strFields=strFields & replace(RST.FIELDS(I).NAME,"ID","id") & ","
NEXT
strFields=LEFT(strFields,LEN(strFields)-1)
Response.Write(strFields)
END IF
Response.Write(vbcrlf)
Response.Write(RST.GetString(,,","))
Response.End
END IF
RST.CLOSE:SET RST=Nothing
%>
<%
Response.Buffer = true
Response.Charset = "UTF-8"
Session.Codepage = 65001
Session.Timeout = 1440
Server.Scripttimeout = 9999
Dim DBName,Conn
DBName="Access.mdb" '定义数据库路径及名称
SET Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(DBName)
DIM ID,strSQL,strFields
DIM RST
DIM I
ID=request.querystring("id") '这里只做针对具体id进行csv输出,其他自行修改即可
if isnull(ID) or not isNumeric(ID) then ID=0 '对于id并未做严格检查,防止注入请自行过滤
strSQL="select ID,NAME,AGE FROM EMPLOYEES where id="&ID&"" 'strSQL 要导出的SQL查询语句
strFields="编号,姓名,年龄" 'strFields 字段名称列表,如果为空字符,则使用SQL语句中的字段名
strSQL=replace(strSQL,"%","%") '解决%号丢失问题, 参数中把%变成全角的,不然全丢失
strSQL=replace(strSQL,"#","#") '解决#号丢失问题
'Response.Write(strSQL &VBCRLF)
SET RST = CONN.EXECUTE(strSQL)
IF RST.EOF THEN
Response.Write("没有可导出数据!")
response.end
Else
Session.Codepage = 936
Response.AddHeader "Content-Disposition","attachment;filename=exportdata.csv;" '这里自定义导出的文件名
Response.ContentType = "application/octet-stream"
IF LEN(strFields)>0 THEN
Response.Write replace(strFields,"ID","id")
ELSE
FOR I =0 TO RST.FIELDS.COUNT-1
'strFields=strFields &RST.FIELDS(I).NAME & ","
'以ID(大写)内容开头的文件
strFields=strFields & replace(RST.FIELDS(I).NAME,"ID","id") & ","
NEXT
strFields=LEFT(strFields,LEN(strFields)-1)
Response.Write(strFields)
END IF
Response.Write(vbcrlf)
Response.Write(RST.GetString(,,","))
Response.End
END IF
RST.CLOSE:SET RST=Nothing
%>
list.asp
程序代码
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<%
Response.Buffer = true
Response.Charset = "UTF-8"
Session.Codepage = 65001
Session.Timeout = 1440
Server.Scripttimeout = 9999
Dim DBName,Conn
DBName="Access.mdb" '定义数据库路径及名称
SET Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(DBName)
DIM strSQL,strFields
DIM RST
DIM I
strSQL="select ID,NAME,AGE FROM EMPLOYEES" 'strSQL 要导出的SQL查询语句
SET RST = CONN.EXECUTE(strSQL)
IF RST.EOF THEN
Response.Write("没有可导出数据!")
response.end
Else
do while not rst.eof
response.write("<a href=""export.asp?id="&rst(0)&""" target=""_blank"">点击导出 编号:"&rst(0)&" 姓名:"&rst(1)&" 年龄:"&rst(2)&"岁的小朋友资料到CSV</a><br />")
rst.movenext
loop
END IF
RST.CLOSE:SET RST=Nothing
%>
<%
Response.Buffer = true
Response.Charset = "UTF-8"
Session.Codepage = 65001
Session.Timeout = 1440
Server.Scripttimeout = 9999
Dim DBName,Conn
DBName="Access.mdb" '定义数据库路径及名称
SET Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(DBName)
DIM strSQL,strFields
DIM RST
DIM I
strSQL="select ID,NAME,AGE FROM EMPLOYEES" 'strSQL 要导出的SQL查询语句
SET RST = CONN.EXECUTE(strSQL)
IF RST.EOF THEN
Response.Write("没有可导出数据!")
response.end
Else
do while not rst.eof
response.write("<a href=""export.asp?id="&rst(0)&""" target=""_blank"">点击导出 编号:"&rst(0)&" 姓名:"&rst(1)&" 年龄:"&rst(2)&"岁的小朋友资料到CSV</a><br />")
rst.movenext
loop
END IF
RST.CLOSE:SET RST=Nothing
%>
数据库Access.mdb
程序代码
Access.mdb{
EMPLOYEES{
ID 自动编号
NAME 文本
AGE 数字
}
}
EMPLOYEES{
ID 自动编号
NAME 文本
AGE 数字
}
}
1、如果字段里面带双引号需要用 chr(34) 包括起来
2、字段里面有html代码的话 需要用replace(str,vbcrlf,"") 替换掉 字段里面 vbcrlf 代码
评论