nodejs excel导入导出

来源：互联网发布：歌词找歌名软件编辑：程序博客网时间：2024/04/26 09:25

nodejs excel导入导出
- 用excel-export包导出
  - 1安装excel-export
  - 2自定义exportExcel函数封装
  - 3业务使用
- excel导入
  - 1需要安装的包
  - 2前端代码示例
  - 3后端代码示例

nodejs excel导入导出

用excel-export包导出

1.1安装excel-export

npm install --save excel-export

1.2自定义exportExcel函数，封装

var nodeExcel = require('excel-export');/** *  * @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和rowsvar 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');............router.get('/exportMember',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);})

0 0