js前端excel导出带图片(亲测可用)
1，js-table2excel npm包有问题，导出后一片空白
2，改写一下js-table2excel
/* eslint-disable */ let idTmr; const getExplorer = () => { let explorer = window.navigator.userAgent; //ie if (explorer.indexOf("MSIE") >= 0) { return 'ie'; } //firefox else if (explorer.indexOf("Firefox") >= 0) { return 'Firefox'; } //Chrome else if (explorer.indexOf("Chrome") >= 0) { return 'Chrome'; } //Opera else if (explorer.indexOf("Opera") >= 0) { return 'Opera'; } //Safari else if (explorer.indexOf("Safari") >= 0) { return 'Safari'; } } // 判断浏览器是否为IE const exportToExcel = (data, name) => { // 判断是否为IE if (getExplorer() == 'ie') { tableToIE(data, name) } else { tableToNotIE(data, name) } } const Cleanup = () => { window.clearInterval(idTmr); } // ie浏览器下执行 const tableToIE = (data, name) => { let curTbl = data; let oXL = new ActiveXObject("Excel.Application"); //创建AX对象excel let oWB = oXL.Workbooks.Add(); //获取workbook对象 let xlsheet = oWB.Worksheets(1); //激活当前sheet let sel = document.body.createTextRange(); sel.moveToElementText(curTbl); //把表格中的内容移到TextRange中 sel.select; //全选TextRange中内容 sel.execCommand("Copy"); //复制TextRange中内容 xlsheet.Paste(); //粘贴到活动的EXCEL中 oXL.Visible = true; //设置excel可见属性 try { let fname = oXL.Application.GetSaveAsFilename("Excel.xls", "Excel Spreadsheets (*.xls), *.xls"); } catch (e) { print("Nested catch caught " + e); } finally { oWB.SaveAs(fname); oWB.Close(savechanges = false); //xls.visible = false; oXL.Quit(); oXL = null; // 结束excel进程，退出完成 window.setInterval("Cleanup();", 1); idTmr = window.setInterval("Cleanup();", 1); } } // 非ie浏览器下执行 const tableToNotIE = (function () { // 编码要用utf-8不然默认gbk会出现中文乱码 const uri = 'data:application/vnd.ms-excel;base64,', template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><meta charset="UTF-8"><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>'; const base64 = function (s) { return window.btoa(unescape(encodeURIComponent(s))); } const format = (s, c) => { return s.replace(/{(\w+)}/g, (m, p) => { return c[p]; }) } return (table, name) => { const ctx = { worksheet: name, table } const url = uri + base64(format(template, ctx)); if (navigator.userAgent.indexOf("Firefox") > -1) { window.location.href = url } else { const aLink = document.createElement('a'); aLink.href = url; aLink.download = name || ''; let event; if (window.MouseEvent) { event = new MouseEvent('click'); } else { event = document.createEvent('MouseEvents'); event.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0, null); } aLink.dispatchEvent(event); } } })() // 导出函数 const table2excel = async (column, data, excelName) => { const typeMap = { image: getImageHtml, text: getTextHtml } let thead = column.reduce((result, item) => { result += `<th>${item.title}</th>` return result }, '') thead = `<thead><tr>${thead}</tr></thead>` var tbody = `` for (var item of data) { var temp = `` for (var item1 of column) { temp += await typeMap[item1.type || 'text'](item[item1.key], item1) } tbody += `<tr>${temp}</tr>` } tbody = `<tbody>${tbody}</tbody>` const table = thead + tbody // console.log(table) // 导出表格 exportToExcel(table, excelName) function getTextHtml (val) { return new Promise((resolve, reject) => { resolve(`<td style="text-align: center;vnd.ms-excel.numberformat:@">${val}</td>`) }) // return `<td style="text-align: center;vnd.ms-excel.numberformat:@">${val}</td>` } function imgUrlToBase64 (url) { return new Promise((resolve, reject) => { if (!url) { resolve('') } if (/\.(png|jpe?g|gif|svg)(\?.*)?$/.test(url)) { // 图片地址 const image = new Image() // 设置跨域问题 image.setAttribute('crossOrigin', 'anonymous') // 图片地址 image.src = url image.onload = () => { const canvas = document.createElement('canvas') const ctx = canvas.getContext('2d') canvas.width = image.width canvas.height = image.height ctx.drawImage(image, 0, 0, image.width, image.height) // 获取图片后缀 const ext = url.substring(url.lastIndexOf('.') + 1).toLowerCase() // 转base64 const dataUrl = canvas.toDataURL(`image/${ext}`) resolve(dataUrl || '') } } else { // 非图片地址 resolve('非(png/jpe?g/gif/svg等)图片地址'); } }) } function getImageHtml (val, options) { return new Promise((resolve, reject) => { resolve(`<td style="width: ${options.width}px; height: ${options.height}px; text-align: center; vertical-align: middle"><img src="${res}" width=${options.width} height=${options.height}></td>`) // 注意: base64 word打开有问题，wps没问题 // imgUrlToBase64(val).then(res => { // options = Object.assign({ width: 40, height: 60 }, options) // resolve(`<td style="width: ${options.width}px; height: ${options.height}px; text-align: center; vertical-align: middle"><img src="${res}" width=${options.width} height=${options.height}></td>`) // }).catch(e => { // resolve(e) // }) }) } } export default table2excel
3, 使用
handleExport() {
if (this.tableData.length == 0) return;
const loading = this.$loading({
target: ".facility-query",
lock: true,
text: '导出数据准备中...',
spinner: 'el-icon-loading',
background: 'rgba(0, 0, 0, 0.7)'
});
const data = this.tableData.map((v: any) => {
return {
...v,
picaddress: "/mediadl/media/getdata//LP/01/0101/52958960532.jpg"
}
}
);
const column = [
{
title: '名称',
key: 'note_',
type: 'text'
},
{
title: 'photo',
key: 'picaddress',
type: 'image',
width: 100,
height: 100
}
];
const tableData = JSON.parse(JSON.stringify(data));
table2excel(column, tableData, "导出数据_" + moment().format("YYYY-MM-DD_HHmmss")).then(() => {
loading.close();
}).catch(() => {
loading.close();
});
}
