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