nodejs 匯出csv

洪小燈發表於2015-11-26
NodeJS

匯出csv是比較常見的一種共能，在很多的時候都要使用，比如說使用者列表、商品列表等

excel檔案比較大，使用csv檔案就比較合適了。

後臺程式碼：

var beginTime = req.query.beginTime;
    beginTime = new Date(beginTime).getTime() / 1000;
    var endTime = req.query.endTime;
    endTime = new Date(endTime).getTime() / 1000;
    var nodeId = req.query.nodeId;
    var alarmName = req.query.alarmName;
    var alarmDesc = req.query.alarmDesc;
    if (!alarmName) {
        alarmName = '';
    }
    if (!alarmDesc) {
        alarmDesc = '';
    }
    var apLevel = req.query.apLevel;
    var sql = '';
    if (nodeId) {
        sql = 'select * from aalarm where ND = ' + nodeId;
    }
    else {
        sql = 'select * from aalarm';
    }
    var pointResult = openPlant.getHisResult(null, null, parseInt(beginTime).toString(), parseInt(endTime).toString(), sql);
    var pointIds = utils.getPointIdArray(pointResult);
    var nodeIds = utils.getNodeIdArray(pointResult);
    var nodeResult = openPlant.getSQLResult('select ID,PN,ND from node', null, nodeIds);
    var dataBaseId = utils.getNodeIdArray(nodeResult);
    var dataBaseResult = openPlant.getSQLResult('select ID,PN from database', null, dataBaseId);
    sql = 'select ID,PN,ND,AP,ED,RT,LC,C1,C2,C3,C4,C5,C6,C7,C8 from Point';
    var pointsInfo = openPlant.getSQLResult(sql, null, pointIds);
    var alarmArray = new Array();
    var alarmInfo;
    for (var i = 0; i < pointResult.length; i++) {
        var resObj = pointResult[i];
        for (var j = 0; j < pointsInfo.length; j++) {
            var poiObj = pointsInfo[j];
            alarmInfo = new Object();
            if (resObj.ID == poiObj.ID) {
                if ((poiObj.PN.indexOf(alarmName) > -1) && (poiObj.ED.indexOf(alarmDesc) > -1) && (apLevel.indexOf(poiObj.AP) > -1)) {
                    alarmInfo.ID = resObj.ID;
                    alarmInfo.TM = resObj.TM * 1000;
                    alarmInfo.AV = resObj.AV;
                    alarmInfo.AP = poiObj.AP;
                    alarmInfo.PN = utils.getPointName(dataBaseResult, nodeResult, poiObj);
                    alarmInfo.ED = poiObj.ED;
                    var alarmDescT = utils.getAlarmDesc(poiObj.RT, poiObj.LC, resObj.DS);
                    alarmInfo.alarmDesc = alarmDescT;
                    alarmInfo.COLOR = utils.getAlarmColor(alarmDescT, poiObj);
                    alarmArray.push(alarmInfo);
                }
                break;
            }
        }
    }
    res.send(JSON.stringify(alarmArray));


上面是我使用到的一段程式碼，使用到了實時資料庫，我在這裡將物件轉化為json傳送到前臺

前臺程式碼：

var beginTime = $('#hisBegin').datetimebox('getValue');
    var endTime = $('#hisEnd').datetimebox('getValue');
    var nodeId = $('#pointHisTable').combobox('getValue');
    var alarmRealName = $('#alarmHisName').textbox('getValue');
    var alarmRealDesc = $('#alarmHisDesc').textbox('getValue');
    var apLevel = '';
    if ($("#alarmHisRed").is(':checked')) {
        apLevel += '0';
    }
    if ($("#alarmHisYellow").is(':checked')) {
        apLevel += '1';
    }
    if ($("#alarmHisWhite").is(':checked')) {
        apLevel += '2';
    }
    if ($("#alarmHisGreen").is(':checked')) {
        apLevel += '3';
    }
    $.ajax({
        url: '/alarmHisExport',
        type: 'get',
        data: {
            beginTime: beginTime,
            endTime: endTime,
            nodeId: nodeId,
            alarmName: alarmRealName,
            alarmDesc: alarmRealDesc,
            apLevel: apLevel
        },
        dataType: 'json',
        success: function (data) {
            var value = data;
            var name = 'AlarmHistory' + new Date().getTime() + '.csv';
            var csvContent = "data:text/csv;charset=utf-8,\ufeff";
            if (window.navigator.msSaveOrOpenBlob) {
                csvContent = "\ufeff";
            }
            //表頭
            csvContent += '序號,';
            csvContent += 'ID,';
            csvContent += '級別,';
            csvContent += '報警名稱,';
            csvContent += '報警時間,';
            csvContent += '描述,';
            csvContent += '當前值,';
            csvContent += '報警描述\n';
            var index = 1;
            //構建內容
            for (var i = 0; i < value.length; i++) {
                var obj = value[i];
                csvContent += index + ',';
                csvContent += obj.ID + ',';
                csvContent += obj.AP + ',';
                csvContent += obj.PN + ',';
                csvContent += utils.formatTime(obj.TM) + ',';
                csvContent += obj.ED + ',';
                csvContent += obj.AV + ',';
                csvContent += obj.alarmDesc + '\n';
                index++;
            }
            //匯出
            utils.csvExport(name, csvContent);
        }
        ,
        error: function () {
            console.log('報警歷史匯出異常');
        }
    });

將獲取的資料，按著一定格式進行拼裝，列與列之間使用逗號(,)分割，換行使用 \n;

csvExport工具類程式碼：

csvExport: function (name, csvContent) {
        //如果是IE瀏覽器
        if (window.navigator.msSaveOrOpenBlob) {
            var blob = new Blob([decodeURIComponent(encodeURI(csvContent))], {
                type: "text/csv;charset=utf-8;"
            });
            navigator.msSaveBlob(blob, name);
        } else {
            var encodedUri = encodeURI(csvContent);
            var link = document.createElement("a");
            link.setAttribute("href", encodedUri);
            link.setAttribute("download", name);
            document.body.appendChild(link);
            link.click();
        }
    }


工具類程式碼沒有什麼東西，主要是判斷瀏覽器型別的，然後進行匯出。

實際上csv的匯出是從後臺拿到資料，在前端生成csv檔案的。


相關文章