js 对已知数组数据的导出EXCEL

1. 方法一

<a id="frontExportLogLink" href="javascript:void(0)" ng-click="exportLog()" class="btn btn-danger">导出<span class="glyphicon glyphicon-question-sign mgl10" tooltip="{{'不支持ie' | translate}}"></span></a>
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';
    }
}
vm.cacheData = "data":[
    {
        "lineNo":1,
        "msg":"test1"
    },
    {
        "lineNo":2,
        "msg":"test2"
    },
]
vm.exportLog = function() {
    if(getExplorer()=='ie')
    {
        //TODO
        var oXL = new ActiveXObject("Excel.Application"),
            oWB = oXL.Workbooks.Add(),
            oSheet = oWB.ActiveSheet,
            i = 0,
            j;

        for(; i < vm.list.data.length; i++){
            var row = vm.list.data[i];
            j = 0;
            angular.forEach(vm.cacheData, function(item, key) {
                oSheet.Cells(i + 1, j + 1).value = item[key];
                j++;
            });
        }
    }
    else
    {
        var fileName = getExcelFileName();
        try {
            //方法1,2,3,
            tableToExcel(fileName, vm.cacheData);
        }
        catch(e) {
            console.log(e);
            notificationService.error('导出失败', false);
        }
    }
}

function getExcelFileName() {
    var d = new Date();
    var curYear = d.getFullYear();
    var curMonth = "" + (d.getMonth() + 1);
    var curDate = "" + d.getDate();
    var curHour = "" + d.getHours();
    var curMinute = "" + d.getMinutes();
    var curSecond = "" + d.getSeconds();
    if (curMonth.length == 1) {
        curMonth = "0" + curMonth;
    }

    if (curDate.length == 1) {
        curDate = "0" + curDate;
    }

    if (curHour.length == 1) {
        curHour = "0" + curHour;
    }

    if (curMinute.length == 1) {
        curMinute = "0" + curMinute;
    }

    if (curSecond.length == 1) {
        curSecond = "0" + curSecond;
    }

    var fileName = "导入错误信息" + "_" + curYear + curMonth + curDate + "_"
            + curHour + curMinute + curSecond + ".xlsx";
    return fileName;
}

var tableToExcel = function(name, data) {
    var uri = 'data:application/vnd.ms-excel;base64,',
            template = '<html><head><meta charset="UTF-8"></head><body><table style="border: 1px solid #dee5e7">{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]; }) }
    var html = '<thead><tr><th style="width: 200px; border: 1px solid #dee5e7"><span>行号</span></th><th style="width: 500px;border: 1px solid #dee5e7"><span>错误信息</span></th></tr></thead><tbody>';
    angular.forEach(data, function(item, key) {
          html += '<tr>' +
          '    <td style="border: 1px solid #dee5e7">' +
          '      <span>' + item.lineNo + '</span>' +
          '    </td>' +
          '    <td style="border: 1px solid #dee5e7">' +
          '          <span>' + item.msg + '</span>' +
          '  </td>' +
          '  </tr>';
    });
    html += '</tbody>';
    var ctx = {worksheet: name || 'Worksheet', table: html};
    document.getElementById("frontExportLogLink").href = uri + base64(format(template, ctx));
    document.getElementById("frontExportLogLink").download = name;
    //document.getElementById("frontExportLogLink").click();
}

2. 方法二

<script src="${staticPath}/lib/alasql.min.js"></script>
<script src="${staticPath}/lib/xlsx.core.min.js"></script>
<a id="frontExportLogLink" href="javascript:void(0)" ng-click="exportLog()" class="btn btn-danger">导出</span></a>
vm.cacheData = "data":[
    {
        "lineNo":1,
        "msg":"test1"
    },
    {
        "lineNo":2,
        "msg":"test2"
    },
]
vm.exportLog = function() {
    var fileName = getExcelFileName();
    try {
        //方法1,2,3,
        tableToExcel(fileName, vm.cacheData);
    }
    catch(e) {
        console.log(e);
        notificationService.error('导出失败', false);
    }
}

function getExcelFileName() {
    var d = new Date();
    var curYear = d.getFullYear();
    var curMonth = "" + (d.getMonth() + 1);
    var curDate = "" + d.getDate();
    var curHour = "" + d.getHours();
    var curMinute = "" + d.getMinutes();
    var curSecond = "" + d.getSeconds();
    if (curMonth.length == 1) {
        curMonth = "0" + curMonth;
    }

    if (curDate.length == 1) {
        curDate = "0" + curDate;
    }

    if (curHour.length == 1) {
        curHour = "0" + curHour;
    }

    if (curMinute.length == 1) {
        curMinute = "0" + curMinute;
    }

    if (curSecond.length == 1) {
        curSecond = "0" + curSecond;
    }

    var fileName = "导入错误信息" + "_" + curYear + curMonth + curDate + "_"
            + curHour + curMinute + curSecond + ".xlsx";
    return fileName;
}

var tableToExcel = function(name, data) {
    var data = angular.copy(data);
    data.unshift({lineNo: '行号', msg: '错误信息'});
    alasql('SELECT lineNo,msg INTO XLSX("' + name + '",{headers:false}) FROM ?',[data]);
}

 

posted @ 2017-08-24 17:47  李晓菲  阅读(2113)  评论(0)  编辑  收藏  举报