用ASP生成Excel文件

用asp生成Excel文件不是像我认为的那么容易。我想这小段代码应该能帮助大家找到输出数据到csv和xls的方法。



<%@LANGUAGE="VBSCRIPT" CODEPAGE="936"%> <meta http-equiv="Content-Type" content="text/html; charset=gb2312" /> <% response.charset="gb2312" session.codepage=936 %> <%Response.expires = -1%> <%Response.Buffer = true%> <% dim s,sql,filename,fs,myfile,x,act,stime act=request("act") IF act="edown" THEN stime=request("stime") ac_type=request("ac_type") if stime="" or ac_type="" then Response.Write "<SCRIPT language=JavaScript>alert('请选择日期和激活码类型!');javascript:history.go(-1)</SCRIPT>" end if if ac_type="1" then file_name=stime&"一档" elseif ac_type="2" then file_name=stime&"二档" elseif ac_type="3" then file_name=stime&"三档" end if 'Response.Charset = "gb2312" Response.ContentType = "application/octet-stream" Response.ContentType = "application/vnd.ms-excel" Response.AddHeader "Content-Disposition", "attachment; filename=" + file_name + ".xls" Response.ContentType = "application/download" %> <!--#include file="inc/Conn.asp" --> <html> <body> <table border="1"> <tr> <td>激活码</td> <td>档次</td> </tr> <% strSql = "select ac_num from activation_code where ac_state=0 and ac_type='"&ac_type&"' and ac_addtime = #"&stime&"#" Set rstData =conn.execute(strSql) if not rstData.EOF and not rstData.BOF then while not rstData.eof %> <tr> <td><%=rstData("ac_num")%></td> <td> <% if left(rstData("ac_num"),1)="1" then response.Write("一档") end if if left(rstData("ac_num"),1)="2" then response.Write("二档") end if if left(rstData("ac_num"),1)="3" then response.Write("三档") end if %> </td> </tr> <% rstData.movenext wend %> </table> </body> <% else Response.Write "<SCRIPT language=JavaScript>alert('没有相关激活码信息!');window.location.href ='excel.asp'</SCRIPT>" end if rstData.Close set rstData = nothing Conn.Close Set Conn = nothing END IF %> </html>

下面是其他办法


<%@LANGUAGE="JAVASCRIPT" CODEPAGE="65001"%> <% function getData(connectionString, sql){ var result = null; var adStateOpen = 1; var connection = new ActiveXObject("ADODB.CONNECTION"); try{ connection.Open(connectionString); } catch(e1){ return null; } if (connection.State !== adStateOpen) { return null; } try{ var recordset = connection.Execute(sql); } catch(e2){ return null; } if (!recordset.EOF) { result = recordset.GetRows().toArray(); recordset.Close(); } recordset = null; connection.Close(); connection = null; return result; } function writeCsvHttpHeaders(filename){ Response.ContentType = "text/csv"; Response.Charset = "utf-8"; Response.AddHeader("Content-Disposition", "attachment; filename="+filename+".csv"); } function writeXlsHttpHeaders(filename){ Response.ContentType = "application/vnd.ms-excel"; Response.Charset = "utf-8"; Response.AddHeader("Content-Disposition", "attachment; filename="+filename+".xls"); } function getXlsStart(){ return "" + "<html>\n" + "<head>\n" + "<meta http-equiv=\"Content-Type\" " + "content=\"text/html; charset=UTF-8\">\n" + "<style type=\"text/css\">\n" + "html, body, table {\n" + " margin: 0;\n" + " padding: 0;\n" + " font-size: 11pt;\n" + "}\n" + "table, th, td { \n" + " border: 0.1pt solid #D0D7E5;\n" + " border-collapse: collapse;\n" + " border-spacing: 0;\n" + "}\n" + "</style>\n" + "</head>\n" + "<body>\n" + "<table>\n" + ""; } function getXlsEnd(){ return "" + "</table>\n" + "</body>\n" + "</html>" + ""; } function csvEscape(val){ if (typeof val === "number") { return val.toString(10).replace(".", ","); } else if (typeof val === "string") { if (val.indexOf("\"") !== -1) { return "\""+val.replace(/"/g, "\"\"")+"\""; } else if (val.indexOf(";") !== -1) { return "\""+val+"\""; } else { return val; } } else if (val === null) { return "#NULL#"; } else if (val === undefined) { return "#UNDEFINED#"; } else { return "#ERROR#"; } } function writeCsv(filename, data, columnCount){ writeCsvHttpHeaders(filename); // utf-8 BOM (very important for special characters) Response.Write("\uFEFF"); for (var i=0, il=data.length; i<il; i+=columnCount) { for (var j=0; j<columnCount; j++) { Response.Write(csvEscape(data[i+j])); if (j !== columnCount-1) { Response.Write(";"); } } Response.Write("\n"); // prevent Response Buffering Limit Exceeded if (i % 1000 === 0) { Response.Flush(); } } } function xlsEscape(val){ if (typeof val === "number") { return val.toString(10).replace(".", ","); } else if (typeof val === "string") { return Server.HTMLEncode(val); } else if (val === null) { return "#NULL#"; } else if (val === undefined) { return "#UNDEFINED#"; } else { return "#ERROR#"; } } function writeXls(filename, data, columnCount){ writeXlsHttpHeaders(filename); Response.Write(getXlsStart()); for (var i=0, il=data.length; i<il; i+=columnCount) { Response.Write("<tr>"); for (var j=0; j<columnCount; j++) { Response.Write("<td>"); Response.Write(xlsEscape(data[i+j])); Response.Write("</td>"); } Response.Write("</tr>\n"); // prevent Response Buffering Limit Exceeded if (i % 1000 === 0) { Response.Flush(); } } Response.Write(getXlsEnd()); } function main(){ var filetype = Request.QueryString("filetype")(); var connectionString = "Provider=SQLOLEDB.1;" + "Data Source=LAPTOP\\SQLEXPRESS;" + "User ID=internal;" + "Password=internal;" + "Initial Catalog=trees_in_sql"; var sql = "" + "SELECT id \n" + ", name \n" + "FROM People \n" + ";"; var filename = "filename"; var columnCount = 2; var data = getData(connectionString, sql); if (data !== null) { Response.Clear(); if (filetype === "csv") { writeCsv(filename, data, columnCount); } else { writeXls(filename, data, columnCount); } } else { Response.Write("Error, no data found"); } Response.End(); } main(); %>

相关内容推荐

获得当前页面地址和来路地址

当前页面地址: html="http://" html=htmlRequest.ServerVariables("Server_Name") html=htmlRequest.ServerVariables("URL") ifrequest.ServerVariables("QUERY_STRING")""then html=html"?"Request.ServerVariables("QUERY_STRING") endif session("

实现多条件模糊查询SQL语句

很多网友问到如何写模糊查询语句和多条件查询,这里我整理了一下,假设以姓名、性别、电话号...作为数据库中的字段名。 通常写一个简单的模糊查询的SQL语句格式可以如下例: sq

怎样用ASP压缩文件

多数的时候我们需要压缩文件,保存到特定的位置,或者你可以使用它实现所有你想要的效果。 我们需要服务器支持WScript.Shell-多数的站长都不会允许这样的。 这里你可以获取下载链接

html中用js调用ASP文件,实现静态页面动态显示

html中用js调用ASP文件,实现静态页面动态显示,比如HMTL文章的动态新闻评论等 ---简单版[调用代码,把他放哪儿,就在哪儿显示]-------- script language="javascript" src="asdf.asp"/script-----asdf.asp文

用ASP生成Excel文件

用asp生成Excel文件不是像我认为的那么容易。我想这小段代码应该能帮助大家找到输出数据到csv和xls的方法。 %@LANGUAGE="VBSCRIPT" CODEPAGE="936"%meta http-equiv="Content-Type" content="text/html; charset=

返回
顶部