asp and javascript: sql server export data to csv and to xls

灰太狼 2021-12-13 00:51 145阅读 0赞

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

转载于:https://www.cnblogs.com/geovindu/p/3380947.html