需求简介
很多时候，我们都会有这样一个业务。 将列表中的数据导出为excel。 这样做的目的是为了方便查看，同时可以保存在本地归档。 还可以将导出的Excel后的数据进行加工。 node-xlsx 的简单介绍
下载node-xlsx模块：cnpm install node-xlsx --save node-xlsx 模块提供了excel 文件解析器和构建器。 它通过 xlsx.build 可以构建 xlsx 文件(就是将数据转为excel) 简单使用如下: let buffer = xlsx.build([{name: 'excel工作薄的名称', data: '需要的数据-通常是数组'}]); data 中的数据格式通常是这样的 data:[ { name: "第1个工作薄的名称如：sheet", data: [ ["第1行第1列的数据", "第1行第2列的数据", "第1行第3列的数"], ["第2行第1列的数据", "第2行第2列的数据", "第2行第3列的数据"] ], }, { name: "第2个工作薄的名称如：sheet", data: [ ["第1行第1列的数据", "第1行第2列的数据", "第1行第3列的数"], ["第2行第1列的数据", "第2行第2列的数据", "第2行第3列的数据"] ], } ] 同时node-xlsx也可以解析excel xlsx.parse(filepath,{otherOptions}) {cellDates: true} 可以将将时间格式转化为 ISO 8601 ISO 8601：是全世界日期和时间相关的数据交换的国际标准。 这个标准的目标是在全世界范围的通信中提供格式良好的、无歧义的时间和日期表示。 node-xlsx 构建 xlsx 文件[将数据转化为excel]
//引入生成excel的依赖包 const xlsx = require("node-xlsx"); let fs = require("fs"); const list = [ { name: "sheet", // 工作薄的名称 data: [ ["第1行第1列", "第1行第2列", "第1行第3列"], ["第2行第1列", "第2行第2列", "第2行第3列"] ], }, // 如果多个工作薄， 就是多个对象。格式如上 ]; // 使用提供的构建 xlsx 文件的方法 const buffer = xlsx.build(list); fs.writeFile("导出excel的名称.xlsx", buffer, function (err) { if (err) { console.log(err, "导出excel失败"); } else { console.log("导出excel成功!"); } }); 
需要注意的2点
需要注意的1点:如果当前目录下有一个excel的名称与你现在导出的名称相同。 就会出现覆盖，后面的覆盖前面的数据。 需要注意的2点:还有一个注意的点是：如果你把导出文件名相同的excel打开。 就会出现导出失败： 提示为：s[Error: EBUSY: resource busy or locked]
如何设置列宽呢？
刚刚我们虽然导出成功。 但是我们发现列宽太窄。我们需要设置一下列宽。 我们需要通过一个配置参数来处理 我们可以通过配置项 sheetOptions 来处理 通过 xlsx.build 的第2个参数来处理 const sheetOptions = {'!cols': [{wch: 20}, {wch: 30}]}; //设置宽度 var buffer = xlsx.build([{name: 'mySheetName', data: data}], {sheetOptions}); //引入生成excel的依赖包 const xlsx = require("node-xlsx"); let fs = require("fs"); const data = [ ["姓名", "地址", "性别", '联系方式'], ["张三", "四川", "男", '18485645634'], ]; // wch 设置列宽 const sheetOptions = {'!cols': [{wch: 20}, {wch: 30}, {wch: 40}, {wch: 50}]}; // mySheetName 表名 data导出的数据 sheetOptions 是配置项 var buffer = xlsx.build([{name: 'mySheetName', data: data}], {sheetOptions}); fs.writeFile("导出excel的名称.xlsx", buffer, function (err) { if (err) { console.log(err, "导出excel失败"); } else { console.log("导出excel成功!"); } }); 03png
实现导出下载功能-node后端代码
//引入生成excel的依赖包 const xlsx = require("node-xlsx"); let fs = require("fs"); let express = require('express'); let router = express.Router(); // 引入连接数据库的模块 const connection=require("./connectmysql.js") // 查询 router.get('/export', function (req, res) { // 写一个简单的查询语句 const sqlStr = 'select * from account'; //执行sql语句 connection.query(sqlStr, (err, data) => { if (err) { res.send({ code: 1, msg:'查询失败' }); throw err } else { exportFun((obj) => { console.log('obj',obj) // 设置响应头 res.setHeader( 'Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' ); res.setHeader('Content-Disposition', 'attachment; filename=test.xlsx'); // 将 Excel 文件的二进制流数据返回给客户端 res.end(obj.data, 'binary'); }) } }) }) function exportFun(callback) { const data = [ ["用户名", "密码", "出生年月"], ["张三", "qwer090910989", "1999-02-12"] ]; // wch 设置列宽 const sheetOptions = {'!cols': [{wch: 30}, {wch: 30}, {wch: 30}]}; // mySheetName 表名 data导出的数据 sheetOptions 是配置项 var buffer = xlsx.build([{ name: 'mySheetName', data: data }], { sheetOptions }); callback({ success: true, data:buffer, info:'导出excel成功' }) } module.exports = router; 实现导出下载功能-前端代码
<el-button @click="downLoadHandler">下载</el-button> methods: { downLoadHandler(){ axios({ method: 'get', url: 'http://127.0.0.1:666/download/export', responseType: 'blob' // 资源的类型 }).then(res => { console.log('返回来的数据', res) this.downLoadFile(res.data, 'excel名称.xlsx', () => {}) }).catch(err => { console.log(err) }) } }
刚刚我们知道了返回来的数据格式是Blob类型的。 现在只需要我们进行一次转换。然后创建a标签。 模拟点击事件进行下载 downLoadFile(fileData, fileName, callBack) { // 创建Blob实例 fileData 接受的是一个Blob let blob = new Blob([fileData], { type: 'applicationnd.ms-excel', }) if (!!window.ActiveXObject || 'ActiveXObject' in window) { window.navigator.msSaveOrOpenBlob(blob, fileName) } else { // 创建a标签 const link = document.createElement('a') // 隐藏a标签 link.style.display = 'none' // 在每次调用 createObjectURL() 方法时，都会创建一个新的 URL 指定源 object的内容 // 或者说(link.href 得到的是一个地址，你可以在浏览器打开。指向的是文件资源) link.href = URL.createObjectURL(blob) console.log('link.href指向的是文件资源', link.href) //设置下载为excel的名称 link.setAttribute('download', fileName) document.body.appendChild(link) // 模拟点击事件 link.click() // 移除a标签 document.body.removeChild(link) // 回调函数，表示下载成功 callBack(true) } } 
关于axios.get() 置请求头responseType:'blob'不生效
之前在遇见一个问题。 就是关于axios.get() 置请求头responseType:'blob'是不生效。 这里我想说明一下，其实也是会生效的。只是可能设置的方式不正确。 如果你是这样写的，确实不会生效，并且下载还会出现一些乱七八糟的情况。 // 错误的写法 这种设置类型会失败的。 // axios.get() 就没有第三个参数。如果有是我们自定义的。它本身是没有的 axios.get('url', {}, { responseType: 'blob' }).then((response) => { console.log('返回来的数据', response) }).catch(function (error) { console.log(error); });
这个时候，我们发现返回来的不再是 blob 类型。 那为什么会出现这样的原因呢？ 因为我们上面设置类型压根就没有设置成功。 不应该设置在第3个参数中(它本身是没有的第3个参数。第3个是我们自定义的)。应该放置在第2个参数中 正确的设置方法 axios.get(url[, config]) // 将设置数据类型放置在 第2个参数中 axios.get('url', { responseType: 'blob' }).then((response) => { console.log('返回来的数据', response) this.downLoadFile(response.data, 'excel.xlsx', () => {}) }).catch(function (error) { console.log(error); }); 
mockjs会导致文件下载失败及原因
如果你的项目中有使用mockjs 那么下载肯定会失败的。因为mockjs初始化了responseType 从而导致下载失败。 验证 mockjs 会导致下载失败
当我们的项目使用了mockjs之后。 返回来的数据不再是 Blob。 我们现在在项目中使用了mockjs 看看文件是否可以正常的下载成功 created() { Mock.mock("/api/login", { code: 200, msg: "登录成功", user: { name: "李四", age: 18, sex: '男' }, token: 'token2023', }) } <el-button @click="downLoadHandler">下载</el-button> downLoadHandler() { axios.get('http://127.0.0.1:666/download/export', { responseType: 'blob' }).then((response) => { console.log('返回来的数据', response) this.downLoadFile(response.data, 'excel.xlsx', () => {}) }).catch(function (error) { console.log(error); }); }
引入 mockjs 之后，文件果然下载失败了。 那怎么解决这个问题呢？ 注释掉 mockjs 就可以了 node-xlsx 结合 multer 实现excel导入
multer:是一个node.js中间件，主要用于上传文件。 安装 npm install --save multer multer的基本用法
let multer = require('multer'); let Storage = multer.diskStorage({ // 存储文件的位置 destination: (req, file, callback) => { //指定当前这个文件存放的目录,如果没有这个目录将会报错 callback(null, 'public/upload'); }, // 文件中的文件名称 filename: (req, file, callback) => { // 文件命名 callback(null, '可以重新命名文件'); } }); 每个文件都包含以下信息： fieldname 表单中指定的字段名称 originalname 用户计算机上的文件的名称 filename 文件中的文件名称 path 上传文件的完整路径 path 上传文件的完整路径 其他配置项 limits：一个对象，指定一些数据大小的限制。 limits：{ files:'文件最大数', fileSize: '文件最大长度 (字节单位byte)' 1MB=1024KB= 1048576 byte node-xlsx怎么解析excel
//引入模块 let xlsx = require('node-xlsx'); // 解析 xlsx 文件,处理时间否者时间会发生变化 let sheets = xlsx.parse('./test.xlsx'); // 获取工作薄中的数据 // 数据格式为：[ { name: 'mySheetName', data: [ [Array], [Array] ] } ] console.log('数据格式为：',sheets); let arr = []; // 全部表中的数据 sheets.forEach((sheet) => { for (let i = 1; i < sheet['data'].length; i++) { //excel第一行是是表头，所以从1开始循环 let row = sheet['data'][i]; // 获取行数据 if (row && row.length > 0) { // moment处理 ISO 8601格式的时间, arr.push({ name: row[0], // row[0]对应表格里A列 password: row[1], // row[1]对应表格里B列 brith:row[2], // row[2]对应表格里C列 }); } } console.log('读取的数据', arr) });
如何处理时间读取的时候发生的变化
在 xlsx.parse方法的第二个参数中设置 cellDates: true 可以将时间转为 ISO 8601 如下： let sheets = xlsx.parse(fileUrl,{cellDates: true});
使用 moment 来处理 ISO 8601格式的时间 YYYY-MM-DD HH:mm
// moment处理 ISO 8601格式的时间, let dateTime = moment(row[2]); dateTime.utc().format('YYYY-MM-DD HH:mm') , 我们发现时间虽然是 YYYY-MM-DD HH:mm 但是与我们表格中的数据相差了8个小时。 怎么处理？别急。我们可以让 UTC 偏移为 8个小时 13png
使用偏移与时间时间保持一致
let dateTime = moment(row[2]); brith:dateTime.utc('+8:00').format('YYYY-MM-DD HH:mm')
node-xlsx 实现对excel的解析写入数据库
let express = require('express'); let multer = require('multer'); let xlsx = require('node-xlsx'); let moment = require('moment'); let fs = require('fs'); let router = express.Router(); let Storage = multer.diskStorage({ destination: (req, file, callback) => { // 指定当前这个文件存放的目录 // 如果没有这个目录将会报错 callback(null, 'public/upload'); }, filename: (req, file, callback) => { console.log('fieldname',file) // 文件命名：当前时间戳 + "_" + 源文件名称 callback(null, new Date().getTime() + '_' + file.originalname); } }); // 我们这里支持多文件上传，上传名为 file。 let upload = multer({ storage: Storage, limits: { fileSize: 1024 * 1024*10, // 限制文件大小 files: 5 // 限制上传数量 } }).array('file', 99999); router.post('/upload', function (req, res) { upload(req, res, (err) => { if (err) { res.send({ code:'1', msg:'导入失败', err:err}) } else { // 获取这个文件的路径 const fileUrl = req.files[0].path; // 解析 xlsx 文件,处理时间否者时间会发生变化 var sheets = xlsx.parse(fileUrl,{cellDates: true}); // 获取工作薄中的数据 // 数据格式为：[ { name: 'mySheetName', data: [ [Array], [Array] ] } ] console.log('数据格式为：',sheets); var arr = []; // 全部表中的数据 sheets.forEach((sheet) => { for (var i = 1; i < sheet['data'].length; i++) { //excel第一行是是表头，所以从1开始循环 var row = sheet['data'][i]; // 获取行数据 if (row && row.length > 0) { // moment处理 ISO 8601格式的时间, var dateTime = moment(row[2]); arr.push({ name: row[0], // row[0]对应表格里A列 password: row[1],// row[1]对应表格里B列 // 使用偏移与时间时间保持一致 brith: dateTime.utc('+8:00').format('YYYY-MM-DD HH:mm'), }); } } }); // 读取成功1分钟后将这个文件删除掉 setTimeout(() => { fs.unlinkSync(fileUrl); }, 1000 * 60); console.log('解析后的数据',arr ) res.send({ code:'0', msg:'导入成功',data: arr,total: arr.length}) } }); }); module.exports = router;
前端代码
<h2>文件上传</h2> <el-upload class="upload-demo" action="https" :http-request="uploadExcelFile"> <el-button size="small" type="primary">点击上传</el-button> </el-upload> uploadExcelFile(file) { let formdata = new FormData(); console.log(file); formdata.append("file", file.file); axios.post('http://127.0.0.1:666/upload/upload', formdata, { 'Content-type': 'multipart/form-data' } ).then(function (response) { console.log(response); }).catch(function (error) { console.log(error); }); }
![洛谷 P11345 [KTSC 2023 R2] 基地简化 题解](http://www.itfaba.com/wp-content/themes/kemi/timthumb.php?src=http://www.itfaba.com/wp-content/themes/kemi/img/random/1.jpg&w=218&h=124&zc=1)
