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

最新推荐文章于 2024-05-22 21:31:48 发布

苏果

最新推荐文章于 2024-05-22 21:31:48 发布

阅读量2.9k

点赞数 2

分类专栏： WEB前端文章标签：源码 json csv excel

WEB前端专栏收录该内容

1 篇文章 0 订阅

订阅专栏

最近实验室接了一个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>