node-js 服務端實現 excel 導出以及 vue 前端本地保存文件並導出
我們的項目是前端用 vue, 服務端用 node.js, 這個 excel 導出我們已經用了一年, 我們目前用到的無非是圖片導出, 文本導出, 調調 excel 單元格距離等.
這個 node 端的封裝是經過同事不斷的 review(感謝同事), 俺不斷的修改優化後的代碼, 當時也是我第一次接觸 node.js, 只想告訴你, 用它, 穩穩的！
node.js 服務端代碼
1. 拿到需要導出的數據之後如何使用 (假數據模擬, 下面是頁面)
constructor(prop) {
super(prop)
this.header = [
{ header: '遊戲', key: 'gameInfo', width: 30 },
{ header: '宣傳圖片', key: 'image', width: 15 },
{ header: '遊戲詳情頁', key: 'path', width: 15 },
{ header: '狀態', key: 'activeStatus', width: 30 },
{ header: '排序權重', key: 'sort', width: 30 },
{ header: '最近編輯時間', key: 'updateTime', width: 30 },
{ header: '最近編輯人', key: 'operatorName', width: 30 },
]
}
async exportGameEndGameManage() {
const { list } = await this.commonGameEndGameManage(true)
console.log(list, 'list')
const baseExcelInfo = {
data: list,
filename: 'gameManageList',
header: this.header,
sheetName: '遊戲管理列表',
imageKeys: [
{
name: 'image',
imgWidth: '100',
imgHeight: '100',
},
],
}
await this.service.common.exportFile.exportExcel(baseExcelInfo)
}
複製代碼list 就是拿到的數據, 打印如下
baseExcelInfo 用來定義基本的參數
data 表示的是 excel 的數據來源
filename 是文件名 (但是前端的 excel 導出會進行覆蓋)
header 表示的是表格的頭部
sheetName 表示的是 excel 的表名
imageKeys: 圖片的信息: 字段名稱，圖片的寬高, 但是隻要有圖片, name 必須設
很重要的一點就是,假設從表裏面的數據返回的status是1,那麼我肯定導出的不能1,應該是對應的一箇中文,所以在導出前,應該進行處理,這個處理應該是在服務端來做,而不是前端做一遍,然後爲了導出這個功能重新做一遍舉個例子
async commonGameEndGameManage(isExport) {
const activeStatus = {
1: '打開',
2: '關閉',
}
const { ctx, app } = this
const { limit, offset } = this.paginationDeal(ctx.request.query)
const isPagi = isExport ? {} : { limit, offset }
const { list, total } = await ctx.service.operateManage.gameEndPage.
getGameEndGameManage({ isPagi })
const data = list.map(node => {
const { status, ...params } = node.toJSON()
const activeStatus = activeStatus[status]
return { activeStatus, status, ...params }
})
return { list: data, total }
}
複製代碼2.exportExcel 的封裝
首先安裝對應的包 npm install exceljs --save
然後複製下面的代碼就好了
'use strict'
const Service = require('egg').Service
const Excel = require('exceljs')
class exportFileService extends Service {
constructor(prop) {
super(prop)
this.defaultViews = [
{
x: 0,
y: 0,
width: 10000,
height: 20000,
firstSheet: 0,
activeTab: 1,
visibility: 'visible',
},
]
this.fontName = 'Arial Unicode MS'
this.font = { name: this.fontName, family: 4, size: 13 }
this.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF8DB4E2' } }
this.border = { style: 'thin', color: { argb: 'cccccc' } }
}
async exportExcel({
data = [],
filename = 'file',
header,
sheetName = 'sheet1',
imageKeys = [],
creator = 'me',
lastModifiedBy = 'her',
}) {
const { ctx } = this
const workbook = new Excel.Workbook()
workbook.creator = creator
workbook.lastModifiedBy = lastModifiedBy
const now = new Date()
workbook.created = now
workbook.modified = now
workbook.lastPrinted = now
const worksheet = workbook.addWorksheet(sheetName)
workbook.views = this.defaultViews
worksheet.state = 'visible'
worksheet.columns = header
for (let i = 1; i <= header.length; i++) {
worksheet.getColumn(i).alignment = { vertical: 'middle', horizontal: 'center' }
worksheet.getColumn(i).font = { name: 'Arial Unicode MS' }
}
worksheet.addRows(data)
const imageList = this.getImageList(imageKeys, data, header)
await this.addPicToSheet(imageList, imageKeys, workbook, worksheet)
const headerOPtion = header.filter((item, index) => {
if (item.type && item.type === 'multi') {
header.splice(index, 1)
return item
}
return item.type && item.type === 'multi'
})
if (headerOPtion.length) {
headerOPtion[0].headerText.forEach((text, index) => {
const borderAttr = { top: this.border, left:
this.border, bottom: this.border, right: this.border, index }
const headerAttr = [
{
attr: 'values',
value: text,
},
{
attr: 'font',
value: this.font,
},
{
attr: 'fill',
value: this.fill,
},
{
attr: 'border',
value: borderAttr,
},
]
headerAttr.map(item => {
worksheet.getRow(index + 1)[item.attr] = item.value
return worksheet
})
})
headerOPtion[0].mergeOption.forEach(merge => {
worksheet.mergeCells(merge)
})
} else {
worksheet.getRow(1).font = this.font
worksheet.getRow(1).fill = this.fill
}
const bufferContent = await workbook.xlsx.writeBuffer()
ctx.set('Content-disposition', `attachment;filename=${filename}.xlsx`)
ctx.body = bufferContent
}
getImageList(imageKeys, data, header) {
return imageKeys.map(
key => data.map(
(item, index) => ({
key,
url: item[key.name],
col: this.app.utils.index.getIndexByKey(header, key.name) + 1,
row: index + 2,
width: key.imgWidth,
height: key.imgHeight,
})
)
)
}
async addPicToSheet(imageList, imageKeys, workbook, worksheet) {
if (imageKeys.length > 0) {
await Promise.all(imageList.map(async imgArr => {
return await Promise.all(imgArr.map(item => {
const { url, width, height, row, col } = item
if (url) {
return this.app.utils.index.getBase64(url, this.ctx).then(res => {
if (!url) return
const imgType = url.split('?')[0].substring(url.split('?')[0].
lastIndexOf('.') + 1).toLowerCase()
const id = workbook.addImage({
base64: res,
extension: imgType,
})
worksheet.addImage(id, {
tl: { col: col - 1, row: row - 1 },
ext: { width, height },
})
worksheet.getRow(row).height = height
worksheet.getRow(row).getCell(item.key.name).value = ''
})
}
return item
}))
}))
}
}
}
module.exports = exportFileService
複製代碼3. 調用下載接口後 node.js 返回的信息
前端看到的就是一個二進制文件流
推薦瞭解 content-type: application/octet-stream
前端代碼
接口
export function exportFile(url, params) {
return request({
responseType: 'blob',
headers: {
'Content-Type': 'application/json',
},
timeout: 1000 * 60,
url: url,
method: 'get',
params: {
query: qs.stringify(params),
},
})
}
複製代碼utils
export function loacalSaveFile({ file, fileName, option = { type: 'application/vnd.ms-excel' }}) {
const ieKit = judgeBrowser('ie')
const blobData = new Blob([file], option)
if (ieKit && navigator.msSaveBlob) {
navigator.msSaveBlob && navigator.msSaveBlob(blobData, fileName)
} else {
const save_link = document.createElement('a')
const url = URL.createObjectURL(file)
save_link.href = url
save_link.download = fileName
document.body.appendChild(save_link)
save_link.click()
setTimeout(() => {
document.body.removeChild(save_link)
window.URL.revokeObjectURL(url)
}, 0)
}
}
複製代碼調用
const file = await exportFile(this.exportItem.apiUrl, data)
loacalSaveFile({ file, fileName: `${this.exportItem.fileName}.xlsx` })
複製代碼效果
本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://juejin.cn/post/6953882449235410951