1、一般后端直接返回二进制文件流，前端直接处理文件流下载就行了
2、前端直接根据表格数据导出
下载速度较快，前端可以直接处理要下载的数据，唯一不足图片样式不能自适应，导出来不咋美观
function tableToExcel(colums = [], data = [], name = '表格') {
// 列标题
let head = colums.reduce((pev, cur) => {
pev += `${cur.title}`
}, '') + ''
let tbody = ''
for (let i = 0; i < data.length; i++) {
let g = data[i]
let tds = ''
for (let j = 0; j < colums.length; j++) {
let key = colums[j].key
let td = g[key]
// 图片链接
if (key === 'pictureUrl' || key === 'imageUrl' || key === 'imgUrl' || key === 'imageSrc' || key === 'imgSrc') {
let imgNode = td ? `` : ''
tds += imgNode
} else {
tds += `${td}`
}
}
tbody += `${tds}`
}
let str = head + tbody // 头部跟身体内容连接
// Worksheet名
let worksheet = name
let uri = 'data:application/vnd.ms-excel;base64,'
// 下载的表格模板数据
let template = `
${str}
`
// 下载模板
let url = uri + window.btoa(unescape(encodeURIComponent(template)))
downloadExcel(url)
}
function downloadExcel(path) {
const a = document.createElement('a')
a.setAttribute('download', '我的表格.xlsx')
a.setAttribute('href', path)
document.body.appendChild(a)
a.click()
document.body.removeChild(a)
}
3、借助第三方插件 exceljs
可以动态获取到每个图片的宽高，样式比较美观，但是因为前端遍历请求了一遍所有图片来获取宽高，速度较慢
// npm i exceljs
function exportExcel(colums = [], data = [], seetName = '表格') {
const tableData = colums.reduce((pev, cur, index) => {
if (cur.key === 'pictureUrl' || cur.key === 'imageUrl' || cur.key === 'imgUrl' || cur.key === 'imageSrc' || cur.key === 'imgSrc') {
pev.img.push({
key: cur.url,
index,
})
}
return pev
}, { img: [] })
const workbook = new ExcelJS.Workbook()
const worksheet = workbook.addWorksheet(seetName)
worksheet.columns = colums
worksheet.getRow(1).font = { size: 13, bold: true }
worksheet.getRow(1).border = {
top: { style: 'thin' },
left: { style: 'thin' },
bottom: { style: 'thin' },
right: { style: 'thin' },
}
worksheet.getRow(1).fill = {
bgColor: { rgb: '#a6edaf' },
}
let promiseList = []
data.map(async (m, i) => {
worksheet.addRows([m])
let worksheetRow = worksheet.getRow(i + 2)
worksheetRow.border = {
top: { style: 'thin' },
left: { style: 'thin' },
bottom: { style: 'thin' },
right: { style: 'thin' },
}
if (tableData.img.length) {
for (let k = 0; k < tableData.img.length; k++) {
const curKey = tableData.img[k].key
const curIndex = tableData.img[k].index
if (m[curKey]) {
let pro = loadImg(m[curKey])
promiseList.push(pro)
pro.then(img => {
let height = (180 / img.width) * img.height
const imageId = workbook.addImage({
base64: img.base64,
extension: 'png',
})
worksheet.addImage(imageId, {
tl: { col: curIndex + 0.1, row: i + 1 + 0.3 },
ext: { width: 180, height },
})
worksheetRow.height = (height * 72) / 96 + 10
worksheetRow.alignment = { vertical: 'middle' }
})
}
}
}
})
if (promiseList.length) {
Promise.all(promiseList).then(async () => {
const buffer = await workbook.xlsx.writeBuffer()
const blob = new Blob([buffer])
downloadExcel(window.URL.createObjectURL(blob))
})
} else {
const buffer = await workbook.xlsx.writeBuffer()
const blob = new Blob([buffer])
downloadExcel(window.URL.createObjectURL(blob))
}
}
function downloadExcel(path) {
const a = document.createElement('a')
a.setAttribute('download', '我的表格.xlsx')
a.setAttribute('href', path)
document.body.appendChild(a)
a.click()
document.body.removeChild(a)
}
function image2Base64(img) {
let canvas = document.createElement('canvas')
canvas.width = img.width
canvas.height = img.height
let ctx = canvas.getContext('2d')
ctx.drawImage(img, 0, 0, img.width, img.height)
let dataURL = canvas.toDataURL('image/png')
return dataURL
}
function loadImg(url) {
return new Promise((resolve, reject) => {
const img = new Image()
let base64 = ''
// 解决跨域
img.src = url + `${url.includes('?') ? '&' : '?'}` + new Date().valueOf()
img.setAttribute('crossOrigin', 'anonymous')
img.onload = () => {
base64 = image2Base64(img)
resolve({
base64,
height: img.height,
width: img.width,
})
}
img.onerror = (e) => {
reject(e)
}
})
}
直接导出和用exceljs插件(右)导出对比
