nodejs excel导入导出
用excel-export包导出
1.1安装excel-export
npm install --save excel-export
1.2自定义exportExcel函数,封装
var nodeExcel = require('excel-export');
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) {
.....
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
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');
......
......
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;
var worksheet = workbook.Sheets[sheetNames[0]];
var ref = worksheet['!ref'];
var reg = /[a-zA-Z]/g;
ref = ref.replace(reg,"");
var line = parseInt(ref.split(':')[1]);
console.log("line====>",line);
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){
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);
})