网页端中将表格数据和JSON数据读取（导出）到本地的excel和csv文件中

来源：互联网发布：html5 加网络连接编辑：程序博客网时间：2024/05/21 07:08

最近实验室接了一个web端的项目，自己负责的模块需要完成导出文件的工作。一开始，要求的是导出表格中的内容到本地excel文件，之后增加了将JSON数据导出到本地CSV文件中。下面分别给出它们的源码。

将表格内容导出到本地excel文件

    var idTmr;    function getExplorer() {        var explorer = window.navigator.userAgent;        //ie        if (explorer.indexOf("MSIE") >= 0) {            return 'ie';        }        //firefox        else if (explorer.indexOf("Firefox") >= 0) {            return 'Firefox';        }        //Chrome        else if (explorer.indexOf("Chrome") >= 0) {            return 'Chrome';        }        //Opera        else if (explorer.indexOf("Opera") >= 0) {            return 'Opera';        }        //Safari        else if (explorer.indexOf("Safari") >= 0) {            return 'Safari';        }    }    function method1(tableid) {        if (getExplorer() == 'ie') {            var curTbl = document.getElementById(tableid);            var oXL = new ActiveXObject("Excel.Application");            var oWB = oXL.Workbooks.Add();            var xlsheet = oWB.Worksheets(1);            var sel = document.body.createTextRange();            sel.moveToElementText(curTbl);            sel.select();            sel.execCommand("Copy");            xlsheet.Paste();            oXL.Visible = true;            try {                var fname = oXL.Application.GetSaveAsFilename("Excel.xls", "Excel Spreadsheets (*.xls), *.xls");            } catch (e) {                print("Nested catch caught " + e);            } finally {                oWB.SaveAs(fname);                oWB.Close(savechanges = false);                //xls.visible = false;                oXL.Quit();                oXL = null;                //window.setInterval("Cleanup();",1);                idTmr = window.setInterval("Cleanup();", 1);            }        }        else {            tableToExcel(tableid)        }    }    function Cleanup() {        window.clearInterval(idTmr);        CollectGarbage();    }    var tableToExcel = (function () {        var uri = 'data:application/vnd.ms-excel;base64,',                template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>',                base64 = function (s) {                    return window.btoa(unescape(encodeURIComponent(s)))                },                format = function (s, c) {                    return s.replace(/{(\w+)}/g,                            function (m, p) {                                return c[p];                            })                }        return function (table, name) {            if (!table.nodeType) table = document.getElementById(table)            var ctx = {worksheet: name || 'Worksheet', table: table.innerHTML}            window.location.href = uri + base64(format(template, ctx))        }    })()

代码不是很难，比较好懂。

将JSON数据导出到本地CSV文件

var json2csv = function(JSONData, ReportTitle, ShowLabel) {            //If JSONData is not an object then JSON.parse will parse the JSON string in an Object            var arrData = typeof JSONData != 'object' ? JSON.parse(JSONData) : JSONData;            var CSV = '';            //Set Report title in first row or line            CSV += ReportTitle;            //This condition will generate the Label/Header            if (ShowLabel) {                var row = "";                //This loop will extract the label from 1st index of on array                for (var index in arrData[0]) {                    //Now convert each value to string and comma-seprated                    row += index + ',';                }                row = row.slice(0, -1);                //append Label row with line break                CSV += row + '\r\n';            }            //1st loop is to extract each row            for (var i = 0; i < arrData.length; i++) {                var row = "";                //2nd loop will extract each column and convert it in string comma-seprated                for (var index in arrData[i]) {                    row += '"' + arrData[i][index] + '",';                }                row.slice(0, row.length - 1);                //add a line break after each row                CSV += row + '\r\n';            }            if (CSV == '') {                alert("无效的数据");                return;            }            //Generate a file name            var fileName = "下载文件名";            //this will remove the blank-spaces from the title and replace it with an underscore            fileName += ReportTitle.replace(/ /g, "_");            //Initialize file format you want csv or xls            var uri = 'data:text/csv;charset=utf-8,' + encodeURI(CSV);            // Now the little tricky part.            // you can use either>> window.open(uri);            // but this will not work in some browsers            // or you will not get the correct file extension            //this trick will generate a temp <a /> tag            var link = document.createElement("a");            link.href = uri;            //set the visibility hidden so it will not effect on your web-layout            link.style = "visibility:hidden";            link.download = fileName + ".csv";            //this part will append the anchor tag and remove it after automatic click            document.body.appendChild(link);            link.click();            document.body.removeChild(link);        };

<span style="white-space:pre"></span>三个参数，<pre style="background-color:#2b2b2b;color:#a9b7c6;font-family:'Consolas';font-size:13.5pt;"><span style="background-color:#344134;">JSONData 代表要导出的JSON数据，</span><pre style="background-color:#2b2b2b;color:#a9b7c6;font-family:'Consolas';font-size:13.5pt;"><span style="background-color:#344134;">ReportTitle 代表的是导出时的文件名，</span>

ShowLabel 代表是否显示标题。
其中需要注意的是， var uri = 'data:text/csv;charset=utf-8,' + encodeURI(CSV); 其中的<span style="font-family: Consolas;">encodeURI表示的是对CSV即要输出的数据进行编码，</span>
<span style="font-family: Consolas;">之前找的代码用的espace函数，结果导出时的汉字部分全部显示为URL编码。换成</span><span style="font-family: Consolas;">encodeURI编码后可以导出汉字了。</span>
<span style="white-space:pre"></span>

0 0