asp导出CSV格式数据

参数

strSQL 要导出的SQL查询语句

strFields 字段名称列表,如果为空字符,则使用SQL语句中的字段名

用法示例:

1:export.asp?strSQL=select * from table1

2:export.asp?strSQL=select ID,NAME,AGE FROM EMPLOYEES&strFields =编号,姓名,年龄

export.asp

<％
Response.Buffer = true
Response.AddHeader "Content-Disposition","attachment;filename=exportdata.csv;"
Response.CharSet = "bg2312"
Response.ContentType = "application/octet-stream"

Dim DBName,Conn
DBName="DATA/EPM.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=REQUEST("strSQL")
strSQL=replace(strSQL,"％","％") '解决％号丢失问题, 参数中把％变成全角的,不然全丢失
strSQL=replace(strSQL,"＃","#") '解决＃号丢失问题
strFields=REQUEST("strFields")

'Response.Write(strSQL &VBCRLF)

SET RST = CONN.EXECUTE(strSQL)

IF RST.EOF THEN
 Response.Write("没有可导出数据！")
 response.end
END IF

IF LEN(strFields)>0 THEN
 Response.Write replace(strFields,"ID","id") '防止出现SYLK文件打开提示
ELSE
 FOR I =0 TO RST.FIELDS.COUNT-1
 'strFields=strFields &RST.FIELDS(I).NAME & ","
 '防止出现SYLK文件打开提示
 '以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(,,","))

％>

1、如果字段里面带双引号需要用 chr(34) 包括起来

2、字段里面有html代码的话 需要用replace(str,vbcrlf,"") 替换掉 字段里面 vbcrlf 代码