Generating Excel files in ASP Classic is not as easy as I thought. I hope someone finds use in this little piece of code that shows a way to export data to csv and to xls.
<%@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(); %>
Resources:
@rave
you could try
td {mso-number-format:”\@”;}
is there any way to ensure all fields are text fields within the generated excel file? ie even if there is a number passed off, how can I ensure that it remains a text field?
Thanx