Our project uses vue on the front end and Node.js on the server. We have been using this Excel export for a year. What we are using now is nothing more than image export, text export, adjust the distance of Excel cells and so on.
The encapsulation of the node side was reviewed by my colleagues (thanks to my colleagues), and I modified and optimized the code constantly. At that time, it was also my first time to contact with Node.js. I just want to tell you that using it is stable!
Node.js Server code
1. How to use the data to be exported after getting it (fake data simulation, below is the page)
constructor(prop) {
super(prop)
// Define excel header data
this.header = [
{ header: 'games'.key: 'gameInfo'.width: 30 },
{ header: 'Publicity pictures'.key: 'image'.width: 15 },
{ header: 'Game Details Page'.key: 'path'.width: 15 },
{ header: 'state'.key: 'activeStatus'.width: 30 },
{ header: 'Sort weight'.key: 'sort'.width: 30 },
{ header: 'Last Edited time'.key: 'updateTime'.width: 30 },
{ header: 'Last editor'.key: 'operatorName'.width: 30]}},/** * Export game management data */
async exportGameEndGameManage() {
const { list } = await this.commonGameEndGameManage(true)
console.log(list, 'list')
const baseExcelInfo = {
data: list,
filename: 'gameManageList'.header: this.header,
sheetName: 'Game Management List'.imageKeys: [{name: 'image'.imgWidth: '100'.imgHeight: '100',},],}await this.service.common.exportFile.exportExcel(baseExcelInfo)
}
Copy the codeBaseExcelInfo defines the basic parameters. Data refers to the source of the excel data. Filename is the filename (but the front end excel export overwrites it). Header refers to the header of the table SheetName indicates the excel table name. ImageKeys: Image information: field name, width and height of the image. However,name must be set as long as there is a picture
It is important, it is assumed that the data returned from the list of status is 1, so I can’t 1 must be exported, should be corresponding to a Chinese, so before export, should be handled, the treatment should be done on the server, rather than the front to do it again, and then to derive the function to do it again, for example
/** * Public game management data *@param { Boolean } IsExport Whether to export */
async commonGameEndGameManage(isExport) {
const activeStatus = { // this should be written in constructor
1: 'open'.2: 'off',}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 }
}
Copy the code2. ExportExcel encapsulation
Install the NPM install exceljs –save package and copy the code below
'use strict'
const Service = require('egg').Service
/ / introduce exceljs
const Excel = require('exceljs')
// Export file related services
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'}}}/** * Export excel *@param { Object } Config passes the Excel object *@param { Array } Config. data Excel data *@param { String } Config. filename specifies the excel filename *@param { Array } Config. header Specifies the excel header@param { String } The config. SheetName table name *@param { Array } Config. imageKeys requires the key * to convert the image@param { String } Config. creator The person who created the table *@param { String } Config. lastModifiedBy The person who last modified the table *@param { String } The width of the * config. ImageKeys. ImgWidth images@param { String } The config. ImageKeys. ImgHeight picture height * * /
async exportExcel({
data = [],
filename = 'file',
header,
sheetName = 'sheet1',
imageKeys = [],
creator = 'me',
lastModifiedBy = 'her',}) {
const { ctx } = this
const workbook = new Excel.Workbook()
// Set properties - the person who created and last modified it
workbook.creator = creator
workbook.lastModifiedBy = lastModifiedBy
// Get time once
const now = new Date()
workbook.created = now
workbook.modified = now
workbook.lastPrinted = now
const worksheet = workbook.addWorksheet(sheetName)
// Set the view when open - set the position
workbook.views = this.defaultViews
// Make the worksheet visible
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)
// Manipulate the image
const imageList = this.getImageList(imageKeys, data, header)
// Add the image to sheet
await this.addPicToSheet(imageList, imageKeys, workbook, worksheet)
// Multistage header
const headerOPtion = header.filter((item, index) = > {
if (item.type && item.type === 'multi') {
header.splice(index, 1)
return item
}
return item.type && item.type === 'multi'
})
// Multilevel header reset sets the header
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 {
// Set the header style
worksheet.getRow(1).font = this.font
worksheet.getRow(1).fill = this.fill
}
const bufferContent = await workbook.xlsx.writeBuffer()
/ / set
ctx.set('Content-disposition'.`attachment; filename=${filename}.xlsx`)
// Return the file buffer
ctx.body = bufferContent
}
// Set the image size
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,
})
)
)
}
// Add the image to sheet
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
// Because some pictures do not exist
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
// // Remove background links
worksheet.getRow(row).getCell(item.key.name).value = ' '})}return item
}))
}))
}
}
}
module.exports = exportFileService
Copy the code3. The message returned by Node.js after the download interface is invoked
What the front end sees is a stream of binary files
Content-type: application/octet-stream
The front-end code
interface
// Export the file
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),
},
})
}
Copy the codeutils
/** * Save the file locally and export *@param { Object } Obj Export file parameter object *@param { Blob } File File resources *@param { String } FileName specifies the fileName (note: the fileName contains the suffix */)
export function loacalSaveFile({ file, fileName, option = { type: 'application/vnd.ms-excel' }}) {
const ieKit = judgeBrowser('ie')
const blobData = new Blob([file], option) // Generate a Blob file
if (ieKit && navigator.msSaveBlob) {
navigator.msSaveBlob && navigator.msSaveBlob(blobData, fileName)
} else {
/ / other
const save_link = document.createElement('a')
const url = URL.createObjectURL(file) / / create a url
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) / / recycling url
}, 0)}}Copy the codecall
const file = await exportFile(this.exportItem.apiUrl, data)
loacalSaveFile({ file, fileName: `The ${this.exportItem.fileName}.xlsx` })
Copy the code