nodejs excel导入导出

nodejs excel导入导出

用excel-export包导出

1.1安装excel-export

npm install --save excel-export

1.2自定义exportExcel函数，封装

var nodeExcel = require('excel-export');

/**
 * 导出excel
 * @param _headers example  [
 {caption:'用户状态',type:'string'},
 {caption:'部门',type:'string'},
 {caption:'姓名',type:'string'},
 {caption:'邮箱',type:'string'},
 {caption:'有效期',type:'string'},
 {caption:'身份',type:'string'}];
 * @param rows example 
 [['未激活','信息部','testname','123@qq.com','2019-11-09','管理员'],
 ['未激活','信息部','testname2','12345@qq.com','2019-11-09','普通成员']]
 */
exports.exportExcel = function(_headers,rows){
    var conf ={};
    conf.name = "mysheet";
    conf.cols = [];
    for(var i = 0; i < _headers.length; i++){
        var col = {};
        col.caption = _headers[i].caption;
        col.type = _headers[i].type;
        conf.cols.push(col);
    }
    conf.rows = rows;
    var result = nodeExcel.execute(conf);
    return result;
}

1.3业务使用

....
router.get('/exportMember',function(req, res) {
.....
//自己构造_headers和rows,导出excel
var result = exportExcel(_headers,rows);
        res.setHeader('Content-Type', 'application/vnd.openxmlformats');
        res.setHeader("Content-Disposition", "attachment; filename=" + "test.xlsx");
        res.end(result, 'binary');
        return;
}

excel导入

1需要安装的包

npm install --save busboy  //用来解析前端传的流文件
npm install --save xlsx   //解析excel文件

2前端代码示例

//enctype 必须设置成这种，可以将文件已流的形式传送到后台
    <form action="http://localhost:3001/system/importAccount" method='post' enctype="multipart/form-data">  
        <input type='file' name='file'> 
        <input type='submit' value='submit'>    
    </form>

3后端代码示例

let XLSX = require('xlsx');
let Busboy = require('busboy');
......
......
//导入excel解析出来
router.get('/importMember',function(req, res) {
......
......
    let busboy = new Busboy({
            headers: req.headers,
            limits: {
                files: 1,
                fileSize: 50000000
            }
        });

        busboy.on('file', function(fieldname, file, filename, encoding, mimetype) {
            file.on('limit', function() {
                res.json(Result.FAIL('To large'));
                return
            });
            file.on('data', function(data) {
                console.log('File [' + fieldname + '] got ' + data.length + ' bytes');
                var workbook = XLSX.read(data);
                var sheetNames = workbook.SheetNames; // 返回 ['sheet1', 'sheet2',……]
                var worksheet = workbook.Sheets[sheetNames[0]];// 获取excel的第一个表格
                var ref = worksheet['!ref']; //获取excel的有效范围,比如A1:F20
                var reg = /[a-zA-Z]/g;
                ref = ref.replace(reg,"");
                var line = parseInt(ref.split(':')[1]); // 获取excel的有效行数
                console.log("line====>",line);
                // header ['姓名','邮箱','身份','部门','手机号'] 

                //循环读出每一行，然后处理 
                for(var i = 2; i <= line; i++){
                    if(!worksheet['A'+i] && !worksheet['B'+i] && !worksheet['C'+i] && !worksheet['D'+i] && !worksheet['E'+i] && i != 2){   //如果大于2的某行为空,则下面的行不算了
                         break;
                     }
                     var name = worksheet['A'+i].v || '';
                     var email = worksheet['B'+i].v || '';
                     var role = worksheet['C'+i].v || '';
                     var department = worksheet['D'+i].v || '';
                     console.log("department===>",department);
                     var phone = worksheet['E'+i].v || '';
                     .......
                     .......
                 }
            }
        })
     return req.pipe(busboy);
})