建站代码网

热门标签

用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(); %>