asp and javascript: sql server export data to csv and to xls
<%@LANGUAGE="JAVASCRIPT" CODEPAGE="65001"%> <% //塗聚文 //20131021 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 ="Excel" ' Request.QueryString("filetype")(); var connectionString = "Provider=SQLOLEDB.1;" + "Data Source=188.68.218.86;" + "User ID=sa;" + "Password=2011intranet;" + "Initial Catalog=intranet"; var sql = "" + "SELECT * \n" '+ ", B_Name \n" '+ ", B_EnglishName \n" + "FROM branch \n" + ";"; var filename = "filename"; var columnCount = 3; 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(); %>
哲学管理(学)人生, 文学艺术生活, 自动(计算机学)物理(学)工作, 生物(学)化学逆境, 历史(学)测绘(学)时间, 经济(学)数学金钱(理财), 心理(学)医学情绪, 诗词美容情感, 美学建筑(学)家园, 解构建构(分析)整合学习, 智商情商(IQ、EQ)运筹(学)生存.---Geovin Du(涂聚文)