node使用node-xlsx實現excel的下載與匯入，保證你看的明明白白

需求簡介

很多時候，我們都會有這樣一個業務。
將列表中的資料匯出為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);
  });
}