(资料图)

需求简介

很多时候，我们都会有这样一个业务。将列表中的数据导出为excel。这样做的目的是为了方便查看，同时可以保存在本地归档。还可以将导出的Excel后的数据进行加工。

node-xlsx 的简单介绍

下载node-xlsx模块：cnpm install node-xlsx --savenode-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也可以解析excelxlsx.parse(filepath,{otherOptions}){cellDates: true} 可以将将时间格式转化为 ISO 8601ISO 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;

实现导出下载功能-前端代码

下载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",  })}下载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;

前端代码

文件上传
  点击上传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);  });}

关键词：