excel-export，关于node生成文件流导出excel

本文介绍vue+node项目中，node请求数据并生成文件流供前端导出execl

1. 首先在server目录下创建index.js供node.js使用
2. npm install excel-export 下载依赖
   

3. index.js页面，使用node index命令启动node服务，index文件每次修改需重新运行启动命令
   styles.xml可在点击此处下载

	const nodeExcel = require('excel-export');
	const app = express();
	
	/**
	 * 导出excel
	 * @param _headers example  
	 * [{caption(列标题):'用户状态',type:'string', width(宽度): '40', value(取返回值对应键值): ''}];
	 * @param rows example 
	 [['张三','线路一','节点一','主机厂','三一集团','2019-10-25','2019-10-25'],
	 ['张三','线路一','节点一','主机厂','三一集团','2019-10-25','2019-10-25']]
	 */
	const exportExcel = function (_headers, rows) {
	    var conf = {};
	    conf.stylesXmlFile = "styles.xml";
	    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;
	        col.width = _headers[i].width;
	        // col.captionStyleIndex = _headers[i].captionStyleIndex;
	        conf.cols.push(col);
	    }
	    conf.rows = rows;
	    var result = nodeExcel.execute(conf);
	    return result;
	}
	// 导出列表
	app.post('/downLoadExcel', function (req, res,) {
		//真实场景resultList替换成数据库数据，_headers的caption列名跟value字段名对应修改即可
	    const resultList = [{
	        name: '测试名称',
	        route: '测试路由',
	        room: '456',
	        type: '1',
	        party: '测试主题',
	        time: '2012',
	        billing: '123'
	    }]
	    var _headers = [
	        { caption: '客户', type: 'string', width: 40, value: 'name' },
	        { caption: '线路', type: 'string', width: 15, value: 'route' },
	        { caption: '节点', type: 'string', width: 40, value: 'room', },
	        { caption: '客户类型', type: 'string', width: 15, value: 'type' },
	        { caption: '主体', type: 'string', width: 40, value: 'party' },
	        { caption: '交付时间', type: 'string', width: 15, value: 'time' },
	        { caption: '计费时间', type: 'string', width: 15, value: 'billing' },
	    ]
	    var arr = resultList.map(val => {
	        return _headers.map(item => {
	            return val[item.value]
	        })
	
	    })
	    var conf = exportExcel(_headers, arr)
	    res.setHeader('Content-Type', 'application/vnd.openxmlformats');
	    res.setHeader("Content-Disposition", "attachment; filename=" + "Report.xlsx");
	    res.end(conf, 'binary'); //返回给前台
	
	});
	
	app.listen(5004, () => {
	    console.log('服务器开启成功！')
	})

4. vue页面调用

	<script>
	import axios from "axios";
	export default {
	  name: "nodeExcel",
	  methods: {
	    downLoadExcel() {
	      let url = `http://xxxx:5004/downloadExcel`;//xxxx代表本地ip
	      let xhr = new XMLHttpRequest();
	      xhr.open("POST", url, true);
	      xhr.setRequestHeader("Content-type", "application/json");
	      xhr.responseType = "blob";
	      xhr.onreadystatechange = function () {
	        if (xhr.readyState == 4) {
	          if (this.status === 200) {
	            console.log(this.response);
	            var blob = new Blob([this.response], {
	              type: "application/vnd.ms-excel",
	            });
	            var reader = new FileReader();
	            reader.readAsDataURL(blob);
	            reader.onload = function (e) {
	              var link = document.createElement("a");
	              link.download = "report.xlsx";
	              link.href = window.URL.createObjectURL(blob);
	              document.body.append(link);
	              link.click();
	              setTimeout(() => {
	                document.body.removeChild(link);
	              }, 100);
	            };
	          }
	        }
	      };
	      xhr.send(); //发送请求 将json写入send中   
	    },
	  },
	};
	</script>

结果